SEO

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,
The duration of your statspack (about 9 hrs) is high, normally
Statspack reports are generated for a half-hour duration during peak
load.
Think you are running 8i.
A low value for Parse CPU to Parse Elapsd % means that the during
parsing, the server process is waiting for something, most likely
contending for a latch (shared pool and library cache latches).
But in your case this is not much of a disadvantage as you have a high
% Non-Parse CPU (98.94 %).
You should have also shown the Top 5 Wait Events (Top 5 Timed Events
from 9.2) and the latch information from the statspack.
You also have a high soft parse % - 98.98%.
So most of your parse calls are resulting in soft parses.
In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
(you can start with a value of 150).
You may also want to look at the code, to see why there are so many
parse calls.
Ideally you should parse once and execute many times.
regards
Srivenu

Hi Srivenu,

Thanks a lots for your reply.
Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue 33,290 10,195,809 57.13
latch free 1,887,651 6,092,953 34.14
db file scattered read 1,788,217 537,596 3.01
db file sequential read 1,589,366 369,621 2.07
log file sync 32,896 164,777 .92
-------------------------------------------------------------

I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

Can you give me some advise on latch free and enqueue wait?
Any input is appreciated.
Thanks a lots.

Regards,
Joe




"srivenu" <srivenu@hotmail.com> ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
> Joe,
> The duration of your statspack (about 9 hrs) is high, normally
> Statspack reports are generated for a half-hour duration during peak
> load.
> Think you are running 8i.
> A low value for Parse CPU to Parse Elapsd % means that the during
> parsing, the server process is waiting for something, most likely
> contending for a latch (shared pool and library cache latches).
> But in your case this is not much of a disadvantage as you have a high
> % Non-Parse CPU (98.94 %).
> You should have also shown the Top 5 Wait Events (Top 5 Timed Events
> from 9.2) and the latch information from the statspack.
> You also have a high soft parse % - 98.98%.
> So most of your parse calls are resulting in soft parses.
> In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
> (you can start with a value of 150).
> You may also want to look at the code, to see why there are so many
> parse calls.
> Ideally you should parse once and execute many times.
> regards
> Srivenu

Hello Joe,

Can you please post whole Statspack report. This would make analysis much easier.

Regards,

Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer: http://www.dbainfopower.com/dbaip_ad...isclaimer.html

"Joe" <yung103@netvigator.com> wrote in message news:c2ab5g$16i2039@imsp212.netvigator.com...
Hi Srivenu,

Thanks a lots for your reply.
Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue 33,290 10,195,809 57.13
latch free 1,887,651 6,092,953 34.14
db file scattered read 1,788,217 537,596 3.01
db file sequential read 1,589,366 369,621 2.07
log file sync 32,896 164,777 .92
-------------------------------------------------------------

I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it.
I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

Can you give me some advise on latch free and enqueue wait?
Any input is appreciated.
Thanks a lots.

Regards,
Joe




"srivenu" <srivenu@hotmail.com> ??? news:1a68177.0403050248.a6acf5@posting.google.com ???...
> Joe,
> The duration of your statspack (about 9 hrs) is high, normally
> Statspack reports are generated for a half-hour duration during peak
> load.
> Think you are running 8i.
> A low value for Parse CPU to Parse Elapsd % means that the during
> parsing, the server process is waiting for something, most likely
> contending for a latch (shared pool and library cache latches).
> But in your case this is not much of a disadvantage as you have a high
> % Non-Parse CPU (98.94 %).
> You should have also shown the Top 5 Wait Events (Top 5 Timed Events
> from 9.2) and the latch information from the statspack.
> You also have a high soft parse % - 98.98%.
> So most of your parse calls are resulting in soft parses.
> In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
> (you can start with a value of 150).
> You may also want to look at the code, to see why there are so many
> parse calls.
> Ideally you should parse once and execute many times.
> regards
> Srivenu

> 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

I agree with Ron. You need to post the entire file. Under 8i the top
5 events don't mean much unless you can see the CPU time used later in
the report.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Ron" <support@dbainfopower.com> wrote in message news:<zK-dnXbuzLJUW9XdRVn-vg@comcast.com>...
> Hello Joe,
>
> Can you please post whole Statspack report. This would make analysis
> much easier.
>
> Regards,
>
> Ron
> DBA Infopower
> http://www.dbainfopower.com
> Standard disclaimer:
> http://www.dbainfopower.com/dbaip advice disclaimer.html
>
> "Joe" <yung103@netvigator.com> wrote in message
> news:c2ab5g$16i2039@imsp212.netvigator.com...
> Hi Srivenu,
>
> Thanks a lots for your reply.
> Yes, I am using 8.1.7.4. and i am experiencing contention in the
> following area
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait
> % Total
> Event Waits Time (cs)
> Wt Time
> -------------------------------------------- ------------ ------------
> -------
> enqueue 33,290 10,195,809
> 57.13
> latch free 1,887,651 6,092,953
> 34.14
> db file scattered read 1,788,217 537,596
> 3.01
> db file sequential read 1,589,366 369,621
> 2.07
> log file sync 32,896 164,777
> .92
>
> -------------------------------------------------------------
>
> I understand the 3-5 wait events, and able to handle them. However for
> the enqueue and latch free, I don't have much idea on how to improve it.
>
> I do read from the metalink that setting the spin count may help to
> improve latch free wait. However, spin count is a undocumented
> parameter, and i don't have much information of it. I want to reduce the
> value of this parameter, but I don't even know what is the current value
> of it. I can't find it in v$system parameter view.
>
> Can you give me some advise on latch free and enqueue wait?
> Any input is appreciated.
> Thanks a lots.
>
> Regards,
> Joe
>
>
>
>
> "srivenu" <srivenu@hotmail.com> ???
> news:1a68177.0403050248.a6acf5@posting.google.com ???...
> > Joe,
> > The duration of your statspack (about 9 hrs) is high, normally
> > Statspack reports are generated for a half-hour duration during peak
> > load.
> > Think you are running 8i.
> > A low value for Parse CPU to Parse Elapsd % means that the during
> > parsing, the server process is waiting for something, most likely
> > contending for a latch (shared pool and library cache latches).
> > But in your case this is not much of a disadvantage as you have a
> high
> > % Non-Parse CPU (98.94 %).
> > You should have also shown the Top 5 Wait Events (Top 5 Timed Events
> > from 9.2) and the latch information from the statspack.
> > You also have a high soft parse % - 98.98%.
> > So most of your parse calls are resulting in soft parses.
> > In such cases, the setting of SESSION CACHED CURSORS parameter helps
> > (you can start with a value of 150).
> > You may also want to look at the code, to see why there are so many
> > parse calls.
> > Ideally you should parse once and execute many times.
> > regards
> > Srivenu
> --