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

原创

mb43f60adeco02a 2025-10-09 15:56:31 ©著作权

文章标签 sql 数据库 sed EXEC 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者mb43f60adeco02a的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

1,从cursor中查询指定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
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;

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等