oracle數據庫怎么優化

本篇內容主要講解“oracle數據庫怎么優化”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“oracle數據庫怎么優化”吧!

目錄SQL優化的本質

SQL優化Road Map

2.1 制定SQL優化目標

2.2 檢查執行計劃

2.3 檢查統計信息

2.4 檢查高效訪問結構

2.5 檢查影響優化器的參數

2.6 SQL語句編寫問題

2.7 SQL優??\x2F限制導致的執行計劃差

SQL優化案例

SQL執行計劃獲取

4.1 如何獲取準確的執行計劃

4.2 看懂執行計劃執行順序

一SQL優化的本質

一般來說，SQL優化是讓SQL運行得更快，使SQL更快的方式有很多，比如提高索引的使用效率，或者并行查詢。可以看到里面的公式：

執行效率或者一般說的執行時間，是和完成一次SQL所需要訪問的資源總量（S）成正比以及單位時間內能夠訪問的資源量（V）成反比，S越大，效率越低，V越大效率越高。 比如通過并行查詢，則可以提升單位時間內訪問的資源量。

當然，這僅僅是從執行時間上考慮，SQL優化肯定不僅僅是執行時間降低，應該是資源使用與執行時間降低之間尋求一種平衡，否則，盲目并行，可能提升不了效率，反而讓系統資源消耗殆盡。

http\x3A?說，SQL優化的本質就是：1、縮短響應時間；2、提升系統吞吐量；3、提升系統負載能力。要使用多種手段，在提升系統吞吐量和增加系統負載能力，提高單個SQL效率之間尋求一種平衡。就是要盡量減少一條SQL需要訪問的資源總量，比如走索引更好，那么不要使用全表掃描。

二SQL優化Road Map

一條SQL的優化路線圖如下所示：

具體操作步驟：

2.1 制定SQL優化目標

獲取待優化SQL、制定優化目標：從AWR、ASH、ORA工具等主動發現有問題的SQL、用戶報告有性能問題DBA介入等，通過對SQL的執行情況進行了解，先初步制定SQL的優化目標。

2.2 檢查執行計劃

explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。 執行計劃是我們進行SQL優化的核心內容，無計劃，不優化。看執行計劃有一些技巧，也有很多方式，各種方式之間是有區別的。

2.3 檢查統計信息

ORACLE使用DBMS_STATS包對統計信息進行管理，涉及系統統計信息、表、列、索引、分區等對象的統計信息，統計信息是SQL能夠使用正確執行計劃的保證。我們知道，ORACLE CBO優化器是利用統計信息來判斷正確的執行路徑，JOIN方式的，因此，準確的統計信息是產生正確執行計劃的首要條件。

可以從這個圖看出，一條SQL產生執行計劃需要經過哪些步驟，在我看來：1、正確的查詢轉換；2、準確的統計信息，是產生正確執行計劃的重要保證。當然，還有BUG，或優化器限制等也會導致SQL效率低下，無法產生正確的執行計劃。

如圖所示：

2.4 檢??/效訪問結構

重要的訪問結構，諸如索引、分區等能夠快速提高SQL執行效率。表存儲的數據本身，如碎片過多、數據傾斜嚴重、數據存儲離散度大，也會影響效率。

2.5 檢查影響優化器的參數

2016-02-21 23:17izer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等對SQL執行計劃影響較大。比如有時候我們通過禁用_optimizer_mjc_enabled 參數，讓執行計劃不要使用笛卡爾積來提升效率，因為這個參數開啟有很多問題，所以一般生產庫都要求禁用。

