一些TOP SQL语句，便于我们寻找一些有问题的SQL语句
set linesize 3000set pagesize 1000
col SQL format a200
spool top.result
----Top 10 by Buffer Gets:
SELECT *
FROM (SELECT buffer_gets,
executions,
buffer_gets / executions "Gets/Exec",
hash_value,
address,
sql_text sql
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10;
----Top 10 by Physical Reads:
SELECT *
FROM (SELECT disk_reads,
executions,
disk_reads / executions "Reads/Exec",
hash_value,
address,
sql_text sql
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;
----Top 10 by Executions:
SELECT *
FROM (SELECT executions,
rows_processed,
rows_processed / executions "Rows/Exec",
hash_value,
address,
sql_text sql
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;
----Top 10 by Parse Calls:
SELECT *
FROM (SELECT parse_calls, executions, hash_value, address, sql_text sql
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;
----Top 10 by Sharable Memory:
SELECT *
FROM (SELECT sharable_mem, executions, hash_value, address, sql_text sql
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
----Top 10 by Version Count:
SELECT *
FROM (SELECT version_count, executions, hash_value, address, sql_text sql
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10;
spool off
页:
[1]