新手發(fā)帖，很多方面都是剛?cè)腴T，有錯誤的地方請大家見諒，歡迎批評指正

?????

????

1 ????? 看查 Oracle 執(zhí)行籌劃的幾種方法

????

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

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

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

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

????

1.2 ????? 通過sqlplus

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

???? Sql> set autotrace on

???? Sql> select * from dual;

???? 執(zhí)行完句語后，會顯示 explain plan? 與統(tǒng)計信息。

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

???? Sql> set autotrace traceonly

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

???? (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 種方法只能為在本話會中正在行運的句語生產(chǎn)執(zhí)行籌劃，即我們要需經(jīng)已知道了哪條句語行運的效率很差，我們是有的目只對這條 SQL 句語去化優(yōu)。其實，在很多況情下，我們只會聽一個客戶怨抱說在現(xiàn)統(tǒng)系行運很慢，而我們不知道是哪個 SQL 起引的。此時有很多現(xiàn)成的句語可以找出耗消資源比較多的句語，如：

???? 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

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

????

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

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

???? ???? 前當(dāng) session 中設(shè)置：

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

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

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

???? ???? 對其他戶用停止跟蹤設(shè)置：

???? ?? ??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);

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

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

????

1.4 ????? 應(yīng)用tkprof格式化跟蹤件文：?

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

???? 1 、界定要需跟蹤的目標范圍，并應(yīng)用當(dāng)適的命令啟用所需跟蹤。

???? 2 、經(jīng)過一段間時后，停止跟蹤。此時該應(yīng)生產(chǎn)了一個跟蹤結(jié)果件文。

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

???? -- 應(yīng)用一下 SQL 找到前當(dāng) 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; ?

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

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

???? 件文容內(nèi)大致如下

???? 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 執(zhí)行籌劃

????

2.1 ????? 什么是執(zhí)行籌劃

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

????

2.2 ????? 如何問訪數(shù)據(jù)

???? ??? 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 ????? 執(zhí)行籌劃層次系關(guān)

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

????

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)

???? 側(cè)左的兩排數(shù)據(jù)，面前的是序列號 ID ，面后的是對應(yīng)的 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.?? -- 全表掃描模式下會讀數(shù)據(jù)到表的高水位線（ HWM 即表現(xiàn)表曾擴展的最后一個數(shù)據(jù)塊），讀取度速依賴于 Oracle 初始化參數(shù) db_block_multiblock_read_count( 我得覺該應(yīng)這樣翻譯 :FTS 掃描會使表應(yīng)用上升到高水位 (HWM),HWM 識標了表最后寫入數(shù)據(jù)的塊 , 如果你用 DELETE 刪除了全部的數(shù)據(jù)表仍然處于高水位 (HWM), 只有效 TRUNCATE 才能使表回歸 ,FTS 應(yīng)用多 IO 從盤磁讀取數(shù)據(jù)塊 ).

????

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 況情下常產(chǎn)生

???? 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 件條列長短引索的前導(dǎo)列況情下常產(chǎn)生

???? 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 掃描是最快的問訪數(shù)據(jù)方法

???? ?

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

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

????

3 ????? 執(zhí)行籌劃穩(wěn)定性能帶給我們什么

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

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

????

3.1 ????? Oracle Hints的應(yīng)用

???? 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 集收統(tǒng)計更新信息

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

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

???? 請清晰 dbms_stat 包的用法，體具請查閱相干資料學(xué)習(xí)。

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

???? ?

?

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

執(zhí)行計劃信息Oracle 執(zhí)行計劃總結(jié)