• 思維導(dǎo)圖
• Optimizer概述
• RBORule Based Optimizer
  • RBO概述
  • 案例說明
• CBOCost Based Optimizer
  • CBO概述
  • 案例說明
  • 全表掃描
  • 索引掃描

思維導(dǎo)圖

---

Optimizer概述

Oracle數(shù)據(jù)庫中的優(yōu)化器是SQL分析和執(zhí)行的優(yōu)化工具，它負(fù)責(zé)制定SQL的執(zhí)行計(jì)劃，也就是它負(fù)責(zé)保證SQL執(zhí)行效率最高.

比如優(yōu)化器決定Oracle是以什么樣的方式方式訪問數(shù)據(jù)，是全表掃描(Full Table Scan) 、索引范圍掃描（Index Range Scan）還是全索引快速掃描（INDEX Fast Full Scan, INDEX_FFS）.

對于多表關(guān)聯(lián)查詢，它負(fù)責(zé)確定表之間以一種什么樣的方式來關(guān)聯(lián)，比如Hash Join 還是 NESTED LOOPS或者是MERGE JOIN。

這些因素直接決定著SQL的執(zhí)行效率，所以優(yōu)化器是SQL執(zhí)行的核心，它做出的執(zhí)行計(jì)劃的好壞，直接決定了SQL的執(zhí)行效率。

Oracle的優(yōu)化器有兩種

• RBO 基于規(guī)則的優(yōu)化器
• CBO 基于代價的優(yōu)化器

從ORACLE10G開始，RBO已經(jīng)被棄用（但是我們依然可以通過HINT的方式使用它）。

---

RBO(Rule Based Optimizer)

RBO概述

在8i之前，ORACLE使用RBO（Rule Based Optimizer 基于規(guī)則的優(yōu)化器）優(yōu)化器。

它的執(zhí)行機(jī)制非常簡單，就是在優(yōu)化器里面嵌入各種若干規(guī)則，執(zhí)行的SQL符合那種規(guī)則，則按照對應(yīng)的規(guī)則制定出相應(yīng)的執(zhí)行計(jì)劃。

比如表上有索引，如果謂詞上有索引的列存在，則ORACLE會選擇索引，否則全表掃描。

又比如兩個表關(guān)聯(lián)的時候，按照表在SQL中的位置來決定哪個是驅(qū)動表，哪個是被驅(qū)動表。

RBO選擇執(zhí)行計(jì)劃的優(yōu)先級列表如下：

其中，排名越靠前，Oracle認(rèn)為效率越高。例如：按索引訪問的效率肯定高于全表掃描，多字段復(fù)合索引的效率高于單字段索引，等等。可見，RBO策略其實(shí)很簡單。而且在SQL語句存在多個索引時，由于無法判別各個索引的可選性，RBO的分析過程與語句的語法相關(guān)，如where條件順序，或者隨機(jī)選取。

通俗地講，RBO就是不關(guān)心被訪問對象的實(shí)際數(shù)據(jù)分布情況、索引效率等，僅憑想象去決定應(yīng)該如何去訪問數(shù)據(jù)庫。可見，RBO是一種非常粗放型的優(yōu)化器。

---

案例說明

思考：表中有索引，數(shù)據(jù)就必須選擇索引嗎？

首先創(chuàng)建一個數(shù)據(jù)分部非常不均勻的表

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as xxx@xgj##建表 SQL> create table t as select 1 id ,object_name from dba_objects ;Table created ##更新一條數(shù)據(jù) SQL> update t set id=99 where rownum=1;1 row updatedSQL> commit;Commit complete ##創(chuàng)建索引 SQL> create index idx_t on t(id);Index createdSQL> select id,count(1) from t group by id ;ID COUNT(1) ---------- ----------1 3525199 1

現(xiàn)在表中的數(shù)據(jù) id =1 的 35251條記錄， id =99 的 1條記錄。

如果發(fā)出兩條這樣的SQL

SQL> select * from t where t.id=1; SQL> select * from t where t.id=99;

在ＲＢＯ的年代里，執(zhí)行計(jì)劃是這樣的

---

select * from t where t.id=99

##首先執(zhí)行SQL SQL> select /*+ rule */ * from t where id=99;ID OBJECT_NAME ---------- -------------------99 ICOL$##查找SQL對應(yīng)的SQL_ID SQL> 　SELECT sql_id, child_number, a.* from v$sql a where a.SQL_TEXT like '%select /*+ rule */ * from t where id=99%'; SQL_ID CHILD_NUMBER ------------- ------------ axszbzuk341sw 0##通過DBMS_XPLAN.display_cursor查詢執(zhí)行計(jì)劃 SQL> select * from table(DBMS_XPLAN.display_cursor('axszbzuk341sw',0));

---

select * from t where t.id=1;

同樣的方式我們得出執(zhí)行計(jì)劃

使用 hint /+ rule /的方式強(qiáng)制讓ORACLE使用RBO優(yōu)化器來產(chǎn)生執(zhí)行計(jì)劃，結(jié)果非常令人失望，不出所料，ORACLE在ID字段有索引的情況下，毫無懸念的全部選擇了索引。

實(shí)際上我們知道，對于id=1,幾乎所有的數(shù)據(jù)全都符合謂詞條件，選擇索引只能增加額外的開銷（因?yàn)镺RACLE首先要訪問索引數(shù)據(jù)塊，在索引上找到了相應(yīng)的鍵值，然后按照鍵值上的rowid 再去訪問表中相應(yīng)的數(shù)據(jù)）。

既然我們幾乎要訪問所有表中的數(shù)據(jù)，那么全表掃描自然是最優(yōu)的選擇，很遺憾RBO做出了錯誤的選擇。

---

CBO(Cost Based Optimizer)

CBO概述

從8i開始，ORACLE引入了CBO，它的思路是讓ORACLE獲取所有執(zhí)行計(jì)劃相關(guān)的信息，通過對這些信息進(jìn)行計(jì)算分析，最后得出一個代價最小的執(zhí)行計(jì)劃作為最終的執(zhí)行計(jì)劃。

CBO基于成本的優(yōu)化器，其特點(diǎn)如下。

（1）依賴于數(shù)據(jù)對象的統(tǒng)計(jì)信息，例如表的記錄數(shù)、消耗的數(shù)據(jù)塊數(shù)、索引的可選性（不同的記錄值數(shù)量）等。

（2）使用精細(xì)的成本模型來評估SQL語句的執(zhí)行性能。例如CPU消耗、邏輯讀寫（內(nèi)存消耗）、I/O讀寫、網(wǎng)絡(luò)傳輸量的消耗分析等。

（3）語句優(yōu)化的整體質(zhì)量高于RBO。

（4）Oracle的大量新特性只能在CBO下運(yùn)行。例如：HASH_JOIN操作、Bitmap索引、物化視圖等。

可見相比RBO，CBO是一種更加精確而有效的優(yōu)化器。

---

案例說明

同樣是使用RBO中的案例，我們來看下CBO的表現(xiàn)。

##減少影響，先清空shared_Pool中的數(shù)據(jù) SQL> alter system flush shared_pool;System altered ##對表做一次分析（cascade => true ,索引也會被一同分析） SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);PL/SQL procedure successfully completed SQL>select /*+ all_rows */ * from t where t.id=1;SQL>SELECT sql_id, child_number, a.* from v$sql a where a.SQL_TEXT like '%select /*+ all_rows */ * from t where t.id=1%';SQL>select * from table(DBMS_XPLAN.display_cursor('d6adsf8y8c6mu',0));

SQL>select /*+ all_rows */ * from t where t.id=99;SQL>SELECT sql_id, child_number, a.* from v$sql a where a.SQL_TEXT like '%select /*+ all_rows */ * from t where t.id=99%';SQL>select * from table(DBMS_XPLAN.display_cursor('8w6r8hzrkunnu',0));

可以看出 CBO表現(xiàn)的非常完美，

• id=1 全表掃描 TABLE ACCESS FULL
• id=99 索引掃描 INDEX RANGE SCAN

因?yàn)檫@樣的選擇代價是最小的，為了驗(yàn)證CBO選擇的正確性，我們分別讓這兩條SQL各自做一次全表掃描和和選擇索引。

---

全表掃描

select * from t where id=1; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter session set tracefile_identifier='mytest';Session altered.SQL> alter session set sql_trace=true;Session altered.SQL> select * from t where id=1; ....... 35251 rows selected.SQL> alter session set sql_trace=false;Session altered. SQL> select name, value from v$parameter where name = 'user_dump_dest';NAME VALUE ------------ ---------- user_dump_dest /oracle/diag/rdbms/cc/cc/trace

ORACLE主機(jī)上查看

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *mytest* cc_ora_8752_mytest.trc cc_ora_8752_mytest.trm

tkprof 工具匯總分析

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_8752_mytest.trc mytest.txt sys=no explain=xxx/xgjTKPROF: Release 11.2.0.4.0 - Development on Tue Dec 27 23:24:32 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

截取部分關(guān)鍵信息：

---

索引掃描

select /*+ index(t IDX_T) */* from t where id=1; SQL> alter session set tracefile_identifier='index_scan';Session alteredSQL> alter session set sql_trace=true;Session alteredSQL> select /*+ index(t IDX_T) */* from t where id=1; ....... 35251 rows selected.SQL> alter session set sql_trace=false;Session altered.

獲取到trc原文件后，使用tkprof分析匯總

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_8709_index_scan.trc index_scan.txt explain=xxx/xgj sys=noTKPROF: Release 11.2.0.4.0 - Development on Tue Dec 27 23:35:57 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

截取關(guān)鍵信息：

比較：

select * from t where id=1;--全表掃描了2470個數(shù)據(jù)塊 select /*+ index(t IDX_T) */* from t where id=1;--索引掃描了4892個數(shù)據(jù)塊

顯然這種情況下，CBO的選擇是正確的，使用全表掃描的效率更高，速度更快。

---

同樣的方式我們比較下

select * from t where id=99; select /*+ full(t) */ * from t where id=99;

select * from t where id=99;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as xxx@xgjSQL> alter session set tracefile_identifier='full_scan';Session alteredSQL> alter session set sql_trace=true;Session alteredSQL> select * from t where id=99;ID OBJECT_NAME ---------- ------------------------99 ICOL$SQL> alter session set sql_trace=false;Session alteredSQL>

在oracle主機(jī)上使用tkprof分析匯總

[root@entel1 ~]# su - oracle oracle@entel1:[/oracle]$cd /oracle/diag/rdbms/cc/cc/trace oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *full* cc_ora_16197_full_scan.trc cc_ora_16197_full_scan.trm oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_16197_full_scan.trc full_scan.txt explain=zmc/smart sys=noTKPROF: Release 11.2.0.4.0 - Development on Wed Dec 28 23:41:24 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$cat full_scan.txt

部分關(guān)鍵信息如下：

---

采用同樣的方式我們分析下
select /+ full(t) / * from t where id=99;

---

通過對比上述的執(zhí)行計(jì)劃，選擇索引的只掃描了3個數(shù)據(jù)塊，而全表掃描則掃描了135個數(shù)據(jù)塊，再此證明了CBO選擇的正確性。

---

從ORACLE10g開始，ORACLE已經(jīng)徹底丟棄了RBO。
即使在表索引沒有被分析的時候，Oracle依然會使用CBO，此時，ORACLE會使用一種叫做動態(tài)采樣的技術(shù)，在分析SQL的時候，動態(tài)的搜集表、索引上的一些數(shù)據(jù)塊，使用這些數(shù)據(jù)塊的信息及字典表中關(guān)于這些對象的信息來計(jì)算出執(zhí)行計(jì)劃的代價，從而挑選出最優(yōu)的執(zhí)行計(jì)劃。

舉例說明：

SQL> drop table t ;Table droppedSQL> create table t (x int ,y varchar2(2000));Table createdSQL> insert into t select object_id ,object_name from dba_objects;35254 rows insertedSQL> commit;Commit completeSQL> create index idx_t on t(x);Index createdSQL> alter session set tracefile_identifier='xgj_test';Session alteredSQL> alter session set sql_trace=true;Session alteredSQL> select count(1) from t where x<100;COUNT(1) ----------98SQL> alter session set sql_trace=false;Session altered##查看SQL_ID SQL> SELECT sql_id, child_number, a.* from v$sql a where a.SQL_TEXT like '% select count(1) from t where x<100%'; ##根據(jù)SQL_ID查看執(zhí)行計(jì)劃 SQL>select * from table(DBMS_XPLAN.display_cursor('5yk2jzsm7cpnh',0));

如下：

---

