【DB筆試面試686】在Oracle中，SQL_TRACE是什麼？診斷事件是什麼？
- 2019 年 11 月 14 日
- 筆記
♣
題目部分
在Oracle中，SQL_TRACE是什麼？診斷事件是什麼？
♣
答案部分
（一）SQL_TRACE
SQL_TRACE是Oracle提供的用於進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的資料庫問題診斷和解決中，SQL_TRACE是非常常用的方法。可以設置SQL_TRACE為TRUE，但是一般不推薦在全局指定為TRUE，只建議在會話級別指定。若在全局設定則對性能會造成較大影響。
（二）診斷事件
Oracle為資料庫提供了多種的診斷工具，診斷事件（Event）是其中一種非常實用的方法，它能使DBA可以方便地轉儲資料庫各種結構及跟蹤特定事件的發生過程。
通過如下的腳本可以獲取所有的診斷事件列表：
1DECLARE 2 ERR_MSG VARCHAR2(32767); 3BEGIN 4 DBMS_OUTPUT.ENABLE(''); 5 FOR ERR_NUM IN 10000 .. 10999 LOOP 6 ERR_MSG := SQLERRM(-ERR_NUM); 7 IF ERR_MSG NOT LIKE '%Message ' || ERR_NUM || ' not found%' THEN 8 DBMS_OUTPUT.PUT_LINE(ERR_MSG); 9 END IF; 10 END LOOP; 11END; 12/
在Linux系統下診斷事件的消息文件放在目錄：$ORACLE_HOME/rdbms/mesg/oraus.msg。oraus.msg文件屬於文本文件，可以直接打開查看。
診斷事件可以像普通的ORA錯誤一樣採用oerr命令來查詢，如下所示：
1[oracle@orclalhr ~]$ oerr ora 10046 210046, 00000, "enable SQL statement timing" 3// *Cause: 4// *Action: 5[oracle@orclalhr ~]$ oerr ora 10053 610053, 00000, "CBO Enable optimizer trace" 7// *Cause: 8// *Action: 9[oracle@orclalhr ~]$ oerr ora 10704 1010704, 00000, "Print out information about what enqueues are being obtained" 11// *Cause: When enabled, prints out arguments to calls to ksqcmi and 12// ksqlrl and the return values. 13// *Action: Level indicates details: 14// Level: 1-4: print out basic info for ksqlrl, ksqcmi 15// 5-9: also print out stuff in callbacks: ksqlac, ksqlop 16// 10+: also print out time for each line
設置診斷事件有兩種方法，一種是在pfile參數文件中設置事件，這樣資料庫在OPEN後，將影響到所有的會話。設置格式如下：
1event="eventnumber trace name eventname [forever,] [level levelnumber] : ......."
通過冒號（:）符號，可以連續設置多個事件，也可以通過連續使用event來設置多個事件。如：
1event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"
或者分開寫，如：
1event="10248 trace name context forever, level 10" 2event="10249 trace name context forever, level 10"
另一種方法是在會話過程中使用ALTER SESSION SET EVENTS命令，只對當前會話有影響。設置格式如下：
1ALTER SESSION|SYSTEM SET EVENTS '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'
通過英文冒號（:）符號，可以連續設置多個事件，也可以通過連續使用ALTER SESSION SET EVENTS來設置多個事件，如：
1ALTER SYSTEM SET 2 EVENTS='10325 trace name context forever, level 10','10015 trace name context forever, level 1' 3 COMMENT='Debug tracing of control and rollback';
或：
1ALTER SYSTEM SET EVENTS='10325 trace name context forever, level 10:10015 trace name context forever, level 1';
格式說明：
l eventnumber指觸發dump的事件號，事件號可以是Oracle錯誤號（出現相應錯誤時跟蹤指定的事件）或Oralce內部事件號，內部事件號在10000到10999之間，不能與immediate關鍵字同用。
l immediate關鍵字表示命令發出後，立即將指定的結構dump到跟蹤文件中，這個關鍵字只用在ALTER SESSION語句中，並且不能與eventnumber、forever關鍵字同用。
l trace name是關鍵詞，trace name位於第二、三項，除它們外的其它限定詞是供Oracle內部開發組用的。
l eventname指事件名稱，即要進行dump的實際結構名。若eventname為context，則指根據內部事件號進行跟蹤。
l forever關鍵字表示事件在實例或會話的周期內保持有效狀態，不能與immediate同用。
l level為事件級別關鍵字。但在dump錯誤棧（errorstack）時不存在級別。level通常位於1-10之間（10046有時用到12），10意味著轉儲事件所有的資訊。例如當轉儲控制文件時，level 1表示轉儲控制文件頭，而level 10表明轉儲控制文件全部內容。
l levelnumber表示事件級別號，一般從1到10，1表示只dump結構頭部資訊，10表示dump結構的所有資訊。
l 轉儲所生成的trace文件在user_dump_dest初始化參數指定的位置。
l 移除所有的診斷事件（Event）：
1ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;
如果設置了診斷事件（Event），那麼如何知道在系統中設置了哪些診斷事件（Event）呢？如果事件是在pfile文件中設置的，那麼可以用「SHOW PARAMETER EVENT」來查看。如果是通過「ALTER SYSTEM|SESSION」來設置的，那麼可以使用如下的SQL來查看：
1SET SERVEROUTPUT ON SIZE 1000000 2DECLARE 3 EVENT_LEVEL NUMBER; 4BEGIN 5 FOR I IN 10000 .. 99999 LOOP 6 SYS.DBMS_SYSTEM.READ_EV(I, EVENT_LEVEL); 7 IF (EVENT_LEVEL > 0) THEN 8 DBMS_OUTPUT.PUT_LINE('Event ' || TO_CHAR(I) || ' set at level ' ||TO_CHAR(EVENT_LEVEL)); 9 END IF; 10 END LOOP; 11END; 12/ 13
但是，10046和10053事件不能通過這種方式查詢，只能通過oradebug來查詢，如下：
1SYS@orclasm > oradebug setmypid 2SYS@orclasm > oradebug eventdump system 3SYS@orclasm > oradebug eventdump session
設置診斷事件需要注意的是，即可以使用「ALTER SYSTEM EVENTS」，也可以使用「ALTER SYSTEM EVENT」，它們的區別在如下2點：
① EVENTS可以動態修改，可以使用「ALTER SESSION」或「ALTER SYSTEM」設置，隻影響記憶體不影響參數文件
② EVENT不能動態修改，只能使用「ALTER SYSTEM」或在參數文件里設置，必須重啟庫方可生效
另外，「ALTER SYSTEM」會記錄到告警日誌中，「ALTER SESSION」不會記錄在告警日誌中。
本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗
