idea怎么看sql执行打印_如何从oracle执行计划看sql语句执行效率？

以你之姓@ 2023-01-10 13:11 203阅读 0赞

# 概述 #

如何快速判断 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'));

![8a68bf761f9ab1f50c4f5ffa109aea23.png][]

![de4d0fe0ead3e983f4d821ec5ff32c6d.png][]

说明：总共获取 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);

![770443aa82b5c0d09a974271157599d1.png][]

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;/

![e3fd13e29d823e45e4043e25af3526a4.png][]

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());

![cd923962eb769fc8b8a5a13a2fce3534.png][]

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'));

![a739fe5baf1e22302fb0f7500ca6af90.png][]

--------------------

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

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;

![e5a3253f671b39efbdcaad80edc7cf70.png][]

![a69a1b74d30c966095470e4766608db2.png][]

如果是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;

![0dbea8ceb61cacdc92d607cced51be2d.png][]

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;/

![282c4e944c010462032818d7c8cd37b1.png][]

3、查看递归调用

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

![a4aa99eb31bb30f2704550f65d73fdbc.png][]

--------------------

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

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);

![b347db59b8dc6943cfd0b24ee687a275.png][]

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

![616b5fe0fc2490f1d537e26f181930e1.png][]

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'));

![9aa4686b72e0a07e4fce0b539744e3e9.png][]

--------------------

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

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;

![3b7aa9eabee22e3c7798351fcfd83fe0.png][]

![d7978d3e35aa17da7083f5ac79e7e19d.png][]

![927406b28f71ed2204b959b8e8e4b950.png][]

--------------------

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

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;

![9ef947297c8e282ba3bf8508d5b98088.png][]

--------------------

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

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

![20e9cf46ee4cfb4bcf78a2f2b274819d.gif][]

[8a68bf761f9ab1f50c4f5ffa109aea23.png]: /images/20221119/402166d983444aabb264d0a982c3fc60.png
[de4d0fe0ead3e983f4d821ec5ff32c6d.png]: /images/20221119/4b7a776050ef422390e38a5a12c7292b.png
[770443aa82b5c0d09a974271157599d1.png]: /images/20221119/fa75493634f7441b9eae8ba26f1a982c.png
[e3fd13e29d823e45e4043e25af3526a4.png]: /images/20221119/873db4fa399f469b9312665ee156d9d6.png
[cd923962eb769fc8b8a5a13a2fce3534.png]: https://img-blog.csdnimg.cn/img_convert/cd923962eb769fc8b8a5a13a2fce3534.png
[a739fe5baf1e22302fb0f7500ca6af90.png]: https://img-blog.csdnimg.cn/img_convert/a739fe5baf1e22302fb0f7500ca6af90.png
[e5a3253f671b39efbdcaad80edc7cf70.png]: https://img-blog.csdnimg.cn/img_convert/e5a3253f671b39efbdcaad80edc7cf70.png
[a69a1b74d30c966095470e4766608db2.png]: https://img-blog.csdnimg.cn/img_convert/a69a1b74d30c966095470e4766608db2.png
[0dbea8ceb61cacdc92d607cced51be2d.png]: https://img-blog.csdnimg.cn/img_convert/0dbea8ceb61cacdc92d607cced51be2d.png
[282c4e944c010462032818d7c8cd37b1.png]: https://img-blog.csdnimg.cn/img_convert/282c4e944c010462032818d7c8cd37b1.png
[a4aa99eb31bb30f2704550f65d73fdbc.png]: https://img-blog.csdnimg.cn/img_convert/a4aa99eb31bb30f2704550f65d73fdbc.png
[b347db59b8dc6943cfd0b24ee687a275.png]: https://img-blog.csdnimg.cn/img_convert/b347db59b8dc6943cfd0b24ee687a275.png
[616b5fe0fc2490f1d537e26f181930e1.png]: https://img-blog.csdnimg.cn/img_convert/616b5fe0fc2490f1d537e26f181930e1.png
[9aa4686b72e0a07e4fce0b539744e3e9.png]: https://img-blog.csdnimg.cn/img_convert/9aa4686b72e0a07e4fce0b539744e3e9.png
[3b7aa9eabee22e3c7798351fcfd83fe0.png]: https://img-blog.csdnimg.cn/img_convert/3b7aa9eabee22e3c7798351fcfd83fe0.png
[d7978d3e35aa17da7083f5ac79e7e19d.png]: https://img-blog.csdnimg.cn/img_convert/d7978d3e35aa17da7083f5ac79e7e19d.png
[927406b28f71ed2204b959b8e8e4b950.png]: https://img-blog.csdnimg.cn/img_convert/927406b28f71ed2204b959b8e8e4b950.png
[9ef947297c8e282ba3bf8508d5b98088.png]: https://img-blog.csdnimg.cn/img_convert/9ef947297c8e282ba3bf8508d5b98088.png
[20e9cf46ee4cfb4bcf78a2f2b274819d.gif]: https://img-blog.csdnimg.cn/img_convert/20e9cf46ee4cfb4bcf78a2f2b274819d.gif