還有什么能夠影響執行計劃呢？對，new features,每個版本的new features，引入的目的都是好的，但是實際使用中，可能觸發BUG。比如11g的ACS(自適應游標共享）、automatic serial direct path（自動串行直接路徑讀）、extended statistics、SQL query result cache等。有的新特性會導致問題，所以需要謹慎使用。

比如11g adaptive cursor sharing,自適應游標共享，它的引入是為了解決使用綁定變量與數據傾斜值，要產生多樣性執行計劃。因為綁定變量是為了共享執行計劃，但是數據傾斜了，有的值要求走索引，有的值要求走全表，這樣與使用綁定變量就產生了矛盾。以前是通過cursor_sharing=similar這樣的設置可以解決，但是有很多BUG，會產生version count過高的問題，或者我們對不同的值（如果值很少），可以寫多條SQL來解決，這都不是好的方案，11g acs引入就是為了解決這些問題，讓這些東西交給oracle來做。但是事與愿違，以后你們遇到執行計劃一會變一下，有快有慢，首先可以檢查acs有沒有關閉。

alter system set “_optimizer_extended_cursor_sharing_rel”=’NONE';

2.6 SQL語句編寫問題

SQL語句結構復雜、使用了不合理的語法，比如UNION代替UNION ALL都可能導致性能低下。 并不是說ORACLE優化器很強大了，我們就可以隨便寫SQL了，那是不正確的。SQL是一門編程語言，它能夠執行的快，是有一些普遍的規則的，遵循這種編程語言特性，簡化語句，才能寫出好的程序。SQL語句編寫出了問題，我們就需要改寫，就需要調整業務，改涉及等。

2.7 SQL優化器限制導致的執行計劃差

這個很重要，統計信息準確，SQL也不復雜，索引也有。。。都滿足，為什么我的SQL還是差，那么得考慮優化器限制因素了。這里說1點常見的執行計劃限制，當semi join與or連用的時候（也就是exists(subquery) or ...或者in (subquery) or...，如果執行計劃中因為OR導致有FILTER操作符，就得注意了，可能慢的因素就和OR有關。這時候我們得改寫SQL，當然改寫為UNION或UNION ALL了。

OK，以上全部檢查完畢，我的系統還是很差，功能還是很慢，或者已經無法從SQL本身進行調整提升性能了，那咋辦？優化設計，這是終極方法。有些東西不優化設計是無法解決的，比如業務高峰期跑了一堆SQL，CPU已經很吃緊，又不給增加，突然上線一個耗資源的業務，其他SQL已無法調整。那只能優化設計，比如有些耗資源的業務可以換時間段執行等。

以上幾點，是我們進行優化需要考慮的地方，可以逐步檢查。當然，80%到90%的純SQL性能調整，我們通過建立索引，收集正確統計信息，改寫避免優化器限制，已經能夠解決了。

三SQL優化案例

看第一個獲取待優化的SQL.......如果主動優化，一般從AWR、ASH等里面找到性能差的SQL，然后優化之。

看一個案例，占CPU 72%的SQL來自于同一模塊，第一行是存儲過程，通過下面綠色框住的SQL與第一行比較，主要通過EXECUTION，基本判斷下面的綠色框住的SQL就是那個存儲過程中的。也可以和業務確認下，OK，這些SQL的執行頻次很高，因為是營銷業務，如果要優化，就得搞定這些SQL。

這些SQL，單條SQL的buffer gets也就1000多點，效率還是很高的，但是因為執行的太過于頻繁，所以資源消耗極大，因此，得檢查下，能不能更優呢？

以第1條SQL：58q183atbusat為例：

SELECT B.ACT_ID,

B.ACT_NAME,

B.TASK_ID,

B.MKT_DICTION,

B.CUST_GROUP_ID,

NVL(B.ATTEST_FLAG, 'N'),

NVL(B.DOUWIN_FLAG, 'N'),

B.CHN_DESC,

NVL(B.SIGN_FLAG, 'N'),

B.MAX_EXECUTE_NUM

FROM (SELECT DISTINCT (ACT_ID)

FROM MK_RULECHN_REL

WHERE CHN_STATUS = '04'

AND CHN_TYPE = :B1) A,

TABLE(CAST(:B2 AS TYPE_MK_ACTIONINFO_TABLE)) B

WHERE A.ACT_ID = B.ACT_ID

SQL其實很簡單，一個查詢構建的A表，一個TABLE函數構建的B表關聯..... 不知道大家對這個TABLE函數熟悉不熟悉？也就是將一個集合轉成表，是PL/SQL里的東西

那個collection部分就是TABLE函數，下面的表走了全表掃描：

按步驟檢查，發現不了問題，但是知道，可能是因為HASH JOIN導致全表掃描的問題，是否走NESTED LOOPS+INDEX更好，很顯然，要檢查TABLE函數大概返回多少行。

經過確認，最多也就返回200-300行，最終結果集也是幾百行而已。

那么猜測，問題就在于TABLE函數，走了HASH JOIN，上面的執行計劃，TABLE函數部分，ROWS為空。

來單獨檢查一把：返回8168行，返回8000多行，足以導致走HASH JOIN了....而事實，我們至多返回200-300行：

所以每個步驟返回的行，是JOIN方式選擇的重要因素，可以谷歌一把，TABLE函數返回8168就是個固定值，block_size=8K的時候就是這么大，可以說，這是ORACLE的一個限制。

只要你用了TABLE函數，就偏向于走HASH JOIN了

http://www.oracle-developer.net/display.php?id=427 有興趣的可以看這個鏈接的內容。

解決方式很多了，也就是要走NESTED LOOPS+index, 既然8168很大，那么我們就讓優化器知道TABLE函數返回的行少點，才百行左右。

以下些都可以，當然也可以使用hint:use_nl等

CARDINALITY hint (9i+) undocumented;

OPT_ESTIMATE hint (10g+) undocumented;

DYNAMIC_SAMPLING hint (11.1.0.7+);

Extensible Optimiser (10g+).

因為SQL的SELECT部分只訪問B，全部來自于TABLE函數，所以改寫為子查詢就可以了，使用子查詢，自然distinct也就沒有必要了，因為是semi join(半連接）。

最終改寫使用cardinality hint讓優化器知道B返回的行只有100行，你給我走NESTED LOOPS+INDEX，然后解決。

原來的sql：

修改后的sql：

效率提升幾十倍：

一個占72%的應用，我們提升幾十倍后，那對系統性能明顯是極好的。最終，在執行次數增加50%的情況下，w4sd08pa主機CPU使用率由原來的高峰期平均47%的使用率降低為23%。

這個問題能夠解決有兩個方面：

1、猜測并測試優化器的限制（table函數固定返回行8168)；2、實際返回的行200-300。兩者缺一不可。如果實際返回的行就是幾千上萬，那么，單純通過優化SQL，也是無法取得良好效果的。

掃描文末二維碼，關注DBA+社群微信公眾號（dbaplus），可下載DBA+社群技術沙龍、OOW大會、2015GOPS、DCon2015等技術盛典PPT。

四SQL執行計劃獲取

執行計劃就是SQL調優的核心，上面的SQL也是通過看到執行計劃走HASH JOIN可能有問題出發的。

那么首先要搞定2個問題：

1、如何獲取我要的執行計劃（準確的計劃）；

2、怎么看懂并找出執行計劃里的問題。

4.1 如何獲取準確的執行計劃

獲取SQL執行計劃的方式：

EXPLAIN PLAN

估算

忽略綁定變量

非執行

SQL_TRACE

真實計劃，需要用TKPROF工具解析

可以獲得綁定變量值

EVENT 10053

真實計劃

研究執行計劃產生的原因

AUTOTRACE

內部使用EXPLAIN PLAN

DBMS_XPLAN

dbms_xplan.display_cursor

dbms_xplan.display_awr

真實計劃

OTHERS

如awrsqrpt、sqlt、pl/sql、sql developer、toad等

大家一般怎么獲取執行計劃？我一般用的較多的是dbms_xplan.display_cursor，優點很明顯：1、獲取的是真實執行的計劃；2、多種參數。還可以獲取綁定變量的值方便驗證。

10053是檢查優化器行為的，實在搞不懂為什么走那個計劃可以看看，用得較少。

10046可以檢查一些等待事件的內容，也可以獲取綁定變量，一般用得也比較少。

set autotrace traceonly或者explain,他們的執行計劃是同一來源，記住，都來自plan_table，是估算的，可能不是真實執行的計劃，可能是不準的。

所以，你看得不對勁了，就得質疑它的準確性，autotrace traceonly的好處是可以看到一致性讀，物理讀，返回行等，這是真實的。因為可以用一致性讀，物理讀來驗證優化效果

其他的，比如awrsqrpt等都可以獲取執行計劃，不過我很少用，特別是plsq developer這種工具，F5看計劃，我幾乎是不用的，他也是plan table里的估算計劃。如果很長，那無法分析。

建議大家看真實的計劃，說一點，我經常通過alter session set statistics_level=all或者gather_plan_statistics hint，然后執行sql,然后通過

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));來看實際執行的信息

好處很明顯，能夠看到執行計劃每步的E-ROWS（估算的行）,A-ROWS（真實的行）,STARTS,BUFFER GETS，A-TIME（真實的執行時間）等信息。。。我們通過對比估算的與真實的差距，可以判斷哪些表統計信息可能有問題，執行計劃是不是走錯了，省的我們自己根據謂詞去計算這步導致返回多少行。

注意一點，如果一SQL執行很長時間，通過上面的方式來看計劃，我們是可以終止的，比如執行2小時執行不玩的SQL，一般我沒有耐心，最多5分鐘，我就終止。終止完，通過display_cursor也是可以看出執行信息的。

比如某個步驟執行100萬次，我這條SQL才能執行完，要3小時才可以，我5分鐘執行了100次，我終止了SQL我要看的就是一個比例情況，可以通過這個比例來判斷，哪個步驟耗的時間最長，哪里大概有問題，然后解決。

優化器很多限制的，比如剛才的TABLE函數固定返回8168，或者算法限制.....很多不準的，如果算法算出來的與真實差別很大，那可能就會導致問題。統計信息有時候也無法收集準確的，比如直方圖，就有很多問題，所以12c的直方圖多了幾種....之前只有等高和等頻直方圖。

剛才的set statistics_level直接寫會輸出結果，我們可以讓他不輸出結果：

