| statspack report [message #143835] |
Sun, 23 October 2005 03:56  |
lallmanish
Messages: 28
Registered: September 2005
|
Junior Member |
|
|
hi
i have got a concern.
the statspackreport in my prod database shows this;
load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 48,624.79 48,624.79
Logical reads: 8,705.15 8,705.15
Block changes: 349.93 349.93
Physical reads: 194.03 194.03
Physical writes: 26.81 26.81
User calls: 162.15 162.15
Parses: 25.57 25.57
Hard parses: 0.58 0.58
Sorts: 53.15 53.15
Logons: 0.21 0.21
Executes: 358.12 358.12
Transactions: 1.00
% Blocks changed per Read: 4.02 Recursive Call %: 78.61
Rollback per transaction %: 81.67 Rows per Sort: 114.50
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.90
Buffer Hit %: 98.01 In-memory Sort %: 99.96
Library Hit %: 99.62 Soft Parse %: 97.73
Execute to Parse %: 92.86 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 75.07 % Non-Parse CPU: 98.58
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 97.60 97.85
% SQL with executions>1: 67.57 77.96
% Memory for SQL w/exec>1: 68.75 75.74
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
db file sequential read 184,406 474 23.46
LGWR-LNS wait on channel 93,143 261 12.94
LGWR wait on LNS 594 260 12.86
LNS wait on SENDREQ 578 260 12.85
log buffer space 335 241 11.94
this shows a very high value of execute/parse-92% but most of the parses are soft and the library cache hit ratio showing 99%.
how come with 99 % hit ratio implies most of the statements are been hit in the shared pool whereas execute/parse high value implies most of the statements are parsed although in my case its the soft parse. plzz clarify me and let me know if
any tuning aspect is there.does increse of shared pool size is needed.
one more problem that we have a ram size of 8gb and my sga size is 3 gb. i am unable to use the db_cache_size parameter
because of some limitation imposed by oracle that for such high sga use use indirect block buffers- i want to use db_keep_cache_size but unable to use. --
suggestions welcome.
manish
e:manishlall@mhd.co.om
|
|
|
|
|
|
| Re: statspack report [message #143852 is a reply to message #143849] |
Sun, 23 October 2005 08:10  |
lallmanish
Messages: 28
Registered: September 2005
|
Junior Member |
|
|
yeah
my db file sequential read is highest wait event .
infact it takes 70% of the total response time.
there are a lot of indexes been used with mainly few tables
in million rows being executed several times with a lot of
AND predicates and functions.
probably i think the reson would be clustering factor very high- but the biggest problem is so many indexes are used that
once you try to restructure one to reduce the clustering factor
the other index starts showinh high clustering factor- i hAVE BEEN NEWLY APPOINTED HERE AS DBA IN THIS COMPANY.
any suggestions??
|
|
|
|
| Re: statspack report [message #143913 is a reply to message #143852] |
Mon, 24 October 2005 02:38 |
ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia
|
Junior Member |
|
|
In my opinion best way is trying to understand business logic of your application, thus you can extremely increase performance.
Difference between percents and times.
At first try look on top sql and their explain plans.
Regards,
Ziggy.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
