<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>


            Part 1中我們了解了兩種獲取執行計劃的方式,第一種是優化器預測它將會怎么執行,通過以下方法查詢獲?。?/span>


            explain plan for {sql statement}
            select * from table(dbms_xplan.display);


            第二種是優化器在我們執行語句后真正的執行路徑,通過以下方法查詢獲?。?/span>


            set serveroutput off
            {sql statement}
            select * from table(dbms_xplan.display_cursor);


            事實上,在
            Part 1中,我故意使用兩種獲取執行計劃的方法,來證明對同一條存在綁定變量的語句可以生成兩種截然不同的執行計劃。

            Part 2中我們會評估幾種查看真實執行計劃的方法,但是首先我們關注目前我們接觸到函數的其他調用選項,這會使我們對執行計劃在最終用戶的環境中的復現更加自信。



            Format Options

            一般來說,如果想要執行計劃與最終用戶環境一致的話,我們需要他們生產數據的備份,對應的統計信息,系統的參數還有一致的輸入。生產數據以及統計信息一般都是一樣的,所以我們真正需要關注的就是客戶環境是否不同以及語句輸入;我們可以通過數據庫來幫助我們獲取在某一刻的執行信息。

            這里也有一個小提醒,需要注意數據、統計信息還有謂詞條件的及時同步。舉個例子,如果你的生產數據是好幾個星期前的,那么你執行語句時要根據幾個星期前用戶當時執行的語句去執行;因為如果條件中存在SYSDATE,那么就不能很好的模擬當時的環境。

            Part 1中提到過的,調用dbms_xplan.display_cursor()可以賦值三個參數,它們分別是sql_id,child_numberformatting option。有兩個formatting option參數能有效的幫助你解決最近一次出現的問題,"peeked_binds""outline"。前者會列出(只需要一點運氣)優化查詢時使用的真實的值,后者會提供一個hint的列表,如果我們為了這個查詢創建了一個存儲大綱或者SQL Plan Baseline,通過這些hint我們可以發現優化器的環境是否不同。

            這里有個小例子,通過對視圖dba_extentscopy表的查詢來演示使用和輸出。


            alter session setworkarea_size_policy = manual;
            alter session setsort_area_size = 10485760;
            
            alter session setoptimizer_mode = first_rows_10;
            alter session set"_hash_join_enabled" = false;
            
            set serveroutput off
            
            variable m_owner varchar2(32)
            variable m_object varchar2(32)
            execute :m_owner :="TEST_USER"
            execute :m_object := "T1"
            
            select /*+ tracking */ * from t1 
            where owner = :m_owner 
            and segment_name =:m_object 
            order by extent_id;
            
            select * from table(
            dbms_xplan.display_cursor(null,null,"outlinepeeked_binds")
            );


            我修改了一些優化器相關的參數,set serveroutput off不會導致dbms_xplan.display_cursor()輸出關于dbms_output.get_lines()的調用,可以在查詢時通過定義的一組參數使用綁定變量。之后我調用display_cursor(null, null, "outlinepeeked_binds")來展示我最近的查詢并且添加可用的綁定變量的值到Outline/SQL Pan Management信息中。這是輸出結果:


            SQL_ID  0wwbn4bhvrrxj, child number 0
            -------------------------------------
            select /*+ tracking */  * from t1 where owner = :m_owner and
            segment_name = :m_object order by extent_id
            
            Plan hash value: 3684778271
            
            --------------------------------------------------------------------------------------
            | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
            |   1 |  SORT ORDER BY               |       |     1 |    65 |     3  (34)| 00:00:01 |
            |*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    65 |     2   (0)| 00:00:01 |
            |*  3 |    INDEX RANGE SCAN          | T1_I2 |     2 |       |     1   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------
            
            Outline Data
            -------------
              /*+
                  BEGIN_OUTLINE_DATA
                  IGNORE_OPTIM_EMBEDDED_HINTS
                  OPTIMIZER_FEATURES_ENABLE("11.2.0.4")
                  DB_VERSION("11.2.0.4")
                  FIRST_ROWS(10)
                  OUTLINE_LEAF(@"SEL$1")
                  INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."SEGMENT_NAME"))
                  END_OUTLINE_DATA
              */
            
            Peeked Binds (identified by position):
            --------------------------------------
               1 - :M_OWNER (VARCHAR2(30), CSID=178): "TEST_USER"
               2 - :M_OBJECT (VARCHAR2(30), CSID=178): "T1"
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - filter("OWNER"=:M_OWNER)
               3 - access("SEGMENT_NAME"=:M_OBJECT)


            這個例子中,我們很幸運的獲得了優化器在執行計劃信息中記錄的所有綁定變量的值,包括CSID(charcater set id)這種細節,當你開使用NLS字符集時,有時候會造成一些意外。

            我們先看”Outline Data”,可以看到hint “first_rows(10)”是唯一被記錄的我們會話的變更,剩下的變更都沒有被捕捉到;format option也給了我們一個提示,我們可以在這個查詢開始試驗時需要做一些額外的檢查,來確認我們能否獲得一個更好的計劃。這次我們假設其他人已經執行過這個查詢了,在我們分析前,我們需要根據一些特殊的文本來追蹤(這就是我為什么在語句中添加/*+ tracking */,這個實際上不是hint)。


            column sql_id new_valuem_sql_id
            column child_number new_valuem_child_no
            
            select  sql_id,child_number, sql_text 
            from   v$sql 
            where   sql_textlike "%tracking%" 
            and    sql_text not like "%v$sql%"
            ;
            
            select * from table(
            dbms_xplan.display_cursor("&m_sql_id",&m_child_no,"outlinepeeked_binds")
            );
            
            select 
            name, value 
            from 
            v$sql_optimizer_env 
            where 
            sql_id      = "&m_sql_id" 
            and child_number =&m_child_no 
            and isdefault   = "NO"
            ;


            這些代碼都能在SQL*PLUS里執行,我已經設置了一些列定義用來捕捉這個查詢語句上次返回的值,SQL_IDCHILD_NUMBER的值是我們所需要的。在這里我假設這條語句僅僅只有一個子游標。就像你看到的,我使用"&替換"將獲取到的值帶入到display_cursor()的調用與v$sql_optimizer_env的查詢中。這里不顯示對display_cursor()的調用結果(輸出結果與前面的重復),只顯示對v$sql_optimizer_env的查詢結果。


            NAME                                    VALUE
            -----------------------------------------------------------------
            hash_area_size                          20971520
            sort_area_size                          10485760
            optimizer_mode                          first_rows_10
            _hash_join_enabled                      false
            workarea_size_policy                    manual
            _smm_auto_cost_enabled                  false


            v$sql_optimizer_env視圖保存了優化器環境的參數,這些參數在優化每一個子游標時都是生效的。我選擇了那些沒有默認值,可以看到Oracle輸出了6個參數,其中4個是我們設置的,還有兩個:hash_area_size,_smm_auto_cost_enabled。hash_area_size如果沒有明確的設置過,它會等于兩倍的sort_area_size,當我們將workarea_size_policy設置為manual時,_smm_auto_cost_enabled參數也會自動的設置為false。11.2.0.4中有330個優化器參數(12.1.0.1中增加至415個,這也是為什么很難從二手信息中獲取到給定查詢到執行路徑原因之一),其中50個參數是非隱藏參數,其余的大部分都是隱藏參數,只有在改變他們的默認值時才會展示在查詢結果里。

            我在簡單的提下另一個formatting 選項 "advanced",奇怪的是他會給我們比"all"選項更多的信息。如果你僅僅想看執行查詢時內存里所有的信息,那么你可以調用display_cursor({sql_id},{child_number},’all’)(如果有許多子游標,可以將child_number值設為NULL,則會輸出所有的語句)。之后如果需要解決問題時我們還會繼續學習更多formatting選項,不過目前我們要暫時先放下,來看看一些可以"事后"從生產系統獲取執行計劃的選項。如果想獲得更多可用的選項,可以查看$ORACLE_HOME/rdbms/admin/dbmsxpln.sql.


            AWR and Statspack

            如果一個查詢屬于"資源密集型"的,那么他會出現在statspackAWR里,并且它的執行計劃以及一些統計會保存在資源庫里。OEM(Grid Control,Cloud Control)中有關于AWR的圖形化界面,$ORACLE_HOME/rdbms/admin中有statspackAWRSQL接口。statspack的腳本為sprepsql.sql,AWR的為awrsqrpt.sql。

            如果你對于用命令行生成AWR或者statspack很熟悉,那么除了需要SQL_ID(AWR)或者"old hashvalues"(statspack)以外,幾乎都是一樣的。報告會給你語句的全文,一些執行統計信息,在指定期間所有不同的執行計劃,還有不同計劃進入和離開library cache的時間表。這個計劃不會輸出綁定值,outline information,甚至謂詞信息,就是很基礎的執行計劃還有一些性能的統計。AWR在這方面比statspack好,因為它分別聚合了不同執行計劃的統計數據,所以更容易比較不同的執行計劃。

            另一個AWR的優點是你可以通過調用dbms_xplan.display_awr()獲取AWR里歷史的執行計劃。這個調用需要4個參數,SQL_ID,plan_hash_value,database idformat optino;最簡單的調用就是只給定SQL_ID的值,結果會輸出AWR中保存的所有這條SQL的執行計劃,你也可以選擇輸出你想要的執行計劃。就像調用dbms_xplan.display_cursor() 一樣,你也可以得到outline information和優化器使用的綁定值,下面是個例子:


            select * from table(
            dbms_xplan.display_awr("9yaqj3djhwxa2",null, null,"outline peeked_binds")
            );


            關于使用dbms_xplan查找和操作執行計劃的內容方面還有很多變化,也隨著Oracle的新版本變得更多樣化,最近的版本甚至可以輸出計劃中的不同點,其中一個最有用的小特性與如何填充AWR有關而不是從AWR中獲取報告。11g中,你可以將一個SQL_ID標記為"感興趣",當AWR快照進程在創建快照時會捕獲任何關于該語句可用的信息。因為除非該語句是"top N"的語句,否則它不會出現在AWR報告中,但是你可以使用awrsqrpt.sql或者dbms_xplan.display_awr()來獲得計劃。

            如果你想標記某條SQL,你可以調用dbms_workload_repository包:


            executedbms_workload_repository.add_colored_sql({sql_id})
            --
            -- 輸出當前被標記的語句
            --
            select * fromwrm$_colored_sql;
            --
            -- 解除語句標記
            --
            executedbms_workload_repository.remove_colored_sql({sql_id})


            這個過程的好處是(甚至可以針對一些效率很高的語句),當性能較好的應用突然變慢了,如果你標記了所有進程執行的語句,那么你可以很輕松的找到出現什么問題以及執行計劃是什么時候改變的。


            Conclusion

            盡管我們沒有列舉出所有生成或者獲取執行計劃的方法,但是我們學習了足夠的原理保證我們可以十分精確地獲得我們需要檢查的語句執行時的環境,也學習了如何獲得一些歷史的、不在內存里的執行計劃;還有一種保證我們所需要的語句總是會被AWR快照捕捉的方法。

            下一篇文章我們會來學習一些基本的解釋執行計劃的原理,從簡單的select語句開始,使用視圖來區分連接順序,訪問方式和連接方式。


            原文鏈接:

            https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-2-things-to-see/

            原文作者:Jonathan Lewis


            | 譯者簡介

            林錦森·沃趣科技數據庫技術專家

            沃趣科技數據庫工程師,多年從事Oracle數據庫,較豐富的故障處理、性能調優、數據遷移及備份恢復經驗






            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3