eagle’s home

简介：

   Automatic Undo Management下禁止SMON Offline空闲的rollback segment

使用方法:

   在init.ora中设置event:

    event=”10511 trace name context forever, level 2″

    然后restart database

相关文档:

    metalink Note:301432.1

Eagle Fan oracle events

简介：

将索引结构以树状打印出来

使用方法：

alter session set events ‘immediate trace name treedump level obj#’;

－－ obj# is the index’s object_id

Trace结果范例:

—– begin tree dump
branch: 0x8001ea 8389098 (0: nrow: 5, level: 2)
   branch: 0x300049a 50332826 (-1: nrow: 5, level: 1)
      leaf: 0x8001eb 8389099 (-1: nrow: 5 rrow: 5)
      leaf: 0x8001f7 8389111 (0: nrow: 1 rrow: 1)
      leaf: 0x8001f6 8389110 (1: nrow: 1 rrow: 1)
      leaf: 0x8001f4 8389108 (2: nrow: 1 rrow: 1)
      leaf: 0x8001f3 8389107 (3: nrow: 1 rrow: 1)
   branch: 0x8001f5 8389109 (0: nrow: 4, level: 1)
      leaf: 0x8001f1 8389105 (-1: nrow: 3 rrow: 3)
      leaf: 0x30004a0 50332832 (0: nrow: 1 rrow: 1)
      leaf: 0x300049e 50332830 (1: nrow: 1 rrow: 1)
      leaf: 0x300049d 50332829 (2: nrow: 1 rrow: 1)
   branch: 0x8001f2 8389106 (1: nrow: 2, level: 1)
      leaf: 0x300049c 50332828 (-1: nrow: 1 rrow: 1)
      leaf: 0×3000499 50332825 (0: nrow: 1 rrow: 1)
   branch: 0x300049f 50332831 (2: nrow: 2, level: 1)
      leaf: 0x8001f0 8389104 (-1: nrow: 1 rrow: 1)
      leaf: 0x8001ef 8389103 (0: nrow: 1 rrow: 1)
   branch: 0x300049b 50332827 (3: nrow: 3, level: 1)
      leaf: 0x8001ee 8389102 (-1: nrow: 3 rrow: 3)
      leaf: 0x8001ed 8389101 (0: nrow: 1 rrow: 1)
      leaf: 0x8001ec 8389100 (1: nrow: 1 rrow: 1)
—– end tree dump

Eagle Fan oracle events

简介：

10132 trace event 生成实际执行的execution plan,格式简单明了，包括PQ和Partition信息

使用方法：

alter session set events ’10132 trace name context forever,level 1′;

alter session set events ’10132 trace name context off’;

Trace结果范例:

*** 2007-01-15 19:13:48.890
*** SESSION ID:(18.2014) 2007-01-15 19:13:48.888
Current SQL statement for this session:
select * from t
Plan Table
——–
————————————————————————————————————————-
| Operation                      | Name               | Rows  | Bytes | Cost  |  TQ  |IN-OUT| PQ Distrib |Pstart| Pstop |
————————————————————————————————————————-
| SELECT STATEMENT               |                    |     0 |     0 |     4 |      |      |           |       |       |
|  TABLE ACCESS FULL             | T                  |   10K |   68K |     4 |      |      |           |       |       |
————————————————————————————————————————-
*** 2007-01-15 19:14:08.770
Current SQL statement for this session:
select * from t where x=:”SYS_B_0″
Plan Table
——–
————————————————————————————————————————-
| Operation                      | Name               | Rows  | Bytes | Cost  |  TQ  |IN-OUT| PQ Distrib |Pstart| Pstop |
————————————————————————————————————————-
| SELECT STATEMENT               |                    |     0 |     0 |     2 |      |      |           |       |       |
|  TABLE ACCESS BY INDEX ROWID   | T                  |     1 |     7 |     2 |      |      |           |       |       |
|   INDEX RANGE SCAN             | T_IDX              |     1 |     0 |     1 |      |      |           |       |       |
————————————————————————————————————————-

Eagle Fan oracle events

在一次troubleshooting中使用到了这个trace event,感觉还是蛮好用的，和大家分享一下。

10704 event用于trace enqueue的获取过程。
$oerr ora 10704
10704, 00000, “Print out information about what enqueues are being obtained”
// *Cause: When enabled, prints out arguments to calls to ksqcmi and
// ksqlrl and the return values.
// *Action: Level indicates details:
// Level: 1-4: print out basic info for ksqlrl, ksqcmi
// 5-9: also print out stuff in callbacks: ksqlac, ksqlop
// 10+: also print out time for each line

SQL> alter session set events ’10704 trace name context forever, level 12′;

Session altered.

SQL> alter index test_idx rebuild online;

Index altered.

SQL> alter session set events ’10704 trace name context off’;

Session altered.

到udump下察看trace文件如下:

Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
ORACLE_HOME = /export/home/oracle/products/9205
System name: SunOS
Node name: qadb08
Release: 5.8
Version: Generic_108528-18
Machine: sun4u
Instance name: CSHAN
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 5613, image: oracle@qadb08 (TNS V1-V3)

*** SESSION ID:(17.5439) 2006-09-11 19:39:43.866
*** 2006-09-11 19:39:43.866
ksqcmi: TT,1,0 mode=4 timeout=21474836
ksqcmi: returns 0
*** 2006-09-11 19:39:43.900
ksqcmi: TM,1b79,0 mode=4 timeout=21474836
ksqcmi: returns 0
*** 2006-09-11 19:39:43.900
ksqcmi: TM,1b79,0 mode=2 timeout=21474836
ksqcmi: returns 0
*** 2006-09-11 19:39:43.905
ksqcmi: TM,1b79,0 mode=4 timeout=21474836
ksqcmi: returns 0
*** 2006-09-11 19:39:43.908
ksqcmi: TM,1c15,0 mode=6 timeout=0
ksqcmi: returns 0

其中的”1b79″,”1c15″是object_id的16进制号，可以转换成10进制察看相应的object_name

SQL> select to_number(’1b79′,’xxxxxxxxxxxx’) from dual;

TO_NUMBER(’1B79′,’XXXXXXXXXXXX’)
——————————–
7033

SQL> select to_number(’1c15′,’xxxxxxxxxxxx’) from dual;

TO_NUMBER(’1C15′,’XXXXXXXXXXXX’)
——————————–
7189

SQL> select object_name,object_type from user_objects where object_id in (7033,7189);

OBJECT_NAME OBJECT_TYPE
—————————— ——————
TEST TABLE

7189对应的是SYS_JOURNAL_ 的临时logging Table. Rebuild成功后被drop掉了。

Eagle Fan oracle events