亚洲日韩欧美人妻,国产92福利200视频

Oracle SQL性能優(yōu)化

作者：xcbeyond
瘋狂源自夢(mèng)想，技術(shù)成就輝煌！微信公眾號(hào)：《程序猿技術(shù)大咖》號(hào)主，專注后端開發(fā)多年，擁有豐富的研發(fā)經(jīng)驗(yàn)，樂于技術(shù)輸出、分享，現(xiàn)階段從事微服務(wù)架構(gòu)項(xiàng)目的研發(fā)工作，涉及架構(gòu)設(shè)計(jì)、技術(shù)選型、業(yè)務(wù)研發(fā)等工作。對(duì)于Java、微服務(wù)、數(shù)據(jù)庫(kù)、Docker有深入了解，并有大量的調(diào)優(yōu)經(jīng)驗(yàn)。

1、選擇合適的Oracle優(yōu)化器

      Oracle在執(zhí)行一個(gè)SQL之前,首先要分析一下語(yǔ)句的執(zhí)行計(jì)劃,然后再按執(zhí)行計(jì)劃去執(zhí)行。分析語(yǔ)句的執(zhí)行計(jì)劃的工作是由優(yōu)化器(Optimizer)來(lái)完成的。不同的情況,一條SQL可能有多種執(zhí)行計(jì)劃,但在某一時(shí)點(diǎn),一定只有一種執(zhí)行計(jì)劃是最優(yōu)的,花費(fèi)時(shí)間是最少的。

       通過PL/SQL Developer、Toad等工具可以方便的查看一個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃。

      在Load中，使用快捷鍵Ctrl+E，查看執(zhí)行計(jì)劃。也可以在sqlplus中通過命令查看：

    SQL> explain plan for select * from emp;

    已解釋。

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    Plan hash value: 3956160932

    --------------------------------------------------------------------------
    | Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 | TABLE ACCESS FULL| EMP |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    已選擇8行。

注：ID: 一個(gè)序號(hào)，但不是執(zhí)行的先后順序。執(zhí)行的先后根據(jù)縮進(jìn)來(lái)判斷。

       Operation：當(dāng)前操作的內(nèi)容。

       Rows：當(dāng)前操作的Cardinality，Oracle估計(jì)當(dāng)前操作的返回結(jié)果集。

       Cost（CPU）：Oracle 計(jì)算出來(lái)的一個(gè)數(shù)值（代價(jià)），用于說明SQL執(zhí)行的代價(jià)。

       Time：Oracle 估計(jì)當(dāng)前操作的時(shí)間。
(1).RBO方式

       即基于規(guī)則的優(yōu)化方式(Rule-Based Optimization,簡(jiǎn)稱為RBO)。優(yōu)化器在分析SQL語(yǔ)句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引。
(2).CBO方式

        基于代價(jià)的優(yōu)化方式(Cost-Based Optimization,簡(jiǎn)稱為CBO)。顧名思義，它是看語(yǔ)句的代價(jià)(Cost)了,這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小、有少行、每行的長(zhǎng)度等信息。這些統(tǒng)計(jì)信息起初在庫(kù)內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的,很多的時(shí)侯過期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃,因些我們應(yīng)及時(shí)更新這些信息。在Oracle8及以后的版本,Oracle列推薦用CBO的方式。

         我們要明了,不一定走索引就是優(yōu)的 ,比如一個(gè)表只有兩行數(shù)據(jù),一次IO就可以完成全表的檢索,而此時(shí)走索引時(shí)則需要兩次IO,這時(shí)對(duì)這個(gè)表做全表掃描(full table scan)是最好的。

         新版本的oracle逐漸拋棄對(duì)Rule方式的支持,即使是Rule方式,最后sql執(zhí)行效率的衡量標(biāo)準(zhǔn)都是,sql執(zhí)行消耗了多少資源?對(duì)代價(jià)(COST)的優(yōu)化方式,需要表,索引的統(tǒng)計(jì)信息,需要每天多表和索引進(jìn)行定時(shí)的分析,但是統(tǒng)計(jì)信息也是歷史的,有時(shí)候也不一定是最優(yōu)的,統(tǒng)計(jì)信息等于就是一個(gè)人的經(jīng)驗(yàn),根據(jù)以前的經(jīng)驗(yàn)來(lái)判斷sql該怎么執(zhí)行(得到優(yōu)化的sql執(zhí)行路徑),所以具體優(yōu)化執(zhí)行的時(shí)候,先手工分析sql,看是用RBO方式消耗大,還是CBO消耗大;DBA的工作就是要根據(jù)當(dāng)前oracle的運(yùn)行日志,進(jìn)行各種調(diào)整,使當(dāng)前的oracle運(yùn)行效率盡量達(dá)到最優(yōu).可以在運(yùn)行期間,采用hint靈活地采用優(yōu)化方式。
