SQL追踪和事件追踪

 追踪enqueue lock,DDL语句锁
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10704 trace name context forever, level 12';
SQL> create table test121(a varchar2(10));
SQL> alter session set events '10704 trace name context off';

比如10046的trace，我们可以跟踪等待的事件和时间；

以下是10046的生成过程：

SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever, level 12';
SQL>
SQL> alter session set events '10046 trace name context off';

使用10053执行SQL计划的追踪。
SQL> alter session set events '10053 trace name context forever,level 1'
SQL> explain plan for your_select_query;
SQL> alter session set events '10053 trace name context off';

10049的trace，我们可以看到在语句解析的过程中获取到的锁的信息
以下操作会帮您获取你想要得到的lock /pin/enq 等信息：

SQL> alter session set timed_statistics = true;

SQL> alter session set statistics_level=all;

SQL> alter session set max_dump_file_size = unlimited;

SQL> alter session set events '10049 trace name context forever, level 48 ';

SQL> alter table test add name2 varchar2(10); <<<===========DDL

SQL> alter session set events '10049 trace name context off';

SQL_TRACE追踪session.
SQL> select sid,serial# from v$session where SID=267;
SQL> execute dbms_system.set_sql_trace_in_session(267,996,true);
SQL> execute dbms_system.set_sql_trace_in_session(267,996,false);

使用10046事件追踪session.
SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true); 
SQL> exec dbms_monitor.session_trace_disable(267,996);

tkprof/tvdxstat分析SQL

通过dbsm_stats包清除和重新收集表的统计信息：
SQL> exec dbms_stats.delete_table_stats(user,'TEST1');
SQL> exec dbms_stats.gather_table_stats(user,'TEEST1');

 

查看表结构，是否存在有效的索引。
select index_name,table_name,column_name from user_ind_columns

 where table_name=upper('test');

 

使用oradebug跟踪活动的会话：
oradebug setospid 9077
oradebug event 10046 trace name context forever,level 4
oradebug tracefile_name
oradebug event 10046 trace name context off;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23757700/viewspace-751896/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23757700/viewspace-751896/