1、sql內容放到文件中，前面加上set termout off （這樣可以對輸出結果不輸出）

2、然后display_cursor文件中

用這種東西看執行計劃，有時候很方便找出問題,否則我們自己得手動根據每個步驟對應的謂詞，自己寫SQL去計算真實返回的行，然后再來比較，用這個，ORACLE全幫我們干好了。

4.2 看懂執行計劃執行順序

一般怎么看執行計劃呢？

COPY到UE里去。

用光標大法，找到入口，最先執行的，光標定位ID=0的，然后一直縮進向下，如果被擋住了，那么這部分就是入口了。

比如ID=10的繼續索引，就被ID=11的擋住了，所以第10步就是入口。

找到入口后，反向光標來，利用平行級別的最上最先執行，最右最先執行原則，來看父操作與子操作的關系，移動光標即可。

比如這里的第13步，我只需要定位光標在PARTITION這個P前面，然后向上移動，立馬就知道，它的驅動表是ID=5的VIEW，因為他們是對齊的。

然后看看之間的JOIN關系是不是有問題，返回的行估算等。

執行計劃最右最上最先執行規則，有個例外，大家知道不？？就是通過以上規則，是不正確的。

（標量子查詢）

SELECT a.employee_id,

a.department_id,

(SELECT COUNT(*) FROM emp_b b

WHERE a.department_id=b.department_id

) cnt

FROM emp_a a;

比如這個ID=2的在前面，但是它事實上是被ID=3的驅動的，也就是被emp_a驅動的，這違背了一般的執行計劃順序規則，平時注意點就行了，標量子查詢謂詞里會出現綁定變量，比如這里的：B1，因為每次帶一個值去驅動子查詢。

搞清楚執行計劃怎么干，那么看執行計劃看啥？

1、看JOIN的方式

2、看表的訪問方式，走全表，走索引

3、看有沒有一些經常影響性能的操作，比如FILTER

4、看cardinality(rows)與真實的差距

不要太過于關注COST，COST是估算的，大不一定就慢，小不一定就快……當然比如COST很小，rows返回的都是很小的，很慢。那么，我們可能得考慮統計信息是不是過舊問題。

統計信息很重要，就說一個例子：

走了索引，COST很小，一切都很完美，但是AWR現實占80%的資源。一般啥情況？單純從SQL上看，也就是這執行計劃估計不對，自己測一下，很慢。也就是COST很小，ROWS很小，走索引，很完美的計劃是錯誤的，那么很顯然，基本就是統計信息導致的了。

實際第4步走sendtime索引，應該返回1689393行，但是執行計劃估算返回1行，統計信息不準確，再次檢查統計信息收集日期是5月前的。

SQL> SELECT COUNT(1) FROM MSP.T_MS_MEDIA_TASK WHERE SENDTIME >=TRUNC(SYSDATE,'dd') AND MONTHDAY = TO_CHAR(SYSDATE,'mmdd') ;

? COUNT(1)

----------

? ?1689393

收集統計信息，for all columns size repeat 保持原有直方圖信息

?exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MSP',tabname=>'T_MS_MEDIA_TASK',estimate_percent=>10,method_opt=>'for all columns size repeat', no_invalidate=>false,cascade=>true,degree => 10);

返回168萬行，但是現有統計信息卻讓cbo認為是1行，這差別也太大了。

method_opt=>'for all columns size repeat', 這里說下，更新統計信息，最好使用for all columns size repeat...

