1 2 Previous Next 17 Replies Latest reply: Oct 4, 2008 10:05 AM by Jonathan Lewis RSS

    Parse CPU to Parse Elapsd

    user00726
      HI,

      I have a question our DB is showing
      Parse CPU to Parse Elapsd %:  is very less 8.37. 
      .

      What should be done to overcome this problem....

      actually we are facing a latch free wait event.....iand i have also identofy hot blocks....but i donlt know waht should be the next step...

      can anybody suggest Pase CPU to Parse elapsed
        • 1. Re: Parse CPU to Parse Elapsd
          247514
          You might want to post more of your statspack report.
          Check the SQL statement that use most resources. Do you have queries not using bind variable issue?

          CPU parse and latch free event not quite related to hot blocks.
          • 2. Re: Parse CPU to Parse Elapsd
            user00726
                         Snap Id     Snap Time      Sessions Curs/Sess Comment
                        --------- ------------------ -------- --------- -------------------
            Begin Snap:        21 25-Sep-08 14:04:27  #######        .0
              End Snap:        24 25-Sep-08 14:33:23  #######        .0
               Elapsed:               28.93 (mins)

            Cache Sizes (end)
            ~~~~~~~~~~~~~~~~~
                           Buffer Cache:     2,512M      Std Block Size:          8K
                       Shared Pool Size:       608M          Log Buffer:        977K

            Load Profile
            ~~~~~~~~~~~~                            Per Second       Per Transaction
                                               ---------------       ---------------
                              Redo size:             13,586.21             20,817.00
                          Logical reads:             14,877.99             22,796.28
                          Block changes:                 42.25                 64.74
                         Physical reads:              1,356.41              2,078.31
                        Physical writes:                  5.98                  9.16
                             User calls:                193.72                296.82
                                 Parses:                 25.57                 39.17
                            Hard parses:                  0.29                  0.44
                                  Sorts:                  6.76                 10.36
                                 Logons:                  0.04                  0.06
                               Executes:                 75.31                115.39
                           Transactions:                  0.65

              % Blocks changed per Read:    0.28    Recursive Call %:     30.62
            Rollback per transaction %:   17.30       Rows per Sort:    156.05

            Instance Efficiency Percentages (Target 100%)
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                        Buffer Nowait %:   99.99       Redo NoWait %:    100.00
                        Buffer  Hit   %:   90.90    In-memory Sort %:     99.99
                        Library Hit   %:   99.66        Soft Parse %:     98.87
                     Execute to Parse %:   66.05         Latch Hit %:     99.79
            Parse CPU to Parse Elapsd %:    7.74     % Non-Parse CPU:     99.60

            Shared Pool Statistics        Begin   End
                                           ------  ------
                         Memory Usage %:   90.88   90.53
                % SQL with executions>1:   78.36   78.39
              % Memory for SQL w/exec>1:   77.65   77.75

            Top 5 Timed Events
            ~~~~~~~~~~~~~~~~~~                                                     % Total
            Event                                               Waits    Time (s) Ela Time
            -------------------------------------------- ------------ ----------- --------
            latch free                                        104,237       5,601    37.53
            CPU time                                                        2,922    19.58
            db file sequential read                            93,077       1,840    12.33
            global cache cr request                           417,985       1,625    10.89
            db file parallel read                              14,526       1,221     8.18
                      -------------------------------------------------------------
            some of the sql statements
            SQL ordered by Gets for DB: AI  Instance: AI11  Snaps: 21 -24
            -> End Buffer Gets Threshold:     10000
            -> Note that resources reported for PL/SQL includes the resources used by
               all SQL statements called within the PL/SQL code.  As individual SQL
               statements are also reported, it is possible and valid for the summed
               total % to exceed 100

                                                                 CPU      Elapsd
              Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
            --------------- ------------ -------------- ------ -------- --------- ----------
                  6,521,823           33      197,631.0   25.3   277.94   3275.94   17574787
            SELECT /*+ INDEX (OM_COURSE_FEE OCF_CFC_CODE)  */DISTINCT CF_COU
            RSE_CODE   FROM OM_COURSE_FEE,OT_STUDENT_FEE_COL_HEAD,OT_STUDENT
            FEECOL_DETL  WHERE SFCH_SYS_ID = SFCD_SFCH_SYS_ID  AND SFCD_FE
            E_TYPE_CODE = CF_TYPE_CODE  AND CF_COURSE_CODE IN ( 'PE1','PE2',
            'CCT','CPT'  ) AND SFCH_TXN_CODE = :b1  AND SFCD_SUBSCRBE_JOURNA

                  4,062,487          981        4,141.2   15.7   498.49   5742.26  782969499
            SELECT COUNT(ROWID) FROM OT_PENDENCY_DETL WHERE PED_PD_CODE = :B
            3 AND PED_TXN_CODE = :B2 AND PED_PH_SYS_ID = :B1

                  2,645,629           43       61,526.3   10.2    41.62    501.22 3102119461
            SELECT ROWID,AR_DT,AR_TXN_CODE,AR_NO,AR_AMD_NO,AR_REF_FROM,AR_RE
            F_TXN_CODE,AR_REF_NO,AR_DAK_CODE,AR_DED_SYS_ID,AR_COMP_CODE,AR_A
            CNT_YR,AR_AMD_DT,AR_AMD_UID,AR_AMD_RES_CODE,AR_REF_SYS_ID,AR_STU
            D_SRN,AR_APPL_DT,AR_ISSUE_DT,AR_ENROL_FRM_COURSE,AR_ENROL_DT,AR_
            JOURNAL_YN,AR_TYPE,AR_TITLE,AR_VACANCY_CODE,AR_MRH_MRN,AR_INDUST

                  1,820,705           79       23,046.9    7.0    14.03    140.44   23088203
            INSERT INTO OT_MEM_FEE_COL_DETL(MFCD_FEE_TYPE,MFCD_CONDON_FEE_YN
            ,MFCD_EXCESS_USED_YN,MFCD_CONDN_CODE,MFCD_PM_CODE,MFCD_CURR_CODE
            ,MFCD_INSTR_NO,MFCD_INSTR_DT,MFCD_AMT,MFCD_BANK_CODE,MFCD_INSTR_
            TYPE,MFCD_BRANCH,MFCD_COLLECTION,MFCD_FM_DT,MFCD_TO_DT,MFCD_RES_
            CODE,MFCD_CR_UID,MFCD_CR_DT,MFCD_UPD_UID,MFCD_UPD_DT,MFCD_CONDON
            Sql ordered by parse calls
            SQL ordered by Parse Calls for DB: AI  Instance: AI11  Snaps: 21 -24
            -> End Parse Calls Threshold:      1000

                                       % Total
            Parse Calls  Executions   Parses  Hash Value
            ------------ ------------ -------- ----------
            ND DED_DAK_CODE = :b2  AND DED_NAME = :b3  AND DED_SYS_ID = :b4

                     229          278     0.52 1458860745
            SELECT COURSE_TYPE   FROM OM_COURSE  WHERE COURSE_CODE = :b1

                     221          322     0.50 2069927382
            SELECT MD_MEM_STATUS,MD_MEM_TYPE,MD_COP_STATUS   FROM OS_MEM_DET
            L  WHERE MD_MRH_MRN = :b1
            Edited by: user00726 on Sep 28, 2008 10:04 PM

            Edited by: user00726 on Sep 28, 2008 11:54 PM
            • 3. Re: Parse CPU to Parse Elapsd
              user00726
              If i would increase the value of session_cached_cursors to 50 as it is a default value since the current value of session_cached_cursors value is 0.does it improve the % of parse cpu to parse elapsed.....

              SQL> show parameter session

              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ----------------------
              java_max_sessionspace_size           integer     0
              java_soft_sessionspace_limit         integer     0
              license_max_sessions                 integer     0
              license_sessions_warning             integer     0
              logmnr_max_persistent_sessions       integer     1
              mts_sessions                         integer     0
              session_cached_cursors               integer     0
              session_max_open_files               integer     10
              sessions                             integer     555
              shared_server_sessions               integer     0
              SQL> show parameter open

              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ----------------------
              open_cursors                         integer     20000
              open_links                           integer     4
              open_links_per_instance              integer     4
              read_only_open_delayed               boolean     FALSE
              session_max_open_files               integer     10
              SQL>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
              • 4. Re: Parse CPU to Parse Elapsd
                Jonathan Lewis
                Duplicate thread:

                Re: Cache buffer chain problem

                Which one do you want people to reply to ?

                On my machine, the statspack output is mangled - have you used the "code" "/code" tags, or the "pre" "/pre". "Code" seems to work better than "pre".

                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk

                "The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." Stephen Hawking.
                • 5. Re: Parse CPU to Parse Elapsd
                  user00726
                  YOU CAN REPLY TO BOTH OF THE QUESTIONS...IF YOU DON'T MIND
                  • 6. Re: Parse CPU to Parse Elapsd
                    Charles Hooper
                    user00726 wrote:
                    HI,

                    I have a question our DB is showing Parse CPU to Parse Elapsd %: is very less 8.37.

                    What should be done to overcome this problem....

                    actually we are facing a latch free wait event.....iand i have also identofy hot blocks....but i donlt know waht should be the next step...

                    can anybody suggest Pase CPU to Parse elapsed
                    I believe that Jonathan suggested in the other thread that you should take a look at the LATCH FREE wait event to determine the specific latches with a large number of sleeps. Start here:
                    http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#12946

                    In SQL*Plus:
                    SQL> DESC V$LATCH_CHILDREN
                     Name
                     --------------------------
                     ADDR
                     LATCH#
                     CHILD#
                     LEVEL#
                     NAME
                     HASH
                     GETS
                     MISSES
                     SLEEPS
                     IMMEDIATE_GETS
                     IMMEDIATE_MISSES
                     WAITERS_WOKEN
                     WAITS_HOLDING_LATCH
                     SPIN_GETS
                     SLEEP1
                     SLEEP2
                     SLEEP3
                     SLEEP4
                     SLEEP5
                     SLEEP6
                     SLEEP7
                     SLEEP8
                     SLEEP9
                     SLEEP10
                     SLEEP11
                     WAIT_TIME
                    If the detailed latch information does not appear in the Statspack report, capture the output of a "SELECT * FROM V$LATCH_CHILDREN;", wait 10 minutes, then generate a second capture from that table. Subtract the first set of values from the second set of values (match LATCH# and CHILD# between the two captures), and reply back to the forum with the NAME, LATCH#, and CHILD# with the five highest number of SLEEPS (include the number of GETS, MISSES, and SLEEPS).

                    You may want to look closely at the SQL statements with the highest number of BUFFER GETS to see if there is a problem. The top SQL statement sorted by buffer gets is forcing an index access path, and another SQL statement is an INSERT which performs 23,047 buffer gets per execution (missing foreign key index?).

                    You might try posting the output of the following:
                    SELECT
                      SUBSTR(NAME,1,30) NAME,
                      SUBSTR(VALUE,1,40) VALUE
                    FROM
                      V$PARAMETER
                    ORDER BY
                      NAME;
                    Reposting the section of the Statspack that you provided:
                    Begin Snap:        21 25-Sep-08 14:04:27  #######        .0
                      End Snap:        24 25-Sep-08 14:33:23  #######        .0
                       Elapsed:               28.93 (mins)
                    
                    Cache Sizes (end)
                    ~~~~~~~~~~~~~~~~~
                                  Buffer Cache:     2,512M      Std Block Size:          8K
                              Shared Pool Size:       608M          Log Buffer:        977K
                    
                    Load Profile
                    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                     Redo size:             13,586.21             20,817.00
                                 Logical reads:             14,877.99             22,796.28
                                 Block changes:                 42.25                 64.74
                                Physical reads:              1,356.41              2,078.31
                               Physical writes:                  5.98                  9.16
                                    User calls:                193.72                296.82
                                        Parses:                 25.57                 39.17
                                   Hard parses:                  0.29                  0.44
                                         Sorts:                  6.76                 10.36
                                        Logons:                  0.04                  0.06
                                      Executes:                 75.31                115.39
                                  Transactions:                  0.65
                    
                     % Blocks changed per Read:    0.28    Recursive Call %:     30.62
                    Rollback per transaction %:   17.30       Rows per Sort:    156.05
                    
                    Instance Efficiency Percentages (Target 100%)
                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                               Buffer Nowait %:   99.99       Redo NoWait %:    100.00
                               Buffer  Hit   %:   90.90    In-memory Sort %:     99.99
                               Library Hit   %:   99.66        Soft Parse %:     98.87
                            Execute to Parse %:   66.05         Latch Hit %:     99.79
                    Parse CPU to Parse Elapsd %:    7.74     % Non-Parse CPU:     99.60
                    
                    Shared Pool Statistics        Begin   End
                    ------
                                Memory Usage %:   90.88   90.53
                       % SQL with executions>1:   78.36   78.39
                     % Memory for SQL w/exec>1:   77.65   77.75
                    
                    Top 5 Timed Events
                    ~~~~~~~~~~~~~~~~~~                                                     % Total
                    Event                                               Waits    Time (s) Ela Time
                    ----------------------------------------------------
                    latch free                                        104,237       5,601    37.53
                    CPU time                                                        2,922    19.58
                    db file sequential read                            93,077       1,840    12.33
                    global cache cr request                           417,985       1,625    10.89
                    db file parallel read                              14,526       1,221     8.18
                             -------------------------------------------------------------
                    
                    some of the sql statements
                    
                    SQL ordered by Gets for DB: AI  Instance: AI11  Snaps: 21 -24
                    
                    End Buffer Gets Threshold: 10000
                    Note that resources reported for PL/SQL includes the resources used by
                      all SQL statements called within the PL/SQL code.  As individual SQL   statements are also reported, it is possible and valid for the summed   total % to exceed 100                                                     CPU      Elapsd Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value      6,521,823           33      197,631.0   25.3   277.94   3275.94   17574787 SELECT /*+ INDEX (OM_COURSE_FEE OCF_CFC_CODE)  */DISTINCT CF_COU RSE_CODE   FROM OM_COURSE_FEE,OT_STUDENT_FEE_COL_HEAD,OT_STUDENT _FEE_COL_DETL  WHERE SFCH_SYS_ID = SFCD_SFCH_SYS_ID  AND SFCD_FE E_TYPE_CODE = CF_TYPE_CODE  AND CF_COURSE_CODE IN ( 'PE1','PE2', 'CCT','CPT'  ) AND SFCH_TXN_CODE = :b1  AND SFCD_SUBSCRBE_JOURNA      4,062,487          981        4,141.2   15.7   498.49   5742.26  782969499 SELECT COUNT(ROWID) FROM OT_PENDENCY_DETL WHERE PED_PD_CODE = :B 3 AND PED_TXN_CODE = :B2 AND PED_PH_SYS_ID = :B1      2,645,629           43       61,526.3   10.2    41.62    501.22 3102119461 SELECT ROWID,AR_DT,AR_TXN_CODE,AR_NO,AR_AMD_NO,AR_REF_FROM,AR_RE F_TXN_CODE,AR_REF_NO,AR_DAK_CODE,AR_DED_SYS_ID,AR_COMP_CODE,AR_A CNT_YR,AR_AMD_DT,AR_AMD_UID,AR_AMD_RES_CODE,AR_REF_SYS_ID,AR_STU D_SRN,AR_APPL_DT,AR_ISSUE_DT,AR_ENROL_FRM_COURSE,AR_ENROL_DT,AR_ JOURNAL_YN,AR_TYPE,AR_TITLE,AR_VACANCY_CODE,AR_MRH_MRN,AR_INDUST      1,820,705           79       23,046.9    7.0    14.03    140.44   23088203 INSERT INTO OT_MEM_FEE_COL_DETL(MFCD_FEE_TYPE,MFCD_CONDON_FEE_YN ,MFCD_EXCESS_USED_YN,MFCD_CONDN_CODE,MFCD_PM_CODE,MFCD_CURR_CODE ,MFCD_INSTR_NO,MFCD_INSTR_DT,MFCD_AMT,MFCD_BANK_CODE,MFCD_INSTR_ TYPE,MFCD_BRANCH,MFCD_COLLECTION,MFCD_FM_DT,MFCD_TO_DT,MFCD_RES_ CODE,MFCD_CR_UID,MFCD_CR_DT,MFCD_UPD_UID,MFCD_UPD_DT,MFCD_CONDON Sql ordered by parse calls SQL ordered by Parse Calls for DB: AI  Instance: AI11  Snaps: 21 -24
                    End Parse Calls Threshold: 1000
                                              % Total Parse Calls  Executions   Parses  Hash Value ND DED_DAK_CODE = :b2  AND DED_NAME = :b3  AND DED_SYS_ID = :b4         229          278     0.52 1458860745 SELECT COURSE_TYPE   FROM OM_COURSE  WHERE COURSE_CODE = :b1         221          322     0.50 2069927382 SELECT MD_MEM_STATUS,MD_MEM_TYPE,MD_COP_STATUS   FROM OS_MEM_DET L  WHERE MD_MRH_MRN = :b1
                    Charles Hooper
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.

                    Edited by: Charles Hooper on Sep 29, 2008 7:54 AM
                    Changed square brackets around code to curly brackets to improve formatting.
                    • 7. Re: Parse CPU to Parse Elapsd
                      user00726
                      NAME                                                                 LATCH#     
                      ---------------------------------------------------------------- ----------     
                          CHILD#       GETS     MISSES     SLEEPS                                     
                      ---------- ---------- ---------- ----------                                     
                      cache buffers chains                                                     98     
                            1343 1081203632     492704      46912                                     
                                                                                                      
                      cache buffers chains                                                     98     
                             510  621275363     101770      59618                                     
                                                                                                      
                      cache buffers chains                                                     98     
                            2301 1587834610     108901      63320                                     
                                                                                                      
                      
                      NAME                                                                 LATCH#     
                      ---------------------------------------------------------------- ----------     
                          CHILD#       GETS     MISSES     SLEEPS                                     
                      ---------- ---------- ---------- ----------                                     
                      ges enqueue table freelist                                               53     
                               2  171415075     530781      84887                                     
                                                                                                      
                      KCL name table parent latch                                             119     
                               7  139898421     159169      97373                                     
                                                                                                      
                      KCL name table parent latch                                             119     
                               3  141122585     160531      97418                              
                      NAME                                                                 LATCH#     
                      ---------------------------------------------------------------- ----------     
                          CHILD#       GETS     MISSES     SLEEPS                                     
                      ---------- ---------- ---------- ----------                                     
                      KCL gc element parent latch                                             118     
                               1  704376044    1299274     297905                                     
                                                                                                      
                      KCL gc element parent latch                                             118     
                               6  722214938    1363956     302500                                     
                                                                                                      
                      KCL gc element parent latch                                             118     
                               4 1633306777    4366125     818657                                     
                                                                                                      
                      
                      NAME                                                                 LATCH#     
                      ---------------------------------------------------------------- ----------     
                          CHILD#       GETS     MISSES     SLEEPS                                     
                      ---------- ---------- ---------- ----------                                     
                      KCL gc element parent latch                                             118     
                               2 1596883300    4469190     821856                                     
                                                                                                      
                      KCL gc element parent latch                                             118     
                               7 1617592694    4400345     845935                                     
                                                                                                      
                      KCL gc element parent latch                                             118     
                               5 1617244638    4816969     910145                                     
                                                                                                      
                      
                      NAME                                                                 LATCH#     
                      ---------------------------------------------------------------- ----------     
                          CHILD#       GETS     MISSES     SLEEPS                                     
                      ---------- ---------- ---------- ----------                                     
                      KCL freelist parent latch                                               120     
                               1 3357378187   10143242     963554                                     
                                                                                                      
                      KCL freelist parent latch                                               120     
                               2 3343547419   10101839     977779                                     
                                                                                                      
                      cache buffers lru chain                                                  93     
                               3 2270638130   22606426    7456673                                     
                                                                                                      
                      
                      9573 rows selected.
                      
                      SQL> spool off
                      • 8. Re: Parse CPU to Parse Elapsd
                        Charles Hooper
                        user00726,

                        It looks like you were almost able to post the output in a readable format. It appears that the forum software does not like /code in curly brackets - I also had difficulty with that on the second try after finding that the square brackets did not work as expected.

                        You posted the following:
                        NAME                            LATCH#  CHILD#       GETS     MISSES     SLEEPS                                     
                        cache buffers chains                98    1343 1081203632     492704      46912                                     
                        cache buffers chains                98     510  621275363     101770      59618                                     
                        cache buffers chains                98    2301 1587834610     108901      63320                                     
                        ges enqueue table freelist          53       2  171415075     530781      84887                                     
                        KCL name table parent latch        119       7  139898421     159169      97373                                     
                        KCL name table parent latch        119       3  141122585     160531      97418                              
                        KCL gc element parent latch        118       1  704376044    1299274     297905                                     
                        KCL gc element parent latch        118       6  722214938    1363956     302500                                     
                        KCL gc element parent latch        118       4 1633306777    4366125     818657                                     
                        KCL gc element parent latch        118       2 1596883300    4469190     821856                                     
                        KCL gc element parent latch        118       7 1617592694    4400345     845935                                     
                        KCL gc element parent latch        118       5 1617244638    4816969     910145                                     
                        KCL freelist parent latch          120       1 3357378187   10143242     963554                                     
                        KCL freelist parent latch          120       2 3343547419   10101839     977779                                     
                        cache buffers lru chain             93       3 2270638130   22606426    7456673                                     
                        Is the above a direct select from V$LATCH_CHILDREN, is it the delta (change) values from two selects from V$LATCH_CHILDREN, or is it something else? If the above shows the delta values from two selects from V$LATCH_CHILDREN, it appears that the "cache buffers lru chain" latch is the one with the greatest number of sleeps. The Oracle 11g document provides a good description of what should be done if you are having problems with this latch:
                        http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm
                        "cache buffers lru chain:
                        Possible Causes: Excessive buffer cache throughput. For example, inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans; DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer; Cache may be too small.
                        Look For: Statements with very high logical I/O or physical I/O, using unselective indexes.
                        The cache buffers lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained. For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient. Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application."

                        So, if the (performance) problem is caused by "cache buffers lru chain" latch contention, you may want to focus on the SQL statements being executed, and possibly less time trying to determine why the elapsed time for parsing the SQL statements is significantly greater than the CPU time for parsing - fixing the "cache buffers lru chain" latch contention may reduce contention for the CPU, in the process freeing up more CPU cycles to parse the SQL statements more rapidly.

                        Charles Hooper
                        IT Manager/Oracle DBA
                        K&M Machine-Fabricating, Inc.
                        • 9. Re: Parse CPU to Parse Elapsd
                          247514
                          Charles has made some good suggestion.

                          You might want to look into this query
                          Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
                          6,521,823 33 197,631.0 25.3 277.94 3275.94 17574787
                          SELECT /*+ INDEX (OM_COURSE_FEE OCF_CFC_CODE) */DISTINCT CF_COU
                          It escapes 3275 seconds in your 28 min statspack. Check the explain plan of this query, and condition of index OM_COURSE_FEE OCF_CFC_CODE. And why a hint needed for this index?

                          It's recommended to keep discussion on one thread because people might post duplicate information not being aware of your other thread. It's wasting of time and effort.
                          • 10. Re: Parse CPU to Parse Elapsd
                            Jonathan Lewis
                            user00726 wrote:
                            YOU CAN REPLY TO BOTH OF THE QUESTIONS...IF YOU DON'T MIND
                            It's the same question - you have a performance problem over a 29 minute interval.
                            Charles has repeated my request for latch information.
                            Ying Kuan has repeated the direction to look at one particular SQL statement.

                            You still haven't posted the latch activity report from the 29 minute statspack, and you haven't told us how many CPUs there are in the system - which is the information I requested in the other thread.

                            Charles,
                            The latch child listing isn't an interval (or at least, it isn't a relevant interval) - the statspack report had 100,000 latch free waits, the report has about 13,000,000.

                            The CBC LRU chain latch also gets active when a block is passed from one node to another in RAC, and we can see that a lot of that is happening in the Top 5 - so the high latch activity may simply be related to too much traffic, which could be related to the top SQL that Ying Kuan has pointed to.

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk

                            "The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." Stephen Hawking.
                            • 11. Re: Parse CPU to Parse Elapsd
                              Charles Hooper
                              Jonathan,

                              Thanks for clarifying that the latch activity that user00726 posted was not for a short duration time period (10 minutes) as requested, nor was it the latch statistics from the Statspack report. I was 98% certain that the values that the OP provided were the current values (since the last bounce of the database instance), and I was hoping that the OP would provide the requested values based on my response. My hinting skills still need a bit of work.

                              I was not aware that cache buffers lru chain latch would be utilized when a block is passed from one node to another. That information begs the question: might this problem be related to a problem with the network segment between nodes, which is further complicated by an inefficient execution plan for a couple SQL statements. I suppose that it will be much easier to answer this question once user00726 provides the information which you have requested in this and the other thread. Even if the OP does not provide the requested information, there are others who benefitted from your post.

                              Charles Hooper
                              IT Manager/Oracle DBA
                              K&M Machine-Fabricating, Inc.
                              • 12. Re: Parse CPU to Parse Elapsd
                                user00726
                                Hi all,

                                Sorry for being late.....As Yungkhan suggested me to looking into the query....
                                SQL> explain plan for SELECT /*+ INDEX (OM_COURSE_FEE OCF_CFC_CODE)  */DISTINCT
                                CF_COURSE_CODE   FROM OM_COURSE_FEE,OT_STUDENT_FEE_COL_HEAD,OT_STUDENT_FEE_COL_D
                                ETL  WHERE SFCH_SYS_ID = SFCD_SFCH_SYS_ID  AND SFCD_FEE_TYPE_CODE = CF_TYPE_CODE
                                  AND CF_COURSE_CODE IN ( 'PE1','PE2','CCT','CPT'  ) AND SFCH_TXN_CODE = :b1  AN
                                D SFCD_SUBSCRBE_JOURNAL_YN IN ( 'T','R','1','C'  ) AND SFCH_APPR_UID IS NOT NULL
                                   AND SFCH_APPR_DT IS
                                  2  NOT NULL   AND SFCH_NO = :b2  AND NOT EXISTS  (SELECT 'X'   FROM OM_STUDENT
                                HEAD  WHERE STUDSRN = SFCH_STUD_SRN_TEMP_NO  AND NVL(STUD_CLO_STATUS,0) != 1
                                AND NVL(STUD_REG_STATUS,0) != 23  AND STUD_COURSE_CODE != 'CCT'  AND CF_COURSE_C
                                ODE
                                  3  = STUD_COURSE_CODE )  ORDER BY 1 DESC
                                  4  ;

                                Explained.

                                SQL> select * from table(dbms_xplan.display());

                                PLAN_TABLE_OUTPUT
                                --------------------------------------------------------------------------------


                                --------------------------------------------------------------------------------

                                --------------------------------------------

                                | Id  | Operation                                  |  Name
                                   | Rows  | Bytes | Cost  | Pstart| Pstop |

                                --------------------------------------------------------------------------------

                                --------------------------------------------

                                |   0 | SELECT STATEMENT                           |

                                PLAN_TABLE_OUTPUT
                                --------------------------------------------------------------------------------

                                   |     1 |    69 |    34 |       |       |

                                |   1 |  SORT UNIQUE                               |
                                   |     1 |    69 |    22 |       |       |

                                |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID       | OT_STUDENT_FEE_COL_DETL
                                   |     1 |    12 |     2 | ROWID | ROW L |

                                |   3 |    NESTED LOOPS                            |
                                   |     1 |    69 |     9 |       |       |


                                PLAN_TABLE_OUTPUT
                                --------------------------------------------------------------------------------

                                |   4 |     NESTED LOOPS                           |
                                   |     1 |    57 |     7 |       |       |

                                |   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID    | OT_STUDENT_FEE_COL_HEAD
                                   |     1 |    48 |     5 | ROWID | ROW L |

                                |   6 |       INDEX SKIP SCAN                      | OT_STUDENT_FEE_COL_HEAD_UK0

                                1  |     1 |       |     4 |       |       |

                                |   7 |      INLIST ITERATOR                       |
                                   |       |       |       |       |       |

                                PLAN_TABLE_OUTPUT
                                --------------------------------------------------------------------------------


                                |   8 |       TABLE ACCESS BY INDEX ROWID          | OM_COURSE_FEE
                                   |     1 |     9 |     2 |       |       |

                                |   9 |        INDEX RANGE SCAN                    | OCF_CFC_CODE
                                   |     1 |       |     1 |       |       |

                                |  10 |         FILTER                             |
                                   |       |       |       |       |       |

                                |  11 |          TABLE ACCESS BY GLOBAL INDEX ROWID| OM_STUDENT_HEAD

                                PLAN_TABLE_OUTPUT
                                --------------------------------------------------------------------------------

                                   |     1 |    21 |     4 | ROWID | ROW L |

                                |  12 |           INDEX RANGE SCAN                 | IDM_STUD_SRN_COURSE
                                   |     1 |       |     3 |       |       |

                                |  13 |     INDEX RANGE SCAN                       | IDM_SFCD_FEE_TYPE_CODE
                                   | 34600 |       |     1 |       |       |

                                --------------------------------------------------------------------------------

                                --------------------------------------------
                                There are so many Index Range Scans Query is using.....

                                Johanathan.....

                                Total NO. of CPU is 2
                                • 13. Re: Parse CPU to Parse Elapsd
                                  user00726
                                  When i execute the V$latch_children

                                  I found that today is also greates no. of sleeps is CACHE BUFFE LRY CHAINS
                                                                           
                                                                                                                  
                                  LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                          50 ges resource hash list                                               
                                      601847      24245      22350                                                
                                                                                                                  
                                          50 ges resource hash list                                               
                                      617442      24184      22367                                                
                                                                                                                  
                                          50 ges resource hash list                                               
                                      749689      23959      22403                                                
                                                                                                                  
                                  LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                          50 ges resource hash list                                               
                                      612959      24281      22460                                                
                                                                                                                  
                                          50 ges resource hash list                                               
                                      753679      24146      22491                                                
                                                                                                                  
                                          50 ges resource hash list                                               
                                      610225      24510      22550                                                
                                                                                                                  
                                  
                                      LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                          50 ges resource hash list                                               
                                      605734      24468      22570                                                
                                                                                                                  
                                          50 ges resource hash list                                               
                                      751314      24771      23073                                                
                                                                                                                  
                                         157 library cache                                                        
                                   342286141     281771      25970                                                
                                                                                                                  
                                  
                                      LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                         157 library cache                                                        
                                   280560658     329228      31768                                                
                                                                                                                  
                                         156 shared pool                                                          
                                   463957862     289795      49417                                                
                                                                                                                  
                                         118 KCL gc element parent latch                                          
                                   328364965     438291      51954                                                
                                                                                                             
                                     LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                         118 KCL gc element parent latch                                          
                                   443999194     772076      91141                                                
                                                                                                                  
                                         120 KCL freelist parent latch                                            
                                   909808383    2092535     128985                                                
                                                                                                                
                                         120 KCL freelist parent latch                                            
                                   903524925    2097603     136115                                                
                                                                                                                  
                                     LATCH# NAME                                                                 
                                  ---------- ----------------------------------------------------------------     
                                        GETS     MISSES     SLEEPS                                                
                                  ---------- ---------- ----------                                                
                                          93 cache buffers lru chain                                              
                                   617430181    2728584     382138                                              
                                                                                                                  
                                  9568 rows selected.
                                  SQL> spool off

                                  I am really finding difficult to post my query here.....
                                  • 14. Re: Parse CPU to Parse Elapsd
                                    Charles Hooper
                                    user00726,

                                    What you have posted is very difficult to read. In the future, try using the code tags in curly brackets { code }.

                                    Taking a look at what you posted, you need to keep in mind that Explain Plan may lie about the plan from time to time, and it happens more frequently when bind variables are involved. Take a look at using DBMS_XPLAN to pull the actual execution plan directly from the database using SQL_ID and CHILD_NUMBER from V$SQL - something like this once you find the SQL_ID and CHILD_NUMBER:
                                    SELECT
                                      *
                                    FROM
                                      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number));
                                    Trying to reformat what you posted:
                                        LATCH# NAME                              GETS     MISSES     SLEEPS                                                
                                    ---------- ---------- ----------                                                
                                            50 ges resource hash list          601847      24245      22350                                                                                   
                                            50 ges resource hash list          617442      24184      22367                                                
                                            50 ges resource hash list          749689      23959      22403                                                
                                            50 ges resource hash list          612959      24281      22460                                                
                                            50 ges resource hash list          753679      24146      22491                                                
                                            50 ges resource hash list          610225      24510      22550                                                
                                            50 ges resource hash list          605734      24468      22570                                                
                                            50 ges resource hash list          751314      24771      23073                                                
                                           157 library cache                342286141     281771      25970                                                
                                           157 library cache                280560658     329228      31768                                                
                                           156 shared pool                  463957862     289795      49417                                                
                                           118 KCL gc element parent latch  328364965     438291      51954                                                
                                           118 KCL gc element parent latch  443999194     772076      91141                                                
                                           120 KCL freelist parent latch    909808383    2092535     128985                                                
                                           120 KCL freelist parent latch    903524925    2097603     136115                                                
                                            93 cache buffers lru chain      617430181    2728584     382138   
                                     
                                    SELECT /*+ INDEX (OM_COURSE_FEE OCF_CFC_CODE)  */ DISTINCT
                                      CF_COURSE_CODE
                                    FROM
                                      OM_COURSE_FEE,
                                      OT_STUDENT_FEE_COL_HEAD,
                                      OT_STUDENT_FEE_COL_DETL
                                    WHERE
                                      SFCH_SYS_ID = SFCD_SFCH_SYS_ID
                                      AND SFCD_FEE_TYPE_CODE = CF_TYPE_CODE
                                      AND CF_COURSE_CODE IN ( 'PE1','PE2','CCT','CPT'  )
                                      AND SFCH_TXN_CODE = :b1
                                      AND SFCD_SUBSCRBE_JOURNAL_YN IN ( 'T','R','1','C'  )
                                      AND SFCH_APPR_UID IS NOT NULL
                                      AND SFCH_APPR_DT IS NOT NULL
                                      AND SFCH_NO = :b2
                                      AND NOT EXISTS (
                                        SELECT
                                          'X'
                                        FROM
                                          OM_STUDENT_HEAD
                                        WHERE
                                          STUD_SRN = SFCH_STUD_SRN_TEMP_NO
                                          AND NVL(STUD_CLO_STATUS,0) != 1
                                          AND NVL(STUD_REG_STATUS,0) != 23
                                          AND STUD_COURSE_CODE != 'CCT'
                                          AND CF_COURSE_CODE = STUD_COURSE_CODE)
                                    ORDER BY
                                      1 DESC
                                     
                                    | Id  | Operation                                  |  Name                     | Rows  | Bytes | Cost  | Pstart| Pstop |
                                    |   0 | SELECT STATEMENT                                                             1      69      34
                                    |   1 |  SORT UNIQUE                                                                 1      69      22
                                    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID       | OT_STUDENT_FEE_COL_DETL   |     1 |    12 |     2 | ROWID | ROW L |
                                    |   3 |    NESTED LOOPS                                                              1      69       9
                                    |   4 |     NESTED LOOPS                                                             1      57       7
                                    |   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID    | OT_STUDENT_FEE_COL_HEAD   |     1 |    48 |     5 | ROWID | ROW L |
                                    |   6 |       INDEX SKIP SCAN                       OT_STUDENT_FEE_COL_HEAD_UK0|     1 |     1 |     4 |
                                    |   7 |      INLIST ITERATOR                       |
                                    |   8 |       TABLE ACCESS BY INDEX ROWID          | OM_COURSE_FEE             |     1 |     9 |     2 |       |       |
                                    |   9 |        INDEX RANGE SCAN                    | OCF_CFC_CODE              |     1 |       |     1 |       |       |
                                    |  10 |         FILTER                             
                                    |  11 |          TABLE ACCESS BY GLOBAL INDEX ROWID| OM_STUDENT_HEAD           |     1 |    21 |     4 | ROWID | ROW L |
                                    |  12 |           INDEX RANGE SCAN                 | IDM_STUD_SRN_COURSE       |     1 |       |     3 |       |       |
                                    |  13 |     INDEX RANGE SCAN                       | IDM_SFCD_FEE_TYPE_CODE    | 34600 |       |     1 |       |       |
                                    It appears, based just on the SQL statement, that there is no direct relationship between OM_COURSE_FEE and OT_STUDENT_FEE_COL_HEAD, yet the plan above indicates that the two tables are being joined together, likely as a result of the index hint. There is the possibility of additional predicates being generated by Oracle which will make this possible without introducing a Cartesian join, but those predicates are not displayed with an explain plan (they will appear with a DBMS_XPLAN). I may not be remembering correctly, but if the optimizer goal is set to first rows, a Cartesian join might appear in a plan as a nested loops join - Jonathan will know for certain if that is the case.

                                    The number of SLEEPS reported is smaller than what you provided last time. Did you bounce the database? More specifically, what is the time duration for the latch statistics that you provided? I requested a duration of 10 minutes, and Jonathan simply requested the latch statistics from the Statspack report.

                                    Charles Hooper
                                    IT Manager/Oracle DBA
                                    K&M Machine-Fabricating, Inc.
                                    1 2 Previous Next