之前的一篇文章解释了为什么要使用V$SQLSTATS视图。
当数据库表现出各种不同的性能问题的症状时,您可以通过调整SQL语句中WHERE条件来查询需要优化的SQL。下面是一些例子:
按Buffer Gets降序排列TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,buffer_gets,trunc(buffer_gets/executions) gets_per
FROM V$SQLSTATS
WHERE buffer_gets > 10000 and executions<>0
ORDER BY buffer_gets DESC)
WHERE rownum <=10;
Elapsed time的单位是微秒,一微秒等于一百万分之一秒。
关于号主,姚远:
按物理读降序排列TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,disk_reads,trunc(disk_reads/executions) reads_per
FROM V$SQLSTATS
WHERE disk_reads> 1000 and executions<>0
ORDER BY disk_reads DESC)
WHERE rownum <=10;
按执行次数降序排列TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,rows_processed,trunc(rows_processed/executions) rows_per
FROM V$SQLSTATS
WHERE executions> 100 and executions<>0
ORDER BY executions DESC)
WHERE rownum <=10;
按解析次数降序排列TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,parse_calls
FROM V$SQLSTATS
WHERE parse_calls> 100 and executions<>0
ORDER BY parse_calls DESC)
WHERE rownum <=10;
按使用内存降序排列TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,sharable_mem
FROM V$SQLSTATS
WHERE sharable_mem> 1048576 and executions<>0
ORDER BY sharable_mem DESC)
WHERE rownum <=10;
上述查询是汇总SQL在所有执行中消耗的资源来识别TOP SQL。在某些情况下,例如当应用程序的代码不使用绑定变量时,根据SQL在单次执行中消耗的资源作为标准来查询TOP SQL可能更恰当。
下面的例子根据单次执行中的Buffer Gets来查找TOP SQL:
set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
executions,buffer_gets,trunc(buffer_gets/executions) gets_per
FROM V$SQLSTATS
WHERE buffer_gets > 100 and executions<>0
ORDER BY gets_per DESC)
WHERE rownum <=10;
上面是一些查询TOP SQL的例子,您可以根据您的数据库的特定情况,稍作修改后生成更适合的查询脚本。