首页 > 代码库 > 识别低效率的SQL语句
识别低效率的SQL语句
1.返回行与逻辑读的比率
CREATE TABLE t as select * from dba_objects;--CREATE INDEX idx ON t (object_id);---例1alter session set statistics_level=all;set linesize 1000set pagesize 2000select * from t where object_id=6;SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,‘allstats last‘));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------SQL_ID 8cxbzma1az713, child number 0-------------------------------------select * from t where object_id=6Plan hash value: 1601196873---------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.07 | 1048 | 774 ||* 1 | TABLE ACCESS FULL| T | 1 | 12 | 1 |00:00:00.07 | 1048 | 774 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"=6)Note----- - dynamic sampling used for this statement (level=2)
上面的语句只返回了1行数据却产生了1048个逻辑读。
执行计划显示的是全表扫描，创建索引
CREATE INDEX idx ON t (object_id);
执行计划如下：
select * from t where object_id=6Plan hash value: 2770274160----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.01 | 4 ||* 2 | INDEX RANGE SCAN | IDX | 1 | 1 | 1 |00:00:00.01 | 3 |----------------------------------------------------------------------------------------------
逻辑读为4。
2.执行计划中的评估是否准确。
查看e-rows 预估数量，a-rows 实际返回的数量。如果相差过大则说明需要收集表的统计信息。
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,‘allstats last‘));
3.类型转换需要关注。
举例如下：
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);
注意ID的数据类型为VARCHAR(20)
select * from t_col_type where id=6;执行计划----------------------------------------------------------Plan hash value: 3191204463--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_NUMBER("ID")=6)
注意这里没有使用到索引。
执行
select * from t_col_type where id=‘6‘;执行计划----------------------------------------------------------Plan hash value: 3998173245------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=‘6‘)
使用到了索引。
4.小心递归调用。
6种获取执行计划的方法中，只有 autotrace 的方式可以看出递归调用的次数（recursive calls）
注意SQL中尽量不要使用函数的使用例如：
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;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;/
执行：
set linesize 1000set pagesize 2000set autotrace traceonly--例1：select sex_id,first_name||‘ ‘||last_name full_name,get_sex_name(sex_id) genderfrom people;
执行计划如下：
----------------------------------------------------------Plan hash value: 2528372185----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 80635 | 16M| 137 (1)| 00:00:02 || 1 | TABLE ACCESS FULL| PEOPLE | 80635 | 16M| 137 (1)| 00:00:02 |----------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 73121 recursive calls 0 db block gets 517142 consistent gets 0 physical reads 0 redo size 3382143 bytes sent via SQL*Net to client 54029 bytes received via SQL*Net from client 4876 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73121 rows processed
产生了73121此递归调用。
消除办法，直接使用关联查询。
5.表的访问次数。
6种获取执行计划的方法中，只有 statisitcs_level=all 的方式可以看出表访问次数（STARTS)，这个很重要！
执行：
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‘));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID g048suxnxkxyr, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ count(t2.col2) FROM t1 ,t2 WHEREt1.id=t2.id and t1.col1 = 666Plan hash value: 3711554156----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.30 | 94651 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.30 | 94651 || 2 | NESTED LOOPS | | 1 | | 75808 |00:00:00.31 | 94651 || 3 | NESTED LOOPS | | 1 | 32 | 75808 |00:00:00.19 | 18843 || 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 32 | 80016 |00:00:00.08 | 1771 ||* 5 | INDEX RANGE SCAN | T1_COL1 | 1 | 32 | 80016 |00:00:00.03 | 169 ||* 6 | INDEX UNIQUE SCAN | T2_PK | 80016 | 1 | 75808 |00:00:00.08 | 17072 || 7 | TABLE ACCESS BY INDEX ROWID | T2 | 75808 | 1 | 75808 |00:00:00.08 | 75808 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("T1"."COL1"=666) 6 - access("T1"."ID"="T2"."ID")
这里表的访问次数过大，应该走hash或排序合并连接，原因是表的收集信息不准确。
NL连接表的访问次数不会这么大。
6.注意表的真实访问行数。
数据准备：
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;alter session set statistics_level=all;set linesize 1000set pagesize 2000
优化前执行如下：
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 table(dbms_xplan.display_cursor(NULL,NULL,‘allstats last‘));SQL_ID ayzfn8k0j3sms, child number 0-------------------------------------select * from (select t1.*, rownum as rn from t1, t2 wheret1.object_id = t2.id1) a where a.rn >= 1 and a.rn <= 10Plan hash value: 3062220019---------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.11 | 1052 | 749 | | | ||* 1 | VIEW | | 1 | 1008 | 10 |00:00:00.11 | 1052 | 749 | | | || 2 | COUNT | | 1 | | 943 |00:00:00.11 | 1052 | 749 | | | ||* 3 | HASH JOIN | | 1 | 1008 | 943 |00:00:00.11 | 1052 | 749 | 1036K| 1036K| 1197K (0)|| 4 | TABLE ACCESS FULL| T2 | 1 | 1000 | 1000 |00:00:00.01 | 4 | 0 | | | || 5 | TABLE ACCESS FULL| T1 | 1 | 70183 | 73156 |00:00:00.08 | 1048 | 749 | | | |---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(("A"."RN"<=10 AND "A"."RN">=1)) 3 - access("T1"."OBJECT_ID"="T2"."ID1")Note----- - dynamic sampling used for this statement (level=2)
这个查询总共返回10记录，但是内部查询返回了73156 条记录。
优化后：
select * from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) a where a.rn >= 1; SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,‘allstats last‘));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------SQL_ID 7wzvqay91x14y, child number 0-------------------------------------select * from (select t1.*, rownum as rn from t1, t2 wheret1.object_id = t2.id1 and rownum<=10) a where a.rn >= 1Plan hash value: 1802812661------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 9 | | | ||* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 9 | | | ||* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 9 | | | ||* 3 | HASH JOIN | | 1 | 1008 | 10 |00:00:00.01 | 9 | 1036K| 1036K| 1210K (0)|| 4 | TABLE ACCESS FULL| T2 | 1 | 1000 | 1000 |00:00:00.01 | 4 | | | || 5 | TABLE ACCESS FULL| T1 | 1 | 70183 | 10 |00:00:00.01 | 5 | | | |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"."RN">=1) 2 - filter(ROWNUM<=10) 3 - access("T1"."OBJECT_ID"="T2"."ID1")Note----- - dynamic sampling used for this statement (level=2)
T1表只返回十条数据。
这种修改 可以优化分页数据。
第一页select *from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) awhere a.rn >= 1;第二页select *from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=20) awhere a.rn >= 10;第三页select *from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=30) awhere a.rn >= 20;
这样，可以提高前几页的分页效率。
7.使用索引消除排序。
比如需要根据object_id 进行排序，那么可以使用索引消除排序操作，因为索引本身有序。
create index idx_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。