版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認領
文檔簡介
1、.:.;怎樣看懂Oracle的執(zhí)行方案一、什么是執(zhí)行方案An explain plan is a representation of the access path that is taken when a query is executed within Oracle.二、如何訪問數(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
2、 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 三、執(zhí)行方案層次關(guān)系When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that i
3、s executed. -采用最右最上最先執(zhí)行的原那么看層次關(guān)系,在同一級假設某個動作沒有子ID就最先執(zhí)行1.看一個簡單的例子:Query Plan-SELECT STATEMENT CHOOSE Cost=1234*TABLE ACCESS FULL LARGE :Q65001 ANALYZED -:Q65001表示是并行方式,ANALYZED表示該對象曾經(jīng)分析過了優(yōu)化方式是CHOOSE的情況下,看Cost參數(shù)能否有值來決議采用CBO還是RBO:SELECT STATEMENT CHOOSE Cost=1234 -Cost有值,采用CBOSELECT STATEMENT CHOOSE Cos
4、t= -Cost為空,采用RBO2.層次的父子關(guān)系,看比較復雜的例子:PARENT1*FIRST CHILD*FIRST GRANDCHILD*SECOND CHILD Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output. 四、例子講解Execution Plan-0 *SELECT STATEMENT Op
5、timizer=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,后面的是對應的PID父ID。A shortened summary of this is:Execution starts with ID=0: SELECT STATEM
6、ENT but this is dependand on its child objectsSo it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on its child objectsSo it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF DEPTThen the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF EMPRows are retur
7、ned to the parent step(s) until finished 五、表訪問方式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
8、 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即表示表曾經(jīng)擴展的最后一個數(shù)據(jù)塊,讀取速度依賴于Oracle初始化參數(shù)db_block_multiblock_read_countQuery Plan-SELECT STATEMENT CHOOSE Cost=1*INDEX UNIQUE SCAN EMP_I1 -假設索引里就找到了所要的數(shù)據(jù),就不會再去訪問表了2.Ind
9、ex 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 fr
10、om 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. = explain plan for select mgr from emp where
11、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
12、 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情況下常發(fā)生Scans all the block in the index, R
13、ows 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.
14、eg: SQL explain plan for select empno,ename from big_emp;index skip scan -索引騰躍掃描,where條件列是非索引的前導列情況下常發(fā)生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 se
15、lect /*+ index_ss(emp i_emp)*/ job from emp where ename=SMITH;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ù)方式六、表銜接方式有三種銜接方式:1.Sort Merge Join (SMJ) -由于sort是非常耗資源的,所以這種銜接方式要防止Rows are produce
16、d by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently. SQL explain plan forselect /*+ ordered */ e.deptno,d.deptnofrom emp e,dept dwhere e.deptno = d.deptnoorder by e.deptno,d.d
17、eptno;Query Plan-SELECT STATEMENT CHOOSE Cost=17*MERGE JOIN*SORT JOIN*TABLE ACCESS FULL EMP ANALYZED*SORT JOIN*TABLE ACCESS FULL DEPT ANALYZED Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method. 2.Nested Loops (NL)
18、-比較高效的一種銜接方式Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the seco
19、nd row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.SQL explain plan forselect a.dname,b.sqlfrom dept a,emp bwhere a.deptno = b.deptno; Query Plan-SELECT STATEMENT CHOOSE Cost=5*NESTED LOOPS*T
20、ABLE ACCESS FULL DEPT ANALYZED*TABLE ACCESS FULL EMP ANALYZED 3.Hash Join -最為高效的一種銜接方式New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and ch
21、ecked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory. SQL explain plan forselect /*+ use_hash(emp) */ empnofrom emp,deptwhere emp.deptno = dept.deptno; Q
22、uery Plan-SELECT STATEMENT CHOOSE Cost=3*HASH JOIN*TABLE ACCESS FULL DEPT*TABLE ACCESS FULL EMP Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.3.Cartesian Product -笛卡爾積,不算真正的銜接方式,sql一定寫的有問題A Cartesian Product is done where they a
23、re no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.It can be useful in some circumstances - Star joins uses cartesian products
24、.Notice that there is no join between the 2 tables: SQL explain plan forselect emp.deptno,dept,deptnofrom emp,dept Query Plan-SLECT STATEMENT CHOOSE Cost=5*MERGE JOIN CARTESIAN*TABLE ACCESS FULL DEPT*SORT JOIN*TABLE ACCESS FULL EMP The CARTESIAN keyword indicate that we are doing a cartesian product
25、. 七、運算符1.sort -排序,很耗費資源There are a number of different operations that promote sorts:order by clausesgroup bysort merge join 2.filter -過濾,如not in、min函數(shù)等容易產(chǎn)生Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, f
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負責。
- 6. 下載文件中如有侵權(quán)或不適當內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 多輛掛車買賣合同范例
- 國家出境旅游合同范例
- 化工代理采購合同范例
- 雙匯供銷合同范例
- 客戶居間服務合同范例
- 湖北企業(yè)靈活用工合同范例
- 與林地建房合同范例
- 建筑外墻粉刷合同范例
- 紹興商標注冊代理合同范例
- 冬季清雪合同范例
- 20S515 鋼筋混凝土及磚砌排水檢查井
- 醫(yī)院重點監(jiān)控藥品管理制度
- 崗前安全培訓試題及參考答案【奪分金卷】
- GB/T 25356-2024機場道面除冰防冰液
- 九年級下冊孔乙己課文原文及解讀
- 英漢漢英口譯智慧樹知到答案2024年山東科技大學
- DB63T 2318-2024 辦公用房維修管理規(guī)范
- 2024年人教版小學四年級科學(下冊)期末試卷及答案
- 研究生考試考研法律碩士專業(yè)基礎(法學)2025年試題及解答
- 2024年全國高中數(shù)學聯(lián)賽北京賽區(qū)預賽一試試題(解析版)
- 人教版數(shù)學九年級上冊說課稿22.1.4《二次函數(shù)y=ax2+bx+c的圖象和性質(zhì)》
評論
0/150
提交評論