新手發帖，很多方面都是剛入門，有錯誤的地方請大家見諒，歡迎批評指正

?????

????

1 ????? 看查 Oracle 執行籌劃的幾種方法

????

1.1 ????? 通過PL/SQL Dev工具

???? 1 、直接 File->New->Explain Plan Window ，在窗口中執行 sql 可以看查籌劃結果。其中， Cost 表現 cpu 的耗消，位單為 n% ， Cardinality 表現執行的行數，等價 Rows 。

???? ?2 、先執行 ?EXPLAIN PLAN FOR???select * from tableA where paraA=1 ，再 ?select * from table(DBMS_XPLAN.DISPLAY) 便可以看到 oracle 的執行籌劃了，看到的結果和 1 中的一樣，所以應用工具的時候推薦應用 1 方法。

???? 注意： PL/SQL Dev 工具的 Command window 中不持支 set autotrance on 的命令。還有應用工具方法看查籌劃看到的信息不全，有些時候我們要需 sqlplus 的持支。

????

1.2 ????? 通過sqlplus

???? 1 ．最簡略的辦法

???? Sql> set autotrace on

???? Sql> select * from dual;

???? 執行完句語后，會顯示 explain plan? 與統計信息。

???? 這個句語的長處就是它的點缺，這樣在用該方法看查執行間時較長的 sql 句語時，要需等待該句語執行勝利后，才返回執行籌劃，使化優的期周大大增加。如果不想執行句語而只是想失掉執行籌劃可以用采：

???? Sql> set autotrace traceonly

???? 這樣，就只會列出執行籌劃，而不會真正的執行句語，大大減少了化優間時。雖然也列出了統計信息，但是因為沒有執行句語，所以該統計信息沒有效處，如果執行該句語時到遇錯誤，解決方法為：

???? (1 ）在要分析的戶用下：

???? Sqlplus > @ ?

???? dbmsadminutlxplan.sql

???? (2)? 用 sys 戶用登岸

???? Sqlplus > @ ?sqlplusadminplustrce.sql

???? Sqlplus > grant plustrace to user_name;

???? - - user_name 是下面所說的分析戶用

???? ?

???? 2 ．用 explain plan 命令

???? (1) sqlplus > explain plan for select * from testdb.myuser

???? (2) sqlplus > select * from table(dbms_xplan.display);

???? 下面這 2 種方法只能為在本話會中正在行運的句語生產執行籌劃，即我們要需經已知道了哪條句語行運的效率很差，我們是有的目只對這條 SQL 句語去化優。其實，在很多況情下，我們只會聽一個客戶怨抱說在現統系行運很慢，而我們不知道是哪個 SQL 起引的。此時有很多現成的句語可以找出耗消資源比較多的句語，如：

???? SELECT? ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,

???? buffer_gets/executions AVG ??FROM?? v$sqlarea

???? WHERE? executions>0 AND buffer_gets > 100000??? ORDER BY ?5;

???? ADDRESS??????TEXT?????????????????????BUFFER_GETS????EXECUTIONS????????AVG

???? -------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------

???? 66D83D64???select t.name,??(sel???????????????421531????????60104?????????????7.01336017

???? 66D9E8AC???select t.schema, t.n???????????????1141739????????2732?????????????417.913250

???? 66B82BCC???select s.synonym_nam?????????????441261????????6????????????????73543.5

???? 從而對找出的句語停止進一步化優。當然我們還可認為一個正在行運的話會中行運的全部 SQL 句語成生執行籌劃，這要需對該話會停止跟蹤，生產 trace 件文，然后對該件文用 tkprof 序程格式化一下，種這失掉執行籌劃的方法很有效，因為它含包其它額外信息，如 SQL 句語執行的每一個階段 ( 如 Parse 、 Execute 、 Fetch) 分離耗消的各個資源況情 ( 如 CPU 、 DISK 、 elapsed 等 ) 。

????

1.3 ????? 啟用SQL_TRACE跟蹤全部后臺進程動活：

???? 全局參數設置： ?.OracleHome/admin/SID/pfile 中指定 : SQL_TRACE = true (10g)

???? ???? 前當 session 中設置：

???? ?? ??SQL> alter session set SQL_TRACE=true;

???? ????SQL> select * from dual;

???? ????SQL> alter session set SQL_TRACE=false;

???? ???? 對其他戶用停止跟蹤設置：

???? ?? ??SQL> select sid,serial#,username from v$session where username='XXX';

???? ???????SID????SERIAL# USERNAME

???? ????------ ---------- ------------------

???? ???????127??????31923 A

???? ???????128??????54521 B

???? ???? 開啟跟蹤 :SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);

???? ???? 關閉跟蹤 :SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);

???? ???? 然后應用 oracle 自帶的 tkprof 命令行工具格式化跟蹤件文。

????

1.4 ????? 應用tkprof格式化跟蹤件文：?

???? 一般，一次跟蹤可以分為以下幾步：

???? 1 、界定要需跟蹤的目標范圍，并應用當適的命令啟用所需跟蹤。

