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

【学习笔记】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的统计信息案例