常用脚本4:查询指定SQL的统计信息(cursor,awr)

huangtingzhong 发布于 2014-07-07 00:16SQL抢沙发

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’
SELECT
       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_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’
SELECT
       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_fetches

FROM 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_count
FROM 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_fetches
FROM 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;