<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                ??一站式輕松地調用各大LLM模型接口,支持GPT4、智譜、豆包、星火、月之暗面及文生圖、文生視頻 廣告
                http://dbaplus.cn/news-10-80-1.html 聽“俊”一席話,勝讀十年書。看了這篇由DBA+社群聯合發起人丁俊大師(網名:dingjun123)分享的SQL優化大作,其他Oracle SQL優化文章都不必再看了! 專家簡介 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060618651.jpg) 丁俊 網名:dingjun123 DBA+社群聯合發起人 性能優化專家,Oracle ACEA,ITPUB開發版資深版主。8年電信行業從業經驗,在某大型電信系統提供商工作7年,任資深工程師,從事過系統開發與維護、業務架構和數據分析、系統優化等工作。擅長基于ORACLE的系統優化,精通SQL、PL/SQL、JAVA等。電子工業出版社終身榮譽作者,《劍破冰山-Oracle開發藝術》副主編。 目錄 1. SQL優化的本質 2. SQL優化Road Map 2.1 制定SQL優化目標 2.2 檢查執行計劃 2.3 檢查統計信息 2.4 檢查高效訪問結構 2.5 檢查影響優化器的參數 2.6 SQL語句編寫問題 2.7 SQL優化器限制導致的執行計劃差 3. SQL優化案例 4. SQL執行計劃獲取 4.1 如何獲取準確的執行計劃 4.2 看懂執行計劃執行順序 一 SQL優化的本質 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060634527.jpg "1.jpg") 一般來說,SQL優化是讓SQL運行得更快,使SQL更快的方式有很多,比如提高索引的使用效率,或者并行查詢。可以看到里面的公式: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060642550.jpg "2.jpg") 執行效率或者一般說的執行時間,是和完成一次SQL所需要訪問的資源總量(S)成正比以及單位時間內能夠訪問的資源量(V)成反比,S越大,效率越低,V越大效率越高。 比如通過并行查詢,則可以提升單位時間內訪問的資源量。 當然,這僅僅是從執行時間上考慮,SQL優化肯定不僅僅是執行時間降低,應該是資源使用與執行時間降低之間尋求一種平衡,否則,盲目并行,可能提升不了效率,反而讓系統資源消耗殆盡。 最終來說,SQL優化的本質就是:1、縮短響應時間;2、提升系統吞吐量;3、提升系統負載能力。要使用多種手段,在提升系統吞吐量和增加系統負載能力,提高單個SQL效率之間尋求一種平衡。就是要盡量減少一條SQL需要訪問的資源總量,比如走索引更好,那么不要使用全表掃描。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060652467.jpg "3.jpg") 二 SQL優化Road Map 一條SQL的優化路線圖如下所示: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060659341.jpg "4.jpg") 具體操作步驟: 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效率低下,無法產生正確的執行計劃。 如圖所示: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060709613.jpg "5.jpg") 2.4 檢查高效訪問結構 重要的訪問結構,諸如索引、分區等能夠快速提高SQL執行效率。表存儲的數據本身,如碎片過多、數據傾斜嚴重、數據存儲離散度大,也會影響效率。 2.5 檢查影響優化器的參數 optimizer_mode、optimizer_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,然后優化之。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060718494.jpg "6.jpg") ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060727995.jpg "7.jpg") 看一個案例,占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函數,下面的表走了全表掃描: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060735515.jpg "8.jpg") ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060743259.jpg "9.jpg") 按步驟檢查,發現不了問題,但是知道,可能是因為HASH JOIN導致全表掃描的問題,是否走NESTED LOOPS+INDEX更好,很顯然,要檢查TABLE函數大概返回多少行。 經過確認,最多也就返回200-300行,最終結果集也是幾百行而已。 那么猜測,問題就在于TABLE函數,走了HASH JOIN,上面的執行計劃,TABLE函數部分,ROWS為空。 來單獨檢查一把:返回8168行,返回8000多行,足以導致走HASH JOIN了....而事實,我們至多返回200-300行: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060751407.jpg "10.jpg") 所以每個步驟返回的行,是JOIN方式選擇的重要因素,可以谷歌一把,TABLE函數返回8168就是個固定值,block_size=8K的時候就是這么大,可以說,這是ORACLE的一個限制。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060759570.jpg "11.jpg") 只要你用了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: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060808477.jpg "11-1.jpg") 修改后的sql: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060815990.jpg "11-2.jpg") 效率提升幾十倍: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060823800.jpg "12.jpg") 一個占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可能有問題出發的。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060834480.jpg "13.jpg") 那么首先要搞定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文件中 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060844900.jpg "13-1.jpg") 用這種東西看執行計劃,有時候很方便找出問題,否則我們自己得手動根據每個步驟對應的謂詞,自己寫SQL去計算真實返回的行,然后再來比較,用這個,ORACLE全幫我們干好了。 4.2 ?看懂執行計劃執行順序 一般怎么看執行計劃呢? ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060853838.jpg "14.jpg") COPY到UE里去。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060901990.jpg "15.jpg") 用光標大法,找到入口,最先執行的,光標定位ID=0的,然后一直縮進向下,如果被擋住了,那么這部分就是入口了。 比如ID=10的繼續索引,就被ID=11的擋住了,所以第10步就是入口。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060911591.jpg "16.jpg") 找到入口后,反向光標來,利用平行級別的最上最先執行,最右最先執行原則,來看父操作與子操作的關系,移動光標即可。 比如這里的第13步,我只需要定位光標在PARTITION這個P前面,然后向上移動,立馬就知道,它的驅動表是ID=5的VIEW,因為他們是對齊的。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060920965.jpg "17.jpg") 然后看看之間的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,因為每次帶一個值去驅動子查詢。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060928619.jpg "18.jpg") 搞清楚執行計劃怎么干,那么看執行計劃看啥? 1、看JOIN的方式 2、看表的訪問方式,走全表,走索引 3、看有沒有一些經常影響性能的操作,比如FILTER 4、看cardinality(rows)與真實的差距 不要太過于關注COST,COST是估算的,大不一定就慢,小不一定就快……當然比如COST很小,rows返回的都是很小的,很慢。那么,我們可能得考慮統計信息是不是過舊問題。 統計信息很重要,就說一個例子: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060946747.jpg "19.jpg") 走了索引,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做的,全部走索引: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628060956277.jpg "20.jpg") 但是11.2.0.4全表掃描了。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061004843.jpg "21.jpg") 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導致,還是優化器改進問題,參數設置問題: ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061013698.jpg "22.jpg") 10053看到默認參數被關了,檢查下,大概和查詢轉換的兩個參數: _optimizer_cost_based_transformation _optimizer_squ_bottomup 都被關了,當然10.2.0.4和11.2.0.3被關了也是可以的。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061022467.jpg "22-1.jpg") 還看到基于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對應的。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061031142.jpg "23.jpg") ************************** 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非常緩慢,有的甚至幾天,幾十天也別想運行結束了。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061042623.jpg "24.jpg") 第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之后,是否還有優化空間? ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061052580.jpg "25.jpg") 特別邏輯讀少了很多。后續優化: 1)改寫使用了UNION,是否能改成UNION ALL避免排序? 2)這么多全表掃描,是否能夠讓一些可以走索引?當然,這些是可以做到的,但是不是主要工作了。這個案例告訴我們,優化器是有很多限制的,不是萬能的。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061100474.jpg "26.jpg") 除了統計信息正確,良好的SQL結構,能夠讓SQL正確進行查詢轉換,正確的訪問結構,如索引等……都是讓SQL高效執行的前提條件。復雜!=低效,簡單!=高效。讓優化器理解,并且有合適的訪問結構支持,才是王道! 簡單的SQL不是快的保證,復雜的也不一定見得慢,高效的執行計劃才是最重要的,索引優化SQL,最重要的就是讓不好的執行計劃變得好。 也就是從多個方面入手,最終達到我們的優化目標。 ![](http://dbaplus.cn/uploadfile/2016/0628/20160628061109996.jpg "27.jpg") “DBA+社群”將陸續在各大城市群進行線上專題分享活動,以后每周一、周三晚上為【DBA+專業群】的固定時間,每周二、周四晚上為【DBA+各城市群】的固定時間,每周五晚上為【DAMS架構師精英群】的固定時間,歡迎大家積極加入我們。無論是內容還是形式,有好的建議我們都會積極采納。 未入群的小伙伴們可先關注DBA+社群微信公眾號:dbaplus,回復“我要入群”獲得各城市群二維碼,再加入你所在的城市微群。 關注DBA+社群微信公眾號:dbaplus
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看