Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 想深入了解下truncate 表 背后机制
发新帖
baolei

36

积分

0

好友

14

主题

想深入了解下truncate 表 背后机制

1#
发表于 2012-5-29 18:13:05 | 查看: 5346| 回复: 6
昨晚做月度维护，清理过期数据，在truncate 几张大表花了3个小时左右，背后的等待事件，有 db file sequence read， local write wait  ，enq：RO ，DFS handle lock  ，开贴想深入了解下看似简单的一条truncate table 背后的秘密。 不想白忙活一晚。
   谢谢maclean大哥！
2#
发表于 2012-5-30 10:52:01
我吧我会的答上，等大神来补充啊~~
truncate不会产生大量的roolback，不会占用很多的rollback segments， truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被  立即释放，相当于windows中用shift+delete删除数据，不能够恢复！
TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比Delete操作后的表要快得多。

在执行truncate之前，把trace打开：收集trace文件
alter session set events = '10046 trace name context forever,level 12';
执行truncate，然后查看session对应的dump文件，看看你的数据库到底在忙些什么。

回复 只看该作者 道具 举报

3#
发表于 2012-6-2 00:02:16
大表在做truncate之前，最好先做下check point，然后按以下步骤操作：

1、清空数据并且保留原来的extent

truncate table table_name reuse storage;

注：reuse storage 是truncate的参数，表示保持原来的存储空间，一般我们写的truncate table table_name 相当于truncate table table_name drop storage。

2、逐步回收extent

ALTER table table_name DEALLOCATE UNUSED KEEP 1000M;  
ALTER table table_name DEALLOCATE UNUSED KEEP 800M;  
ALTER table table_name DEALLOCATE UNUSED KEEP 600M;  
……  
ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 10M;

注：执行时，可以根据实际情况调整每次回缩空间的大小。

回复 只看该作者 道具 举报

4#
发表于 2012-6-2 10:26:11
谢谢，maclean 大拿怎么没看到你的解释呢

回复 只看该作者 道具 举报

5#
发表于 2012-6-4 23:38:24
SOME INTERNAL TRACE:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table truncateme as select * from dba_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','TRUNCATEME');

PL/SQL procedure successfully completed.

select pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
alter session set events 'trace[rdbms_event] disk highest :trace[db_trace] disk highest: 10046 trace name context forever,level 8';


