Loading ...
Sorry, an error occurred while loading the content.

nVision Performance issues on PeopleSoft Financials

Expand Messages
  • skilaurieski
    Hi, We have severe optimizer instability when running nVision reports on our Financials system. We are on Oracle 10.2.0.5 and PeopleTools 8.49. Reports
    Message 1 of 5 , Nov 6, 2012
    • 0 Attachment
      Hi,
      We have severe optimizer instability when running nVision reports on our Financials system. We are on Oracle 10.2.0.5 and PeopleTools 8.49. Reports against ps_ledger are fast some days and slow other days. We are running stats on our most active ps_ledger partition every hour. For nVision SQLs that are slow, I will run SQL Advisor and it always finds a much better profile. After we pin the profile to that particular SQL, then that sql runs fast. But we have to continually do this all day on various nVision SQL's as they are all slightly different.
      Has anyone else had a problem like this?
      GLPPOST runs every 10 mins and does DML on ps_ledger so that is why we run stats so often. Problem gets worse with heavy load days at month-end.
      Thanks in Advance,
      Laurie
    • David Kurtz
      Laurie I have written about nVision in the past - see http://www.go-faster.co.uk/pres.htm#nvision_performance_tuning and
      Message 2 of 5 , Nov 6, 2012
      • 0 Attachment
        RE: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

        Laurie

        I have written about nVision in the past – see http://www.go-faster.co.uk/pres.htm#nvision_performance_tuning and http://blog.psftdba.com/2006/10/global-nvision-performance-options.html

        I have faced issues like this.  It sounds like even updating the statistics isn’t providing a complete solution.  However, it is very difficult to make and specific suggestions without knowing a lot more about your system without indulging in guesswork.

        Questions:

        1.      You mentioned profiles.  Presumably you have uncheck ‘use literal field values’ in the nVision options – have you also used CURSOR_SHARING=FORCE?   I would not suggest either of these approaches.

        2.      How have you partitioned your ledger table – I usually go monthly partitions for current and last year, perhaps quarterly for the year before, and annually prior to that. 

        3.      How are you collecting statistics at table level on PS_LEDGER?  Are you letting Oracle collect aggregated statistics?  (I have written about that and there are problems with aggregated stats on 10g compared to incremental stats on 11g)

        4.      I wonder if collecting statistics that frequently is counter-productive.  By default it will invalidate any cursors on PS_LEDGER forcing the optimizer to parse the statement again.

        I have generally found that the following approach is successful in nVision.

        1.      It is essential that you get the indexing on the ledger table absolutely right.  You tend to end up with pairs of indexes for each set combination of analysis tree (one index for year to date queries where you have a range of accounting periods, and one for single account period queries).  Index compression is an option, though it can degrade posting to the ledger table.

        2.      Use OPTIMIZER_DYNAMIC_SAMPLING – it can be effective where you cannot maintain statistics because things keep changing too frequently.

        3.      ‘use literal field values’ in the nVision options on your analysis trees – although this produces very variable and non-sharable SQL so you won’t get profiles or stored outlines to work – but this can greatly simply SQL generated by nVision.

        4.      Use static Selectors on your analysis tree dynamic selectors can seem more effective in the sort term, but the statistics on the selector tables will never be correct as nVision report continually extract more selector trees, and you can get debris left in the tables are nVision crashes.  With static selectors you can collect stats on the selector tables and they don’t go out of date.

        5.      Range partition the ledger table at least by fiscal year and accounting period.  Occasionally, list sub-partitioning by LEDGER can be effective.

        6.      The execution plan you really want Oracle to merge the selector tables before visiting the ledger table.  It can be difficult without manually adjusting statistics – have you read Wolfgang Brietling’s ‘Tuning by Cardinality Feedback’ – at least one example is drawn from nVision.

        7.      By default all objects in a PeoplesSoft database are set NOPARALLEL – however, depending on how you run your nVision reports you might want to experiment (cautiously) with a degree of parallelism on the LEDGER table. The risk is that you can end up with lots of concurrent nVision queries demanding parallel query slaves, and then you run out of PQ slaves and have processes waiting for a PQ slave process to become available.

        regards

        _________________________

        David Kurtz

        tel: +44 (0)7771 760660

        mailto:david.kurtz@...



        -----Original Message-----
        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of skilaurieski
        Sent: 06 November 2012 16:10
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

        Hi,

        We have severe optimizer instability when running nVision reports on our Financials system.  We are on Oracle 10.2.0.5 and PeopleTools 8.49.  Reports against ps_ledger are fast some days and slow other days.  We are running stats on our most active ps_ledger partition every hour.  For nVision SQLs that are slow, I will run SQL Advisor and it always finds a much better profile.  After we pin the profile to that particular SQL, then that sql runs fast.  But we have to continually do this all day on various nVision SQL's as they are all slightly different.

        Has anyone else had a problem like this?

        GLPPOST runs every 10 mins and does DML on ps_ledger so that is why we run stats so often.  Problem gets worse with heavy load days at month-end. 

        Thanks in Advance,

        Laurie


      • Laurie Murray
        Hi, David Thanks for your response! 1)  use_literal_field_values is unchecked.   Cursor_sharing = Exact 2)  ps_ledger: haven t considered partitioning by
        Message 3 of 5 , Nov 6, 2012
        • 0 Attachment
          Hi, David
          Thanks for your response!
          1)  use_literal_field_values is unchecked.   Cursor_sharing = Exact

          2)  ps_ledger: haven't considered partitioning by month because we never query by month.  Right now we:
          PARTITION BY RANGE ("FISCAL_YEAR")
          SUBPARTITION BY LIST ("LEDGER").
          However I need to re-work this because when the optimizer chooses poorly and does a full partitition scan it is scanning millions of rows.  I need smaller partitions.

          3)  stats on ps_ledger -- we do histogram stats once per hour for the partition with Fiscal year 2012, and histogram stats once every 4 hours for the partition with Fiscal Year 2011.  we use this:
          BEGIN
                      
          DBMS_STATS.GATHER_TABLE_STATS(
                    
          OWNNAME => 'SYSADM',
                    
          TABNAME => 'PS_LEDGER',
                    
          PARTNAME => 'FY2011',
                    
          METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
                    
          GRANULARITY => 'ALL',
                    
          DEGREE => dbms_stats.default_degree,
                    
          CASCADE => TRUE);
                      
          END;


          4) yes, maybe we are gathering stats too often.  It is hard to know exactly how often to gather them...

          Also: is it true that I can delete 1256333 rows from my pstreeselect10 table?
          SQL> select count(*) from pstreeselect10;

            COUNT(*)
          ----------
             1318721

          SQL> select count(*) from PSTREESELECT10
            2  where SELECTOR_NUM not in (select SELECTOR_NUM from pstreeselctl)
            3  /

            COUNT(*)
          ----------
             1256333

          Here is an example of a SQL that is running now and has been running for 52 minutes.  I know if I used SQL Advisor on it it would have a better profile and use an index.  Right now it is doing a full partition scan on the ledger.  The problem is that the nVision SQL's are always slightly different, so pinning profiles to SQL statements doesn't get me very far, because the next SQL that comes along is slightly different and won't use the profile.

          SELECT A.PRODUCT,SUM(A.POSTED_TOTAL_AMT) FROM PS_LP_NVS_SEC_VW A, PSTREESELECT10 L, PSTREESELECT06 L1 WHERE A.OPRID='SOLORZAJ' AND A.LEDGER='ACT_USD' AND A.FISCAL_YEAR=2012 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND L.SELECTOR_NUM=1280125 AND A.ALTACCT BETWEEN L.RANGE_FROM_10 AND L.RANGE_TO_10 AND L.TREE_NODE_NUM BETWEEN 1625000000 AND 1687499999 AND L1.SELECTOR_NUM=1287279 AND A.PRODUCT>= L1.RANGE_FROM_06 AND A.PRODUCT <= L1.RANGE_TO_06 AND L1.TREE_NODE_NUM BETWEEN 852003498 AND 854251527 AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY A.PRODUCT

          SQL> select text from user_views where view_name='PS_LP_NVS_SEC_VW';

          TEXT
          --------------------------------------------------------------------------------
          SELECT A.OPRID , L.BUSINESS_UNIT , L.LEDGER , L.ACCOUNT,L.ALTACCT ,
           L.DEPTID ,L.OPERATING_UNIT, L.PRODUCT,
          L.FUND_CODE, l.CLASS_FLD, L.PROGRAM_CODE,L.BUDGET_REF,
          L.AFFILIATE, L.AFFILIATE_INTRA1,
          L.AFFILIATE_INTRA2, L.CHARTFIELD1, L.CHARTFIELD2,
          L.CHARTFIELD3 ,L.PROJECT_ID , L.BOOK_CODE ,
          L.GL_ADJUST_TYPE , L.CURRENCY_CD ,
          L.STATISTICS_CODE , L.FISCAL_YEAR , L.ACCOUNTING_PERIOD , L.POSTED_TOTAL_AMT ,
          L.POSTED_TOTAL_DR , L.POSTED_TOTAL_CR , L.POSTED_BASE_AMT , L.POSTED_TRAN_AMT ,
          L.POSTED_TRAN_DR , L.POSTED_TRAN_CR , L.BASE_CURRENCY , L.DTTM_STAMP_SEC,
          L.PROCESS_INSTANCE
          FROM PSOPRDEFN A ,
          PS_SEC_BU_CLS B ,
          PS_LEDGER L
          WHERE A.OPRCLASS = B.OPRCLASS
          AND B.BUSINESS_UNIT = L.BUSINESS_UNIT


          From: David Kurtz <david.kurtz@...>
          To: psftdba@yahoogroups.com
          Cc: skilaurieski@...
          Sent: Tuesday, November 6, 2012 10:11 AM
          Subject: RE: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

           
          Laurie
          I have faced issues like this.  It sounds like even updating the statistics isn’t providing a complete solution.  However, it is very difficult to make and specific suggestions without knowing a lot more about your system without indulging in guesswork.
          Questions:
          1.      You mentioned profiles.  Presumably you have uncheck ‘use literal field values’ in the nVision options – have you also used CURSOR_SHARING=FORCE?   I would not suggest either of these approaches.
          2.      How have you partitioned your ledger table – I usually go monthly partitions for current and last year, perhaps quarterly for the year before, and annually prior to that. 
          3.      How are you collecting statistics at table level on PS_LEDGER?  Are you letting Oracle collect aggregated statistics?  (I have written about that and there are problems with aggregated stats on 10g compared to incremental stats on 11g)
          4.      I wonder if collecting statistics that frequently is counter-productive.  By default it will invalidate any cursors on PS_LEDGER forcing the optimizer to parse the statement again.
          I have generally found that the following approach is successful in nVision.
          1.      It is essential that you get the indexing on the ledger table absolutely right.  You tend to end up with pairs of indexes for each set combination of analysis tree (one index for year to date queries where you have a range of accounting periods, and one for single account period queries).  Index compression is an option, though it can degrade posting to the ledger table.
          2.      Use OPTIMIZER_DYNAMIC_SAMPLING – it can be effective where you cannot maintain statistics because things keep changing too frequently.
          3.      ‘use literal field values’ in the nVision options on your analysis trees – although this produces very variable and non-sharable SQL so you won’t get profiles or stored outlines to work – but this can greatly simply SQL generated by nVision.
          4.      Use static Selectors on your analysis tree dynamic selectors can seem more effective in the sort term, but the statistics on the selector tables will never be correct as nVision report continually extract more selector trees, and you can get debris left in the tables are nVision crashes.  With static selectors you can collect stats on the selector tables and they don’t go out of date.
          5.      Range partition the ledger table at least by fiscal year and accounting period.  Occasionally, list sub-partitioning by LEDGER can be effective.
          6.      The execution plan you really want Oracle to merge the selector tables before visiting the ledger table.  It can be difficult without manually adjusting statistics – have you read Wolfgang Brietling’s ‘Tuning by Cardinality Feedback’ – at least one example is drawn from nVision.
          7.      By default all objects in a PeoplesSoft database are set NOPARALLEL – however, depending on how you run your nVision reports you might want to experiment (cautiously) with a degree of parallelism on the LEDGER table. The risk is that you can end up with lots of concurrent nVision queries demanding parallel query slaves, and then you run out of PQ slaves and have processes waiting for a PQ slave process to become available.
          regards
          _________________________
          David Kurtz
          tel: +44 (0)7771 760660


          -----Original Message-----
          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of skilaurieski
          Sent: 06 November 2012 16:10
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials
          Hi,
          We have severe optimizer instability when running nVision reports on our Financials system.  We are on Oracle 10.2.0.5 and PeopleTools 8.49.  Reports against ps_ledger are fast some days and slow other days.  We are running stats on our most active ps_ledger partition every hour.  For nVision SQLs that are slow, I will run SQL Advisor and it always finds a much better profile.  After we pin the profile to that particular SQL, then that sql runs fast.  But we have to continually do this all day on various nVision SQL's as they are all slightly different.
          Has anyone else had a problem like this?
          GLPPOST runs every 10 mins and does DML on ps_ledger so that is why we run stats so often.  Problem gets worse with heavy load days at month-end. 
          Thanks in Advance,
          Laurie



        • David Kurtz
          Laurie 2. Consider partition by range(fiscal_year, accounting_period) – you can do a range partition on the combination of two columns – it still only one
          Message 4 of 5 , Nov 6, 2012
          • 1 Attachment
          • 130 KB

          Laurie

           

          2. Consider partition by range(fiscal_year, accounting_period) – you can do a range partition on the combination of two columns – it still only one range, but this way you can create a range partition for each period. 

          I would suggest creating one partition for each account period, another for period=0 and and another maxvalue partition to hold periods 998 and 999. So if you have 12 calendar monthly periods you have 14 partitions per year.

           

          3. I haven’t checked this, but I think you table level stats will not be updated by this.

           

          4. Yes you can delete dynamic selectors for completed reports from the PSTREESELECTnn tables – but if you went over to static selectors you wouldn’t have to.

           

          5. What is the execution plan for this query?

          ·         This is a year to date query in the current year, so breaking the FY2012 partition down won’t really help because it will have to look in every partition.  But there will be other queries where it does. You might get lucky and the locally partitioned indexes will have one level less.

          ·         You need a locally partitioned index on LEDGER on something like this: LEDGER, BUSINESS_UNIT, FISCAL_YEAR, PROUCT, ALT_ACCT, ACCOUNTING_PERIOD. 

          o   You might also want currency code if you have multi-currency ledgers.

          o   For single period queries you need something like this: LEDGER, BUSINESS_UNIT, FISCAL_YEAR, ACCOUNTING_PERIOD, PROUCT, ALT_ACCT

          ·         If you used ‘static selectors’ and ‘use literal values’ on the product tree (selector 1287279) and the ALT_ACCT tree (selector 1280125) you  could eliminate the selector tables from this query and nVision would construct a list of literal predicates directly on A.  You would be left with a query on A only, and the optimizer has many fewer ways to choose a poor plan.

           

          regards
          _________________________
          David Kurtz
          tel: +44 (0)7771 760660
          mailto:david.kurtz@...

           

           

          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Laurie Murray
          Sent: 06 November 2012 20:53
          To: psftdba@yahoogroups.com
          Subject: Re: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

           




          Hi, David
          Thanks for your response!
          1)  use_literal_field_values is unchecked.   Cursor_sharing = Exact

          2)  ps_ledger: haven't considered partitioning by month because we never query by month.  Right now we:
          PARTITION BY RANGE ("FISCAL_YEAR")
          SUBPARTITION BY LIST ("LEDGER").
          However I need to re-work this because when the optimizer chooses poorly and does a full partitition scan it is scanning millions of rows.  I need smaller partitions.

          3)  stats on ps_ledger -- we do histogram stats once per hour for the partition with Fiscal year 2012, and histogram stats once every 4 hours for the partition with Fiscal Year 2011.  we use this:
          BEGIN
                      DBMS_STATS.GATHER_TABLE_STATS(
                    OWNNAME =>
          'SYSADM',
                    TABNAME =>
          'PS_LEDGER',
                    PARTNAME =>
          'FY2011',
                    METHOD_OPT =>
          'FOR ALL COLUMNS SIZE SKEWONLY',
                    GRANULARITY =>
          'ALL',
                    
          DEGREE => dbms_stats.default_degree,
                    
          CASCADE => TRUE);
                      
          END;


          4) yes, maybe we are gathering stats too often.  It is hard to know exactly how often to gather them...

          Also: is it true that I can delete 1256333 rows from my pstreeselect10 table?

          SQL> select count(*) from pstreeselect10;

            COUNT(*)
          ----------
             1318721

          SQL> select count(*) from PSTREESELECT10
            2  where SELECTOR_NUM not in (select SELECTOR_NUM from pstreeselctl)
            3  /

            COUNT(*)
          ----------
             1256333

           

          Here is an example of a SQL that is running now and has been running for 52 minutes.  I know if I used SQL Advisor on it it would have a better profile and use an index.  Right now it is doing a full partition scan on the ledger.  The problem is that the nVision SQL's are always slightly different, so pinning profiles to SQL statements doesn't get me very far, because the next SQL that comes along is slightly different and won't use the profile.

           

          SELECT A.PRODUCT,SUM(A.POSTED_TOTAL_AMT) FROM PS_LP_NVS_SEC_VW A, PSTREESELECT10 L, PSTREESELECT06 L1 WHERE A.OPRID='SOLORZAJ' AND A.LEDGER='ACT_USD' AND A.FISCAL_YEAR=2012 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND L.SELECTOR_NUM=1280125 AND A.ALTACCT BETWEEN L.RANGE_FROM_10 AND L.RANGE_TO_10 AND L.TREE_NODE_NUM BETWEEN 1625000000 AND 1687499999 AND L1.SELECTOR_NUM=1287279 AND A.PRODUCT>= L1.RANGE_FROM_06 AND A.PRODUCT <= L1.RANGE_TO_06 AND L1.TREE_NODE_NUM BETWEEN 852003498 AND 854251527 AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY A.PRODUCT

           

          SQL> select text from user_views where view_name='PS_LP_NVS_SEC_VW';

          TEXT
          --------------------------------------------------------------------------------
          SELECT A.OPRID , L.BUSINESS_UNIT , L.LEDGER , L.ACCOUNT,L.ALTACCT ,
           L.DEPTID ,L.OPERATING_UNIT, L.PRODUCT,
          L.FUND_CODE, l.CLASS_FLD, L.PROGRAM_CODE,L.BUDGET_REF,
          L.AFFILIATE, L.AFFILIATE_INTRA1,
          L.AFFILIATE_INTRA2, L.CHARTFIELD1, L.CHARTFIELD2,
          L.CHARTFIELD3 ,L.PROJECT_ID , L.BOOK_CODE ,
          L.GL_ADJUST_TYPE , L.CURRENCY_CD ,
          L.STATISTICS_CODE , L.FISCAL_YEAR , L.ACCOUNTING_PERIOD , L.POSTED_TOTAL_AMT ,
          L.POSTED_TOTAL_DR , L.POSTED_TOTAL_CR , L.POSTED_BASE_AMT , L.POSTED_TRAN_AMT ,
          L.POSTED_TRAN_DR , L.POSTED_TRAN_CR , L.BASE_CURRENCY , L.DTTM_STAMP_SEC,
          L.PROCESS_INSTANCE
          FROM PSOPRDEFN A ,
          PS_SEC_BU_CLS B ,
          PS_LEDGER L
          WHERE A.OPRCLASS = B.OPRCLASS
          AND B.BUSINESS_UNIT = L.BUSINESS_UNIT

           


          From: David Kurtz <david.kurtz@...>
          To: psftdba@yahoogroups.com
          Cc: skilaurieski@...
          Sent: Tuesday, November 6, 2012 10:11 AM
          Subject: RE: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

           

           

          Laurie

          I have faced issues like this.  It sounds like even updating the statistics isn’t providing a complete solution.  However, it is very difficult to make and specific suggestions without knowing a lot more about your system without indulging in guesswork.

          Questions:

          1.      You mentioned profiles.  Presumably you have uncheck ‘use literal field values’ in the nVision options – have you also used CURSOR_SHARING=FORCE?   I would not suggest either of these approaches.

          2.      How have you partitioned your ledger table – I usually go monthly partitions for current and last year, perhaps quarterly for the year before, and annually prior to that. 

          3.      How are you collecting statistics at table level on PS_LEDGER?  Are you letting Oracle collect aggregated statistics?  (I have written about that and there are problems with aggregated stats on 10g compared to incremental stats on 11g)

          4.      I wonder if collecting statistics that frequently is counter-productive.  By default it will invalidate any cursors on PS_LEDGER forcing the optimizer to parse the statement again.

          I have generally found that the following approach is successful in nVision.

          1.      It is essential that you get the indexing on the ledger table absolutely right.  You tend to end up with pairs of indexes for each set combination of analysis tree (one index for year to date queries where you have a range of accounting periods, and one for single account period queries).  Index compression is an option, though it can degrade posting to the ledger table.

          2.      Use OPTIMIZER_DYNAMIC_SAMPLING – it can be effective where you cannot maintain statistics because things keep changing too frequently.

          3.      ‘use literal field values’ in the nVision options on your analysis trees – although this produces very variable and non-sharable SQL – so you won’t get profiles or stored outlines to work – but this can greatly simply SQL generated by nVision.

          4.      Use static Selectors on your analysis tree –dynamic selectors can seem more effective in the sort term, but the statistics on the selector tables will never be correct as nVision report continually extract more selector trees, and you can get debris left in the tables are nVision crashes.  With static selectors you can collect stats on the selector tables and they don’t go out of date.

          5.      Range partition the ledger table at least by fiscal year and accounting period.  Occasionally, list sub-partitioning by LEDGER can be effective.

          6.      The execution plan you really want Oracle to merge the selector tables before visiting the ledger table.  It can be difficult without manually adjusting statistics – have you read Wolfgang Brietling’s ‘Tuning by Cardinality Feedback’ – at least one example is drawn from nVision.

          7.      By default all objects in a PeoplesSoft database are set NOPARALLEL – however, depending on how you run your nVision reports you might want to experiment (cautiously) with a degree of parallelism on the LEDGER table. The risk is that you can end up with lots of concurrent nVision queries demanding parallel query slaves, and then you run out of PQ slaves and have processes waiting for a PQ slave process to become available.

          regards

          _________________________

          David Kurtz

          tel: +44 (0)7771 760660

           

          -----Original Message-----
          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of skilaurieski
          Sent: 06 November 2012 16:10
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials

          Hi,

          We have severe optimizer instability when running nVision reports on our Financials system.  We are on Oracle 10.2.0.5 and PeopleTools 8.49.  Reports against ps_ledger are fast some days and slow other days.  We are running stats on our most active ps_ledger partition every hour.  For nVision SQLs that are slow, I will run SQL Advisor and it always finds a much better profile.  After we pin the profile to that particular SQL, then that sql runs fast.  But we have to continually do this all day on various nVision SQL's as they are all slightly different.

          Has anyone else had a problem like this?

          GLPPOST runs every 10 mins and does DML on ps_ledger so that is why we run stats so often.  Problem gets worse with heavy load days at month-end. 

          Thanks in Advance,

          Laurie

           

           




        • Laurie Murray
          Here is the bad execution plan, per your request.  Thanks for the ideas you already sent!  I look forward to trying them out. --Laurie ...
          Message 5 of 5 , Nov 6, 2012
          • 0 Attachment
            Here is the bad execution plan, per your request.  Thanks for the ideas you already sent!  I look forward to trying them out.
            --Laurie
            ----------------------------------------------------------------------------------------------------
            | Id  | Operation                         | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ----------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                  |                   |   196 | 28616 | 19221   (4)| 00:00:27 |       |       |
            |   1 |  HASH GROUP BY                    |                   |   196 | 28616 | 19221   (4)| 00:00:27 |       |       |
            |   2 |   NESTED LOOPS                    |                   |   196 | 28616 | 19220   (4)| 00:00:27 |       |       |
            |   3 |    MERGE JOIN                     |                   |    83 | 10043 | 19219   (4)| 00:00:27 |       |       |
            |   4 |     SORT JOIN                     |                   |  2785 |   263K| 19215   (4)| 00:00:27 |       |       |
            |   5 |      NESTED LOOPS                 |                   |  2785 |   263K| 19214   (4)| 00:00:27 |       |       |
            |   6 |       NESTED LOOPS                |                   |     1 |    54 |     4   (0)| 00:00:01 |       |       |
            |   7 |        TABLE ACCESS BY INDEX ROWID| PSOPRDEFN         |     1 |    31 |     2   (0)| 00:00:01 |
            |*  8 |         INDEX UNIQUE SCAN         | PS_PSOPRDEFN      |     1 |       |     1   (0)| 00:00:01 |       |   
            |*  9 |        INDEX RANGE SCAN           | PS_PSTREESELECT06 |     1 |    23 |     2   (0)| 00:00:01 |     
            |  10 |       PARTITION RANGE SINGLE      |                   |  9218 |   387K| 19210   (4)| 00:00:27 |    10 |    1
            |  11 |        PARTITION LIST SINGLE      |                   |  9218 |   387K| 19210   (4)| 00:00:27 |   KEY |   KE
            |* 12 |         TABLE ACCESS FULL         | PS_LEDGER         |  9218 |   387K| 19210   (4)| 00:00:27 |   219 |   219
            |* 13 |     FILTER                        |                   |       |       |            |          |       |       |
            |* 14 |      SORT JOIN                    |                   |    12 |   288 |     4  (25)| 00:00:01 |       |       |
            |* 15 |       INDEX RANGE SCAN            | PS_PSTREESELECT10 |    12 |   288 |     3   (0)| 00:00:01 |      
            |* 16 |    INDEX UNIQUE SCAN              | PS_SEC_BU_CLS     |     2 |    50 |     0   (0)| 00:00:01 |       |
            ----------------------------------------------------------------------------------------------------


            From: David Kurtz <david.kurtz@...>
            To: psftdba@yahoogroups.com
            Cc: 'Laurie Murray' <skilaurieski@...>
            Sent: Tuesday, November 6, 2012 2:28 PM
            Subject: RE: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials [1 Attachment]

             
            Laurie
             
            2. Consider partition by range(fiscal_year, accounting_period) – you can do a range partition on the combination of two columns – it still only one range, but this way you can create a range partition for each period. 
            I would suggest creating one partition for each account period, another for period=0 and and another maxvalue partition to hold periods 998 and 999. So if you have 12 calendar monthly periods you have 14 partitions per year.
             
            3. I haven’t checked this, but I think you table level stats will not be updated by this.
             
            4. Yes you can delete dynamic selectors for completed reports from the PSTREESELECTnn tables – but if you went over to static selectors you wouldn’t have to.
             
            5. What is the execution plan for this query?
            ·         This is a year to date query in the current year, so breaking the FY2012 partition down won’t really help because it will have to look in every partition.  But there will be other queries where it does. You might get lucky and the locally partitioned indexes will have one level less.
            ·         You need a locally partitioned index on LEDGER on something like this: LEDGER, BUSINESS_UNIT, FISCAL_YEAR, PROUCT, ALT_ACCT, ACCOUNTING_PERIOD. 
            o   You might also want currency code if you have multi-currency ledgers.
            o   For single period queries you need something like this: LEDGER, BUSINESS_UNIT, FISCAL_YEAR, ACCOUNTING_PERIOD, PROUCT, ALT_ACCT
            ·         If you used ‘static selectors’ and ‘use literal values’ on the product tree (selector 1287279) and the ALT_ACCT tree (selector 1280125) you  could eliminate the selector tables from this query and nVision would construct a list of literal predicates directly on A.  You would be left with a query on A only, and the optimizer has many fewer ways to choose a poor plan.
             
            regards
            _________________________
            David Kurtz
            tel: +44 (0)7771 760660
            mailto:david.kurtz@...

             
             
            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Laurie Murray
            Sent: 06 November 2012 20:53
            To: psftdba@yahoogroups.com
            Subject: Re: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials
             



            Hi, David
            Thanks for your response!
            1)  use_literal_field_values is unchecked.   Cursor_sharing = Exact

            2)  ps_ledger: haven't considered partitioning by month because we never query by month.  Right now we:
            PARTITION BY RANGE ("FISCAL_YEAR")
            SUBPARTITION BY LIST ("LEDGER").
            However I need to re-work this because when the optimizer chooses poorly and does a full partitition scan it is scanning millions of rows.  I need smaller partitions.

            3)  stats on ps_ledger -- we do histogram stats once per hour for the partition with Fiscal year 2012, and histogram stats once every 4 hours for the partition with Fiscal Year 2011.  we use this:
            BEGIN
                        DBMS_STATS.GATHER_TABLE_STATS(
                      OWNNAME =>
            'SYSADM',
                      TABNAME =>
            'PS_LEDGER',
                      PARTNAME =>
            'FY2011',
                      METHOD_OPT =>
            'FOR ALL COLUMNS SIZE SKEWONLY',
                      GRANULARITY =>
            'ALL',
                      
            DEGREE => dbms_stats.default_degree,
                      
            CASCADE => TRUE);
                        
            END;


            4) yes, maybe we are gathering stats too often.  It is hard to know exactly how often to gather them...

            Also: is it true that I can delete 1256333 rows from my pstreeselect10 table?
            SQL> select count(*) from pstreeselect10;

              COUNT(*)
            ----------
               1318721

            SQL> select count(*) from PSTREESELECT10
              2  where SELECTOR_NUM not in (select SELECTOR_NUM from pstreeselctl)
              3  /

              COUNT(*)
            ----------
               1256333
             
            Here is an example of a SQL that is running now and has been running for 52 minutes.  I know if I used SQL Advisor on it it would have a better profile and use an index.  Right now it is doing a full partition scan on the ledger.  The problem is that the nVision SQL's are always slightly different, so pinning profiles to SQL statements doesn't get me very far, because the next SQL that comes along is slightly different and won't use the profile.
             
            SELECT A.PRODUCT,SUM(A.POSTED_TOTAL_AMT) FROM PS_LP_NVS_SEC_VW A, PSTREESELECT10 L, PSTREESELECT06 L1 WHERE A.OPRID='SOLORZAJ' AND A.LEDGER='ACT_USD' AND A.FISCAL_YEAR=2012 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND L.SELECTOR_NUM=1280125 AND A.ALTACCT BETWEEN L.RANGE_FROM_10 AND L.RANGE_TO_10 AND L.TREE_NODE_NUM BETWEEN 1625000000 AND 1687499999 AND L1.SELECTOR_NUM=1287279 AND A.PRODUCT>= L1.RANGE_FROM_06 AND A.PRODUCT <= L1.RANGE_TO_06 AND L1.TREE_NODE_NUM BETWEEN 852003498 AND 854251527 AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY A.PRODUCT
             
            SQL> select text from user_views where view_name='PS_LP_NVS_SEC_VW';

            TEXT
            --------------------------------------------------------------------------------
            SELECT A.OPRID , L.BUSINESS_UNIT , L.LEDGER , L.ACCOUNT,L.ALTACCT ,
             L.DEPTID ,L.OPERATING_UNIT, L.PRODUCT,
            L.FUND_CODE, l.CLASS_FLD, L.PROGRAM_CODE,L.BUDGET_REF,
            L.AFFILIATE, L.AFFILIATE_INTRA1,
            L.AFFILIATE_INTRA2, L.CHARTFIELD1, L.CHARTFIELD2,
            L.CHARTFIELD3 ,L.PROJECT_ID , L.BOOK_CODE ,
            L.GL_ADJUST_TYPE , L.CURRENCY_CD ,
            L.STATISTICS_CODE , L.FISCAL_YEAR , L.ACCOUNTING_PERIOD , L.POSTED_TOTAL_AMT ,
            L.POSTED_TOTAL_DR , L.POSTED_TOTAL_CR , L.POSTED_BASE_AMT , L.POSTED_TRAN_AMT ,
            L.POSTED_TRAN_DR , L.POSTED_TRAN_CR , L.BASE_CURRENCY , L.DTTM_STAMP_SEC,
            L.PROCESS_INSTANCE
            FROM PSOPRDEFN A ,
            PS_SEC_BU_CLS B ,
            PS_LEDGER L
            WHERE A.OPRCLASS = B.OPRCLASS
            AND B.BUSINESS_UNIT = L.BUSINESS_UNIT
             

            From: David Kurtz <david.kurtz@...>
            To: psftdba@yahoogroups.com
            Cc: skilaurieski@...
            Sent: Tuesday, November 6, 2012 10:11 AM
            Subject: RE: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials
             
             
            Laurie
            I have faced issues like this.  It sounds like even updating the statistics isn’t providing a complete solution.  However, it is very difficult to make and specific suggestions without knowing a lot more about your system without indulging in guesswork.
            Questions:
            1.      You mentioned profiles.  Presumably you have uncheck ‘use literal field values’ in the nVision options – have you also used CURSOR_SHARING=FORCE?   I would not suggest either of these approaches.
            2.      How have you partitioned your ledger table – I usually go monthly partitions for current and last year, perhaps quarterly for the year before, and annually prior to that. 
            3.      How are you collecting statistics at table level on PS_LEDGER?  Are you letting Oracle collect aggregated statistics?  (I have written about that and there are problems with aggregated stats on 10g compared to incremental stats on 11g)
            4.      I wonder if collecting statistics that frequently is counter-productive.  By default it will invalidate any cursors on PS_LEDGER forcing the optimizer to parse the statement again.
            I have generally found that the following approach is successful in nVision.
            1.      It is essential that you get the indexing on the ledger table absolutely right.  You tend to end up with pairs of indexes for each set combination of analysis tree (one index for year to date queries where you have a range of accounting periods, and one for single account period queries).  Index compression is an option, though it can degrade posting to the ledger table.
            2.      Use OPTIMIZER_DYNAMIC_SAMPLING – it can be effective where you cannot maintain statistics because things keep changing too frequently.
            3.      ‘use literal field values’ in the nVision options on your analysis trees – although this produces very variable and non-sharable SQL – so you won’t get profiles or stored outlines to work – but this can greatly simply SQL generated by nVision.
            4.      Use static Selectors on your analysis tree –dynamic selectors can seem more effective in the sort term, but the statistics on the selector tables will never be correct as nVision report continually extract more selector trees, and you can get debris left in the tables are nVision crashes.  With static selectors you can collect stats on the selector tables and they don’t go out of date.
            5.      Range partition the ledger table at least by fiscal year and accounting period.  Occasionally, list sub-partitioning by LEDGER can be effective.
            6.      The execution plan you really want Oracle to merge the selector tables before visiting the ledger table.  It can be difficult without manually adjusting statistics – have you read Wolfgang Brietling’s ‘Tuning by Cardinality Feedback’ – at least one example is drawn from nVision.
            7.      By default all objects in a PeoplesSoft database are set NOPARALLEL – however, depending on how you run your nVision reports you might want to experiment (cautiously) with a degree of parallelism on the LEDGER table. The risk is that you can end up with lots of concurrent nVision queries demanding parallel query slaves, and then you run out of PQ slaves and have processes waiting for a PQ slave process to become available.
            regards
            _________________________
            David Kurtz
            tel: +44 (0)7771 760660
             
            -----Original Message-----
            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of skilaurieski
            Sent: 06 November 2012 16:10
            To: psftdba@yahoogroups.com
            Subject: PeopleSoft DBA Forum nVision Performance issues on PeopleSoft Financials
            Hi,
            We have severe optimizer instability when running nVision reports on our Financials system.  We are on Oracle 10.2.0.5 and PeopleTools 8.49.  Reports against ps_ledger are fast some days and slow other days.  We are running stats on our most active ps_ledger partition every hour.  For nVision SQLs that are slow, I will run SQL Advisor and it always finds a much better profile.  After we pin the profile to that particular SQL, then that sql runs fast.  But we have to continually do this all day on various nVision SQL's as they are all slightly different.
            Has anyone else had a problem like this?
            GLPPOST runs every 10 mins and does DML on ps_ledger so that is why we run stats so often.  Problem gets worse with heavy load days at month-end. 
            Thanks in Advance,
            Laurie
             
             





          Your message has been successfully submitted and would be delivered to recipients shortly.