repeat的好處是啥，比如列有直方圖，會給你保留，列沒有統計信息會按照for all columns size 1收集。。。其他原來怎么收就怎么收。

你用一個for all columns size 1或size skewonly,或者不寫（auto)都可能改變原有統計信息的收集方式，都有可能影響SQL的執行效率。

高效訪問結構讓SQL更快，這個不說了，主要是建索引。如何建索引也是一個很復雜的問題，說一點，一般復合索引，等值查詢條件頻率高的，作為前導列較好。因為直接訪問可能效率比>,<...等高，后者訪問了還需要過濾。

下面看下影響優化器的參數導致的性能問題。

這是10g執行計劃，一個視圖是UNION ALL做的，全部走索引：

但是11.2.0.4全表掃描了。

10g視圖有謂詞推薦，也就是查詢轉換里的一種OJPPD=OLD JOIN PUSH PREDICATE

升級到11.2.0.4，視圖里的10張表都變成FULL SCAN。

連接謂詞(A.“PAYIO_SN”=“B”.“WRTOFF_SN”)未推入到視圖中。

執行時間從0.01s到4s,buffer gets從212到99w。

很顯然，我要檢查，統計信息沒有問題，然后怎么干？？看在11g里做優化器降級如何。

在11.2.0.4中使用optimizer_features_enable分別測試10.2.0.4和11.2.0.3均可謂詞推入到視圖中走索引。那么問題就出現在11.2.0.4了，因為11.2.0.3都是可以的。說明11.2.0.4對視圖謂詞推入算法有了改變。很多優化器的東西，oracle都有參數控制的，除了參數，還有各補對應的fix control。那么先檢查補丁相關的

from v$system_fix_control WHERE sql_feature LIKE ‘%JPPD%’

查到了，各種開啟關閉，沒有用。最后看10053，分析10053，詳細參看是否是BUG導致，還是優化器改進問題，參數設置問題：

10053看到默認參數被關了，檢查下，大概和查詢轉換的兩個參數:

_optimizer_cost_based_transformation

_optimizer_squ_bottomup

都被關了，當然10.2.0.4和11.2.0.3被關了也是可以的。

還看到基于CBO的查詢轉換失敗，因為參數被關了，OJPPD（10g那種方式）失效了……那當然走不了，JPPD是11g的，也失效了。

基本知道執行計劃如何看，關注哪些就很有用了，不要太關注啥COST前面講了11.2.0.3都可以，到11.2.0.4不行了，那可能有2種原因：1、算法改了；2、BUG。

當然基于正常的理解，視圖謂詞推薦，ORACLE是必須支持的，也是不存在問題的，所以肯定有正規的解決方式。先看第2個 BUG，按理說，這種常見的東西，特別是這SQL不算復雜，ORACLE應該不會觸發BUG，當然，查詢轉換是存在各種BUG的，11.2.0,4少了很多MOS中搜一下，比如這個JPPD，就有很多BUG，但是沒有看到11.2.0.4對應的。

**************************

Predicate Move-Around (PM)

**************************

。。。

OJPPD: OJPPD bypassed: View semijoined to table.

JPPD: JPPD bypassed: View not on right-side of outer-join.

通過這個判斷，10.2.0.4那種OJPPD，基于規則的查詢轉換不行了，也就是算法改變，因為cost_base_query_transformation參數關了，應該走OJPPD的。現在JPPD也走不了，因為參數被關了，這個是基于成本的查詢轉換才可以。

所以，這是由于算法更新導致的問題，要求必須按照ORACLE官方建議，恢復對應查詢轉換參數默認值：在基于COST的查詢轉換部分，只能走JPPD（和OJPPD類似），ORACLE建議設置CBQT參數，基于COST查詢轉換更準確。

開啟COST查詢轉換，初始化優化器參數 _optimizer_cost_based_transformation設為默認值(linear)。CBQT參數有如下值：

"exhaustive", "iterative", "linear", "on", "off"。

另外通過測試得知，還需要設置_optimizer_squ_bottomup （enables unnesting of subquery in a bottom-up manner）

參數默認值true.

這個問題，但是發了SR，老外也不知道，然后我發現這2個參數恢復默認值可以，當然首先cbqt參數我認為肯定有關系，后面的squ_bottomup是測試出來的。。。后來告訴老外，老外也認可算法改變導致的問題。所以核心參數的默認值改變，是很危險的，可能影響全局，如果這兩個參數不恢復，涉及數百條核心SQL就無法正常執行，也就是系統不具有可用性了。