alter system set "_trace_events"='10000-10999:255:33';
oradebug setmypid;
truncate table truncateme;
oradebug dump errorstack 4;
oradebug tracefile_name;
  1. -------------------------------------------------------------------------------
  2. Trace Bucket Dump Begin: default bucket for process 33 (osid: 5930)
  3. TIME(*=approx):SEQ:COMPONENT:FILE@LINE:FUNCTION:SECT/DUMP: [EVENT#:PID:SID] DATA
  4. -------------------------------------------------------------------------------
  5. 2012-06-04 11:33:50.754750 :80019E2B:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 704c400000000000 0000000000000000 7dbc300000000000
  6. 2012-06-04 11:33:50.754754 :80019E2C:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  7. 2012-06-04 11:33:50.754758 :80019E2D:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  8. 2012-06-04 11:33:50.754762 :80019E2E:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 704c400000000000 0000000000000000 0000000000000000
  9. 2012-06-04 11:33:50.754965 :80019E2F:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 3872400000000000 0000000000000000 7dbc300000000000
  10. 2012-06-04 11:33:50.754969 :80019E30:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  11. 2012-06-04 11:33:50.754973 :80019E31:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  12. 2012-06-04 11:33:50.754977 :80019E32:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 3872400000000000 0000000000000000 0000000000000000
  13. 2012-06-04 11:33:50.754984 :80019E33:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] f872400000000000 0000000000000000 7dbc300000000000
  14. 2012-06-04 11:33:50.754988 :80019E34:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  15. 2012-06-04 11:33:50.754991 :80019E35:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  16. 2012-06-04 11:33:50.754995 :80019E36:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] f872400000000000 0000000000000000 0000000000000000
  17. 2012-06-04 11:33:50.755008 :80019E37:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] f872400000000000 0000000000000000 7dbc300000000000
  18. 2012-06-04 11:33:50.755012 :80019E38:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  19. 2012-06-04 11:33:50.755016 :80019E39:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  20. 2012-06-04 11:33:50.755020 :80019E3A:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] f872400000000000 0000000000000000 0000000000000000
  21. 2012-06-04 11:33:50.755078 :80019E3B:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] f872400000000000 0000000000000000 7dbc300000000000
  22. 2012-06-04 11:33:50.755082 :80019E3C:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  23. 2012-06-04 11:33:50.755085 :80019E3D:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  24. 2012-06-04 11:33:50.755088 :80019E3E:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] f872400000000000 0000000000000000 0000000000000000
  25. 2012-06-04 11:33:50.755254 :80019E3F:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 0b55400000000000 0000000000000000 7dbc300000000000
  26. 2012-06-04 11:33:50.755259 :80019E40:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  27. 2012-06-04 11:33:50.755264 :80019E41:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  28. 2012-06-04 11:33:50.755268 :80019E42:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 0b55400000000000 0000000000000000 0000000000000000
  29. 2012-06-04 11:33:50.755405 :80019E43:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 906a400000000000 0000000000000000 7dbc300000000000
  30. 2012-06-04 11:33:50.755409 :80019E44:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  31. 2012-06-04 11:33:50.755412 :80019E45:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  32. 2012-06-04 11:33:50.755416 :80019E46:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 906a400000000000 0000000000000000 0000000000000000
  33. 2012-06-04 11:33:50.755542 :80019E47:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:33:125] KSL POST SENT postee=30 num=1944 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 id2=0 name=   type=0
  34. 2012-06-04 11:33:50.755581 :80019E48:db_trace:ksl2.c@2598:kslwtbctx(): [10005:33:125] KSL WAIT BEG [row cache lock] 8/0x8 0/0x0 3/0x3 wait_id=2966 seq_num=2997 snap_id=1
  35. 2012-06-04 11:33:50.755847 :80019E5A:db_trace:ksl2.c@14435:ksliwat(): [10005:33:125] KSL FACILITY WAIT fac#=3 time_waited_csecs=0
  36. 2012-06-04 11:33:50.755851 :80019E5C:db_trace:ksl2.c@14563:ksliwat(): [10005:33:125] KSL POST RCVD poster=30 num=6861 loc='kqr.h LINE:2233 ID:kqrbtm' id1=0 id2=0 name=   type=0 fac#=3 posted=0x3 may_be_posted=1
  37. 2012-06-04 11:33:50.755857 :80019E5F:db_trace:ksl2.c@2732:kslwtectx(): [10005:33:125] KSL WAIT END [row cache lock] 8/0x8 0/0x0 3/0x3 wait_id=2966 seq_num=2997 snap_id=1
  38. 2012-06-04 11:33:50.755861 :80019E61:db_trace:ksl2.c@2740:kslwtectx(): [10005:33:125] KSL WAIT END wait times (usecs) - snap=265, exc=265, tot=265
  39. 2012-06-04 11:33:50.755920 :80019E68:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 7fc7400000000000 0000000000000000 7dbc300000000000
  40. 2012-06-04 11:33:50.755924 :80019E69:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  41. 2012-06-04 11:33:50.755928 :80019E6A:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  42. 2012-06-04 11:33:50.755932 :80019E6C:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 7fc7400000000000 0000000000000000 0000000000000000
  43. 2012-06-04 11:33:50.755938 :80019E6E:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 93fa400000000000 0000000000000000 7dbc300000000000
  44. 2012-06-04 11:33:50.755987 :80019E6F:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  45. 2012-06-04 11:33:50.755991 :80019E70:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  46. 2012-06-04 11:33:50.755996 :80019E71:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 93fa400000000000 0000000000000000 0000000000000000
  47. 2012-06-04 11:33:50.756043 :80019E72:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x7278a18c][0x8ac4dfe6],[ND][ext 0x7fff,0x0]
  48. 2012-06-04 11:33:50.756342 :80019E73:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 3b07410000000000 0000000000000000 7dbc300000000000
  49. 2012-06-04 11:33:50.756346 :80019E74:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  50. 2012-06-04 11:33:50.756350 :80019E75:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  51. 2012-06-04 11:33:50.756354 :80019E76:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 3b07410000000000 0000000000000000 0000000000000000
  52. 2012-06-04 11:33:50.756366 :80019E77:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] b707410000000000 0000000000000000 7dbc300000000000
  53. 2012-06-04 11:33:50.756370 :80019E78:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  54. 2012-06-04 11:33:50.756374 :80019E79:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  55. 2012-06-04 11:33:50.756378 :80019E7A:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] b707410000000000 0000000000000000 0000000000000000
  56. 2012-06-04 11:33:50.756387 :80019E7B:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 5501400000000000 0000000000000000 7dbc300000000000
  57. 2012-06-04 11:33:50.756392 :80019E7C:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  58. 2012-06-04 11:33:50.756395 :80019E7D:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  59. 2012-06-04 11:33:50.756400 :80019E7E:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 5501400000000000 0000000000000000 0000000000000000
  60. 2012-06-04 11:33:50.756406 :80019E7F:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 0612400000000000 0000000000000000 7dbc300000000000
  61. 2012-06-04 11:33:50.756410 :80019E80:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  62. 2012-06-04 11:33:50.756414 :80019E81:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  63. 2012-06-04 11:33:50.756418 :80019E82:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 0612400000000000 0000000000000000 0000000000000000
  64. 2012-06-04 11:33:50.756430 :80019E83:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 5101400000000000 0000000000000000 7dbc300000000000
  65. 2012-06-04 11:33:50.756434 :80019E84:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  66. 2012-06-04 11:33:50.756438 :80019E85:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  67. 2012-06-04 11:33:50.756442 :80019E86:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 5101400000000000 0000000000000000 0000000000000000
  68. 2012-06-04 11:33:50.756448 :80019E87:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 362f400000000000 0000000000000000 7dbc300000000000
  69. 2012-06-04 11:33:50.756452 :80019E88:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  70. 2012-06-04 11:33:50.756455 :80019E89:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  71. 2012-06-04 11:33:50.756460 :80019E8A:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 362f400000000000 0000000000000000 0000000000000000
  72. 2012-06-04 11:33:50.756466 :80019E8B:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 813a400000000000 0000000000000000 7dbc300000000000
  73. 2012-06-04 11:33:50.756470 :80019E8C:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  74. 2012-06-04 11:33:50.756474 :80019E8D:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  75. 2012-06-04 11:33:50.756478 :80019E8E:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 813a400000000000 0000000000000000 0000000000000000
  76. 2012-06-04 11:33:50.756624 :80019E8F:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 45ff400000000000 0000000000000000 7dbc300000000000
  77. 2012-06-04 11:33:50.756628 :80019E90:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  78. 2012-06-04 11:33:50.756632 :80019E91:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  79. 2012-06-04 11:33:50.756636 :80019E92:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 45ff400000000000 0000000000000000 0000000000000000
  80. 2012-06-04 11:33:50.756643 :80019E93:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] cbf9400000000000 0000000000000000 7dbc300000000000
  81. 2012-06-04 11:33:50.756647 :80019E94:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  82. 2012-06-04 11:33:50.756651 :80019E95:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  83. 2012-06-04 11:33:50.756655 :80019E96:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] cbf9400000000000 0000000000000000 0000000000000000
  84. 2012-06-04 11:33:50.756679 :80019E97:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 45ff400000000000 0000000000000000 7dbc300000000000
  85. 2012-06-04 11:33:50.756683 :80019E98:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  86. 2012-06-04 11:33:50.756686 :80019E99:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  87. 2012-06-04 11:33:50.756690 :80019E9A:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 45ff400000000000 0000000000000000 0000000000000000
  88. 2012-06-04 11:33:50.756880 :80019E9B:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] f908410000000000 0000000000000000 7dbc300000000000
  89. 2012-06-04 11:33:50.756884 :80019E9C:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  90. 2012-06-04 11:33:50.756888 :80019E9D:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  91. 2012-06-04 11:33:50.756892 :80019E9E:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] f908410000000000 0000000000000000 0000000000000000
  92. 2012-06-04 11:33:50.757198 :80019E9F:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 2305400000000000 0000000000000000 7dbc300000000000
  93. 2012-06-04 11:33:50.757203 :80019EA0:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  94. 2012-06-04 11:33:50.757206 :80019EA1:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  95. 2012-06-04 11:33:50.757211 :80019EA2:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 2305400000000000 0000000000000000 0000000000000000
  96. 2012-06-04 11:33:50.757223 :80019EA3:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 2305400000000000 0000000000000000 7dbc300000000000
  97. 2012-06-04 11:33:50.757227 :80019EA4:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  98. 2012-06-04 11:33:50.757231 :80019EA5:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  99. 2012-06-04 11:33:50.757235 :80019EA6:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 2305400000000000 0000000000000000 0000000000000000
  100. 2012-06-04 11:33:50.757246 :80019EA7:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 2305400000000000 0000000000000000 7dbc300000000000
  101. 2012-06-04 11:33:50.757250 :80019EA8:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  102. 2012-06-04 11:33:50.757254 :80019EA9:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  103. 2012-06-04 11:33:50.757259 :80019EAA:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 2305400000000000 0000000000000000 0000000000000000
  104. 2012-06-04 11:33:50.757268 :80019EAB:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] 2305400000000000 0000000000000000 7dbc300000000000
  105. 2012-06-04 11:33:50.757272 :80019EAC:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  106. 2012-06-04 11:33:50.757276 :80019EAD:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  107. 2012-06-04 11:33:50.757280 :80019EAE:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] 2305400000000000 0000000000000000 0000000000000000
  108. 2012-06-04 11:33:50.757289 :80019EAF:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] f908410000000000 0000000000000000 7dbc300000000000
  109. 2012-06-04 11:33:50.757293 :80019EB0:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  110. 2012-06-04 11:33:50.757297 :80019EB1:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  111. 2012-06-04 11:33:50.757301 :80019EB2:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] f908410000000000 0000000000000000 0000000000000000
  112. 2012-06-04 11:33:50.757390 :80019EB3:db_trace:ktr.c@2797:ktrgcm(): [10812:33:125:2] bcc1400000000000 0000000000000000 7dbc300000000000
  113. 2012-06-04 11:33:50.757394 :80019EB4:db_trace:ktr.c@2799:ktrgcm(): [10812:33:125:3] 0000000000000000 0000000000000000 0000000000000000
  114. 2012-06-04 11:33:50.757397 :80019EB5:db_trace:ktr.c@2801:ktrgcm(): [10812:33:125:4] 0000000000000000 0000000000000000 0000000000000000
  115. 2012-06-04 11:33:50.757401 :80019EB6:db_trace:ktr.c@3458:ktrgcm(): [10812:33:125:5] bcc1400000000000 0000000000000000 0000000000000000
  116. 2012-06-04 11:33:50.757524 :80019EB7:db_trace:ksq.c@7843:ksqgtlctx(): [10704:33:125] ksqgtl: acquire CU-b2fe5e90-00000000 mode=X flags= why="contention"
  117. 2012-06-04 11:33:50.757530 :80019EB8:db_trace:ksq.c@8367:ksqgtlctx(): [10704:33:125] ksqgtl: SUCCESS
  118. 2012-06-04 11:33:50.757637 :80019EB9:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[LB][ext 0x7fff,0x0]
  119. 2012-06-04 11:33:50.757666 :80019EBA:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[NB][ext 0x7fff,0x0]
  120. 2012-06-04 11:33:50.758476 :80019EBB:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[LB][ext 0x7fff,0x0]
  121. 2012-06-04 11:33:50.758497 :80019EBC:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  122. 2012-06-04 11:33:50.758527 :80019EBD:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[LB][ext 0x7fff,0x0]
  123. 2012-06-04 11:33:50.758546 :80019EBE:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[NB][ext 0x7fff,0x0]
  124. 2012-06-04 11:33:50.759180 :80019EBF:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0xaaf85c4b][0x578667af],[LB][ext 0x7fff,0x0]
  125. 2012-06-04 11:33:50.759201 :80019EC0:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0xaaf85c4b][0x578667af],[NB][ext 0x7fff,0x0]
  126. 2012-06-04 11:33:50.759241 :80019EC1:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x3dd08919][0x94da4135],[LB][ext 0x7fff,0x0]
  127. 2012-06-04 11:33:50.759338 :80019EC2:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x3dd08919][0x94da4135],[NB][ext 0x7fff,0x0]
  128. 2012-06-04 11:33:50.759365 :80019EC3:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x483483cf][0x102edb97],[LB][ext 0x7fff,0x0]
  129. 2012-06-04 11:33:50.759385 :80019EC4:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x483483cf][0x102edb97],[NB][ext 0x7fff,0x0]
  130. 2012-06-04 11:33:50.769239 :80019EC5:db_trace:ksq.c@8540:ksqrcli(): [10704:33:125] ksqrcl: release CU-b2fe5e90-00000000 mode=X
  131. 2012-06-04 11:33:50.769425 :80019EC6:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  132. 2012-06-04 11:33:50.769455 :80019EC7:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  133. 2012-06-04 11:33:50.769531 :80019EC8:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  134. 2012-06-04 11:33:50.769680 :80019EC9:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  135. 2012-06-04 11:33:50.769705 :80019ECA:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  136. 2012-06-04 11:33:50.769757 :80019ECB:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  137. 2012-06-04 11:33:50.770032 :80019ECC:db_trace:ksq.c@7843:ksqgtlctx(): [10704:33:125] ksqgtl: acquire CU-b2fdd230-00000000 mode=X flags= why="contention"
  138. 2012-06-04 11:33:50.770081 :80019ECD:db_trace:ksq.c@8367:ksqgtlctx(): [10704:33:125] ksqgtl: SUCCESS
  139. 2012-06-04 11:33:50.770435 :80019ECE:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x805c59b0][0x46ca614f],[LB][ext 0x7fff,0x0]
  140. 2012-06-04 11:33:50.770456 :80019ECF:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x805c59b0][0x46ca614f],[NB][ext 0x7fff,0x0]
  141. 2012-06-04 11:33:50.770481 :80019ED0:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0xd0f2742d][0xdad46b95],[LB][ext 0x7fff,0x0]
  142. 2012-06-04 11:33:50.770501 :80019ED1:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0xd0f2742d][0xdad46b95],[NB][ext 0x7fff,0x0]
  143. 2012-06-04 11:33:50.770528 :80019ED2:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x4be43e00][0x9046201a],[LB][ext 0x7fff,0x0]
  144. 2012-06-04 11:33:50.770547 :80019ED3:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x4be43e00][0x9046201a],[NB][ext 0x7fff,0x0]
  145. 2012-06-04 11:33:50.770600 :80019ED4:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[LB][ext 0x7fff,0x0]
  146. 2012-06-04 11:33:50.770619 :80019ED5:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  147. 2012-06-04 11:33:50.770653 :80019ED6:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[LB][ext 0x7fff,0x0]
  148. 2012-06-04 11:33:50.770674 :80019ED7:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x51570e22][0x5ed8a9a8],[NB][ext 0x7fff,0x0]
  149. 2012-06-04 11:33:50.770715 :80019ED8:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x4be43e00][0x9046201a],[NB][ext 0x7fff,0x0]
  150. 2012-06-04 11:33:50.771483 :80019ED9:db_trace:ksq.c@8540:ksqrcli(): [10704:33:125] ksqrcl: release CU-b2fdd230-00000000 mode=X
  151. 2012-06-04 11:33:50.771549 :80019EDA:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  152. 2012-06-04 11:33:50.771575 :80019EDB:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  153. 2012-06-04 11:33:50.771648 :80019EDC:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  154. 2012-06-04 11:33:50.771795 :80019EDD:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  155. 2012-06-04 11:33:50.771820 :80019EDE:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  156. 2012-06-04 11:33:50.771873 :80019EDF:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  157. 2012-06-04 11:33:50.772008 :80019EE0:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x72a191a5][0x6b9a596c],[NB][ext 0x7fff,0x0]
  158. 2012-06-04 11:33:50.772033 :80019EE1:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  159. 2012-06-04 11:33:50.772226 :80019EE2:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53e1e106][0x4a975f9e],[NC][ext 0x7fff,0x0]
  160. 2012-06-04 11:33:50.772374 :80019EE3:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53594e43][0x24],[IV][ext 0x7fff,0x0]
  161. 2012-06-04 11:33:50.772390 :80019EE4:db_trace:kju.c@1870:kjuscv(): [10425:33:125] kjuscv: [0x53594e43][0x24],[IV][ext 0x7fff,0x0][lockp bcb861d0 ][level 3]
  162. 2012-06-04 11:33:50.772396 :80019EE5:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53594e43][0x24],[IV][ext 0x7fff,0x0]
  163. 2012-06-04 11:33:50.772417 :80019EE6:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:33:125] KSL POST SENT postee=30 num=1945 loc='kji.h LINE:3421 ID:kjatb: wake up enqueue blocker' id1=0 id2=0 name=   type=0
  164. 2012-06-04 11:33:50.772471 :80019EE7:db_trace:ksl2.c@2598:kslwtbctx(): [10005:33:125] KSL WAIT BEG [DFS lock handle] 1230372869/0x49560005 1280262987/0x4c4f434b 36/0x24 wait_id=2967 seq_num=2998 snap_id=1
  165. 2012-06-04 11:33:50.772814 :80019EF9:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x4c4f434b][0x24],[IV][ext 0x7fff,0x0]
  166. 2012-06-04 11:33:50.772822 :80019EFA:db_trace:ksl2.c@14435:ksliwat(): [10005:33:125] KSL FACILITY WAIT fac#=3 time_waited_csecs=0
  167. 2012-06-04 11:33:50.772826 :80019EFB:db_trace:ksl2.c@14563:ksliwat(): [10005:33:125] KSL POST RCVD poster=30 num=6863 loc='kji.h LINE:3418 ID:kjata: wake up enqueue owner' id1=0 id2=0 name=   type=0 fac#=3 posted=0x3 may_be_posted=1
  168. 2012-06-04 11:33:50.772831 :80019EFC:db_trace:ksl2.c@2732:kslwtectx(): [10005:33:125] KSL WAIT END [DFS lock handle] 1230372869/0x49560005 1280262987/0x4c4f434b 36/0x24 wait_id=2967 seq_num=2998 snap_id=1
  169. 2012-06-04 11:33:50.772835 :80019EFD:db_trace:ksl2.c@2740:kslwtectx(): [10005:33:125] KSL WAIT END wait times (usecs) - snap=352, exc=352, tot=352
  170. 2012-06-04 11:33:50.772851 :80019EFE:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:33:125] KSL POST SENT postee=12 num=1946 loc='kjm.h LINE:1224 ID:kjmpost: post lmd' id1=0 id2=0 name=   type=0
  171. 2012-06-04 11:33:50.772889 :80019EFF:db_trace:kju.c@1870:kjuscv(): [10425:33:125] kjuscv: [0x53594e43][0x24],[IV][ext 0x7fff,0x0][lockp bcb861d0 ][level 5]
  172. 2012-06-04 11:33:50.772901 :80019F00:db_trace:ksl2.c@2598:kslwtbctx(): [10005:33:125] KSL WAIT BEG [DFS lock handle] 1230372869/0x49560005 1398361667/0x53594e43 36/0x24 wait_id=2968 seq_num=2999 snap_id=1
  173. 2012-06-04 11:33:50.773512 :80019F11:db_trace:kju.c@1240:kjuef(): [10425:33:125] kjuef: [0x53594e43][0x24],[IV][ext 0x7fff,0x0]
  174. 2012-06-04 11:33:50.773521 :80019F12:db_trace:ksl2.c@14435:ksliwat(): [10005:33:125] KSL FACILITY WAIT fac#=3 time_waited_csecs=0
  175. 2012-06-04 11:33:50.773525 :80019F14:db_trace:ksl2.c@14563:ksliwat(): [10005:33:125] KSL POST RCVD poster=30 num=6866 loc='kji.h LINE:3418 ID:kjata: wake up enqueue owner' id1=0 id2=0 name=   type=0 fac#=3 posted=0x3 may_be_posted=1
  176. 2012-06-04 11:33:50.773530 :80019F15:db_trace:ksl2.c@2732:kslwtectx(): [10005:33:125] KSL WAIT END [DFS lock handle] 1230372869/0x49560005 1398361667/0x53594e43 36/0x24 wait_id=2968 seq_num=2999 snap_id=1
  177. 2012-06-04 11:33:50.773534 :80019F16:db_trace:ksl2.c@2740:kslwtectx(): [10005:33:125] KSL WAIT END wait times (usecs) - snap=621, exc=621, tot=621
  178. 2012-06-04 11:33:50.773557 :80019F17:db_trace:ksq.c@8540:ksqrcli(): [10704:33:125] ksqrcl: release TM-00013135-00000000 mode=X
  179. 2012-06-04 11:33:50.773706 :80019F1A:db_trace:ksq.c@8826:ksqrcli(): [10704:33:125] ksqrcl: SUCCESS
  180. 2012-06-04 11:33:50.773979 :80019F1B:db_trace:ksl2.c@2598:kslwtbctx(): [10005:33:125] KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=2969 seq_num=3000 snap_id=1
  181. 2012-06-04 11:33:50.773989 :80019F1C:db_trace:ksl2.c@2732:kslwtectx(): [10005:33:125] KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=2969 seq_num=3000 snap_id=1
  182. 2012-06-04 11:33:50.773993 :80019F1D:db_trace:ksl2.c@2740:kslwtectx(): [10005:33:125] KSL WAIT END wait times (usecs) - snap=10, exc=10, tot=10
  183. 2012-06-04 11:33:50.774007 :80019F1E:db_trace:ksl2.c@2598:kslwtbctx(): [10005:33:125] KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=2970 seq_num=3001 snap_id=1
  184. 2012-06-04 11:33:50.774334 :80019F1F:db_trace:ksl2.c@2732:kslwtectx(): [10005:33:125] KSL WAIT END [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=2970 seq_num=3001 snap_id=1
  185. 2012-06-04 11:33:50.774338 :80019F20:db_trace:ksl2.c@2740:kslwtectx(): [10005:33:125] KSL WAIT END wait times (usecs) - snap=326, exc=326, tot=326
复制代码

回复 只看该作者 道具 举报

6#
发表于 2012-6-5 11:14:58

回复 4# 的帖子

谢谢，老大挤出时间，能否还能帮我解释下这个trace呢？

回复 只看该作者 道具 举报

7#
发表于 2012-8-9 09:38:10
呃。。。也没看懂。。。。。
汗。。
但在trace里是有几个等待事件。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 00:38 , Processed in 0.050588 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569