可以看到，當(dāng)表沒有做分析的時候，ORACLE 會采用動態(tài)采樣來搜集統(tǒng)計(jì)信息，這個動作只發(fā)生在SQL執(zhí)行的第一次，即硬分析階段使用，后續(xù)的軟分析將不再使用動態(tài)采樣，直接使用第一次SQL硬分析時生成的執(zhí)行計(jì)劃。

同時我們也采集了SQL_TRACE的信息，我們使用tkprof分析匯總后來看下

TKPROF: Release 11.2.0.4.0 - Development on Thu Dec 29 00:00:16 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Trace file: cc_ora_16197_xgj_test.trc Sort options: default******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************SQL ID: 96ckjhxq51nwk Plan Hash: 0alter session set sql_trace=truecall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (ZMC) ********************************************************************************SQL ID: gjz1ws759xbbp Plan Hash: 1807638002SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ :"SYS_B_2" AS C1, CASE WHEN "T"."X"<:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "ZMC"."T" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "T") SAMPLESUBcall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 69 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 69 0 1Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (ZMC) (recursive depth: 1) Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------------1 1 1 SORT AGGREGATE (cr=69 pr=0 pw=0 time=3244 us)11794 11794 11794 TABLE ACCESS SAMPLE T (cr=69 pr=0 pw=0 time=1955 us cost=2 size=525 card=21)error during execute of EXPLAIN PLAN statement ORA-00907: missing right parenthesisparse error offset: 493 ********************************************************************************SQL ID: 1pr6146q5bau5 Plan Hash: 3306840186SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled','false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2")FROM(SELECT /*+ NO_PARALLEL("T") INDEX("T" IDX_T) NO_PARALLEL_INDEX("T") */ :"SYS_B_3" AS C1, :"SYS_B_4" AS C2, :"SYS_B_5" AS C3 FROM "ZMC"."T" "T" WHERE "T"."X"<:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUBcall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 4 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 4 2 0 1Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (ZMC) (recursive depth: 1) Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------------1 1 1 SORT AGGREGATE (cr=2 pr=4 pw=0 time=301 us)98 98 98 VIEW (cr=2 pr=4 pw=0 time=318 us cost=33 size=390 card=10)98 98 98 COUNT STOPKEY (cr=2 pr=4 pw=0 time=216 us)98 98 98 INDEX RANGE SCAN IDX_T (cr=2 pr=4 pw=0 time=213 us cost=33 size=429 card=33)(object id 79508)Rows Execution Plan ------- ---------------------------------------------------0 SELECT STATEMENT MODE: HINT: ALL_ROWS1 SORT (AGGREGATE)98 VIEW98 COUNT (STOPKEY)98 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX_T' (INDEX)上面的信息就是ORACLE做動態(tài)采樣的SQL語句。********************************************************************************SQL ID: 5yk2jzsm7cpnh Plan Hash: 1500240790select count(1) fromt where x<100call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (ZMC) Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------------1 1 1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=43 us)98 98 98 INDEX RANGE SCAN IDX_T (cr=2 pr=0 pw=0 time=110 us cost=2 size=1274 card=98)(object id 79508)Rows Execution Plan ------- ---------------------------------------------------0 SELECT STATEMENT MODE: ALL_ROWS1 SORT (AGGREGATE)98 INDEX MODE: ANALYZED (RANGE SCAN) OF 'IDX_T' (INDEX)上面是SQL本身執(zhí)行時的實(shí)際信息********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 2 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 4 0 1Misses in library cache during parse: 2OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 4 71 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 4 71 0 2Misses in library cache during parse: 2 Misses in library cache during execute: 24 user SQL statements in session.0 internal SQL statements in session.4 SQL statements in session.2 statements EXPLAINed in this session. ******************************************************************************** Trace file: cc_ora_16197_xgj_test.trc Trace file compatibility: 11.1.0.7 Sort options: default1 session in tracefile.4 user SQL statements in trace file.0 internal SQL statements in trace file.4 SQL statements in trace file.4 unique SQL statements in trace file.2 SQL statements EXPLAINed using schema:ZMC.prof$plan_tableDefault table was used.Table was created.Table was dropped.97 lines in trace file.37 elapsed seconds in trace file.

總結(jié)

以上是生活随笔為你收集整理的Oracle优化04-Optimizer优化器的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。