来吧，展示：如何诊断一条sql性能-凯发app官方网站

逐条执行，根据结果分析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;

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

阅读(6606) | 评论(0) | 转发(0) |

上一篇：在oracle rac 19c和18c上使用pdb时的性能问题考虑

下一篇：ora.drivers.acfs 状态offline

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们