???? 2 、經過一段間時后，停止跟蹤。此時該應生產了一個跟蹤結果件文。

???? 3 、找到跟蹤件文，并對其停止格式化，然后讀閱或分析。

???? -- 應用一下 SQL 找到前當 session 的跟蹤件文：

???? SELECT ?d. value ||? '/' ?||lower(rtrim(i. instance , chr(? 0 ?)))||? '_ora_' ?||p.spid||? '.trc' trace_file_name
from
(? select ?p.spid? from ?v$mystat m,v$session s, v$process p
where ?m.statistic# =? 1 ? and ?s.sid = m.sid? and ?p.addr = s.paddr) p,
(? select ?t. instance?from ?v$thread t,v$parameter v
where ?v. name ?=? 'thread' ? and ?(v. value ?=? 0 ? or ?t. thread # = to_number(v. value ))) i,
(? select?value?from ?v$parameter? where?name ?=? 'user_dump_dest' ?) d;
--? 其它戶用的 ?session
???? SELECT ?d. value ||? '/' ?||lower(rtrim(i. instance , chr(? 0 ?)))||? '_ora_' ?||p.spid||? '.trc' trace_file_name
???? from
????(? select ?p.spid? from ?v$session s, v$process p
?????????? where ?s.sid=? '27' ? and ?s. SERIAL#=? '30' ? and ?p.addr = s.paddr) p,
????(? select ?t. instance?from ?v$thread t,v$parameter v
?????????? where ?v. name ?=? 'thread' ? and ?(v. value ?=? 0 ? or ?t. thread # = to_number(v. value ))) i,
????(? select?value?from ?v$parameter? where?name ?=? 'user_dump_dest' ?) d; ?

???? -- 查找后應用 tkprof 命令 , 將 TRACE 件文格式為到 D 盤的 explain_format.txt 件文中

???? SQL > $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc??d:/explain_format.txt

???? 件文容內大致如下

???? TKPROF: Release 9.2.0.1.0 - Production on? 星期二 ?4 月 ?20 13:59:20 2010

???? Copyright (c) 1982, 2002, Oracle Corporation.??All rights reserved.

???? Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.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
********************************************************************************

???? alter session set events '10046 trace name context forever, level 8'?

???? call?????count???????cpu????elapsed???????disk??????query????current????????rows

???? ------- ------??-------- ---------- ---------- ---------- ----------??----------

???? Parse????????0??????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????????1??????0.00???????0.00??????????0??????????0??????????0???????????0?

???? Misses in library cache during parse: 0

???? Misses in library cache during execute: 1

???? Optimizer goal: CHOOSE

???? Parsing user id: SYS

????

2 ????? 如何看懂 ORACLE 執行籌劃

????

2.1 ????? 什么是執行籌劃

???? ??? An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

????

2.2 ????? 如何問訪數據

???? ??? At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
??? Full Table Scan (FTS)??? -- 全表掃描
??? Index Lookup (unique & non-unique)??? -- 引索掃描（一唯和非一唯）
??? Rowid??? -- 物理行 id

????

2.3 ????? 執行籌劃層次系關

???? When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. ? -- 用采最右最上先最執行的準則看層次系關，在統一級如果某個作動沒有子 ID 就先最執行

????

2.3.1 ??????? 例子講解

???? Execution Plan

???? ----------------------------------------------------------

???? 0 **SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)

???? 1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)

???? 2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)

???? 3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

???? 側左的兩排數據，面前的是序列號 ID ，面后的是對應的 PID （父 ID ）。

???? A shortened summary of this is:

???? Execution starts with ID=0: SELECT STATEMENT but this is dependand on it's child objects

???? So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects

???? So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'

???? Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'

???? Rows are returned to the parent step(s) until finished

????

2.4 ????? 表問訪方法

????

2.4.1 ??????? Full Table Scan (FTS) 全表掃描

???? In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk.?? -- 全表掃描模式下會讀數據到表的高水位線（ HWM 即表現表曾擴展的最后一個數據塊），讀取度速依賴于 Oracle 初始化參數 db_block_multiblock_read_count( 我得覺該應這樣翻譯 :FTS 掃描會使表應用上升到高水位 (HWM),HWM 識標了表最后寫入數據的塊 , 如果你用 DELETE 刪除了全部的數據表仍然處于高水位 (HWM), 只有效 TRUNCATE 才能使表回歸 ,FTS 應用多 IO 從盤磁讀取數據塊 ).

????

2.4.2 ??????? Index Lookup 引索掃描

????There are 5 methods of index lookup:

???? index unique scan?? -- 引索一唯掃描

???? Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

???? eg:SQL> explain plan for select empno,ename from emp where empno=10; ?

???? index range scan?? -- 引索局部掃描

???? Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

???? eg:SQL> explain plan for select mgr from emp where mgr = 5;

???? ? index full scan?? -- 引索全局掃描

???? Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

???? eg: SQL> explain plan for select empno,ename from big_emp order by empno,ename;

???? ? index fast full scan ?? -- 引索倏地全局掃描，不帶 order by 況情下常產生

???? Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

???? eg: SQL> explain plan for select empno,ename from big_emp;

???? ? index skip scan? ?-- 引索騰躍掃描， where 件條列長短引索的前導列況情下常產生

???? Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

???? eg:SQL> create index i_emp on emp(empno, ename);

???? SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

????

2.4.3 ??????? .Rowid 物理 ID 掃描

???? This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. ?? --Rowid 掃描是最快的問訪數據方法

???? ?

???? 看執行籌劃時，我們的癥結不是看哪個操縱先執行，哪個操縱后執行，而是癥結看表之間連接的序順 ( 如得悉哪個為驅動表，這要需從操縱的序順停止判斷 ) 、應用了何種型類的關聯及體具的取存路徑 ( 如判斷否是利用了引索 )

???? 在從執行籌劃中判斷出哪個表為驅動表后，根據我們的識知判斷該表作為驅動表否是適合，如果不適合，對 SQL 句語停止改更，使化優器可以選擇準確的驅動表。

????

3 ????? 執行籌劃穩定性能帶給我們什么

???? Oracle 存在著執行籌劃選擇失誤的可能。這也是我們經常見遇的一些象現，比如總有人說我的序程在測試數據庫中跑的很好，但在產品數據庫上就是跑的很差，甚至后者硬件件條比前者還好，這究竟是為什么 ? 硬件資源、統計信息、參數設置都可能對執行籌劃生產影響。由于素因太多，我們是總對來未懷著一種莫名的恐怖，我的產品數據庫上線后到底跑的好不好 ? 于是 Oracle 供給了一種穩定執行籌劃的能力，也就是把在測試環境中的行運精良的執行籌劃所生產的 OUTLINES 植移到產品數據庫，使得執行籌劃不會隨著其他素因的化變而化變。

???? 那么 OUTLINES 是什么呢 ? 先要分析一個容內， Oracle 供給了在 SQL 中應用 HINTS 來導引化優器生產我們想要的執行籌劃的能力。這在多表連接、雜復查詢中別特有效。 HINTS 的型類很多，可以設置化優器目標 (RULE 、 CHOOSE 、 FIRST_ROWS 、 ALL_ROWS) ，可以指定表連接的序順，可以指定應用哪個表的哪個引索等等，可以對 SQL 停止很多細精的控制。通過種這方法生產我們想要的執行籌劃的這些 HINTS,Oracle 可以存儲這些 HINTS ，我們稱之為 OUTLINES 。通過 STORE OUTLINES 可以使得我們有擁當前生產同相執行籌劃的能力，也就是使我們有擁了穩定執行籌劃的能力。

????

3.1 ????? Oracle Hints的應用

???? Eg: alter session enable dml parallel ；

???? insert /*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a;

???? Hints 的體具戶用請自己查詢相干資料。。。。。。

???? http://blog.csdn.net/runming918/article/details/8900750

????

3.2 ????? Oracle 集收統計更新信息

???? 或許我們會有疑難，假如穩定了執行籌劃，那還征集統計信息干嘛 ? 這是因為幾個原因成造的，首先，在現的執行籌劃對于來未產生了化變的數據未必就是適合的，存在著前當的執行籌劃不滿足來未數據的化變后的效率，而新的統計信息的況情下所生產的執行籌劃也是不并全部都理合的。那這個時候，我們可以用采新征集的統計信息，但是卻對新統計信息下不良的執行籌劃用采 Oracle 供給的執行籌劃穩定性這個能力定固執行籌劃，這樣結合起來我們可以建立意滿的高效的數據庫行運環境。

???? 我們還要需注關的一個貨色， Oracle 供給的 dbms_stats 包除了有具征集統計信息的能力，還有具把數據庫中統計信息 (statistics)export/import 的能力，還有具只征集統計信息而使得統計信息應不于用數據庫的能力 ( 把統計信息征集到一個特定的表中而不是即立失效 ) ，在這個基礎上我們就能夠把統計信息 export 出來再 import 到一個測試環境中，再行運我們的應用，在測試環境中我們視察最新的統計信息會致導哪些執行籌劃產生化變 (DB EXPERT 的 Plan Version Tracer 是模擬不同環境并主動檢查不同環境中執行籌劃化變的工具 ) ，是變好了還是變差了。我們可以把變差的這一部分在測試環境中應用 hints 或者利用工具 (SQL EXPERT 是在寫重 SQL 這一范疇現在最強力有的工具 ) 生產精良的執行籌劃的 SQL ，利用這些 SQL 可以生產 OUTLINES, 然后在產品數據庫應用最新的統計信息的同時植移進這些 OUTLINES 。

???? 請清晰 dbms_stat 包的用法，體具請查閱相干資料學習。

???? http://blog.csdn.net/runming918/article/details/7223368

???? ?

?

文章結束給大家分享下程序員的一些笑話語錄： 一個合格的程序員是不會寫出 諸如 “摧毀地球” 這樣的程序的，他們會寫一個函數叫 “摧毀行星”而把地球當一個參數傳進去。

執行計劃信息Oracle 執行計劃總結