在oracle数据库中，一个sql语句有多个执行计划，意味着这个sql语句不稳定，有可能出现有时查询快，有时查询慢，比如这个sql在某一个列上有索引，大家认为有索引了，sql查询肯定会很快，但是即便有索引，但是在oracle数据库中，索引扫描有很多种方式，索引的访问模式有八种。1.INDEX UNIQUE SCAN 效率最高，主键或唯一索引，走树结构。2.INDEX FAST FULL SCAN 读所有块，可以并行访问索引，但输出不按顺序。3.INDEX FULL SCAN 有顺序的输出，不能并行读索引，走链表结构。4.INDEX RANGE SCAN 给定的区间查询，最常见的访问模式。5.INDEX SKIP SCAN 联合索引的第二列为条件，不同值越少的列，越要放在前面。6. SCAN DESCENDING 降序扫描，自动选择降序使用索引。7. index join 索引的连接，通过索引获得全部数据，可以不扫描表。8. bitmap join 索引的位图连接，多个条件上的列都有索引的情况。INDEX UNIQUE SCAN 扫描肯定比INDEX FULL SCAN要快很多。所以一旦一个sql有多个执行计划时，就会出现有时快，有时慢的情况。我们可以使用下面的sql查询发现oracle数据库查询有多个执行计划的sql。
SQL> SELECT 2 sql_id, 3 COUNT(DISTINCT plan_hash_value) as plan_count, 4 COUNT(DISTINCT child_number) as child_count, 5 SUM(executions) as total_executions, 6 MIN(ROUND(elapsed_time/NULLIF(executions, 0)/1000000, 4)) as min_avg_elapsed_sec, 7 MAX(ROUND(elapsed_time/NULLIF(executions, 0)/1000000, 4)) as max_avg_elapsed_sec, 8 SUBSTR((SELECT sql_text FROM v$sqltext_with_newlines 9 WHERE sql_id = v.sql_id AND piece = 0), 1, 100) as sql_text_sample 10 FROM v$sql v 11 WHERE executions > 0 12 AND plan_hash_value > 0 13 GROUP BY sql_id 14 HAVING COUNT(DISTINCT plan_hash_value) > 1 15 ORDER BY plan_count DESC, max_avg_elapsed_sec DESC; SQL_ID PLAN_COUNT CHILD_COUNT TOTAL_EXECUTIONS MIN_AVG_ELAPSED_SEC MAX_AVG_ELAPSED_SEC SQL_TEXT_SAMPLE ------------- ---------- ----------- ---------------- ------------------- ------------------- -------------------------------------------------------------------------------- bbx4agjfr6yrp 2 5 244 0.0011 0.1839 SELECT c.name, c.col#, c.segcol#, c.intcol#, c.type#, c.length, 8vgz0m72d5sp9 2 2 3 0.0158 0.025 with non_existent_objs as (select /*+ materialize */ exp_id, obj g94wn7w0dr4tp 2 2 2 0.0082 0.0099 delete /* KSXM:CLEAN_PEND_COL *//*+ dynamic_sampling(4) */ fro 8dz7mfwvwtckz 2 2 4 0.0012 0.009 SELECT PNAME, LENGTH(PNAME), QTABLE FROM SYS.LOADER_PART_INFO 4faa5w420ua14 2 2 2 0.0064 0.0064 delete /* KSXM:CLEAN_PEND_HIST *//*+ dynamic_sampling(4) */ fr 5pazx6dft6hru 2 9 2360 0.0001 0.0043 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, 4rs3f2phhsb80 2 2 3 0.004 0.0041 delete /* KSXM:CLEAN_DML_INF *//*+ dynamic_sampling(4) */ fro fzsxusp93pxcc 2 4 97 0.0001 0.003 DELETE FROM wri$_adv_rec_actions a WHERE a.task_id = :task_ gxrr466g0v9ck 2 2 7 0.0024 0.0026 delete /* KSXM:CLEAN_PEND_TAB *//*+ dynamic_sampling(4) */ fro a81vzf0fa0q1p 2 2 8 0.0006 0.0026 delete /* KSXM:CLEAN_PEND_IND *//*+ dynamic_sampling(4) */ fro 9kg6pqttrtda0 2 8 1008 0.0001 0.0024 select p_obj# from dependency$ d where d.d_obj#=:1 and not ex 6axhw420xfds6 2 4 89 0.0005 0.002 select unique version from idl_ub1$ where obj# = :1 and version fnafmumu52s48 2 2 27 0.0004 0.0007 delete /* KSXM:CLEAN_USER_PREF *//*+ dynamic_sampling(4) */ fr 0kxn8zx1svcbp 2 7 2948 0 0.0004 select distinct policy# from aud_object_opt$ where object# = :1 14 rows selected SQL>
查询结果中的PLAN_COUNT列表示这个sql有多少个执行计划。SQL_TEXT_SAMPLE是具体的sql语句。因此根据这两个条件可以判断sql的执行情况。可以使用下面的sql查看sql的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bcxdun3fmn0um', 0, 'TYPICAL')); -- child_number 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bcxdun3fmn0um', 1, 'TYPICAL')); -- child_number 1
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bcxdun3fmn0um', 0, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bcxdun3fmn0um, child number 0
-------------------------------------
DELETE FROM "LISMAN"."LIITEMRISKCODEINFO" WHERE "SERIALNO" = :b0
Plan hash value: 1944287200
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)
| 1 | DELETE | LIITEMRISKCODEINFO | | |
|* 2 | INDEX UNIQUE SCAN| PK_LIITEMRISKCODEINFO | 1 | 19 | 2 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SERIALNO"=:B0)
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- SQL profile coe_bcxdun3fmn0um_1944287200 used for this statement
23 rows selected
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bcxdun3fmn0um', 1, 'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bcxdun3fmn0um, child number 0
-------------------------------------
DELETE FROM "LISMAN"."LIITEMRISKCODEINFO" WHERE "SERIALNO" = :b0
Plan hash value: 1944287200
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)
| 1 | DELETE | LIITEMRISKCODEINFO | | |
|* 2 | INDEX FULL SCAN | PK_LIITEMRISKCODEINFO | 200| 170| 8 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SERIALNO"=:B0)
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- SQL profile coe_bcxdun3fmn0um_1944287200 used for this statement
23 rows selected
