一、前言

前段時間應需求，寫存儲過程，以滿足避免在大數據量的原始表中進行直接的查詢工作。從而生成一張中間表，用于以后各個維度的報表統計

　　從而提高每張報表的查詢效率。久而久之，隨著存儲過程越來越多，每天的任務耗時也越來越大，從而不得不考慮對存儲過程進行優化。

二、"10046"事件

Oracle的10046事件，它可以跟蹤應用程序所執行的SQL語句，從而得到每條SQL的解析次數，執行次數，CPU使用時間，每條SQL中每個部位的耗時等。這樣，我們

　　就可以根據這些信息來分析、定位數據庫的性能問題。

　　　　10046event是oracle用于系統性能分析時的一個最重要的事件，當激活這個事件后，將通知oracle kernel追蹤會話的相關即時信息，并寫入相應的trace文件中。這些　

　　信息包括SQL的解析，綁定變量的使用情況，會話中發生的等待時間以及會羅列出主要耗時的部分等。

　　　　10046event有不同的級別（level），分別追蹤記錄不同程度的信息。需要注意的是，級別是向下兼容的 即高一級別生成的trace所包含的信息包含低一級別的所有信息，

　　追蹤級別大致有：

　　　　　level 1：跟蹤sql語句，包括解析、執行、提取、提交和回滾等。

　　　　　level 4：包括變量的詳細信息。

　　　　　level 8：包括等待事件。

　　　　 ??level 12：包括綁定變量與等待事件。

　　　　 ??其中，level 1相當于打開了sql_trace。

　　　　　(Tips) 向下兼容。

三、10046event 追蹤最小化例子

命令行/plsql 鍵入如下：

alter session set tracefile_identifier='10046';-----------------------設置追蹤標識符

alter session set events '10046 trace name context forever, level 12';----------------開啟追蹤，并設置追蹤級別

　　　　select * from ALL_OBJECTS; -----------------所要追蹤的SQL語句

alter session set events '10046 trace name context off';------------關閉追蹤

　　　　執行完后，即所有會話都結束后，oracle將會生成一個orcl_ora_XXXX_10046.trc 的文件，根據個人目錄不同，會生成在不同的

　　目錄當中，我們可以用locate命令來進行查詢

　　　　得到這個文件后，我們再用tkprof這個命令，對該文件進行格式化，便于我們的閱讀。

　　　　如： tkprof trace\orcl_ora_xxxx_10046.trc ?10046.txt sys=no sort=prsela,exeela,fchela

四、解析trace文件

　　　　我們打開10046.txt，對于初學者來說，并不能好好的去分析包含的信息，因為其中包含了許多“名詞”，下面將會對此進行介紹

　　　　1）摘錄第一部分，SQL語句的執行情況總覽
　　　　　　select *
　　　　　　from
　　　　　　ALL_OBJECTS


　　　　call? ???count? ?? ? cpu? ? elapsed? ?? ? disk? ?? ?query? ? current? ?? ???rows
　　　　------- ------??-------- ---------- ---------- ---------- ----------??----------
　　　　Parse? ?? ???1? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0
　　　　Execute? ?? ?1? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0
　　　　Fetch? ?? ???2? ???10.94? ?? ?10.68? ???222186? ???222957? ?? ?? ? 0? ?? ?? ???1
　　　　------- ------??-------- ---------- ---------- ---------- ----------??----------
　　　　total? ?? ???4? ???10.94? ?? ?10.68? ???222186? ???222957? ?? ?? ? 0? ?? ?? ???1

