Hi,
To tune the database i generated statspack report please share me the tips regarding tuning. part of the statspack is given below
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PINDB 3730403337 pindb 1 8.1.7.0.0 NO suportal01
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 21 21-Jul-08 13:08:31 210
End Snap: 22 21-Jul-08 13:22:24 210
Elapsed: 13.88 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 409600 log_buffer: 163840
db_block_size: 4096 shared_pool_size: 1310720000
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 42,186.94 5,846.24
Logical reads: 38,625.42 5,352.68
Block changes: 174.36 24.16
Physical reads: 17,187.35 2,381.81
Physical writes: 173.19 24.00
User calls: 163.64 22.68
Parses: 26.36 3.65
Hard parses: 1.23 0.17
Sorts: 13.91 1.93
Logons: 0.27 0.04
Executes: 708.32 98.16
Transactions: 7.22
% Blocks changed per Read: 0.45 Recursive Call %: 80.91
Rollback per transaction %: 0.67 Rows per Sort: #######
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.10 Redo NoWait %: 99.97
Buffer Hit %: 55.50 In-memory Sort %: 99.69
Library Hit %: 99.62 Soft Parse %: 95.35
Execute to Parse %: 96.28 Latch Hit %: 99.73
Parse CPU to Parse Elapsd %: 30.23 % Non-Parse CPU: 99.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 83.58 83.11
% SQL with executions>1: 62.74 63.24
% Memory for SQL w/exec>1: 37.31 38.34
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PINDB 3730403337 pindb 1 8.1.7.0.0 NO suportal01
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 21 21-Jul-08 13:08:31 210
End Snap: 22 21-Jul-08 13:22:24 210
Elapsed: 13.88 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 409600 log_buffer: 163840
db_block_size: 4096 shared_pool_size: 1310720000
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 42,186.94 5,846.24
Logical reads: 38,625.42 5,352.68
Block changes: 174.36 24.16
Physical reads: 17,187.35 2,381.81
Physical writes: 173.19 24.00
User calls: 163.64 22.68
Parses: 26.36 3.65
Hard parses: 1.23 0.17
Sorts: 13.91 1.93
Logons: 0.27 0.04
Executes: 708.32 98.16
Transactions: 7.22
% Blocks changed per Read: 0.45 Recursive Call %: 80.91
Rollback per transaction %: 0.67 Rows per Sort: #######
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.10 Redo NoWait %: 99.97
Buffer Hit %: 55.50 In-memory Sort %: 99.69
Library Hit %: 99.62 Soft Parse %: 95.35
Execute to Parse %: 96.28 Latch Hit %: 99.73
Parse CPU to Parse Elapsd %: 30.23 % Non-Parse CPU: 99.99
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 83.58 83.11
% SQL with executions>1: 62.74 63.24
% Memory for SQL w/exec>1: 37.31 38.34
Buffer Pool Statistics for DB: PINDB Instance: pindb Snaps: 21 -22
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 14,233,242 0 14,216,020 39,539 0 0 612,534
-------------------------------------------------------------
Buffer wait Statistics for DB: PINDB Instance: pindb Snaps: 21 -22
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 611,175 0 0
undo header 2 0 0
-------------------------------------------------------------
Enqueue activity for DB: PINDB Instance: pindb Snaps: 21 -22
-> ordered by waits desc, gets desc
Enqueue Gets Waits
---------- ------------ ----------
TX 7,173 4
Thanks
Prakash
[
Message was edited by:
user612485