2006. 11. 2. 15:36 오라클
full scan 하는 table 찾는 query
SQL> column sql_text format A100
SQL> select sid,sql_text
From v$session a, v$sqlarea b
Where a.sql_hash_value = b.hash_value
And sid in
(Select sid From v$session_wait
Where event like 'db file scattered read');
를 수행하거나 아니면 ,,,
SQL> rem
SQL> rem ************************************
SQL> rem * Disk 과다사용하는 SQL
SQL> rem ************************************
SQL> rem
SQL>
col buffer_gets format 999999 heading "BUFFER|GETS"
col disk_reads format 999999 heading "DISK|READ"
col executions format 9999 heading "EXEC|CNT"
col parse_calls format 9999 heading "PARSE|CALLS"
col sql_text format A70
select sql_text,
buffer_gets, disk_reads, executions, parse_calls
from v$sqlarea where disk_reads > 1000000
where buffer_gets > 1000 -----------------> 환경에 맞게 조정
or disk_reads > 100000 -----------------> 환경에 맞게 조정
order by disk_reads ;
