Tuesday, April 10, 2007

Entry for April 11, 2007

buffer gets for cpu util
disk reads for disk i/o
sorts for sorting in sqlarea

to get buffer gets higher than some large value
SQL> select a.buffer_gets,a.disk_reads,a.sorts,a.address from v$sqlarea a where a.buffer_gets>1000000;

use similar for getting the other address of interest for disk i/o or sorts

once the address is found for ex.070000002ADDE608 for the highest sorts of 284

BUFFER_GETS DISK_READS SORTS ADDRESS
----------- ---------- ---------- ----------------
1557764 1553482 284 070000002ADDE608
1405968 27827 1 070000002879F370
1407953 27862 0 07000000287D4330
6897100 2659578 0 0700000029F8D330



use
SQL> select SQL_TEXT,PIECE from v$sqltext where address='070000002ADDE608' order by PIECE;

SQL_TEXT PIECE
---------------------------------------------------------------- ----------
SELECT DQD_FIN_ID , DQD_DIS_NO , SUM(DQD_DEMAT_QTY) FROM DMAT_QT 0
Y_DIS_ELEC WHERE DQD_NPN = :B1 GROUP BY DQD_FIN_ID , DQD_DIS_NO 1
2




No comments: