1、执行计划
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));
首先、查看连接方式(哪一种连接方式占大多数)
其次、查看驱动表(从上到下,从左往右)-返回的数据量的大小
然后、再次查看依据两个连接表的数据量,确定连接方式是否正确
最后、若以上都没有问题,则查看cost,确定消耗资源的地方
2、配合执行计划的查看,常用的几个sql
a、查看索引的类型和基本信息
col OWNER for a20col index_name for a30
col index_type for a10
col status for a10
select OWNER,index_name,index_type,STATUS from dba_indexes where TABLE_NAME=upper('&table_name');
col COLUMN_NAME for a20
col INDEX_NAME for a30
col TABLE_OWNER for a20
col TABLE_NAME for a20
select COLUMN_NAME,INDEX_NAME,TABLE_OWNER,TABLE_NAME from dba_ind_columns where TABLE_NAME=upper('&table_name');
b、测试增加索引
select max(report_date) from czyjdb.tbl_rpt_card_reserve where node_no = 30251539 and report_date < 20140714 and card_type = '1';
select /*+ index(a,IDX_RPT_CARD_RESERVE_NODE_NO2) */max(report_date) from czyjdb.tbl_rpt_card_reserve a where node_no = 30251539 and report_date < 20140714 and card_type = '1';
创建了联合索引,但是查询后会产生回表读。
c、查询索引的选择性
select ut.num_rows "num_rows", ui.distinct_keys "distinct_keys", round((ui.distinct_keys/ut.num_rows), 2) "Rate"
from DBA_indexes ui,
DBA_tables ut
where ui.table_name = upper('&tablename')
and ui.index_name = upper('&indexname')
and ut.table_name = ui.table_name;
单独查看统计信息(表级别):--查看其它级别的也是这个视图,desc查看
select row_nums from dba_tables where table_name = upper('&tablename');
d、查询视图的源代码
set long 10000
select text from dba_views where view_name=upper('&viewname');
e、监控索引的使用情况
alter index <schema>.<index_name> monitoring usage;alter index <schema>.<index_name> nomonitoring usage;
查询到当前用户的索引信息
select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
查询到非当前用户的索引监控情况
select z.name||'.'||io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou ,sys.user$ z
Where i.obj# = ou.obj#
and io.obj# = ou.obj# And io.owner#=z.user#
and t.obj# = i.bo#
Order By 4 Desc,2 Desc;
f、收集统计信息
execute dbms_stats.gather_table_stats(ownname => 'schema',tabname => 'table_name' ,estimate_percent =>60 , method_opt=>'for all columns size 1',cascade=>true,degree=>8);
g、查看统计信息
select num_rows from DBA_tables where table_name = upper('&table_name');
定位到cost后发现没有走索引,但是依据索引的选择性,理应走索引;问题正在处理中