oracle查看建表语句sql_如何从oracle执行计划看sql语句执行效率？

概述

如何快速判断 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 200set pagesize 200select * 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 dualCONNECT 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 200set pagesize 200explain 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 200set autotrace traceonlyselect * 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 isv_name sex.name%type;beginselect nameinto v_namefrom sexwhere sex_id=p_id;return v_name;end;/

3、查看递归调用

set autotrace traceonlyselect 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 dualCONNECT 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 traceonlyselect * from t where object_id>2 order by object_id;

---

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

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