♣
题目 部分
在Oracle中,如何dump数据库内部结构?
♣
答案部分
一些常用的dump事件,可以参考下表:
事件ID |
事件 |
说明 |
例子 |
10046 |
enable SQL statement timing |
level 0:禁用SQL_TRACE,等价于SQL_TRACE=FALSE level 1:启用标准的SQL_TRACE功能跟踪SQL语句,包括解析、执行、提取、提交、回滚、响应时间、服务时间、处理的行数,物理读和写的数目、执行计划以及其它一些额外信息,等价于SQL_TRACE=TRUE level 4:Level 1 + 包括绑定变量(Bind Values)的详细信息 level 8:Level 1 + 包括等待事件 level 12:包括绑定变量与等待事件,包含Level 1 + Level 4 + Level 8 |
ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
|
10053 |
CBO Enable optimizer trace |
在分析SQL语句时,Dump出优化器所做的选择,级别level 1最详细 |
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; |
10704 |
Print out information about what enqueues are being obtained |
当启用的时候,可以查看锁的调用信息 |
ALTER SESSION SET EVENTS '10704 trace name context forever,level 10'; |
10710 |
Event 10710 - Trace Bitmap Index Access |
跟踪位图索引的访问情况 |
ALTER SESSION SET EVENTS '10710 trace name context forever, level 1'; |
10711 |
Event 10711 - Trace Bitmap Index Merge Operation |
跟踪位图索引合并操作 |
ALTER SESSION SET EVENTS '10711 trace name context forever, level 1'; |
10712 |
Event 10712 - Trace Bitmap Index OR Operation |
跟踪位图索引或操作情况 |
ALTER SESSION SET EVENTS '10712 trace name context forever, level 1'; |
10713 |
Event 10713 - Trace Bitmap Index AND Operation |
跟踪位图索引与操作 |
ALTER SESSION SET EVENTS '10713 trace name context forever, level 1'; |
10714 |
Event 10714 - Trace Bitmap Index MINUS Operation |
跟踪位图索引MINUS操作 |
ALTER SESSION SET EVENTS '10714 trace name context forever, level 1'; |
10715 |
Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation |
跟踪位图索引转换ROWID操作 |
ALTER SESSION SET EVENTS '10715 trace name context forever, level 1'; |
10716 |
Event 10716 - Trace Bitmap Index Compress/Decompress |
跟踪位图索引压缩和解压缩情况 |
ALTER SESSION SET EVENTS '10716 trace name context forever, level 1'; |
10719 |
Event 10719 - Trace Bitmap Index DML |
跟踪位图索引列的DML操作(引起位图索引改变的DML操作) |
ALTER SESSION SET EVENTS '10719 trace name context forever, level 1'; |
10730 |
Event 10730 - Trace Fine Grained Access Predicates |
跟踪细粒度审计的断语 |
ALTER SESSION SET EVENTS '10730 trace name context forever, level 1'; |
10731 |
Event 10731 - Trace CURSOR Statements |
跟踪CURSOR的语句情况,跟踪游标声明,LEVEL 1表示跟踪父游标和子游标;LEVEL 2只跟踪子游标 |
ALTER SESSION SET EVENTS '10731 trace name context forever, level level'; |
10928 |
Event 10928 - Trace PL/SQL Execution |
跟踪PL/SQL执行情况 |
ALTER SESSION SET EVENTS '10928 trace name context forever, level 1'; |
10938 |
Event 10938 - Dump PL/SQL Execution Statistics |
转储PL/SQL执行统计信息,使用前需要执行脚本文件:$ORACLE_HOME/rdbms/admin/tracetab.sql |
ALTER SESSION SET EVENTS '10938 trace name context forever, level 1'; |