SQL_TRACE的強大功能的又一個體現是得到session中操作的後臺真實的SQL執行語句，下面透過這個實驗給大家演示一下透過SQL_TRACE得到“show parameter”後臺SQL語句的過程。

BTW：另外一種獲得“show parameter”的SQL的方法（透過審計技術）在我的這個小文兒中也有介紹：《【實驗】【審計】【FGA】使用Oracle的審計功能監控資料庫中的可疑操作》http://space.itpub.net/519536/viewspace-613323，如果您有興趣也可以參考一下。

1.啟用session級別的sql trace
sys@ora10g> alter session set sql_trace=true;

Session altered.

2.執行show parameter語句
sys@ora10g> show parameter pga

NAME                                     TYPE                 VALUE
---------------------------------------- -------------------- ----------
pga_aggregate_target                     big integer          16M

3.停止sql trace功能
sys@ora10g> alter session set sql_trace=false;

Session altered.

4.得到生成的trace檔名
sys@ora10g> @trc

TRACE_FILE_NAME
-------------------------------------------------------------------------------------
/oracle/u01/app/oracle/admin/ora10g/udump/ora10g_ora_3656.trc

5.檢視trace檔案，紅色的程式碼就是我們找到的“真實的SQL語句”
sys@ora10g> !cat /oracle/u01/app/oracle/admin/ora10g/udump/ora10g_ora_3656.trc
/oracle/u01/app/oracle/admin/ora10g/udump/ora10g_ora_3656.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      linux5
Release:        2.6.18-53.el5xen
Version:        #1 SMP Wed Oct 10 17:06:12 EDT 2007
Machine:        i686
Instance name: ora10g
Redo thread mounted by this instance: 0
Oracle process number: 0
Unix process pid: 3656, image: oracle@linux5

Dynamic strand is set to TRUE
Running with 1 shared and 61 private strand(s). Zero-copy redo is FALSE
/oracle/u01/app/oracle/admin/ora10g/udump/ora10g_ora_3656.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      linux5
Release:        2.6.18-53.el5xen
Version:        #1 SMP Wed Oct 10 17:06:12 EDT 2007
Machine:        i686
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 3656, image: oracle@linux5 (TNS V1-V3)

*** 2009-03-12 03:07:46.389
*** ACTION NAME:() 2009-03-12 03:07:46.355
*** MODULE NAME:(sqlplus@linux5 (TNS V1-V3)) 2009-03-12 03:07:46.355
*** SERVICE NAME:(SYS$USERS) 2009-03-12 03:07:46.355
*** SESSION ID:(533.30) 2009-03-12 03:07:46.355
=====================
PARSING IN CURSOR #5 len=52 dep=0 uid=0 ct=47 lid=0 tim=1207811002300118 hv=1029988163 ad='24f19df8'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #5:c=0,e=223,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1207811002300082
EXEC #5:c=0,e=394,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1207811002370754
*** 2009-03-12 03:08:07.727
=====================
PARSING IN CURSOR #3 len=33 dep=0 uid=0 ct=42 lid=0 tim=1207811023171823 hv=525901419 ad='0'
alter session set sql_trace=false
END OF STMT
PARSE #3:c=0,e=573,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1207811023171791
EXEC #3:c=0,e=185,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1207811023172842
=====================
PARSING IN CURSOR #5 len=52 dep=0 uid=0 ct=47 lid=0 tim=1207811026352390 hv=1029988163 ad='24f19df8'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #5:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1207811026352359
EXEC #5:c=0,e=331,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1207811026353258
*** 2009-03-12 03:08:26.504
=====================
PARSING IN CURSOR #3 len=280 dep=0 uid=0 ct=3 lid=0 tim=1207811041508367 hv=3529189998 ad='299a991c'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%pga%') ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
PARSE #3:c=28002,e=92892,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1207811041508328
EXEC #3:c=0,e=10648,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1207811041519841
FETCH #3:c=8000,e=9745,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1207811041530033
FETCH #3:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1207811041531729
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=0 pr=0 pw=0 time=9893 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 bj=0 p='COUNT (cr=0 pr=0 pw=0 time=9702 us)'
STAT #3 id=3 cnt=1 pid=2 pos=1 bj=0 p='HASH JOIN (cr=0 pr=0 pw=0 time=9593 us)'
STAT #3 id=4 cnt=4 pid=3 pos=1 bj=0 p='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=340 us)'
STAT #3 id=5 cnt=1495 pid=3 pos=2 bj=0 p='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=37414 us)'
PARSE #4:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1207811046017089
EXEC #4:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1207811046017307

6.格式化後獲得到的sql語句如下
SELECT   NAME name_col_plus_show_param,
         DECODE (TYPE,
                 1, 'boolean',
                 2, 'string',
                 3, 'integer',
                 4, 'file',
                 5, 'number',
                 6, 'big integer',
                 'unknown'
                ) TYPE,
         display_value value_col_plus_show_param
    FROM v$parameter
   WHERE UPPER (NAME) LIKE UPPER ('%pga%')
ORDER BY name_col_plus_show_param, ROWNUM
/

7.小結
使用強大的sql trace不僅可以得到我們需要的SQL最佳化資訊，而且還可以得到很多有趣的內容。

-- The End --

【實驗】【SQL_TRACE】使用sql_trace功能獲得show parameter的sql語句

相關文章