1、检查系统当前参数
You have to enable the timed_statistics parameter to true which will gather the information such as CPU time, Process times etc.
show parameter timed_statistics
必须保证该参数的值是true，如果不是，设置为true
alter system set timed_statistics=true;
2、会话开始执行sql
begin
while true loop
insert into wuxi values('hello',1);
insert into wuxi values('hello',2);
insert into wuxi values('hello',3);
insert into wuxi values('hello',1);
insert into wuxi values('hello',1);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
insert into wuxi values('ello',31);
end loop;
end;
/
<<<<<<<批量插入数据
确认该会话的信息
Select sid,serial#,machine,osuser,program from v$session where sid = &sid;
sid可以从用户角度获得
SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;
3、使用SYS用户跟踪会话
当然也可以使用普通用户来跟踪，但是要赋予相应的权限
<<<<<开始跟踪
EXEC DBMS_SYSTEM.set_sql_trace_in_session(968,28159,True);
<<<<<结束跟踪
EXEC DBMS_SYSTEM.set_sql_trace_in_session(968,28159,False);
4、跟踪生成的trace文件在user dump目录下
>>>>>>>>>根据spid找dump 文件，查询该会话的spid
select s.sid,s.serial#,s.username,p.spid
from v$session s,v$process p
where s.paddr=p.ADDR
and sid = &sid
/
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- -----------------
background_core_dump string partial
background_dump_dest string
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /oravl01/oracle/diag/rdbms/zldb4/zldb4/trace
