来吧，展示：如何诊断一条sql性能

逐条执行，根据结果分析sql性能

首先要知道sql_id
select username,program,event,sql_id from gv$session where wait_class<>'Idle';


--查看执行计划
set linesize 160 pagesize 1000
col column_name for a30 trunc
select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
select * from table(dbms_xplan.display_awr('&sql_id'));


--查看SQL MONITOR
set long 50000 longc 100000  linesize 200 pagesize 10000
select dbms_sqltune.report_sql_monitor(sql_id => '&sql_id' , type => 'text') from dual;


--查看SQL的历史执行情况
set lines 200
set pages 1000
col shijian for a12
col execu_d for 999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999


select to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24') shijian,
       plan_hash_value,
       sum(a.EXECUTIONS_DELTA) execu_d,
       sum(a.BUFFER_GETS_DELTA) bg_d,
       sum(a.DISK_READS_DELTA) dr_d,
       sum(a.ELAPSED_TIME_DELTA / 1000000) et_d,
       sum(a.CPU_TIME_DELTA / 1000000) ct_d,
       sum(IOWAIT_DELTA / 1000000) io_time,
       sum(CLWAIT_DELTA / 1000000) clus_time,
       sum(APWAIT_DELTA / 1000000) ap_time,
       sum(ccwait_delta / 1000000) cc_time,
       decode(sum(a.EXECUTIONS_DELTA),
              0,
              sum(a.BUFFER_GETS_DELTA),
              round(sum(a.BUFFER_GETS_DELTA) / sum(a.EXECUTIONS_DELTA), 0)) get_onetime,
       decode(sum(a.EXECUTIONS_DELTA),
              0,
              sum(a.rows_processed_delta),
              round(sum(a.rows_processed_delta) / sum(a.EXECUTIONS_DELTA), 0)) rows_onetime,
       decode(sum(a.EXECUTIONS_DELTA),
              0,
              sum(a.ELAPSED_TIME_DELTA / 1000),
              round(sum(a.ELAPSED_TIME_DELTA / 1000) /
                    sum(a.EXECUTIONS_DELTA),
                    0)) et_ms_once
  from dba_hist_sqlstat a, dba_hist_snapshot b
 where a.SNAP_ID = b.SNAP_ID
   and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
   and a.sql_id = '&sql_id'
 group by to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24'),plan_hash_value
 order by 1,2;


--查看相关表上的索引是在哪些列上创建的（有多个执行多次）
col INDEX_NAME for a30
col COLUMN_NAME for a30
select index_name,column_name,column_position from dba_ind_columns
where table_name=upper('&TABLE_NAME') order by 1,3;


--查看相关表上的索引的状态和统计信息
col INDEX_NAME for a30
select index_name,status,num_rows,leaf_blocks ,partitioned
from dba_indexes where table_name=upper('&TABLE_NAME') order by 1;


--查看相关表的统计信息
col owner for a20
col table_name for a30
select owner,table_name,num_rows,blocks,partitioned,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where table_name=upper('&TABLE_NAME');


--查看相关表上的列的选择性
col COLUMN_NAME for a30
select column_name,num_distinct,histogram 
from dba_tab_col_statistics  
where table_name=upper('&TABLE_NAME')
 order by 1;

剩下怎么办，就交给你了，回见，再也不见。

诊断sql性能.txt