(3).CHOOSE方式

設(shè)置缺省的Oracle優(yōu)化器，可以通過對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，F(xiàn)IRST_ROWS . 你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋。
 為了使用基于成本的優(yōu)化器(CBO，Cost-Based Optimizer) ，你必須經(jīng)常運(yùn)行analyze 命令，以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性。

 如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(CHOOSE)，那么實(shí)際的Oracle優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān)。如果table已經(jīng)被analyze過，優(yōu)化器模式將自動(dòng)成為CBO ，反之，數(shù)據(jù)庫(kù)將采用RULE形式的優(yōu)化器。
 在缺省情況下，Oracle采用CHOOSE優(yōu)化器，為了避免那些不必要的全表掃描(full table scan) ，你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的Oracle優(yōu)化器。

2、選擇最有效率的表名順序

 ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理。當(dāng)ORACLE處理多個(gè)表時(shí)，會(huì)運(yùn)用排序及合并的方式連接它們。首先，掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序，然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表)，最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并。只在基于規(guī)則的優(yōu)化器中有效。

 例如：

 表 TAB1 100000 條記錄
 表 TAB2 1 條記錄

 選擇TAB2作為基礎(chǔ)表 (最好的方法)
 select count(*) from tab1,tab2 執(zhí)行時(shí)間0.96秒

 選擇TAB2作為基礎(chǔ)表 (不佳的方法)
 select count(*) from tab2,tab1 執(zhí)行時(shí)間26.09秒

 如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表。

 例如：

 SELECT * FROM LOCATION L, CATEGORY C, EMP E
 WHERE E.EMP_NO BETWEEN 1000 AND 2000
 AND E.CAT_NO = C.CAT_NO
 AND E.LOCN = L.LOCN;
 將比下列SQL更有效率
 SELECT * FROM EMP E, LOCATION L, CATEGORY C
 WHERE E.CAT_NO = C.CAT_NO
 AND E.LOCN = L.LOCN
 AND E.EMP_NO BETWEEN 1000 AND 2000;

3、Where子句中的連接順序

 Oracle采用自下而上的順序解析WHERE子句。根據(jù)這個(gè)原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。

 例如:

 (低效,執(zhí)行時(shí)間156.3秒)
 SELECT *
 FROM EMP E
 WHERE SAL > 50000
 AND JOB = ‘MANAGER’
 AND 25 < (SELECT COUNT(*) FROM EMP
 WHERE MGR = E.EMPNO);

 (高效,執(zhí)行時(shí)間10.6秒) SELECT * FROM EMP E WHERE 25 <(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

4、SELECT子句中避免使用“*”

 Oracle在解析SQL語(yǔ)句的時(shí)候，對(duì)于“*”將通過查詢數(shù)據(jù)庫(kù)字典來(lái)將其轉(zhuǎn)換成對(duì)應(yīng)的列名。

 如果在Select子句中需要列出所有的Column時(shí)，建議列出所有的Column名稱，而不是簡(jiǎn)單的用“*”來(lái)替代，這樣可以減少多于的數(shù)據(jù)庫(kù)查詢開銷。

5、減少訪問數(shù)據(jù)庫(kù)的次數(shù)

 當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作：

 解析SQL語(yǔ)句

 估算索引的利用率

 綁定變量

 讀數(shù)據(jù)塊等等

 由此可見，減少訪問數(shù)據(jù)庫(kù)的次數(shù) , 就能實(shí)際上減少ORACLE的工作量。

 如果有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢語(yǔ)句，你可以把它們整合到一個(gè)查詢中（即使它們之間沒有關(guān)系），以減少多于的數(shù)據(jù)庫(kù)IO開銷。雖然采取這種方法，效率得到提高，但是程序的可讀性大大降低，所以還是要權(quán)衡之間的利弊。
6、使用Truncate而非Delete

 Delete表中記錄的時(shí)候，Oracle會(huì)在Rollback段中保存刪除信息以備恢復(fù)。Truncate刪除表中記錄的時(shí)候不保存刪除信息，不能恢復(fù)。因此Truncate刪除記錄比Delete快，而且占用資源少。

 刪除表中記錄的時(shí)候，如果不需要恢復(fù)的情況之下應(yīng)該盡量使用Truncate而不是Delete。

 Truncate僅適用于刪除全表的記錄。

陸續(xù)更新中……

在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

數(shù)據(jù)庫(kù)