oracle最佳化sql的內部過程

 

Oracle對sql的最佳化過程如下



本文只關注查詢重寫和查詢最佳化

查詢重寫

獨立於最佳化器 ，分兩部分

1 子查詢展開

分為相關子查詢和非相關子查詢，將其轉化為等價join；
Hint：unnest/no_unnest/hash_sj/hash_aj
通常子查詢在最後執行，透過Push_subq可儘早執行http://blog.itpub.net/15480802/viewspace-688364/
參考案例
http://blog.itpub.net/15480802/viewspace-703260/
http://blog.itpub.net/15480802/viewspace-688361/

2 檢視合併

或將檢視展開，或把外部條件推入檢視；不能合併的檢視，執行計劃會顯示View關鍵字；
Hint: merge/no_merge
限制條件
1 集合操作 union/intersect/minus/union all
2 connect by
3 rownum

查詢最佳化

1 In-list/OR

最佳化器有3種處理方法
1 IN-list迭代器：
將row source每一行同IN-list值逐一比較，列必須有索引；10157事件可禁用此功能；

2 IN-list擴充套件：
將IN-list或OR擴充套件成UNION ALL；CBO必須對每個擴充套件子句評估cost，且執行時每個分支都要讀一次表，可使用NO_EXPAND禁用(與USE_CONCAT相反)，或將IN-list值存入lookup表並join改進；

3 Filter過濾：
採用filter對取出的結果集進行過濾；

案例

DB:11203
create table temp as select object_id,object_name,status,owner from dba_objects;
exec dbms_stats.gather_table_stats('SYS','TEMP');

select object_name,status from temp where object_id in (1,2,3);

1 filter
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1896031711

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   108 |   188   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEMP |     3 |   108 |   188   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)

13 rows selected.

2 OR展開

use_concat在11203已經不好使了，需要改為USE_CONCAT( OR_PREDICATES(1) )
select /*+ use_concat */ object_name,status from temp where object_id in (1,2,3)； --依舊使用filter
select /*+ USE_CONCAT( OR_PREDICATES(1) ) */ object_name,status from temp where object_id in (1,2,3)--與下面的union all等價
select object_name,status from temp where object_id =1
union all
select object_name,status from temp where object_id =2
union all
select object_name,status from temp where object_id =3;

--使用USE_CONCAT(OR_PREDICATES(1))
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   108 |   562   (1)| 00:00:07 |
|   1 |  CONCATENATION     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  4 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=1)
   3 - filter("OBJECT_ID"=2)
   4 - filter("OBJECT_ID"=3)

--直接使用union all
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   108 |   562  (67)| 00:00:07 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  4 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=1)
   3 - filter("OBJECT_ID"=2)
   4 - filter("OBJECT_ID"=3)

3 IN-list遍歷
要先建立索引
create index ind1 on temp(object_id);

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     3 |   108 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEMP |     3 |   108 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND1 |     3 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)

2 星型轉換

適用於事實表很小，維度表很大且缺失連線條件



容易忽略的Hint

PUSH_SUBQ
This hint causes subqueries to be evaluated at the earliest possible time in a query block. Subqueries are typically executed as the last operation.

PUSH_PRED and NO_PUSH_PRED
These two hints, respectively, force and prevent join predicates from being pushed into a view.

DRIVING_SITE
指定執行計劃在remote還是local，driving_site(local)/driving_site(remote)分別指定執行於local/remote db；
This hint is useful when the local tables are small, a lot of data must be processed at the remote site, and only a small subset of the data (for example, aggregated with
GROUP BY, MIN, MAX) is returned to the originating node. In such cases, it makes sense to use the remote site as the driving site by shipping the small local tables across
for processing.

INDEX_COMBINE
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use whatever Boolean combination of indexes has the best cost estimate. If
certain indexes are given as arguments, the optimizer will try to use some Boolean combination of those particular indexes. Because the Oracle server can convert ROWIDs to bitmaps, the indexes in the bitmap tree do not have to be bitmap indexes; they can be regular B*-tree indexes as well.

INDEX_JOIN
This hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, there must exist a sufficiently small number of
indexes that contain all the columns required to resolve the query.

AND_EQUAL
Choose an access path that merges the scans on several single-column indexes. The maximum number of indexes that can be merged is five.

PQ_DISTRIBUTE http://blog.itpub.net/15480802/viewspace-741125/ 
This hint improves the performance of parallel join operations. You can specify how rows of joined tables should be distributed among producer and consumer query
servers. Using this hint overrides decisions the optimizer would normally make. The second and third arguments specify the distribution for the inner and the outer table,
respectively