最后說一下，經常碰到的一個優化器缺陷：

SELECT ELEMENT_TYPEA,

ELEMENT_IDA,

ELEMENT_TYPEB,

ELEMENT_IDB,

RELATION_TYPE,

EFF_RULE_ID,

EXP_RULE_ID,

CTRL_CODE,

EFF_DATE,

EXP_DATE,

GROUP_ID,

BASE_TIME_ TYPE,

POWER_RIGHT,

POSITIVE_TYPE,

BOTHWAY_FLAG

FROM DBPRODADM.pd_prc_rel a

WHERE EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE

AND (EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

當OR與semi join放在一起的時候，會觸發無法進行subquery unnest的問題，也就是可能會產生FILTER,導致SQL非常緩慢，有的甚至幾天，幾十天也別想運行結束了。

第5、6步執行92萬多次，那肯定慢了……問題就是有個FILTER……

FILTER類似循環，在無法unnest子查詢中存在，類似標量子查詢那種走法，謂詞里也有綁定變量的東西。

他們唯一的好處就是內部構建HASH 表，如果匹配的重復值特別多，那么探測次數少，效率好，但是大部分時候，重復值不多，那么就是災難了

對于這種優化器限制的，一般就是得改寫了，因為SQL結構決定無法走高效的執行計劃。。。因為我這里雖然走了所以，但是執行次數太多，如果執行次數少，到也無所謂。

改寫后的sql：

SELECT ELEMENT_TYPEA,

ELEMENT_IDA,

ELEMENT_TYPEB,

ELEMENT_IDB,

RELATION_TYPE,

EFF_RULE_ID,

EXP_RULE_ID,

CTRL_CODE,

EFF_DATE,

EXP_DATE,

GROUP_ID,

BASE_TIME_ TYPE,

POWER_RIGHT,

POSITIVE_TYPE,

BOTHWAY_FLAG

FROM DBPRODADM.pd_prc_rel a

WHERE EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE

AND (EXISTS (SELECT 1

FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

很顯然，這里的條件是exists or ...那么改寫得用UNION或UNION ALL了，為了避免有重復行，用UNION

select ELEMENT_TYPEA,ELEMENT_IDA,ELEMENT_TYPEB,ELEMENT_IDB,RELATION_TYPE

,EFF_RULE_ID,EXP_RULE_ID,CTRL_CODE,EFF_DATE,EXP_DATE,GROUP_ID,BASE_TIME_TYPE,

POWER_RIGHT,POSITIVE_TYPE,BOTHWAY_FLAG

from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and exists (select 1

from DBPRODADM.pd_prc_dict c

where a.element_idb = c.prod_prcid

and c.prod_prc_type = '1')

and a.relation_type = '10'

union

select ELEMENT_TYPEA,ELEMENT_IDA,ELEMENT_TYPEB,ELEMENT_IDB,RELATION_TYPE

,EFF_RULE_ID,EXP_RULE_ID,CTRL_CODE,EFF_DATE,EXP_DATE,GROUP_ID,BASE_TIME_TYPE,

POWER_RIGHT,POSITIVE_TYPE,BOTHWAY_FLAG

from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and a.element_idb = 'X'

and a.relation_type = '10';

兩個分支都走HASH JOIN，starts全部為1，雖然全部是全表掃描，但是執行效率提升很明顯，執行時間從12s到7s,gets從222w到4.5w之后，是否還有優化空間？

特別邏輯讀少了很多。后續優化：

1)改寫使用了UNION,是否能改成UNION ALL避免排序？

2)這么多全表掃描，是否能夠讓一些可以走索引？當然，這些是可以做到的，但是不是主要工作了。這個案例告訴我們，優化器是有很多限制的，不是萬能的。

除了統計信息正確，良好的SQL結構，能夠讓SQL正確進行查詢轉換，正確的訪問結構，如索引等……都是讓SQL高效執行的前提條件。復雜！=低效，簡單！=高效。讓優化器理解，并且有合適的訪問結構支持，才是王道！

簡單的SQL不是快的保證，復雜的也不一定見得慢，高效的執行計劃才是最重要的，索引優化SQL，最重要的就是讓不好的執行計劃變得好。

也就是從多個方面入手，最終達到我們的優化目標。

到此，相信大家對“oracle數據庫怎么優化”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！