[20111220]Capturing 10053 trace files.txt
oracle 要抓取10053事件信息,执行的sql语句一定要硬分析:
有时候不小心忘记了这个要求,导致没有抓取执行计划。
在11G的DBMS_SQLDIAG包包含一个DUMP_TRACE过程,而且这个过程自动触发一个硬分析,生成跟踪文件。
例子:
1.执行sql获取sql_id
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS');
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2937609675
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 14 |
--------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
2.获取sql_id='g59vz2u4cu404'
SQL> begin
DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'g59vz2u4cu404', p_child_number=>0, p_component=>'Compiler', p_file_id=>'t10053');
end;
/
3.查看文件:
$ ls -l test_ora_5327_t10053.trc
-rw-r----- 1 oracle oinstall 68474 Dec 20 15:32 test_ora_5327_t10053.trc
看见前面的sql如下:
/* SQL Analyze(145,0) */ select count(*) from emp
oracle 要抓取10053事件信息,执行的sql语句一定要硬分析:
有时候不小心忘记了这个要求,导致没有抓取执行计划。
在11G的DBMS_SQLDIAG包包含一个DUMP_TRACE过程,而且这个过程自动触发一个硬分析,生成跟踪文件。
例子:
1.执行sql获取sql_id
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS');
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2937609675
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 14 |
--------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
2.获取sql_id='g59vz2u4cu404'
SQL> begin
DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'g59vz2u4cu404', p_child_number=>0, p_component=>'Compiler', p_file_id=>'t10053');
end;
/
3.查看文件:
$ ls -l test_ora_5327_t10053.trc
-rw-r----- 1 oracle oinstall 68474 Dec 20 15:32 test_ora_5327_t10053.trc
看见前面的sql如下:
/* SQL Analyze(145,0) */ select count(*) from emp
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-713624/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-713624/