--sqlarea
select * from v$sqlarea vs
where 1=1
and vs.HASH_VALUE = '2692618562';

--Top 10 by Buffer Gets:
SELECT * FROM
(SELECT substr(sql_text,1,50) sql,sql_text,

buffer_gets, executions, buffer_gets/executions "Gets/Exec",s.CPU_TIME/1000/1000 CPU_TIME_S,s.ELAPSED_TIME/1000/1000 ELAPSED_TIME_S,
hash_value,address--,SQL_FULLTEXT
FROM V$SQLAREA s
WHERE buffer_gets > 10000
and executions >0
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

--Top 10 by Cpu Time:
SELECT * FROM
(SELECT substr(sql_text,1,50) sql,

buffer_gets, executions, buffer_gets/executions "Gets/Exec",s.CPU_TIME/1000/1000 CPU_TIME_S, s.CPU_TIME/1000/1000/executions "CPU_Time_s/Exec",s.ELAPSED_TIME/1000/1000 ELAPSED_TIME_S,
hash_value,address,SQL_FULLTEXT
FROM V$SQLAREA s
WHERE executions >= 1
ORDER BY CPU_TIME DESC)
WHERE rownum <= 50
;

--Top 10 by ELAPSED_TIME:
SELECT * FROM
(SELECT substr(sql_text,1,50) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",s.CPU_TIME/1000/1000 CPU_TIME_S, s.CPU_TIME/1000/1000/executions "CPU_Time_s/Exec",s.ELAPSED_TIME/1000/1000 ELAPSED_TIME_S, s.ELAPSED_TIME/1000/1000/executions "ELAPSED_TIME/Exec",
hash_value,address,SQL_FULLTEXT
FROM V$SQLAREA s
WHERE executions >= 1
ORDER BY ELAPSED_TIME DESC)
WHERE rownum <= 50
;

--Top 10 by Physical Reads:
SELECT * FROM
(SELECT substr(sql_text,1,50) sql,
disk_reads, executions, disk_reads/decode(executions,0,1,executions) "Reads/Exec",
hash_value,address,SQL_FULLTEXT
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

--Top 10 by Executions:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address,SQL_FULLTEXT
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;

--Top 10 by Parse Calls:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address,SQL_FULLTEXT
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

--Top 10 by Sharable Memory:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address，SQL_FULLTEXT
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

--Top 10 by Version Count:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10

ITPub博客

Top 10 Sql (DBA 调优参考)

最新文章