1 2 Previous Next 22 Replies Latest reply: Oct 23, 2011 12:11 AM by Paulie Go to original post RSS
      • 15. Re: low Execute to Parse % and high soft parse %
        807722
        Top five timed events(Default snapshot interval -1 hour),this is time when load on db is considerable
        (What does cpu time indicates?)


        Top 5 Timed Events Avg %Total
        ~~~~~~~~~~~~~~~~~~ wait Call
        Event Waits Time (s) (ms) Time Wait Class
        ------------------------------ ------------ ----------- ------ ------ ----------
        CPU time 1,109 88.9
        db file scattered read 23,168 73 3 5.9 User I/O
        db file sequential read 57,713 69 1 5.6 User I/O
        SQL*Net more data to client 1,403,686 12 0 1.0 Network
        SQL*Net more data from client 49,276 11 0 0.9 Network

        Thanks
        • 16. Re: low Execute to Parse % and high soft parse %
          Chinar
            Top 5 Timed Events                                         Avg %Total
                                                                                 wait   Call
            Event                                 Waits    Time (s)   (ms)   Time Wait Class
            ------------------------------ ------------ ----------- ------ ------ ----------
            CPU time                                          1,109          88.9
            db file scattered read               23,168          73      3    5.9   User I/O
            db file sequential read              57,713          69      1    5.6   User I/O
            SQL*Net more data to client       1,403,686          12      0    1.0    Network
            SQL*Net more data from client        49,276          11      0    0.9    Network
            
          How many CPU`s you system has?
          • 17. Re: low Execute to Parse % and high soft parse %
            807722
            total 8 cpus (2 core quadro),but average cpu usage not more than 5-6 % as observed by top
            • 18. Re: low Execute to Parse % and high soft parse %
              807722
              total 8 cpus (2 core quadro),but average cpu usage not more than 5 - 6 % as observed by top
              • 19. Re: low Execute to Parse % and high soft parse %
                Chinar
                With 8 CPUs it looks there is no problem,approximately 4% cpu used and this indicate according snapshot interval and cpu count there CPU is not bottleneck.This event indicate service time actually but not wait time.So in your top wait events is not indicate any problems(because there was happen little FTS and index scans,due to wait time% according DB TIME and average wait times are too small).
                • 20. Re: low Execute to Parse % and high soft parse %
                  807722
                  Thanks for reply
                  for same purpose for analysing low Execute to Parse % and high soft parse %
                  I need help for interpreteting below
                  1)
                  select
                  PGA_TARGET_FOR_ESTIMATE,
                  PGA_TARGET_FACTOR,
                  ADVICE_STATUS,
                  ESTD_PGA_CACHE_HIT_PERCENTAGE
                  from v$pga_target_advice order by PGA_TARGET_FOR_ESTIMATE;

                  output of this query shows after particular value of
                  PGA_TARGET_FOR_ESTIMATE ,ESTD_PGA_CACHE_HIT_PERCENTAGE doesnt change ,should we consider this as optimum value of pga which we can set


                  2) select * from v$pgastat;
                  gives values of col as below-

                  aggregate PGA target parameter                         
                  aggregate PGA auto target                         
                  global memory bound                         
                  total PGA inuse                          
                  total PGA allocated                         
                  maximum PGA allocated                         
                  total freeable PGA memory                    
                  process count                              
                  max processes count                         
                  PGA memory freed back to OS                         
                  total PGA used for auto workareas               
                  maximum PGA used for auto workareas               
                  total PGA used for manual workareas                    
                  maximum PGA used for manual workareas               
                  over allocation count                         
                  extra bytes read/written                         
                  cache hit percentage                    
                  recompute count (total)      


                  out of this which is useful to check and what it means
                  like total pga in use,does it shows current/dyanamic pga in use
                  if so i could see very less value compare to total pga available
                  does it means my pga is oversized or not required to be increased


                  3) As per one of oracle docs i am selecting v$event_histogram
                  for event ='enq: TX - row lock contention'               
                  what does bucket(wait_time_milli) and wait_count "signifies"


                  4) first few rows of v$latch shows as below
                  is anything worrying in this ( if wait_time in microseconds
                  shared pool latch doesnt seems to be much) and what does
                  "slave class create" means ,do need to do anything abt it

                  NAME                              WAIT_TIME
                  -------------------------------------------------- ----------
                  slave class create                    169434266
                  shared pool                         26710142
                  cache buffers chains                    24554819
                  process allocation                    23033021
                  cache buffer handles                    13813326
                  session allocation                    7335203
                  library cache lock                    6088370
                  library cache                         4057734


                  5)sga is showing 24% free and shared pool showing 60 mb free out of 700 mb total size
                  by querying v$sgastat
                  what does it signifies?

                  Thanks again

                  Edited by: 804719 on Oct 19, 2011 2:10 AM

                  Edited by: 804719 on Oct 21, 2011 2:28 AM
                  • 21. Re: low Execute to Parse % and high soft parse %
                    Jonathan Lewis
                    804719 wrote:
                    Thanks for reply
                    for same purpose for analysing low Execute to Parse % and high soft parse %
                    I need help for interpreteting below
                    I suggest you try looking in the right place to investigate the problem - trying to get anything out of this ratios is a bad idea. (See http://jonathanlewis.wordpress.com/2006/12/27/analysing-statspack-2/ )

                    Before you do anything else, look at the instance activity statistics for
                    execute count
                    parse count (total)
                    parse count (hard)
                    session cursor cache hits
                    parse time CPU
                    parse time elapsed
                    Regards
                    Jonathan Lewis
                    • 22. Re: low Execute to Parse % and high soft parse %
                      Paulie
                      >
                      there is not any performance problem reported
                      Then just sit back and drink coffee until your users start complaining ;).
                      do i need to change anything or just keep monitoring
                      You should constantly* be monitoring your system - if you
                      don't have a baseline, how will you know what has gone wrong
                      when (hopefully not!) your users start complaining?


                      Paul...
                      1 2 Previous Next