概述

如何快速判断 SOL 执行计划是否高效，其实这是一个知识和经验的完美结合过程。其实也有一些维度可以作为参考的，下面一起来看看吧。

从执行计划读效率--返回行与逻辑读比率

DROP TABLE t;
CREATE TABLE t as select * from dba_objects;
--CREATE INDEX idx ON t (object_id);
alter session set statistics_level=all;
set linesize 200
set pagesize 200
select * from t where object_id=6;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

说明：总共获取 1 条记录（A-ROWS），产生 1249 次逻辑读（ Buffers）,这里很明显就有问题了。

从执行计划读效率--评估值准确的重要性

1、创建测试数据

DROP TABLE t1;
CREATE TABLE t1 (id, col1, col2, pad) AS SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END,rownum, lpad('*',100,'*') FROM dual
CONNECT BY level <= 10000;
INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;
COMMIT;
CREATE INDEX t1_col1 ON t1 (col1);
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);

2、统计分析，但是不搜集直方图

BEGIN
 dbms_stats.gather_table_stats(
 ownname=>user, 
 tabname=>'T1', 
 cascade=>TRUE,
 estimate_percent=>100,
 method_opt=>'for all columns size 1',
 no_invalidate=>FALSE);
END;
/
BEGIN
 dbms_stats.gather_table_stats(
 ownname=>user, 
 tabname=>'T2', 
 cascade=>TRUE,
 estimate_percent=>100,
 method_opt=>'for all columns size 1',
 no_invalidate=>FALSE);
END;
/

3、评估值是否准确

set linesize 200
set pagesize 200
explain plan for SELECT count(t2.col2) FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
select * from table(dbms_xplan.display());

SELECT /*+ gather__plan_statistics */ count(t2.col2) FROM t1, t2 WHERE t1.id=t2.id and t1.col1 = 666;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

从执行计划读效率--类型转换

drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 200
set autotrace traceonly
select * from t_col_type where id=6;

如果是filter一般就是走全扫了，access是索引扫

从执行计划读效率--递归调用

1、创建测试数据

set autotrace off;
drop table people purge;
create table people (first_name varchar2(200),last_name varchar2(200),sex_id number);
create table sex(name varchar2(20), sex_id number);
insert into people (first_name,last_name,sex_id) select object_name,object_type,1 from dba_objects;
insert into sex(name,sex_id) values('男',1);
insert into sex(name,sex_id) values('女',2);
insert into sex(name,sex_id) values ('不详',3);
commit;

2、创建函数

create or replace function get_sex_name(p_id sex.sex_id%type) return sex.name%type is
v_name sex.name%type;
begin
select name
into v_name
from sex
where sex_id=p_id;
return v_name;
end;
/

3、查看递归调用

set autotrace traceonly
select sex_id,first_name||' '||last_name full_name,get_sex_name(sex_id) gender from people;

从执行计划读效率--表访问次数

1、创建测试数据

DROP TABLE t1;
CREATE TABLE t1 (id, col1, col2, pad)
AS 
SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END, rownum, lpad('*',100,'*')
FROM dual
CONNECT BY level <= 10000;
INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;
COMMIT;
CREATE INDEX t1_col1 ON t1 (col1);
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);

2、统计分析，但是不搜集直方图

BEGIN
 dbms_stats.gather_table_stats(
 ownname=>user, 
 tabname=>'T1', 
 cascade=>TRUE,
 estimate_percent=>100,
 method_opt=>'for all columns size 1',
 no_invalidate=>FALSE);
END;
/
BEGIN
 dbms_stats.gather_table_stats(
 ownname=>user, 
 tabname=>'T2', 
 cascade=>TRUE,
 estimate_percent=>100,
 method_opt=>'for all columns size 1',
 no_invalidate=>FALSE);
END;
/

3、查看表访问次数

alter session set statistics_level=all;
SELECT /*+ gather_plan_statistics */ count(t2.col2) FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

从执行计划读效率--表真实访问行数

drop table t1 cascade constraints;
create table t1 as select * from dba_objects;
drop table t2 cascade constraints;
create table t2 (id1,id2) as select rownum ,rownum+100 from dual connect by level <=1000;
set autotrace traceonly;
select * from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1) a where a.rn >= 1 and a.rn <= 10;
select * from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10 ) a where a.rn >= 1;

从执行计划读效率--是否排序

drop table t purge;
create table t as select * from dba_objects;
set autotrace traceonly
select * from t where object_id>2 order by object_id;

上面是几个维度判断执行计划中sql的效率，实际上还有一个计算方式是通过consistent gets来计算内存大致使用多少，通过physical reads来计算IO。

后面会分享更多关于DBA方面内容，感兴趣的朋友可以关注下！！