如何获取sql语句的执行情况

Question

近来我的oracle数据库（9201）运行慢，我想查看数据库执行了什么sql语句，什么时候执行，执行了多长时间，如何查询，谢谢。

Answer 1

SELECT V.LAST_CALL_ET, S.SQL_TEXT
  FROM V$SESSION V, V$SQL S
 WHERE V.LAST_CALL_ET >= 0
   AND V.STATUS = 'ACTIVE'
   AND V.USERNAME != 'SYS'
   AND S.ADDRESS = V.SQL_ADDRESS
   AND S.HASH_VALUE = V.SQL_HASH_VALUE
 ORDER BY LAST_CALL_ET DESC;

Answer 2

Session中的情况是变化的，那种处理可能捕捉不到你想要的SQL以及处理情况，
最好进行session trace 然后分析trace日志，或者使用SPACKSTAT进行较全面的性能检查。
找出TOP SQL，有的放矢的优化。

Answer 3

SQL trace 跟踪其他session的执行情况
步骤：1、查询session：sid和#serial
  select sid,serial#,username from v$session   where username is not null;
  2、将查询结果（有选择性的）输入到
  exec dbms_system.set_sql_trace_in_session(:sid,:serial,true)
  开始跟踪；  等待一段时间后结束跟踪
  exec dbms_system.set_sql_trace_in_session(:sid,:serial,false)
  然后，
3、在user_dump_dest目录下，获得trace日志信息。
或者
跟踪的信息在user_dump_dest  目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）
SELECT  p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora'  filename
FROM v$process  p,v$session  s,v$parameter  p1,v$parameter  p2
  WHERE  p1.name  = 'user_dump_dest' AND  p2.name  = 'db_name' AND p.addr  =s.paddr
AND  s.audsid  = USERENV('SESSIONID')可以直接浏览，但有很多东西是看不出来的
4、最后，可以通过Tkprof来解析跟踪文件，如：
Tkprof  原文件  目标文件  sys=n 可以直接在第三方工具,如Toad中使用tkprof分析日志。还有就是：
对其他用户session设置
通过DBMS_SYSTEM.SET_EV系统包来实现:
SQL> desc dbms_system
...
PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN...
其中的参数SI、SE来自v$session视图:查询获得需要跟踪的session信息:
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 XXX
执行跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,8,'User XXX');PL/SQL procedure successfully completed.结束跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,0,'User XXX');PL/SQL procedure successfully completed.
后续分析相同。

Answer 4

Statspack性能诊断
 在没有提供Statspack工具之前可以通过BStat/EStat进行同样的诊断
 脚本路径：$ORACLE_HOME/rdbms/admin/utlBstat.sql  .../utlEStat.sql
 
 为了顺利安装Statspack:
 1、首先设置：job_queue_processes >0 
 alter system set job_queue_processes =10; （此修改会在重起数据库时失效）
 或者当使用spfile时：alter system set job_queue_process =10 scope=both;（会保持有效）
 2、设置timed_statistics =true 
 alter system set timed_statistics = true;
 
 在结束后：alter system set timed_statistics =false;
 安装Statspack：
 脚本路径：oracle8.16之前：$ORACLE_HOME/rdbms/admin/Statscbps.sql
          oracle8.17之后：$ORACLE_HOME/rdbms/admin/spcreate.sql
 
如：
CREATE TABLESPACE PERFSTATDS
DATAFILE
  '/u01/back_dbdata/PERFSTAT01.dbf' SIZE 300 M 
AUTOEXTEND ON NEXT 32 M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
@$ORACLE_HOME/rdbms/admin/spcreate.sql;
--Windows下则执行如下：
@%ORACLE_HOME%/rdbms/admin/spcreate.sql;--输入PERFSTAT用户的密码:ipii314后，回车
--PERFSTAT用户默认表空间：PERFSTATDS，临时表空间：TEMP。--自动按时间间隔执行
--@$ORACLE_HOME/rdbms/admin/spauto.sql;
--或者使用自己定义的定时作业执行
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'begin  if (to_char(sysdate,''hh24'') >=''08'') and (to_char(sysdate,''hh24'') <''12'') then
    statspack.snap(i_snap_level=>7);
  elsif  (to_char(sysdate,''hh24'') >=''13'') and (to_char(sysdate,''hh24'') <''18'') then
    statspack.snap(i_snap_level=>7);
  end if;
end;',
    trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
  commit;
end;
/variable snap number;
begin   :snap := statspack.snap; 
end;
/
print snap;--第一次执行
connect perfstat/perfstat_password;
execute statspack.snap;--@$ORACLE_HOME/rdbms/admin/sptrunc.sql;select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; --@$ORACLE_HOME/rdbms/admin/spreport.sql;
输入:snap 快照编号，开始和结束。
--@%ORACLE_HOME%\rdbms\admin\spreport.sql;
--产生的report文件一般是放在当前系统路径下，如果是Windows系统，直接使用SQLPLUS连接登录则，在oracle\bin

Answer 5

但是对于插入的sql，比较难找

Answer 6

插入不怎么占用资源的，除非是批量的插入很多数据。
耗资源的基本上是Select