监视提交的SQL文

Question

想监视客户端提交的SQL文（比如1分钟），有什么方法。现在我从V$SQL里能看到一天的SQL文，但是相同SQL，只会显示最近的一次，无法了解执行了多少次。
从redo log中，又只能看到insert和update 的sql文，看不到select的sql文。

Answer 1

楼主可以先通过v$session找到sid和serial#,然后trace该会话
10g可以用dbms_monitor进行traceexec dbms_monitor.session_trace_enable(sid,serial#,true,false);然后到udump下获取文件,使用tkprof对文件转换,如果只看sql可以跟record=...
然后打开...文件就可以看到了
另外v$sqlarea这个视图的executions可以看到SQL执行了多少次

Answer 2

监视sql，是在应用层，还是数据库层，如果是数据库层就只有v$sql这个表了。应用层的话，java有个p6spy的可以监控sql

Answer 3

exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'服务名');这个可以直接跟踪该服务,记得关,要不日志老多了......

Answer 4

忘说关的方法了......exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'...');

Answer 5

jinxino_o:
1 使用v$sqlarea的executions，可以得到现在想要得数据。
2 dbms_monitor.session_trace_enable也试过了，能得到很多数据，但是在利用record的时候，能生成文件，但是总是不输出数据。
quiettown：
是oracle新手，审计的话，内容应该很多，需要慢慢学习。

Answer 6

我先用一个窗口建立一个连接sqlplus scott/tigerSQL> show user                                                                                 
USER is "SCOTT"然后在另一个窗口用sys进行查找,并traceSQL> select sid,serial#,username,osuser,machine from v$session;                                        SID    SERIAL# USERNAME   OSUSER         MACHINE
---------- ---------- ---------- -------------- -------------------------
       110       3099 ANYB       Administrator  WORKGROUP\LION-PC
       111       6006 ANYB       Administrator  WORKGROUP\LION-PC
       112       4606            oracle         db1
       113       6377 ANYB       wxb            WORKGROUP\WXB
       114       5293 ANYB       wxb            WORKGROUP\WXB
       115       4031 ANYB       wxb            WORKGROUP\WXB
       116      15712 ANYB       wxb            WORKGROUP\WXB
       117      13324 ANYB       wxb            WORKGROUP\WXB
       118       8095 ANYB       Administrator  WORKGROUP\LION-PC
       119       7206 ANYB       Administrator  WORKGROUP\LION-PC
       120       6595 SCOTT      oracle         db1
       122      19259 DBSNMP     oracle         db1
       124       5278 ANYB       Administrator  WORKGROUP\TXZ
       125      26428 ANYB       Administrator  WORKGROUP\LION-PC
       126       7756 ANYB       pc             HP\ZJG-1314F3B001D
       127      10400 ANYB       Administrator  WORKGROUP\TXZ
       128      22737 SYS        oracle         db1
       129       8106 ANYB       Administrator  WORKGROUP\TXZ
       130      21632 ANYB       Administrator  WORKGROUP\LION-PC
       131       7776 ANYB       Administrator  WORKGROUP\LION-PC
       132      16273 ANYB       wxb            WORKGROUP\WXB
       134      17217 ANYB       Administrator  WORKGROUP\TXZ
       135      18286 ANYB       Administrator  WORKGROUP\LION-PC
       137      22828 ANYB       wxb            WORKGROUP\WXB
       138      27816 ANYB       wxb            WORKGROUP\WXB
       139        725            oracle         db1
       140      35307 ANYB       wxb            WORKGROUP\WXB
       141      16303 ANYB       Administrator  WORKGROUP\TXZ
       142       5880 ANYB       Administrator  WORKGROUP\LION-PC
       143      45713 ANYB       Administrator  WORKGROUP\LION-PC
       145      16560 ANYB       wxb            WORKGROUP\WXB
       146        171 ANYB       Administrator  WORKGROUP\LION-PC
       149          1            oracle         db1
       153      12299 ANYB       Administrator  WORKGROUP\LION-PC
       154          3            oracle         db1
       156          3            oracle         db1
       158       4294 ANYB       Administrator  WORKGROUP\WWW-14111DF7145
       159          5            oracle         db1
       160          1            oracle         db1
       161          1            oracle         db1
       162          1            oracle         db1
       163          1            oracle         db1
       164          1            oracle         db1
       165          1            oracle         db1
       166          1            oracle         db1
       167          1            oracle         db1
       168          1            oracle         db1
       169          1            oracle         db1
       170          1            oracle         db149 rows selected.SQL> execute dbms_monitor.session_trace_enable(120,6595,true,false);                            PL/SQL procedure successfully completed.之后我用scott用户进行了一些简单的操作
然后我在udump下按时间排列文件,为了省事,我提前把以前的一些文件放都old下边了[oracle@db1 udump]$ ls -lt
total 540
-rw-r-----  1 oracle oinstall  44721 Sep  3 17:20 anyb_ora_21288.trc
-rw-r-----  1 oracle oinstall   6340 Sep  3 11:44 anyb_ora_32473.trc
-rw-r-----  1 oracle oinstall 198776 Sep  2 18:31 anyb_ora_16278.trc
-rw-r--r--  1 oracle oinstall  80801 Sep  2 17:39 read02.prf
-rw-r--r--  1 oracle oinstall  65427 Sep  2 17:39 read02.sql
-rw-r--r--  1 oracle oinstall  58983 Sep  2 17:38 read01.prf
-rw-r--r--  1 oracle oinstall  65427 Sep  2 17:38 read01.sql
drwxr-xr-x  2 oracle oinstall   4096 Sep  2 17:29 old找到需要的文件后,我使用tkprof生成可读文件[oracle@db1 udump]$ tkprof anyb_ora_21288.trc read03.prf record=read03.sqlTKPROF: Release 10.2.0.1.0 - Production on 星期四 9月 3 17:21:33 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.可以了,读一下read03.sql,看看scott用户都进行了什么SQL
[oracle@db1 udump]$ more read03.sql 
select * from tab ;
select * from emp ;
create table test02 as select * from emp ;
select * from test02 ;都看到了......
楼主看下和你的操作有出入吗?

Answer 7

先多谢jinxino_o这么详细介绍。今天公司老大要求，所有客户端程序在一天内的提交的SQL文纪录。
考虑了一下，用V$SQL应该不可行，因为它是保存在SQL CACHE中的所有SQL文纪录，估计没办法取得一天完整的数据。用审计的方法的话，因为是商用机器，如果采用上面说的方法，会不会在性能方面有影响。毕竟要写那么多log。不知道还有没有好的，合理的方法。

Answer 8

看了你的需求才发现用审计不太好:
1.审计信息不能直接转换成SQL文记录,需要通过信息去v$sql或其他视图查找相关SQL
当audit_trail=OS时,审计的信息类似下边:Fri Sep  4 10:05:05 2009
SESSIONID: "28634" ENTRYID: "1" STATEMENT: "1" USERID: "SCOTT" USERHOST: "db1" TERMINAL: "pts/6"
 ACTION: "100" RETURNCODE: "0" COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle" PR
IV$USED: 52.当audit_trail=BD时,审计的确对性能有一些影响,主要是会不停的往库里写信息,尤其是aud$表如果在system表空间下,影响最大......所以建议你用exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'服务名');的方式追踪一天该服务信息,最后进行统计