sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle从cursor,awr中查询指定SQL的统计信息案例

时间:2016-10-27 22:09   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库性能优化的文章,从cursor,awr中查询指定SQL的统计信息详细案例。

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) / DECODhttp://www.oracleplus.netE ( (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;

本文固定链接: http://www.htz.pw/2014/07/07/%e5%b8%b8%e7%94%a8%e8%84%9a%e6%9c%ac4%e6%9f%a5%e8%af%a2%e6%8c%87%e5%ae%9asql%e7%9a%84%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%afcursorawr.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle从cursor,awr中查询指定SQL的统计信息案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1086.html

Oracle研究中心

关键词:

Oracle从cursor中查询指定sql的统计信息的方法,Oracle从awr中查询指定sql的统计信息的方法

Oracle从sqlset中查看统计信息