ITPub博客

首页 > 数据库 > Oracle > Top 10 Sql (DBA 调优参考)

Top 10 Sql (DBA 调优参考)

原创 Oracle 作者:asiaidea 时间:2014-07-23 09:07:15 0 删除 编辑
--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博客 ” ,链接:http://blog.itpub.net/10359218/viewspace-1225015/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    140
  • 访问量
    479455