我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
SQL脚本:查询指定SQL的统计信息(cursor,awr)
1,从cursor中查询指定sql的统计信息
set echo off
set lines 300
set verify off
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
set serveroutput on
set feedback off
set lines 300
set pages 10000
set long 100000
set lines 300
set echo off
set verify off
col sql_id for a18
col i_mem for 999999 heading ‘SHARED|Mem KB’
col sorts for 99999999
col version_count for 999 heading ‘VER|NUM’
col executions for 999999 heading ‘EXEC|NUM’
col parse_calls for 999999 heading ‘PARSE|CALLS’
col disk_reads for 999999 heading ‘DISK|READ’
col direct_writes for 999999 heading ‘DIRECT|WRITE’
col buffer_gets for 99999999999999
col avg_disk_reads for 99999 heading ‘AVG|DISK|READ’
col avg_direct_writes for 99999 heading ‘AVG|DIRECT|WRITE’
col avg_buffer_gets for 9999999 heading ‘AVG|BUFFER|GET’
col sql_profile for a14
col ROWS_PROCESSED for 999999999 heading ‘ROW|PROC’
col avg_rows_processed for 99999999 heading ‘AVG|ROW|PROC’
col avg_fetches for 999999 heading ‘AVG|FETCH’
col AVG_ELAPSED_TIME for 9999999 heading ‘AVG|ELAPSED|TIME’
col AVG_CPU_TIME for 9999999 heading ‘AVG|CPU_TIME’
col PARSING_SCHEMA_NAME for a15 heading ‘PARSING|SCHEMA_NAME’
SELECTplan_hash_value,parsing_schema_name,(executions) executions,(elapsed_time) elapsed_time,TRUNC ( (elapsed_time) / DECODE ( (executions), 0, 1, (executions)))avg_elapsed_time,(cpu_time) cpu_time,TRUNC ( (cpu_time) / DECODE ( (executions), 0, 1, (executions)))avg_cpu_time,(buffer_gets) buffer_gets,TRUNC ( (buffer_gets) / DECODE ( (executions), 0, 1, (executions)))avg_buffer_gets,(disk_reads) disk_reads,TRUNC ( (disk_reads) / DECODE ( (executions), 0, 1, (executions)))avg_disk_reads,(direct_writes) direct_writes,TRUNC ( (direct_writes) / DECODE ( (executions), 0, 1, (executions)))avg_direct_writes,(rows_processed) rows_processed,TRUNC ( (rows_processed) / DECODE ( (executions), 0, 1, (executions)))avg_rows_processed,(fetches) fetches,TRUNC ( (fetches) / DECODE ( (executions), 0, 1, (executions)))avg_fetches
from table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(‘sql_id = ”&sql_id”’));undefine begin_snap;
undefine sql_id;
undefine end_snap;
undefine sort_type;
undefine topn;
2，从awr中查询指定sql的统计信息
set echo off
set lines 300
set verify off
set serveroutput on
set feedback off
set lines 300
set pages 10000
set long 100000
@awr_snapshot_info.sql
set lines 300
set echo off
set verify off
col sql_id for a18
col i_mem for 999999 heading ‘SHARED|Mem KB’
col sorts for 99999999
col version_count for 999 heading ‘VER|NUM’
col executions for 999999 heading ‘EXEC|NUM’
col parse_calls for 999999 heading ‘PARSE|CALLS’
col disk_reads for 999999 heading ‘DISK|READ’
col direct_writes for 999999 heading ‘DIRECT|WRITE’
col buffer_gets for 99999999999999
col avg_disk_reads for 99999 heading ‘AVG|DISK|READ’
col avg_direct_writes for 99999 heading ‘AVG|DIRECT|WRITE’
col avg_buffer_gets for 9999999 heading ‘AVG|BUFFER|GET’
col sql_profile for a14
col ROWS_PROCESSED for 999999999 heading ‘ROW|PROC’
col avg_rows_processed for 99999999 heading ‘AVG|ROW|PROC’
col avg_fetches for 999999 heading ‘AVG|FETCH’
col AVG_ELAPSED_TIME for 9999999 heading ‘AVG|ELAPSED|TIME’
col AVG_CPU_TIME for 9999999 heading ‘AVG|CPU_TIME’
col PARSING_SCHEMA_NAME for a15 heading ‘PARSING|SCHEMA_NAME’
SELECTplan_hash_value,parsing_schema_name,(executions) executions,(elapsed_time) elapsed_time,TRUNC ( (elapsed_time) / DECODE ( (executions), 0, 1, (executions)))avg_elapsed_time,(cpu_time) cpu_time,TRUNC ( (cpu_time) / DECODE ( (executions), 0, 1, (executions)))avg_cpu_time,(buffer_gets) buffer_gets,TRUNC ( (buffer_gets) / DECODE ( (executions), 0, 1, (executions)))avg_buffer_gets,(disk_reads) disk_reads,TRUNC ( (disk_reads) / DECODE ( (executions), 0, 1, (executions)))avg_disk_reads,(direct_writes) direct_writes,TRUNC ( (direct_writes) / DECODE ( (executions), 0, 1, (executions)))avg_direct_writes,(rows_processed) rows_processed,TRUNC ( (rows_processed) / DECODE ( (executions), 0, 1, (executions)))avg_rows_processed,(fetches) fetches,TRUNC ( (fetches) / DECODE ( (executions), 0, 1, (executions)))avg_fetchesFROM TABLE (DBMS_SQLTUNE.select_workload_repository (&begin_id,&end_id,‘sql_id=”&sqlid”’));
undefine begin_id;
undefine sqlid;
undefine end_id;
3，从sqlset中查看统计信息
set echo off
set lines 300
set verify off
set serveroutput on
set feedback off
set lines 300
set pages 10000
set long 100000
set lines 300
set echo off
set verify off
col sql_id for a15
col executions for 999999 heading ‘EXEC|NUM’
col parse_calls for 999999 heading ‘PARSE|CALLS’
col disk_reads for 999999 heading ‘DISK|READ’
col direct_writes for 999999 heading ‘DIRECT|WRITE’
col buffer_gets for 9999999999
col avg_disk_reads for 99999 heading ‘AVG|DISK|READ’
col avg_direct_writes for 99999 heading ‘AVG|DIRECT|WRITE’
col avg_buffer_gets for 999999 heading ‘AVG|BUFFER|GET’
col sql_profile for a14
col ROWS_PROCESSED for 999999 heading ‘ROW|PROC’
col avg_rows_processed for 99999 heading ‘AVG|ROW|PROC’
col avg_fetches for 99999 heading ‘AVG|FETCH’
col fetches for 9999999 heading ‘AVG|FETCH’
col AVG_ELAPSED_TIME for 999999 heading ‘AVG|ELAPSED|TIME’
col AVG_CPU_TIME for 9999999 heading ‘AVG|CPU_TIME’
col PARSING_SCHEMA_NAME for a15 heading ‘PARSING|SCHEMA_NAME’
col plan_hash_value for 99999999999 heading ‘PLAN|HASH_VALUE’col name for a30
col owner for a15
col description for a50
col sqlset_name for a20
SELECT a.id,a.name,a.owner,a.description,to_char(a.created,’yy-mm-dd’) created,to_char(a.last_modified,’yy-mm-dd hh24:mi’) last_modified,a.statement_count sql_countFROM dba_sqlset a
order by a.id/
SELECT sqlset_name,sql_id,plan_hash_value,parsing_schema_name,(executions) executions,(elapsed_time) elapsed_time,TRUNC ( (elapsed_time) / DECODE ( (executions), 0, 1, (executions)))avg_elapsed_time,(cpu_time) cpu_time,TRUNC ( (cpu_time) / DECODE ( (executions), 0, 1, (executions)))avg_cpu_time,(buffer_gets) buffer_gets,TRUNC ( (buffer_gets) / DECODE ( (executions), 0, 1, (executions)))avg_buffer_gets,(disk_reads) disk_reads,TRUNC ( (disk_reads) / DECODE ( (executions), 0, 1, (executions)))avg_disk_reads,(direct_writes) direct_writes,TRUNC ( (direct_writes) / DECODE ( (executions), 0, 1, (executions)))avg_direct_writes,(rows_processed) rows_processed,TRUNC ( (rows_processed) / DECODE ( (executions), 0, 1, (executions)))avg_rows_processed,(fetches) fetches,TRUNC ( (fetches) / DECODE ( (executions), 0, 1, (executions)))avg_fetchesFROM DBA_SQLSET_STATEMENTS
WHERE sqlset_name = NVL (UPPER (‘&sqlset_name’), sqlset_name)AND sql_id = NVL (‘&sql_id’, sql_id);
undefine begin_snap;
undefine sql_id;
undefine end_snap;
undefine sort_type;
undefine topn;
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
