Oracle-洛总脚本--查询相关慢SQL

1、抓出外键没有创建索引的表(不依赖统计信息)
with cons as (select /*+ materialize */ owner,table_name,constraint_name from dba_constraints
where owner='SCOTT' and constraint_type='R'),
idx as (select /*+ materialize */ table_owner,table_name,column_name from dba_ind_columns
where table_owner='SCOTT')
select owner,table_name,constraint_name,column_name from dba_cons_columns
where (owner,table_name,constraint_name) in (select * from cons)
and (owner,table_name,column_name) not in (select * from idx);

2、抓出需要收集直方图的列(依赖统计信息)
select a.owner,a.table_name,a.column_name,b.num_rows,a.num_distinct cardinality,
round(a.num_distinct/b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner='SCOTT'
and round(a.num_distinct/b.num_rows*100,2)<5
and num_rows>50000
and (a.table_name,a.column_name) in
(select o.name,c.name from sys.col_usage$ u,sys.obj$ o ,sys.col$ c, sys.user$ r
where o.obj#=u.obj#
and c.obj#=u.obj#
and c.col#=u.intcol#
and r.name='SCOTT');

3、抓出必须创建索引的列(本脚本依赖统计信息)
select owner,table_name,column_name,num_rows,cardinality,selectivity
from (select a.owner,a.table_name,a.column_name,b.num_rows,a.num_distinct cardinality,
round(a.num_distinct/b.num_rows* 100,2) selectivity from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner and a.table_name=b.table_name and a.owner='SCOTT')
where selectivity>=20
and (table_name,column_name) not in
(select table_name,column_name from dba_ind_columns where table_owner='SCOTT' and column_position=1)
and (table_name,column_name) in
(select o.name,c.name from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
where o.obj#=u.obj# and c.obj#=u.obj# and c.col#=u.intcol# and r.name='SCOTT');

4、抓出select * 的SQL
select a.sql_id,a.sql_text,c.owner,d.table_name,d.column_cnt,c.size_mb
from v$sql a, v$sql_plan b,
(select owner,segment_name,sum(bytes/1024/1024) size_mb from dba_segments group by owner,segment_name) c,
(select owner,table_name,count(*) column_cnt from dba_tab_cols group by owner,table_name) d
where a.sql_id=b.sql_id
and a.child_number=b.child_number
and b.object_owner=c.owner
and b.object_name=c.segment_name
and b.object_owner=d.owner
and b.object_name=d.table_name
and regexp_count(b.projection,']')=d.column_cnt
and c.owner='SCOTT'
order by 6 desc;

5、抓出有标量子查询的SQL
select sql_id,sql_text,module
from v$sql
where parsing_schema_name='SCOTT'
and module='SQL*Plus'
and sql_id in (
select sql_id from (select sql_id,count(*) over(partition by sql_id,child_number,depth) cnt from v$sql_plan
where depth=1 and (object_owner='SCOTT' or object_owner is null))
where cnt>=2);

6、抓出带有自定义函数的SQL
select distinct sql_id,sql_text,module from v$sql,
(select object_name from dba_objects o where owner='SCOTT' and object_type in ('FUNCTION','PACKAGE'))
where (instr(upper(sql_text),object_name)>0)
and plsql_exec_time>0
and regexp_like(upper(sql_fulltext),'^[SELECT]')
and parsing_schema_name='SCOTT';

7、抓出表被多次反复调用的SQL
select a.parsing_schema_name schema,
a.sql_id,a.sql_text,b.object_name,b.cnt
from v$sql a,(select * from (select sql_id,child_number,object_owner,object_name,object_type,count(*) cnt
from v$sql_plan where object_owner='SCOTT' group by sql_id,child_number,object_owner,object_name,object_type)
where cnt>=2) b
where a.sql_id=b.sql_id
and a.child_number=b.child_number;

8、抓出走了filter的SQL
select parsing_schema_name schema,sql_id,sql_text from v$sql
where parsing_schema_name='SCOTT'
and (sql_id,child_number) in
(select sql_id,child_number from v$sql_plan where operation='FILTER' and filter_predicates like '%IS NOT NULL%'
minus
select sql_id,child_number from v$sql_plan where object_owner='SYS');

9、抓出返回行数较多的嵌套循环SQL
select * from (select parsing_schema_name schema,
sql_id,sql_text,rows_processed/executions rows_processed from v$sql
where parsing_schema_name='SYS'
and executions > 0
and rows_processed / executions > 10000
order by 4 desc) a
where a.sql_id in (select sql_id from v$sql_plan where operation like '%NESTED LOOPS%' and id<=5);

10、抓出NL被驱动表走了全表扫描的SQL
select c.sql_text,a.sql_id,b.object_name,d.mb from v$sql_plan a,
(select * from (select sql_id,child_number,object_owner,object_name,parent_id,operation,options,
row_number() over(partition by sql_id,child_number,parent_id order by id) rn from v$sql_plan) where rn=2) b,
v$sql c,(select owner,segment_name,sum(bytes/1024/1024) mb from dba_segments group by owner,segment_name) d
where b.sql_id=c.sql_id and b.child_number=c.child_number and b.object_owner='SCOTT' and a.sql_id=b.sql_id
and a.child_number=b.child_number and a.operation like '%NESTED LOOPS%' and a.id=b.parent_id and b.operation='TABLE ACCESS'
and b.options='FULL' and b.object_owner=d.owner and b.object_name=d.segment_name order by 4 desc;

11、抓出走了TABLE ACCESS FULL的SQL
select a.sql_id,a.sql_text,d.table_name,regexp_count(b.projection,']')||'/'||d.column_cnt column_cnt,
c.size_mb,b.filter_predicates filter
from v$sql a, v$sql_plan b,(select owner,segment_name,sum(bytes/1024/1024) size_mb from dba_segments group by owner,segment_name) c,
(select owner,table_name,count(*) column_cnt from dba_tab_cols group by owner,table_name) d
where a.sql_id=b.sql_id and a.child_number=b.child_number and b.object_owner=c.owner and b.object_name=c.segment_name
and b.object_owner=d.owner and b.object_name=d.table_name and c.owner='SCOTT' and b.operation='TABLE ACCESS' and b.options='FULL'
order by 5 desc;

12、抓出走了INDEX FULL SCAN的SQL
select c.sql_text,c.sql_id,b.object_name,d.mb from v$sql_plan b,v$sql c,
(select owner,segment_name,sum(bytes/1024/1024/1024) mb from dba_segments group by owner,segment_name) d
where b.sql_id=c.sql_id and b.child_number=c.child_number and b.object_owner='SCOTT' and b.operation='INDEX'
and b.options='FULL SCAN' and b.object_owner=d.owner and b.object_name=d.segment_name order by 4 desc;

13、抓出走了INDEX SKIP SCAN的SQL
select c.sql_text,c.sql_id,b.object_name,d.mb from v$sql_plan b,v$sql c,
(select owner,segment_name,sum(bytes/1024/1024) mb from dba_segments group by owner,segment_name) d
where b.sql_id=c.sql_id and b.child_number=c.child_number and b.object_owner='SCOTT' and b.operation='INDEX'
and b.options='SKIP SCAN' and b.object_owner=d.owner and b.object_name=d.segment_name order by 4 desc;

14、抓出索引被哪些SQL引用
select a.sql_text,a.sql_id,b.object_owner,b.object_name,b.object_type from v$sql a,v$sql_plan b
where a.sql_id=b.sql_id and a.child_number=b.child_number and object_owner='SCOTT' and object_type like '%INDEX%'
order by 3,4,5;

15、抓出走了笛卡尔积的SQL
select c.sql_text,a.sql_id,b.object_name,a.filter_predicates filter,a.access_predicates predicate,d.mb
from v$sql_plan a,(select * from (select sql_id,child_number,object_owner,object_name,parent_id,operation,options,
row_number() over(partition by sql_id,child_number,parent_id order by id) rn from v$sql_plan) where rn=1) b,
v$sql c,(select owner,segment_name,sum(bytes/1024/1024) mb from dba_segments group by owner,segment_name) d
where b.sql_id=c.sql_id and b.child_number=c.child_number and b.object_owner='SCOTT' and a.sql_id=b.sql_id
and a.child_number=b.child_number and a.operation='MERGE JOIN' and a.id=b.parent_id and a.options='CARTESIAN'
and b.object_owner=d.owner and b.object_name=d.segment_name order by 4 desc;

16、抓出走了错误的排序合并连接的SQL
select c.sql_id,c.sql_text,d.owner,d.segment_name,d.mb from v$sql_plan a,v$sql_plan b,v$sql c,
(select owner,segment_name,sum(bytes/1024/1024) mb from dba_segments group by owner,segment_name) d
where a.sql_id=b.sql_id and a.child_number=b.child_number and b.operation='SORT' and b.options='JOIN'
and b.access_predicates like '%"="%' and a.parent_id=b.id and a.object_owner='SCOTT' and b.sql_id=c.sql_id
and b.child_number=c.child_number and a.object_owner=d.owner and a.object_name=d.segment_name
order by 4 desc;

17、抓出LOOP套LOOP的PSQL
with x as
(select /*+ materialize */ owner,name,type,line,text,rownum rn from dba_source where
(upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'))
select a.owner,a.name,a.type from x a,x b
where ((upper(a.text) like '%END%LOOP%' and upper(b.text) like '%END%LOOP%' and a.rn+1=b.rn)
or (upper(a.text) like '%FOR%LOOP%' and upper(b.text) like '%FOR%LOOP%' and a.rn+1=b.rn))
and a.owner=b.owner and a.name=b.name and a.type=b.type and a.owner='SCOTT';

18、抓出走了低选择性索引的SQL(依赖于统计信息)
select c.sql_id,c.sql_text,b.index_name,e.table_name,trunc(d.num_distinct/e.num_rows*100,2) selectivity,
d.num_distinct,e.num_rows
from v$sql_plan a,(select * from (select index_owner,index_name,table_owner,table_name,column_name,
count(*) over(partition by index_owner,index_name,table_owner,table_name) cnt from dba_ind_columns)
where cnt=1) b, v$sql c,dba_tab_col_statistics d,dba_tables e
where a.object_owner=b.index_owner and a.object_name=b.index_name and b.index_owner='SCOTT'
and a.access_predicates is not null and a.sql_id=c.sql_id and a.child_number=c.child_number
and d.owner=e.owner and d.table_name=e.table_name and b.table_owner=e.owner and b.table_name=e.table_name
and d.column_name=b.column_name and d.table_name=b.table_name and d.num_distinct/e.num_rows<0.1;

19、抓出可以创建组合索引的SQL(回表再过滤选择性高的列，依赖统计信息)
select a.sql_id,a.sql_text,f.table_name,c.size_mb,e.column_name,round(e.num_distinct/f.num_rows*100,2) selectivity
from v$sql a,v$sql_plan b,(select owner,segment_name,sum(bytes/1024/1024/1024) size_mb from dba_segments
group by owner,segment_name) c,dba_tab_col_statistics e,dba_tables f
where a.sql_id=b.sql_id and a.child_number=b.child_number and b.object_owner=c.owner and b.object_name=c.segment_name
and e.owner=f.owner and e.table_name=f.table_name and b.object_owner=f.owner and b.object_name=f.table_name
and instr(b.filter_predicates,e.column_name)>0 and (e.num_distinct/f.num_rows)>0.1 and c.owner='SCOTT'
and b.operation='TABLE ACCESS' and b.options='BY INDEX ROWID' and e.owner='SCOTT' order by 4 desc;

20、抓出可以创建组合索引的SQL(回表只访问少数字段)
select a.sql_id,a.sql_text,d.table_name,REGEXP_COUNT(b.projection,']')||'/'||d.column_cnt column_cnt,
c.size_mb,b.filter_predicates filter from v$sql a,v$sql_plan b,
(select owner,segment_name,sum(bytes/1024/1024) size_mb from dba_segments group by owner,segment_name) c,
(select owner,table_name,count(*) column_cnt from dba_tab_cols group by owner,table_name) d
where a.sql_id=b.sql_id and a.child_number=b.child_number and b.object_owner=c.owner
and b.object_name=c.segment_name and b.object_owner=d.owner and b.object_name=d.table_name
and c.owner='SCOTT' and b.operation='TABLE ACCESS' and b.options='BY INDEX ROWID'
and REGEXP_COUNT(b.projection,']')/d.column_cnt<0.25
order by 5 desc;

----查询是否合适创建 索引

select a.column_name,
b.num_rows,
a.num_distinct Cardinlity,
round(a.num_distinct / b.num_rows * 100, 2) selectitity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = '&owner'
and a.table_name = '&table_name'
ORDER BY round(a.num_distinct / b.num_rows * 100, 2) DESC;

日积月累