【实验】【SQL_TRACE】使用sql_trace功能获得show parameter的sql语句

2020-02-10 18:23:39

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 <none>
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 --