August 4, 2014 at 2:09 am
Hi DBA's..
We are facing performance issue for an process in peoplesoft..
the select query given below taking almost 13 hours to complete..
any help will be appreciated..
SELECT A.LEDGER , A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT,
A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1,
A.AFFILIATE_INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, A.PROJECT_ID, A.BOOK_CODE,
A.GL_ADJUST_TYPE, A.CURRENCY_CD, A.DATE_CODE, A.FOREIGN_CURRENCY , SUM(A.FOREIGN_AMOUNT) ,
A.OPEN_ITEM_KEY , (CONVERT(CHAR(10),MAX(A.JOURNAL_DATE),121)) , SUM(A.MONETARY_AMOUNT)
FROM PS_OPEN_ITEM_GL A ,PS_OPEN_ITEM_TAO4 T
WHERE
A.BUSINESS_UNIT= T.BUSINESS_UNIT
AND A.LEDGER =T.LEDGER
AND A.OPEN_ITEM_KEY =T.OPEN_ITEM_KEY
AND A.OPEN_ITEM_STATUS='O'
AND A.ACCOUNT=T.ACCOUNT
AND A.ALTACCT=T.ALTACCT
AND A.DEPTID=T.DEPTID
AND A.OPERATING_UNIT=T.OPERATING_UNIT
AND A.PRODUCT=T.PRODUCT
AND A.FUND_CODE=T.FUND_CODE
AND A.CLASS_FLD=T.CLASS_FLD
AND A.PROGRAM_CODE=T.PROGRAM_CODE
AND A.BUDGET_REF=T.BUDGET_REF
AND A.AFFILIATE=T.AFFILIATE
AND A.AFFILIATE_INTRA1=T.AFFILIATE_INTRA1
AND A.AFFILIATE_INTRA2=T.AFFILIATE_INTRA2
AND A.CHARTFIELD1=T.CHARTFIELD1
AND A.CHARTFIELD2=T.CHARTFIELD2
AND A.CHARTFIELD3=T.CHARTFIELD3
AND A.PROJECT_ID=T.PROJECT_ID
AND A.BOOK_CODE=T.BOOK_CODE
AND A.GL_ADJUST_TYPE=T.GL_ADJUST_TYPE
AND A.CURRENCY_CD=T.CURRENCY_CD
AND A.DATE_CODE=T.DATE_CODE
AND A.FOREIGN_CURRENCY=T.FOREIGN_CURRENCY
ANd T.PROCESS_INSTANCE = XXXXXX--here the process_instance number goes
GROUP BY A.LEDGER, A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, A.PROJECT_ID, A.BOOK_CODE, A.GL_ADJUST_TYPE, A.CURRENCY_CD, A.DATE_CODE, A.FOREIGN_CURRENCY, A.OPEN_ITEM_KEY
CUP :4
ram 32 GB
DB size ~ 1.3TB
Peoplesoft version : 9.2
process name:GL_OI_SUM.SelOISum.OISum
thanks in advance..
August 4, 2014 at 2:21 am
Please can you post the table definitions and indexes of all tables being used in the query along with the execution plan for the query.
August 4, 2014 at 2:41 am
First table:
[PS_OPEN_ITEM_GL](
[BUSINESS_UNIT] [varchar](5) NOT NULL,
[JOURNAL_ID] [varchar](10) NOT NULL,
[JOURNAL_DATE] [dbo].[PSDATE] NOT NULL,
[UNPOST_SEQ] [smallint] NOT NULL,
[JOURNAL_LINE] [int] NOT NULL,
[LEDGER] [varchar](10) NOT NULL,
[ACCOUNT] [varchar](10) NOT NULL,
[ALTACCT] [varchar](10) NOT NULL,
[DEPTID] [varchar](10) NOT NULL,
[OPERATING_UNIT] [varchar](8) NOT NULL,
[PRODUCT] [varchar](6) NOT NULL,
[FUND_CODE] [varchar](5) NOT NULL,
[CLASS_FLD] [varchar](5) NOT NULL,
[PROGRAM_CODE] [varchar](5) NOT NULL,
[BUDGET_REF] [varchar](8) NOT NULL,
[AFFILIATE] [varchar](5) NOT NULL,
[AFFILIATE_INTRA1] [varchar](10) NOT NULL,
[AFFILIATE_INTRA2] [varchar](10) NOT NULL,
[CHARTFIELD1] [varchar](10) NOT NULL,
[CHARTFIELD2] [varchar](10) NOT NULL,
[CHARTFIELD3] [varchar](10) NOT NULL,
[BOOK_CODE] [varchar](4) NOT NULL,
[GL_ADJUST_TYPE] [varchar](4) NOT NULL,
[BUDGET_PERIOD] [varchar](8) NOT NULL,
[SCENARIO] [varchar](10) NOT NULL,
[CURRENCY_CD] [varchar](3) NOT NULL,
[BUSINESS_UNIT_PC] [varchar](5) NOT NULL,
[PROJECT_ID] [varchar](15) NOT NULL,
[ACTIVITY_ID] [varchar](15) NOT NULL,
[RESOURCE_TYPE] [varchar](5) NOT NULL,
[RESOURCE_CATEGORY] [varchar](5) NOT NULL,
[RESOURCE_SUB_CAT] [varchar](5) NOT NULL,
[ANALYSIS_TYPE] [varchar](3) NOT NULL,
[MONETARY_AMOUNT] [decimal](26, 3) NOT NULL,
[MOVEMENT_FLAG] [varchar](1) NOT NULL,
[LINE_DESCR] [varchar](30) NOT NULL,
[FOREIGN_CURRENCY] [varchar](3) NOT NULL,
[FOREIGN_AMOUNT] [decimal](26, 3) NOT NULL,
[OPEN_ITEM_KEY] [varchar](30) NOT NULL,
[OPENITEM_RECON_NBR] [int] NOT NULL,
[OPEN_ITEM_STATUS] [varchar](1) NOT NULL,
[OPEN_DT] [dbo].[PSDATE] NULL,
[CLOSED_DT] [dbo].[PSDATE] NULL,
[DOC_SEQ_NBR] [varchar](12) NOT NULL,
[PROCESS_INSTANCE] [decimal](10, 0) NOT NULL,
[DATE_CODE] [varchar](1) NOT NULL
)
Index :
1)
NONCLUSTERED INDEX [PSBOPEN_ITEM_GL] ON [dbo].[PS_OPEN_ITEM_GL]
(
[LEDGER] ,
[OPEN_ITEM_KEY],
[BUSINESS_UNIT],
[OPEN_ITEM_STATUS],
[ACCOUNT]
)
2)
NONCLUSTERED INDEX [PSCOPEN_ITEM_GL] ON [dbo].[PS_OPEN_ITEM_GL]
(
[OPEN_ITEM_STATUS]
)
INCLUDE ( [BUSINESS_UNIT],
[JOURNAL_DATE],
[LEDGER],
[ACCOUNT],
[ALTACCT],
[DEPTID],
[OPERATING_UNIT],
[PRODUCT],
[FUND_CODE],
[CLASS_FLD],
[PROGRAM_CODE],
[BUDGET_REF],
[AFFILIATE],
[AFFILIATE_INTRA1],
[AFFILIATE_INTRA2],
[CHARTFIELD1],
[CHARTFIELD2],
[CHARTFIELD3],
[BOOK_CODE],
[GL_ADJUST_TYPE],
[CURRENCY_CD],
[PROJECT_ID],
[MONETARY_AMOUNT],
[FOREIGN_CURRENCY],
[FOREIGN_AMOUNT],
[OPEN_ITEM_KEY],
[DATE_CODE])
3)
UNIQUE CLUSTERED INDEX [PS_OPEN_ITEM_GL] ON [dbo].[PS_OPEN_ITEM_GL]
(
[BUSINESS_UNIT],
[JOURNAL_ID] ,
[JOURNAL_DATE],
[UNPOST_SEQ] ,
[JOURNAL_LINE],
[LEDGER] ,
[OPEN_ITEM_KEY] ,
[OPENITEM_RECON_NBR]
)
4)
NONCLUSTERED INDEX [PS_OPEN_ITEM_GL]
(
[BUSINESS_UNIT] ,
[JOURNAL_ID] ,
[JOURNAL_DATE],
[UNPOST_SEQ]
)
August 4, 2014 at 2:43 am
Second table:
[PS_OPEN_ITEM_TAO](
[PROCESS_INSTANCE] [decimal](10, 0) NOT NULL,
[BUSINESS_UNIT] [varchar](5) NOT NULL,
[JOURNAL_ID] [varchar](10) NOT NULL,
[JOURNAL_DATE] [dbo].[PSDATE] NOT NULL,
[UNPOST_SEQ] [smallint] NOT NULL,
[JOURNAL_LINE] [int] NOT NULL,
[LEDGER] [varchar](10) NOT NULL,
[ACCOUNT] [varchar](10) NOT NULL,
[ALTACCT] [varchar](10) NOT NULL,
[DEPTID] [varchar](10) NOT NULL,
[OPERATING_UNIT] [varchar](8) NOT NULL,
[PRODUCT] [varchar](6) NOT NULL,
[FUND_CODE] [varchar](5) NOT NULL,
[CLASS_FLD] [varchar](5) NOT NULL,
[PROGRAM_CODE] [varchar](5) NOT NULL,
[BUDGET_REF] [varchar](8) NOT NULL,
[AFFILIATE] [varchar](5) NOT NULL,
[AFFILIATE_INTRA1] [varchar](10) NOT NULL,
[AFFILIATE_INTRA2] [varchar](10) NOT NULL,
[CHARTFIELD1] [varchar](10) NOT NULL,
[CHARTFIELD2] [varchar](10) NOT NULL,
[CHARTFIELD3] [varchar](10) NOT NULL,
[BOOK_CODE] [varchar](4) NOT NULL,
[GL_ADJUST_TYPE] [varchar](4) NOT NULL,
[DATE_CODE] [varchar](1) NOT NULL,
[BUDGET_PERIOD] [varchar](8) NOT NULL,
[SCENARIO] [varchar](10) NOT NULL,
[CURRENCY_CD] [varchar](3) NOT NULL,
[BUSINESS_UNIT_PC] [varchar](5) NOT NULL,
[PROJECT_ID] [varchar](15) NOT NULL,
[ACTIVITY_ID] [varchar](15) NOT NULL,
[RESOURCE_TYPE] [varchar](5) NOT NULL,
[RESOURCE_CATEGORY] [varchar](5) NOT NULL,
[RESOURCE_SUB_CAT] [varchar](5) NOT NULL,
[ANALYSIS_TYPE] [varchar](3) NOT NULL,
[MONETARY_AMOUNT] [decimal](26, 3) NOT NULL,
[MOVEMENT_FLAG] [varchar](1) NOT NULL,
[LINE_DESCR] [varchar](30) NOT NULL,
[FOREIGN_CURRENCY] [varchar](3) NOT NULL,
[FOREIGN_AMOUNT] [decimal](26, 3) NOT NULL,
[OPEN_ITEM_KEY] [varchar](30) NOT NULL,
[OPENITEM_RECON_NBR] [int] NOT NULL,
[OPEN_ITEM_STATUS] [varchar](1) NOT NULL,
[OPEN_DT] [dbo].[PSDATE] NULL,
[CLOSED_DT] [dbo].[PSDATE] NULL,
[DOC_SEQ_NBR] [varchar](12) NOT NULL
)
Index
UNIQUE CLUSTERED INDEX [PS_OPEN_ITEM_TAO] ON [dbo].[PS_OPEN_ITEM_TAO]
(
[BUSINESS_UNIT] ASC,
[JOURNAL_ID] ASC,
[JOURNAL_DATE] ASC,
[UNPOST_SEQ] ASC,
[JOURNAL_LINE] ASC,
[LEDGER] ASC,
[OPEN_ITEM_KEY] ASC,
[OPENITEM_RECON_NBR] ASC,
[PROCESS_INSTANCE] ASC
)
PFA the execution plan.
August 4, 2014 at 2:54 am
The optimiser is detailing a missing index, have you tried creating that index to see what performance improvement it is going to give you?
Can you also save the plan as a sqlplan file. The link in my signature on posting performance problems will help you if your unsure on doing this.
August 4, 2014 at 3:02 am
thank you very much for your reply!
Yes I tried to add the missing index and the performance is same..
I am attaching the sqlplan file for your references...
Thanks in advance!
August 4, 2014 at 3:09 am
You have implicit conversions going on on your process_instance column. Your passing in an int column into a decimal column so SQL is having to convert the int to decimal at run time for each record. Your best off sending in a decimal value rather than the int
SELECT A.LEDGER , A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, A.PROJECT_ID, A.BOOK_CODE, A.GL_ADJUST_TYPE, A.CURRENCY_CD, A.DATE_CODE, A.FOREIGN_CURRENCY , SUM(A.FOREIGN_AMOUNT) , A.OPEN_ITEM_KEY , (CONVERT(CHAR(10),MAX(A.JOURNAL_DATE),121)) , SUM(A.MONETARY_AMOUNT)
FROM PS_OPEN_ITEM_GL A , PS_OPEN_ITEM_TAO4 T with(NOLOCK)
WHERE A.BUSINESS_UNIT= T.BUSINESS_UNIT
AND A.LEDGER =T.LEDGER
AND A.OPEN_ITEM_KEY =T.OPEN_ITEM_KEY
AND A.OPEN_ITEM_STATUS='O'
AND A.ACCOUNT=T.ACCOUNT
AND A.ALTACCT=T.ALTACCT
AND A.DEPTID=T.DEPTID
AND A.OPERATING_UNIT=T.OPERATING_UNIT
AND A.PRODUCT=T.PRODUCT
AND A.FUND_CODE=T.FUND_CODE
AND A.CLASS_FLD=T.CLASS_FLD
AND A.PROGRAM_CODE=T.PROGRAM_CODE
AND A.BUDGET_REF=T.BUDGET_REF
AND A.AFFILIATE=T.AFFILIATE
AND A.AFFILIATE_INTRA1=T.AFFILIATE_INTRA1
AND A.AFFILIATE_INTRA2=T.AFFILIATE_INTRA2
AND A.CHARTFIELD1=T.CHARTFIELD1
AND A.CHARTFIELD2=T.CHARTFIELD2
AND A.CHARTFIELD3=T.CHARTFIELD3
AND A.PROJECT_ID=T.PROJECT_ID
AND A.BOOK_CODE=T.BOOK_CODE
AND A.GL_ADJUST_TYPE=T.GL_ADJUST_TYPE
AND A.CURRENCY_CD=T.CURRENCY_CD
AND A.DATE_CODE=T.DATE_CODE
AND A.FOREIGN_CURRENCY=T.FOREIGN_CURRENCY
AND T.PROCESS_INSTANCE = CONVERT(DECIMAL(10,0),339706)
GROUP BY A.LEDGER, A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, A.PROJECT_ID, A.BOOK_CODE, A.GL_ADJUST_TYPE, A.CURRENCY_CD, A.DATE_CODE, A.FOREIGN_CURRENCY, A.OPEN_ITEM_KEY
I would defiantly recommend putting that index the optimiser is detailing on the table as your doing your the majority of the operation on the process_instance column to which there is no supporting index.
Also are you sure about the NOLOCK? Do you understand the problems that you can face with NOLOCK? Also NOLOCK is not a "Go Faster" switch, sometimes it can slow the process down.
August 4, 2014 at 3:44 am
HI,
The nolock we are not using..It was used for testing..I added the missing index in the table and I ran the query..also I have attached the updated Execution plan.
Thanks!
August 4, 2014 at 4:07 am
Is that the estimated execution plan or the actual execution plan?
August 4, 2014 at 4:24 am
Its estimated execution plan.
created temp table for both tables (row count:453637 for process_instance=339706) and all the indexes as per main table.still the query is keep on running 🙁
total count in main table(PS_OPEN_ITEM_GL):28100077.
When we ran the same query for less number of row(for a process_instance) in the main table its taking ~ 3 seconds..
thanks
August 4, 2014 at 4:29 am
Please provide the actual execution plan
August 4, 2014 at 5:09 am
Sure..but dont know when this query will finish..
FYI this is a upgraded new DEV server from 2005 to 2008R2.
few more details:
Server properties:
Processor:2.67 Ghz( X two)..
ram 36 (usable : 32)
64 bit windows server 2008R2 Standard operating system
SQl server properties:
Version:10.50.4286
Max memory 27648 MB.
Max Degree of parallelism:0
cost Threshold for parallelism :5
Isolation Level:read committed.
thanks
August 4, 2014 at 5:11 am
Have you rebuilt all your indexes and statistics since the upgrade?
If not I would do that before you do anything else
August 4, 2014 at 5:21 am
Ya we rebuild all the indexes, updated the statistics. still we are getting performance issue in this select query..
August 4, 2014 at 5:55 am
Ok the next step would be to post the actual execution plan once you have got it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply