STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
INFO 728677380 INFO 1 9.2.0.5.0 NO ALPHA8
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 4 28-Sep-09 16:36:52 12 5.6
End Snap: 6 28-Sep-09 16:57:43 12 5.5
Elapsed: 20.85 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 32M Std Block Size: 8K
Shared Pool Size: 112M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,698.40 1,062,348.00
Logical reads: 685.84 428,994.50
Block changes: 9.77 6,111.00
Physical reads: 653.58 408,813.50
Physical writes: 0.55 346.00
User calls: 0.58 363.50
Parses: 0.95 591.50
Hard parses: 0.01 4.00
Sorts: 0.63 391.00
Logons: 0.00 1.00
Executes: 1.83 1,145.50
Transactions: 0.00
% Blocks changed per Read: 1.42 Recursive Call %: 85.12
Rollback per transaction %: 0.00 Rows per Sort: 1268.70
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 4.71 In-memory Sort %: 100.00
Library Hit %: 99.67 Soft Parse %: 99.32
Execute to Parse %: 48.36 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 84.62 % Non-Parse CPU: 99.72
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 42.74 42.86
% SQL with executions>1: 59.14 66.60
% Memory for SQL w/exec>1: 50.18 60.29
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 451,828 1,225 90.47
db file scattered read 22,901 86 6.37
CPU time 40 2.94
control file sequential read 294 1 .09
control file parallel write 407 1 .05
-------------------------------------------------------------
Wait Events for DB: INFO Instance: INFO Snaps: 4 -6
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 451,828 0 1,225 3 ########
db file scattered read 22,901 0 86 4 ########
control file sequential read 294 0 1 4 147.0
control file parallel write 407 0 1 2 203.5
db file parallel read 14 0 1 43 7.0
log file parallel write 1,805 0 0 0 902.5
log file sync 3 0 0 3 1.5
SQL*Net more data to client 33 0 0 0 16.5
buffer busy waits 22 0 0 0 11.0
undo segment extension 208 207 0 0 104.0
latch free 16 15 0 0 8.0
direct path read 9 0 0 0 4.5
direct path write 9 0 0 0 4.5
LGWR wait for redo copy 4 0 0 0 2.0
SQL*Net message from client 554 0 2,905 5244 277.0
virtual circuit status 42 42 1,260 30000 21.0
wakeup time manager 40 40 1,185 29620 20.0
SQL*Net more data from clien 72 0 0 0 36.0
SQL*Net message to client 554 0 0 0 277.0
-------------------------------------------------------------
Background Wait Events for DB: INFO Instance: INFO Snaps: 4 -6
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file sequential read 164 0 1 6 82.0
control file parallel write 407 0 1 2 203.5
log file parallel write 1,805 0 0 0 902.5
db file sequential read 27 0 0 6 13.5
db file scattered read 9 0 0 6 4.5
rdbms ipc reply 115 0 0 0 57.5
buffer busy waits 7 0 0 0 3.5
LGWR wait for redo copy 4 0 0 0 2.0
latch free 3 3 0 0 1.5
rdbms ipc message 3,157 1,221 4,917 1558 1,578.5
pmon timer 419 419 1,251 2986 209.5
smon timer 5 3 1,204 ###### 2.5
-------------------------------------------------------------
SQL ordered by Gets for DB: INFO Instance: INFO Snaps: 4 -6
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
365,400 1 365,400.0 42.6 34.31 119.36 767427966
Module: wiqt.exe
SELECT BO_FOURNISSEURS.LBFOU1, BO_ACHATS.NUSUC, BO_ACHA
TS.NUFOU, sum(decode(sign(8+(trunc((to_number(( '200907' ))-0
8)/100)*100) - to_number(substr(BO_ACHATS.DTREC,1,6))-1),-1, d
ecode( sign (to_number(substr(BO_ACHATS.DTREC,1,6))-1 - to_nu
mber(( '200907' ))),-1,BO_ACHATS.CAHRT))) FROM BO_FOURNISS
16,186 40 404.7 1.9 0.92 1.87 815501214
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
14,753 2 7,376.5 1.7 1.19 2.34 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
11,982 2 5,991.0 1.4 0.35 0.43 1116368370
Module: SQL*Plus
INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S
QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT ST1.HAS
H_VALUE , SS.TEXT_SUBSET , ST1.PIECE , ST1.SQL_TEXT , ST1.ADDRES
S , ST1.COMMAND_TYPE , SS.SNAP_ID FROM V$SQLTEXT ST1 , STATS$SQL
_SUMMARY SS WHERE SS.SNAP_ID = :B3 AND SS.DBID = :B2 AND SS.INST
Partager