一、前言

前段時(shí)間應(yīng)需求，寫存儲過程，以滿足避免在大數(shù)據(jù)量的原始表中進(jìn)行直接的查詢工作。從而生成一張中間表，用于以后各個(gè)維度的報(bào)表統(tǒng)計(jì)

　　從而提高每張報(bào)表的查詢效率。久而久之，隨著存儲過程越來越多，每天的任務(wù)耗時(shí)也越來越大，從而不得不考慮對存儲過程進(jìn)行優(yōu)化。

二、"10046"事件

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

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

　　　　10046event是oracle用于系統(tǒng)性能分析時(shí)的一個(gè)最重要的事件，當(dāng)激活這個(gè)事件后，將通知oracle kernel追蹤會話的相關(guān)即時(shí)信息，并寫入相應(yīng)的trace文件中。這些　

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

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

　　追蹤級別大致有：

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

　　　　　level 4：包括變量的詳細(xì)信息。

　　　　　level 8：包括等待事件。

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

　　　　 ??其中，level 1相當(dāng)于打開了sql_trace。

　　　　　(Tips) 向下兼容。

三、10046event 追蹤最小化例子

命令行/plsql 鍵入如下：

alter session set tracefile_identifier='10046';-----------------------設(shè)置追蹤標(biāo)識符

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

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

alter session set events '10046 trace name context off';------------關(guān)閉追蹤

　　　　執(zhí)行完后，即所有會話都結(jié)束后，oracle將會生成一個(gè)orcl_ora_XXXX_10046.trc 的文件，根據(jù)個(gè)人目錄不同，會生成在不同的

　　目錄當(dāng)中，我們可以用locate命令來進(jìn)行查詢

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

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

四、解析trace文件

　　　　我們打開10046.txt，對于初學(xué)者來說，并不能好好的去分析包含的信息，因?yàn)槠渲邪嗽S多“名詞”，下面將會對此進(jìn)行介紹

　　　　1）摘錄第一部分，SQL語句的執(zhí)行情況總覽
　　　　　　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

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

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

　　　　2）摘錄運(yùn)行環(huán)境信息
　　　　Misses in library cache during parse: 0
　　　　Optimizer mode: ALL_ROWS
　　　　Parsing user id: 51

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

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

　　　　3）摘錄執(zhí)行計(jì)劃信息
　　　　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)

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

　　　　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

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

?

五、總結(jié)　

　　　　對于10046事件，本身是非常消耗資源，對于存儲過程，如果對此進(jìn)行設(shè)置跟蹤，會增加該存過的耗時(shí)，因此在使用過程中應(yīng)避免一直啟用追蹤。但對于存儲過程的調(diào)優(yōu)，其包含的各種信息是非常有助于對整個(gè)存過進(jìn)行有效的分析，對于10046擴(kuò)展追蹤,非常適合于對那些把很多業(yè)務(wù)邏輯寫入到存儲過程中的軟件調(diào)優(yōu)

?

?

?

存儲過程調(diào)優(yōu)之“10046”事件