performance issue in peoplesoft Open Item Reconciliation process

  • 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..

  • 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.

  • 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]

    )

  • 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.

  • 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.

  • 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!

  • 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.

  • 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!

  • Is that the estimated execution plan or the actual execution plan?

  • 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

  • Please provide the actual execution plan

  • 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

  • Have you rebuilt all your indexes and statistics since the upgrade?

    If not I would do that before you do anything else

  • Ya we rebuild all the indexes, updated the statistics. still we are getting performance issue in this select query..

  • 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