Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: need help on statspack report, Parse CPU to Parse Elapsd %

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

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Fri, 5 Mar 2004 19:55:15 GMT
Message-ID: <4048DB23.C0A50477@remove_spam.peasland.com>


> Joe wrote:
>
> 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
>
> 1. What Parse CPU to Parse Elapsd % means?
> 2. How can I improve it?
>
> Thanks for any input.
>
> Best Regards,
> Joe
>
>

Joe,

What are you trying to tune? What goal do you have in mind? What is your stopping condition? One should rarely tune just to tune. You mentioned that Oracle was using large amounts of CPU. Have you tracked which session(s) are using the CPU? Or is it a background process that is using the CPU?

I just think there are too many potentials here and more information is needed.

Cheers,
Brian Received on Fri Mar 05 2004 - 13:55:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US