Oracle 11g收集多列統(tǒng)計(jì)信息詳解
前言
通常,當(dāng)我們將SQL語(yǔ)句提交給Oracle數(shù)據(jù)庫(kù)時(shí),Oracle會(huì)選擇一種最優(yōu)方式來(lái)執(zhí)行,這是通過(guò)查詢優(yōu)化器Query Optimizer來(lái)實(shí)現(xiàn)的。CBO(Cost-Based Optimizer)是Oracle默認(rèn)使用的查詢優(yōu)化器模式。在CBO中,SQL執(zhí)行計(jì)劃的生成,是以一種尋找成本(Cost)最優(yōu)為目標(biāo)導(dǎo)向的執(zhí)行計(jì)劃探索過(guò)程。所謂成本(Cost)就是將CPU和IO消耗整合起來(lái)的量化指標(biāo),每一個(gè)執(zhí)行計(jì)劃的成本就是經(jīng)過(guò)優(yōu)化器內(nèi)部公式估算出的數(shù)字值。
我們?cè)趯慡QL語(yǔ)句的時(shí)候,經(jīng)常會(huì)碰到where子句后面有多個(gè)條件的情況,也就是根據(jù)多列的條件篩選得到數(shù)據(jù)。默認(rèn)情況下,oracle會(huì)把多列的選擇率(selectivity)相乘從而得到where語(yǔ)句的選擇率,這樣有可能造成選擇率(selectivity)不準(zhǔn)確,從而導(dǎo)致優(yōu)化器做出錯(cuò)誤的判斷。為了能夠讓優(yōu)化器做出準(zhǔn)確的判斷,從而生成準(zhǔn)確的執(zhí)行計(jì)劃,oracle在11g數(shù)據(jù)庫(kù)中引入了收集多列統(tǒng)計(jì)信息。本文通過(guò)對(duì)測(cè)試表的多條件查詢,介紹收集多列統(tǒng)計(jì)信息的重要性。
一、環(huán)境準(zhǔn)備
我們?cè)贠racle 11g中進(jìn)行試驗(yàn)。
SQL> SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL>
在hr用戶下創(chuàng)建測(cè)試表hoegh,重復(fù)插入數(shù)據(jù),數(shù)據(jù)量相當(dāng)于16個(gè)employees表(總行數(shù)1712=107*16)。
SQL> SQL> conn hr/hr Connected. SQL> SQL> create table hoegh as select * from employees; Table created. SQL> select count(*) from hoegh; COUNT(*) ---------- 107 SQL> SQL> insert into hoegh select * from hoegh; 107 rows created. SQL> / 214 rows created. SQL> / 428 rows created. SQL> / 856 rows created. SQL> commit; Commit complete. SQL> select count(*) from hoegh; COUNT(*) ---------- 1712 SQL>
二、按照常規(guī)方法收集統(tǒng)計(jì)量信息;
SQL> SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\'); PL/SQL procedure successfully completed. SQL>
三、查看執(zhí)行單個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
SQL> SQL> explain plan for select * from hoegh where employee_id=110; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 774871165 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 1104 | 8 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1104 | 8 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter(\"EMPLOYEE_ID\"=110) 13 rows selected. SQL>
從執(zhí)行計(jì)劃可以看出返回了16行記錄,結(jié)果沒(méi)有問(wèn)題??墒?,這個(gè)16是哪兒來(lái)的呢,我們先要了解選擇率(selectivity)和返回行數(shù)是如何計(jì)算的:
選擇率(selectivity)=在本例中是 1/唯一值
返回行數(shù)=選擇率(selectivity)*表記錄總數(shù)
也就是說(shuō),在這個(gè)查詢語(yǔ)句中,選擇率=1/107,返回行數(shù)=1/107*1712=16
四、查看執(zhí)行兩個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
SQL> SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\'; Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 774871165 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 69 | 8 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HOEGH | 1 | 69 | 8 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\') 13 rows selected. SQL>
從執(zhí)行計(jì)劃可以看出返回了1行記錄,而事實(shí)又是什么樣的呢?我們執(zhí)行一下這條sql語(yǔ)句。
SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\'; COUNT(*) ---------- 16 SQL>
由此看出,測(cè)試表hoegh符合查詢條件的數(shù)據(jù)有16行,而執(zhí)行計(jì)劃提示的只有1行,出錯(cuò)了。這是怎么回事呢,也就是我們?cè)陂_(kāi)篇提到的選擇率(selectivity)出了問(wèn)題。
在這個(gè)多列條件查詢語(yǔ)句中,選擇率=1/107*1/107,返回行數(shù)=1/107*1/107*1712=16/107<1;由于表中存在符合條件的記錄,并且返回行數(shù)不可能小于1,所以O(shè)racle返回了1。
五、收集多列統(tǒng)計(jì)信息,再次查看兩個(gè)條件的where語(yǔ)句的執(zhí)行計(jì)劃
SQL> SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\',method_opt=>\'for columns(employee_id,email)\'); PL/SQL procedure successfully completed. SQL> SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 774871165 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 1152 | 8 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1152 | 8 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\') 13 rows selected. SQL>
從執(zhí)行計(jì)劃的結(jié)果來(lái)看,同樣的一條sql查詢語(yǔ)句,在收集多列統(tǒng)計(jì)信息后,Oracle的選擇率(selectivity)由錯(cuò)變對(duì),這是由于sql語(yǔ)句中的兩個(gè)條件是有關(guān)聯(lián)的,即employee_id和email在employees表中都是唯一的,都可以唯一標(biāo)識(shí)一行記錄;而在收集多列統(tǒng)計(jì)信息之前,Oracle并不知道這兩個(gè)查詢條件有關(guān)聯(lián),所以在計(jì)算選擇率(selectivity)時(shí),只是簡(jiǎn)單地采取了相乘的方法。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助,如果有疑問(wèn)大家可以留言交流,謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
Oralce數(shù)據(jù)導(dǎo)入出現(xiàn)(SYSTEM.PROC_AUDIT)問(wèn)題處理方法
A數(shù)據(jù)庫(kù)打開(kāi)了審計(jì),而導(dǎo)入到B數(shù)據(jù)庫(kù)時(shí),B數(shù)據(jù)庫(kù)審計(jì)沒(méi)有打開(kāi),數(shù)據(jù)庫(kù)中沒(méi)有SYSTEM.PROC_AUDIT對(duì)象,本文將此問(wèn)題的解決方法,需要的朋友可以參考下2012-12-12Oracle怎么刪除數(shù)據(jù),Oracle數(shù)據(jù)刪除的三種方式
這篇文章主要介紹了Oracle中刪除數(shù)據(jù)的三種方式小結(jié),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-02-02Oracle隨機(jī)函數(shù)之dbms_random使用詳解
dbms_random是oracle提供的一個(gè)隨機(jī)函數(shù)包,本文將詳細(xì)介紹函數(shù)的使用,需要的朋友可以參考下2012-12-12使用instantclient客戶端連接oracle數(shù)據(jù)庫(kù)
這篇文章介紹了使用instantclient客戶端連接oracle數(shù)據(jù)庫(kù)的方法,對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-04-04win10系統(tǒng)安裝oracle11g時(shí)遇到INS-13001環(huán)境不滿足最低要求解決辦法
這篇文章主要介紹了win10系統(tǒng)安裝oracle11g時(shí)遇到INS-13001環(huán)境不滿足最低要求解決辦法,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2019-09-09[Oracle] 淺析令人抓狂的ORA-01555問(wèn)題
首先要說(shuō)明的一點(diǎn)是:ORA-01555錯(cuò)誤是一個(gè)“安全”的錯(cuò)誤,它不會(huì)造成數(shù)據(jù)丟失或損壞,只是會(huì)讓接收到該錯(cuò)誤的查詢無(wú)法繼續(xù)。以下就是對(duì)發(fā)生ORA-01555錯(cuò)誤的原因進(jìn)行了介紹,需要的朋友可以參考下2013-07-07oracle數(shù)據(jù)庫(kù)在客戶端建立dblink語(yǔ)法
oracle服務(wù)器沒(méi)有建立目標(biāo)數(shù)據(jù)庫(kù)的TNS時(shí),在客戶端(有權(quán)限的情況下)建立dblink語(yǔ)法如下,有需求的朋友可以參考下哈2013-05-05