bonjour,


j'ai actuellement des problèmes sur une base ORACLE 9i pas tres bien architecturée
beaucoup IO

on me demande d otptimiser la base malgre un MCD mediocre

que puis je faire pour améliorer l'existant.

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
j'ai également un report sur ORAPERF

PRODUCING too many read consistency block
TOO may block cleanouts on commits
scanning too many buffers
large of frequent small rollbacks





merci de votre avis