如何获取sql语句的执行情况
近来我的oracle数据库(9201)运行慢,我想查看数据库执行了什么sql语句,什么时候执行,执行了多长时间,如何查询,谢谢。
------解决方案--------------------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.
后续分析相同。
------解决方案--------------------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,'