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;