need help on statspack report, Parse CPU to Parse Elapsd %

From: Joe <joe.yung_at_ecl-consulting.com>
Date: Fri, 5 Mar 2004 09:42:39 +0800
Message-ID: <4047db30@newsgate.hknet.com>

Hi Oracle Experts,

I need some help on performance tuning. Hope someone can give me some hints. I run the statspack report yesterday, the following is part of the report

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:          4 04-Mar-04 09:00:05      140
   End Snap:         13 04-Mar-04 18:00:02      140
    Elapsed:                 539.95 (mins)

Cache Sizes

           db_block_buffers:      60000          log_buffer:    1048576
              db_block_size:       8192    shared_pool_size:  600000000

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             12,923.83             10,119.23
              Logical reads:             24,560.24             19,230.43
              Block changes:                 91.95                 72.00
             Physical reads:                557.54                436.55
            Physical writes:                 51.18                 40.08
                 User calls:                 71.64                 56.09
                     Parses:                 25.66                 20.09
                Hard parses:                  0.26                  0.21
                      Sorts:                 12.12                  9.49
                     Logons:                  0.17                  0.14
                   Executes:                208.73                163.43
               Transactions:                  1.28



  % Blocks changed per Read:    0.37    Recursive Call %:   81.57

 Rollback per transaction %:    7.24       Rows per Sort:   62.75

Instance Efficiency Percentages (Target 100%)

            Buffer Nowait %:  100.00       Redo NoWait %:   99.99
            Buffer  Hit   %:   97.73    In-memory Sort %:   99.97
            Library Hit   %:   99.83        Soft Parse %:   98.98
         Execute to Parse %:   87.71         Latch Hit %:   99.95
Parse CPU to Parse Elapsd %:    1.80     % Non-Parse CPU:   98.94

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   21.78   72.57

% SQL with executions>1: 45.64 60.15
% Memory for SQL w/exec>1: 74.31 73.98

Everything seems fine (if it is not, please tell me), except the Parse CPU to Parse Elapsd %. It is 1.80% only, which is far below the target 100%, and I think it is unreasonable low. I am wondering if this somehow related to high CPU utilization of Oracle (I find the Oracle process taking up a unreasonable high CPU resources on my system).

I want to know

What Parse CPU to Parse Elapsd % means?
How can I improve it?

Thanks for any input.

Best Regards,
Joe Received on Thu Mar 04 2004 - 19:42:39 CST