　　　　關于統計表格的標題信息中count、cpu、elapsed、disk、query、current和rows的說明在該trace文件的最前端有一個簡要的說明，這里再分別贅述一下。
　　　　count? ?：查詢在此階段執行的次數；
　　　　cpu? ???：該查詢在此階段的CPU時間量，以毫秒為單位；
　　　　elapsed ：花費在此階段上的掛鐘時間，該值比cpu值大的時候，表明存在等待事件；
　　　　disk? ? ：執行物理I/O次數；
　　　　query? ?：在意一致性檢索方式獲得塊時，執行邏輯I/O次數；
　　　　current ：邏輯I/O次數；
　　　　rows? ? ：此階段，被處理或受影響的行數。

　　　　關于第一列的贅述：
　　　　Parse? ?：軟編譯和硬編譯次數；
　　　　Execute ：在open和execute語句中完成的內容;
　　　　Fetch? ?：select中會有數據顯示，在update語句中不會有數據顯示。

　　　　2）摘錄運行環境信息
　　　　Misses in library cache during parse: 0
　　　　Optimizer mode: ALL_ROWS
　　　　Parsing user id: 51

　　　　第一行的“0”表示查詢使用的是軟解析（soft parse）。
　　　　優化模式是：ALL_ROWS
　　　　使用最后一行的用戶ID可以獲得執行時的會話信息。獲得用戶信息可以通過下面的SQL語句完成。
　　　　sys@ora10g> select * from all_users where user_id = 51;

　　　　USERNAME? ?? ?? ?? ?? ?? ?? ?? ???USER_ID CREATED
　　　　------------------------------ ---------- -------------------
　　　　SEC? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?51 2015-02-15 13:04:03

　　　　3）摘錄執行計劃信息
　　　　Rows? ???Row Source Operation
　　　　-------??---------------------------------------------------
? ??　　　　 ?1??SORT AGGREGATE (cr=222957 pr=222186 pw=0 time=10686023 us)
　100000000? ?INDEX FAST FULL SCAN PK_T (cr=222957 pr=222186 pw=0 time=100000562 us)(object id 45619)

　　　　有趣發現：通過第二行可以得到這個t表的數據量，這里顯示結果是1億。
　　　　“解剖”上面出現的幾個重要參數：
　　　　cr=222957? ?? ?? ? -- 一致性讀取
　　　　pr=222186? ?? ?? ? -- 物理讀取
　　　　pw=0? ?? ?? ?? ?? ?-- 物理寫
　　　　time=100000562 us??-- 占用時間，單位：微妙 百萬分之一秒

　　　　4）摘錄等待事件
　　　　Elapsed times include waiting on following events:
　　　　??Event waited on? ?? ?? ?? ?? ?? ?? ?? ?? ???Times? ?Max. Wait??Total Waited
　　　　??----------------------------------------? ?Waited??----------??------------
　　　　??SQL*Net message to client? ?? ?? ?? ?? ?? ?? ???3? ?? ???0.00? ?? ?? ? 0.00
　　　　??SQL*Net message from client? ?? ?? ?? ?? ?? ?? ?3? ?? ???0.00? ?? ?? ? 0.00
　　　　??db file scattered read? ?? ?? ?? ?? ?? ?? ? 14249? ?? ???0.00? ?? ?? ? 1.10
　　　　??db file sequential read? ?? ?? ?? ?? ?? ?? ?? ?59? ?? ???0.00? ?? ?? ? 0.00

　　　　通過這段等待事件的描述，可以清楚的得到在執行SQL語句的過程中都出現了哪些引人注目的等待事件。比如上面顯示出的“db file scattered read”和“db file 　　　　sequential read”信息，如果此類信息在生產環境中大量出現，就需要重點深入分析和研究了。

?

五、總結　

　　　　對于10046事件，本身是非常消耗資源，對于存儲過程，如果對此進行設置跟蹤，會增加該存過的耗時，因此在使用過程中應避免一直啟用追蹤。但對于存儲過程的調優，其包含的各種信息是非常有助于對整個存過進行有效的分析，對于10046擴展追蹤,非常適合于對那些把很多業務邏輯寫入到存儲過程中的軟件調優

?

?

?

存儲過程調優之“10046”事件