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

HR chartfield details link performance tuning

Expand Messages
  • yansunrise
    I m working on performance tuning HR8.9 PT8.46. Our HR system has a very bad performance experience on the link ChartField Detail . It takes 8 seconds to
    Message 1 of 7 , Jan 23, 2007
      I'm working on performance tuning HR8.9 PT8.46.

      Our HR system has a very bad performance experience on the link
      'ChartField Detail'. It takes 8 seconds to response. One of our users
      needs to click the link more than one hundred times every day, she is
      really unhappy.

      Navigation to the link:
      Set up HRMS -> Product Related ->Commitment Accounting -> Budget
      Information -> Department Budget Table USA -> Hit Search and Click any
      returned row

      Does your HR system have this performance problem? Do you have a
      solution for it.

      Thank you very much.
      yan sun
    • David Kurtz
      I suggest that you collect a Performance Monitor trace (at verbose level) on that action and work out where you are spending those 8 seconds. regards
      Message 2 of 7 , Jan 23, 2007
        I suggest that you collect a Performance Monitor trace (at verbose level) on that action and work out where you are spending those 8 seconds.
         
         

        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
        web: www.go-faster.co.uk
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        The PeopleSoft DBA Blog: http://psftdba.blogspot.com
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        -----Original Message-----
        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf Of yansunrise
        Sent: 23 January 2007 18:30
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum HR chartfield details link performance tuning

        I'm working on performance tuning HR8.9 PT8.46.

        Our HR system has a very bad performance experience on the link
        'ChartField Detail'. It takes 8 seconds to response. One of our users
        needs to click the link more than one hundred times every day, she is
        really unhappy.

        Navigation to the link:
        Set up HRMS -> Product Related ->Commitment Accounting -> Budget
        Information -> Department Budget Table USA -> Hit Search and Click any
        returned row

        Does your HR system have this performance problem? Do you have a
        solution for it.

        Thank you very much.
        yan sun

      • Bob Ellis
        Assuming you ve traced the component and identified the slow running sql(s) it would be helpful if you could post the sql(s) and the execution plans. You don t
        Message 3 of 7 , Jan 23, 2007

          Assuming you’ve traced the component and identified the slow running sql(s) it would be helpful if you could post the sql(s) and the execution plans.  You don’t mention what database you are on?

           

           


          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of yansunrise
          Sent: 23 January 2007 19:30
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum HR chartfield details link performance tuning

           

          I'm working on performance tuning HR8.9 PT8.46.

          Our HR system has a very bad performance experience on the link
          'ChartField Detail'. It takes 8 seconds to response. One of our users
          needs to click the link more than one hundred times every day, she is
          really unhappy.

          Navigation to the link:
          Set up HRMS -> Product Related ->Commitment Accounting -> Budget
          Information -> Department Budget Table USA -> Hit Search and Click any
          returned row

          Does your HR system have this performance problem? Do you have a
          solution for it.

          Thank you very much.
          yan sun

        • sabrecsuk
          Yan Sun, Agree with Bob that sql/execution plans are the only way to help technically. To me, it actually sounds like you might be after more of a functional
          Message 4 of 7 , Jan 24, 2007
            Yan Sun,

            Agree with Bob that sql/execution plans are the only way to help
            technically.

            To me, it actually sounds like you might be after more of a functional
            solution than a technical one. If one person has to click on the
            chartfield details link 100 times a day, surely there is a more
            effective method of working. Are they simply reviewing the data or
            making modifications within the results?

            If it is a simple review, then perhaps a reporting solution is a better
            option.

            If they are making modifications then are they in the right place?
            Setup HRMS is not a menu I would expect people to be going into daily
            unless they are configuring an initial installation. Is there another
            method of achieving the same end result?

            Cameron Smith
            Consultant
            Allinity (www.allinity.com)
          • yan.sun
            For database, we are using ORACLE 10.1.0.4.0. OS is AIX 5L 64 bits. Yes, I traced the component and found it is because of SQL SELECT COUNT(*) FROM
            Message 5 of 7 , Jan 24, 2007
              For database, we are using ORACLE 10.1.0.4.0. OS is AIX 5L 64 bits.

              Yes, I traced the component and found it is because of SQL

              SELECT COUNT(*) FROM PS_VALD_COMB_CD_VW WHERE SETID = :1.  It runs about 7 seconds every time.


              Here is the execution plan

               -----------------------------------------------------------------------------------------------------------
                     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2288598 Card=1 Bytes=2)
                 1    0   SORT (AGGREGATE)
                 2    1     VIEW OF 'PS_VALD_COMB_CD_VW' (VIEW) (Cost=2288598 Card=201098 Bytes=402196)
                 3    2       SORT (UNIQUE) (Cost=2288598 Card=201098 Bytes=26947132)
                 4    3         FILTER
                 5    4           TABLE ACCESS (FULL) OF 'PS_VALID_COMBO_TBL' (TABLE) (Cost=1065 Card=201098 Bytes=26947132
                 6    4           TABLE ACCESS (BY INDEX ROWID) OF 'PS_FS_CF_TMPLT_IMG' (TABLE) (Cost=20 Card=1 Bytes=14)
                 7    6             BITMAP CONVERSION (TO ROWIDS)
                 8    7               BITMAP OR
                 9    8                 BITMAP CONVERSION (FROM ROWIDS)
                10    9                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                11    8                 BITMAP CONVERSION (FROM ROWIDS)
                12   11                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                13    8                 BITMAP CONVERSION (FROM ROWIDS)
                14   13                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                15    8                 BITMAP CONVERSION (FROM ROWIDS)
                16   15                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                17    8                 BITMAP CONVERSION (FROM ROWIDS)
                18   17                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                19    8                 BITMAP CONVERSION (FROM ROWIDS)
                20   19                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                21    8                 BITMAP CONVERSION (FROM ROWIDS)
                22   21                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                23    8                 BITMAP CONVERSION (FROM ROWIDS)
                24   23                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                25    8                 BITMAP CONVERSION (FROM ROWIDS)
                26   25                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                27    8                 BITMAP CONVERSION (FROM ROWIDS)
                28   27                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                29    8                 BITMAP CONVERSION (FROM ROWIDS)
                30   29                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                31    8                 BITMAP CONVERSION (FROM ROWIDS)
                32   31                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                33    8                 BITMAP CONVERSION (FROM ROWIDS)
                34   33                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                35    8                 BITMAP CONVERSION (FROM ROWIDS)
                36   35                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                37    8                 BITMAP CONVERSION (FROM ROWIDS)
                38   37                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)


              To improve the performance, I took off the 'distinct' code from PS_VALD_COMB_CD_VW,  because for our system the combination of  (FDM_COMBO_CD, EFFDT) in table PS_VALID_COMBO_TBL is always unique. And I dropped the indexes on table PS_FS_CF_TMPLT_IMG. With these two change, the performance is improved to 3 seconds.


              I also tried rewriting the view
              PS_VALID_COMBO_VW to

              SELECT SETID ,A.PROCESS_GROUP ,A.FDM_HASH ,A.EFFDT ,A.EFF_STATUS ,A.FDM_COMBO_CD ,A.DESCR ,A.DESCRSHORT ,A.ACCOUNT ,A.DEPTID_CF ,A.PROJECT_ID ,A.PRODUCT ,A.FUND_CODE ,A.PROGRAM_CODE ,A.CLASS_FLD ,A.AFFILIATE ,A.OPERATING_UNIT ,A.ALTACCT ,A.BUDGET_REF ,A.CHARTFIELD1 ,A.CHARTFIELD2 ,A.CHARTFIELD3 ,A.AFFILIATE_INTRA1 ,A.AFFILIATE_INTRA2 ,A.VALID_FLAG

              FROM PS_VALID_COMBO_TBL A

              WHERE A.VALID_FLAG = 'Y'

              AND  A. AFFILIATE_INTRA1 =' '

              AND A. ALTACCT = ' '

              AND A. BUDGET_REF=' '

              AND A. CHARTFIELD3=' '


              Looking up table PS_FS_CF_TMPLT_IMG, I found the four inactive chart fields in our system. I was told by our functional person that chartfield configuration won't be changed after the system went alive. This change improve the performance to 0.5 second.


              I opened a GSC case talking about our problem and the change I want to make. They suggested me to use index and hint to improve the performance and  told me no one has reported this performance issue except our institution. This makes me think why only we have this performance issue?


              I forwarded reply from Cameron Smith to our functional professionals. Thanks.



              PS_VALD_COMB_CD_VW

              SELECT DISTINCT A.SETID , A.FDM_COMBO_CD ,

               A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.ACCOUNT ,

               A.DEPTID_CF , A.PROJECT_ID , A.PRODUCT , A.FUND_CODE , A.PROGRAM_CODE

               , A.CLASS_FLD , A.AFFILIATE , A.OPERATING_UNIT , A.ALTACCT ,

               A.BUDGET_REF , A.CHARTFIELD1 , A.CHARTFIELD2 , A.CHARTFIELD3 ,

               A.AFFILIATE_INTRA1 , A.AFFILIATE_INTRA2

              FROM PS_VALID_COMBO_VW A

               

              PS_VALID_COMBO_VW

              SELECT SETID ,A.PROCESS_GROUP ,A.FDM_HASH ,A.EFFDT ,A.EFF_STATUS ,A.FDM_COMBO_CD ,A.DESCR ,A.DESCRSHORT ,A.ACCOUNT ,A.DEPTID_CF ,A.PROJECT_ID ,A.PRODUCT ,A.FUND_CODE ,A.PROGRAM_CODE ,A.CLASS_FLD ,A.AFFILIATE ,A.OPERATING_UNIT ,A.ALTACCT ,A.BUDGET_REF ,A.CHARTFIELD1 ,A.CHARTFIELD2 ,A.CHARTFIELD3 ,A.AFFILIATE_INTRA1 ,A.AFFILIATE_INTRA2 ,A.VALID_FLAG

              FROM PS_VALID_COMBO_TBL A

              WHERE A.VALID_FLAG = 'Y'

              AND NOT EXISTS ( SELECT 'X'

                                           FROM PS_FS_CF_TMPLT_IMG F

                                           WHERE (F.ORIG_CF_NAME = 'CHARTFIELD3'           AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD3 <> ' ' )

                                                   OR (F.ORIG_CF_NAME = 'CHARTFIELD2'            AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD2 <> ' ' )

                                                     OR (F.ORIG_CF_NAME = 'CHARTFIELD1'            AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD1 <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'AFFILIATE_INTRA1'  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE_INTRA1 <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'AFFILIATE_INTRA2'  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE_INTRA2 <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'BUDGET_REF'             AND F.CF_ACTIVE_STATUS = 'I'  AND A.BUDGET_REF <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'ALTACCT'                    AND F.CF_ACTIVE_STATUS = 'I'  AND A.ALTACCT <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'OPERATING_UNIT'    AND F.CF_ACTIVE_STATUS = 'I'  AND A.OPERATING_UNIT <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'AFFILIATE'                  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'CLASS_FLD'                 AND F.CF_ACTIVE_STATUS = 'I'  AND A.CLASS_FLD <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'PROGRAM_CODE'      AND F.CF_ACTIVE_STATUS = 'I'  AND A.PROGRAM_CODE <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'FUND_CODE'               AND F.CF_ACTIVE_STATUS = 'I'  AND A.FUND_CODE <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'PRODUCT'                    AND F.CF_ACTIVE_STATUS = 'I'  AND A.PRODUCT <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'PROJECT_ID'                AND F.CF_ACTIVE_STATUS = 'I'  AND A.PROJECT_ID <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'DEPTID_CF'                  AND F.CF_ACTIVE_STATUS = 'I'  AND A.DEPTID_CF <> ' ' ))




              Thank  you very  much
              yan sun



              Bob Ellis wrote:

              Assuming you’ve traced the component and identified the slow running sql(s) it would be helpful if you could post the sql(s) and the execution plans.  You don’t mention what database you are on?

               

               


              From: psftdba@yahoogroups .com [mailto:psftdba@ yahoogroups. com] On Behalf Of yansunrise
              Sent: 23 January 2007 19:30
              To: psftdba@yahoogroups .com
              Subject: PeopleSoft DBA Forum HR chartfield details link performance tuning

               

              I'm working on performance tuning HR8.9 PT8.46.

              Our HR system has a very bad performance experience on the link
              'ChartField Detail'. It takes 8 seconds to response. One of our users
              needs to click the link more than one hundred times every day, she is
              really unhappy.

              Navigation to the link:
              Set up HRMS -> Product Related ->Commitment Accounting -> Budget
              Information -> Department Budget Table USA -> Hit Search and Click any
              returned row

              Does your HR system have this performance problem? Do you have a
              solution for it.

              Thank you very much.
              yan sun


            • yansunrise
              Cameron, I forwarded your reply to our functional professionals. They told me because of some special properties of our institution, such as we are a
              Message 6 of 7 , Jan 25, 2007
                Cameron,
                I forwarded your reply to our functional professionals.
                They told me because of some special properties of our institution,
                such as we are a university and we have a hospital, some employees'
                salary distribution change very often. This is why we use the pages
                under setup HRMS so often.
                I believe for most companies, their employees’ salary distribution
                never change.

                Thank you very much.
                yan sun

                --- In psftdba@yahoogroups.com, "sabrecsuk" <cameron@...> wrote:
                >
                > Yan Sun,
                >
                > Agree with Bob that sql/execution plans are the only way to help
                > technically.
                >
                > To me, it actually sounds like you might be after more of a
                functional
                > solution than a technical one. If one person has to click on the
                > chartfield details link 100 times a day, surely there is a more
                > effective method of working. Are they simply reviewing the data or
                > making modifications within the results?
                >
                > If it is a simple review, then perhaps a reporting solution is a
                better
                > option.
                >
                > If they are making modifications then are they in the right place?
                > Setup HRMS is not a menu I would expect people to be going into
                daily
                > unless they are configuring an initial installation. Is there
                another
                > method of achieving the same end result?
                >
                > Cameron Smith
                > Consultant
                > Allinity (www.allinity.com)
                >
              • yansunrise
                For database, we are using ORACLE 10.1.0.4.0. OS is AIX 5L 64 bits. Yes, I traced the component and found it is because of SQL SELECT COUNT(*) FROM
                Message 7 of 7 , Jan 25, 2007
                  For database, we are using ORACLE 10.1.0.4.0. OS is AIX 5L 64 bits.

                  Yes, I traced the component and found it is because of SQL

                  SELECT COUNT(*) FROM PS_VALD_COMB_CD_VW WHERE SETID = :1.  It runs about 7 seconds every time.


                  Here is the execution plan

                   -----------------------------------------------------------------------------------------------------------
                         0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2288598 Card=1 Bytes=2)
                     1    0   SORT (AGGREGATE)
                     2    1     VIEW OF 'PS_VALD_COMB_CD_VW' (VIEW) (Cost=2288598 Card=201098 Bytes=402196)
                     3    2       SORT (UNIQUE) (Cost=2288598 Card=201098 Bytes=26947132)
                     4    3         FILTER
                     5    4           TABLE ACCESS (FULL) OF 'PS_VALID_COMBO_TBL' (TABLE) (Cost=1065 Card=201098 Bytes=26947132
                     6    4           TABLE ACCESS (BY INDEX ROWID) OF 'PS_FS_CF_TMPLT_IMG' (TABLE) (Cost=20 Card=1 Bytes=14)
                     7    6             BITMAP CONVERSION (TO ROWIDS)
                     8    7               BITMAP OR
                     9    8                 BITMAP CONVERSION (FROM ROWIDS)
                    10    9                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    11    8                 BITMAP CONVERSION (FROM ROWIDS)
                    12   11                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    13    8                 BITMAP CONVERSION (FROM ROWIDS)
                    14   13                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    15    8                 BITMAP CONVERSION (FROM ROWIDS)
                    16   15                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    17    8                 BITMAP CONVERSION (FROM ROWIDS)
                    18   17                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    19    8                 BITMAP CONVERSION (FROM ROWIDS)
                    20   19                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    21    8                 BITMAP CONVERSION (FROM ROWIDS)
                    22   21                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    23    8                 BITMAP CONVERSION (FROM ROWIDS)
                    24   23                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    25    8                 BITMAP CONVERSION (FROM ROWIDS)
                    26   25                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    27    8                 BITMAP CONVERSION (FROM ROWIDS)
                    28   27                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    29    8                 BITMAP CONVERSION (FROM ROWIDS)
                    30   29                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    31    8                 BITMAP CONVERSION (FROM ROWIDS)
                    32   31                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    33    8                 BITMAP CONVERSION (FROM ROWIDS)
                    34   33                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    35    8                 BITMAP CONVERSION (FROM ROWIDS)
                    36   35                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)
                    37    8                 BITMAP CONVERSION (FROM ROWIDS)
                    38   37                   INDEX (RANGE SCAN) OF 'PS_FS_CF_TMPLT_IMG' (INDEX (UNIQUE)) (Cost=0)


                  To improve the performance, I took off the 'distinct' code from PS_VALD_COMB_CD_VW,  because for our system the combination of  (FDM_COMBO_CD, EFFDT) in table PS_VALID_COMBO_TBL is always unique. And I dropped the indexes on table PS_FS_CF_TMPLT_IMG. With these two change, the performance is improved to 3 seconds.


                  I also tried rewriting the view
                  PS_VALID_COMBO_VW to

                  SELECT SETID ,A.PROCESS_GROUP ,A.FDM_HASH ,A.EFFDT ,A.EFF_STATUS ,A.FDM_COMBO_CD ,A.DESCR ,A.DESCRSHORT ,A.ACCOUNT ,A.DEPTID_CF ,A.PROJECT_ID ,A.PRODUCT ,A.FUND_CODE ,A.PROGRAM_CODE ,A.CLASS_FLD ,A.AFFILIATE ,A.OPERATING_UNIT ,A.ALTACCT ,A.BUDGET_REF ,A.CHARTFIELD1 ,A.CHARTFIELD2 ,A.CHARTFIELD3 ,A.AFFILIATE_INTRA1 ,A.AFFILIATE_INTRA2 ,A.VALID_FLAG

                  FROM PS_VALID_COMBO_TBL A

                  WHERE A.VALID_FLAG = 'Y'

                  AND  A. AFFILIATE_INTRA1 =' '

                  AND A. ALTACCT = ' '

                  AND A. BUDGET_REF=' '

                  AND A. CHARTFIELD3=' '


                  Looking up table PS_FS_CF_TMPLT_IMG, I found the four inactive chart fields in our system. I was told by our functional person that chartfield configuration won't be changed after the system went alive. This change improve the performance to 0.5 second.


                  I opened a GSC case talking about our problem and the change I want to make. They suggested me to use index and hint to improve the performance and  told me no one has reported this performance issue except our institution. This makes me think why only we have this performance issue?


                  I forwarded reply from Cameron Smith to our functional professionals. Thanks.



                  PS_VALD_COMB_CD_VW

                  SELECT DISTINCT A.SETID , A.FDM_COMBO_CD ,

                   A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.ACCOUNT ,

                   A.DEPTID_CF , A.PROJECT_ID , A.PRODUCT , A.FUND_CODE , A.PROGRAM_CODE

                   , A.CLASS_FLD , A.AFFILIATE , A.OPERATING_UNIT , A.ALTACCT ,

                   A.BUDGET_REF , A.CHARTFIELD1 , A.CHARTFIELD2 , A.CHARTFIELD3 ,

                   A.AFFILIATE_INTRA1 , A.AFFILIATE_INTRA2

                  FROM PS_VALID_COMBO_VW A

                   

                  PS_VALID_COMBO_VW

                  SELECT SETID ,A.PROCESS_GROUP ,A.FDM_HASH ,A.EFFDT ,A.EFF_STATUS ,A.FDM_COMBO_CD ,A.DESCR ,A.DESCRSHORT ,A.ACCOUNT ,A.DEPTID_CF ,A.PROJECT_ID ,A.PRODUCT ,A.FUND_CODE ,A.PROGRAM_CODE ,A.CLASS_FLD ,A.AFFILIATE ,A.OPERATING_UNIT ,A.ALTACCT ,A.BUDGET_REF ,A.CHARTFIELD1 ,A.CHARTFIELD2 ,A.CHARTFIELD3 ,A.AFFILIATE_INTRA1 ,A.AFFILIATE_INTRA2 ,A.VALID_FLAG

                  FROM PS_VALID_COMBO_TBL A

                  WHERE A.VALID_FLAG = 'Y'

                  AND NOT EXISTS ( SELECT 'X'

                                               FROM PS_FS_CF_TMPLT_IMG F

                                               WHERE (F.ORIG_CF_NAME = 'CHARTFIELD3'           AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD3 <> ' ' )

                                                       OR (F.ORIG_CF_NAME = 'CHARTFIELD2'            AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD2 <> ' ' )

                                                         OR (F.ORIG_CF_NAME = 'CHARTFIELD1'            AND F.CF_ACTIVE_STATUS = 'I'  AND A.CHARTFIELD1 <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'AFFILIATE_INTRA1'  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE_INTRA1 <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'AFFILIATE_INTRA2'  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE_INTRA2 <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'BUDGET_REF'             AND F.CF_ACTIVE_STATUS = 'I'  AND A.BUDGET_REF <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'ALTACCT'                    AND F.CF_ACTIVE_STATUS = 'I'  AND A.ALTACCT <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'OPERATING_UNIT'    AND F.CF_ACTIVE_STATUS = 'I'  AND A.OPERATING_UNIT <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'AFFILIATE'                  AND F.CF_ACTIVE_STATUS = 'I'  AND A.AFFILIATE <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'CLASS_FLD'                 AND F.CF_ACTIVE_STATUS = 'I'  AND A.CLASS_FLD <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'PROGRAM_CODE'      AND F.CF_ACTIVE_STATUS = 'I'  AND A.PROGRAM_CODE <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'FUND_CODE'               AND F.CF_ACTIVE_STATUS = 'I'  AND A.FUND_CODE <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'PRODUCT'                    AND F.CF_ACTIVE_STATUS = 'I'  AND A.PRODUCT <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'PROJECT_ID'                AND F.CF_ACTIVE_STATUS = 'I'  AND A.PROJECT_ID <> ' ' )

                                                           OR (F.ORIG_CF_NAME = 'DEPTID_CF'                  AND F.CF_ACTIVE_STATUS = 'I'  AND A.DEPTID_CF <> ' ' ))




                  Thank  you very  much
                  yan sun


                  --- In psftdba@yahoogroups.com, "Bob Ellis" <robert.ellis@...> wrote:
                  >
                  > Assuming you've traced the component and identified the slow running sql(s)
                  > it would be helpful if you could post the sql(s) and the execution plans.
                  > You don't mention what database you are on?
                  >
                  >
                  >
                  >
                  >
                  > _____
                  >
                  > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of
                  > yansunrise
                  > Sent: 23 January 2007 19:30
                  > To: psftdba@yahoogroups.com
                  > Subject: PeopleSoft DBA Forum HR chartfield details link performance tuning
                  >
                  >
                  >
                  > I'm working on performance tuning HR8.9 PT8.46.
                  >
                  > Our HR system has a very bad performance experience on the link
                  > 'ChartField Detail'. It takes 8 seconds to response. One of our users
                  > needs to click the link more than one hundred times every day, she is
                  > really unhappy.
                  >
                  > Navigation to the link:
                  > Set up HRMS -> Product Related ->Commitment Accounting -> Budget
                  > Information -> Department Budget Table USA -> Hit Search and Click any
                  > returned row
                  >
                  > Does your HR system have this performance problem? Do you have a
                  > solution for it.
                  >
                  > Thank you very much.
                  > yan sun
                  >
                Your message has been successfully submitted and would be delivered to recipients shortly.