通过enq: TS - contention理解Rac环境下temp表空间的管理

最新推荐文章于 2023-11-17 14:31:35 发布
最新推荐文章于 2023-11-17 14:31:35 发布
阅读量471 收藏
点赞数
文章标签： 数据库 php
原文链接：http://www.cnblogs.com/dayu-liu/p/9508119.html
版权

1.先说下什么是enq: TS - contention：

  • 在oracle中区别于undo表空间，temp表空间是两个节点共用的，因此也就存在temp在两个节点上获取和释放的过程。并且temp表空间在存储系统上是预先分配的，即实际使用的时候才会占用系统空间，这也就是为什么我们在创建一个很大临时数据文件的时候很快就能创建完。
  • 当节点1上某个sql由于排序或者hash join需要用到temp表空间的时候，会获取多个单位大小为1M的temp extents，且这些分配给节点1的temp extents对节点2是不可见的。当排序或者hash join继续进行，无法在节点1上继续获取temp extents时，节点1就会向节点2发出cross instance call(CIC)，要求节点2释放temp extents。假设节点2上正好有个用到temp extents的事务已经提交，那么节点2上的smon进程就会开始清理temp extents，并将清理完毕的temp extents分配给节点1，如果此时节点2无法分配temp extents给节点1，就会发生enq:TS-contention的现象。可以在GV$TEMP_EXTENT_POOL上查看相应的节点1、2各自缓存使用的temp extent大小。（以1M为单位这个说法暂时还没有测试证明，这里仅供参考）。

  • 我们经产会遇到等待事件的P1参数为name|more模式，以下为转换的脚本：（很实用） 
    select chr(bitand(&&p1,-16777216)/16777215) || chr(bitand(&&p1,16711680)/65535) type,
mod(&&p1, 16) md from dual;
Enter value for p1: 1397948422

TY         MD
-- ----------
SS          6

Enter value for p1: 1128857605
TY         MD
-- ----------
CI          5

2.再说下今天遇到问题的结论：

在并行收集表的统计信息时，会话挂起。QCSID对应的等待事件为PX Deq: Parse Reply，QCSID发起的并行会话对应的等待事件为enq: TS - contention。发起并行的会话(QCSID)被其发起的会话(sid)堵塞，QCSID等待事件为PX Deq: Parse Reply,对应的堵塞sid等待事件为enq: TS - contention,且堵塞sid挂起。由于当前此数据库配置的max_parallel_server为48。之前发起的并行会话一直不释放，导致后续发起的并行无法正常发起。此问题出发已知问题201702060002，此bug已经在11.2.0.3.8中被解决。


3.以下为分析过程：QCSID与SID的对应关系如下：

SQL> select p.QCSID,s.inst_id,s.sid,s.serial#,s.event,s.blocking_session,s.last_call_et
  2  from gv$session s,gv$px_session p
  3  where s.SID=p.SID
  4  and s.inst_id=p.QCINST_ID
  5  and s.last_call_et > 5000
  6  order by s.last_call_et;

     QCSID    INST_ID        SID    SERIAL# EVENT                          BLOCKING_SESSION LAST_CALL_ET
---------- ---------- ---------- ---------- ------------------------------ ---------------- ------------
       112          1       2355      18919 enq: TS - contention                                  172583
       112          1       1587      55877 enq: TS - contention                                  172583
       112          1       2597       2569 enq: TS - contention                                  172583
       112          1        552      14795 enq: TS - contention                                  172583
       112          1       1981      22315 enq: TS - contention                                  172583
       112          1        728      13411 enq: TS - contention                                  172583
       112          1       1059       1577 enq: TS - contention                                  172583
       112          1       1102       1355 enq: TS - contention                                  172583
       112          1       2004       9471 enq: TS - contention                                  172583
       112          1       2751      14929 enq: TS - contention                                  172583
      1805          2       2025      47121 enq: TS - contention                                  606997

     QCSID    INST_ID        SID    SERIAL# EVENT                          BLOCKING_SESSION LAST_CALL_ET
---------- ---------- ---------- ---------- ------------------------------ ---------------- ------------
      1805          2       1827      61471 enq: TS - contention                                  606997
      1805          2       1871      10351 enq: TS - contention                                  606997
      1805          2       1893       5239 enq: TS - contention                                  606997
      1805          2       2113      60157 enq: TS - contention                                  606997
      1805          2       1937      29377 enq: TS - contention                                  606997
      1805          2       1959      42493 enq: TS - contention                                  606997
      1805          2       1981      48385 enq: TS - contention                                  606997
      1805          2       2003      26417 enq: TS - contention                                  606997
      1805          2       1915      50357 enq: TS - contention                                  606997
      2069          1       2487      41469 enq: TS - contention                                 1116070
      2069          1       2465      22353 enq: TS - contention                                 1116070

     QCSID    INST_ID        SID    SERIAL# EVENT                          BLOCKING_SESSION LAST_CALL_ET
---------- ---------- ---------- ---------- ------------------------------ ---------------- ------------
      2069          1       2443      35739 enq: TS - contention                                 1116070
      2069          1       2399      53135 enq: TS - contention                                 1116070
      2069          1       2377      53577 enq: TS - contention                                 1116070
      2069          1       2333       7979 enq: TS - contention                                 1116070
      2069          1       2311      57591 enq: TS - contention                                 1116070
      2069          1       2181      22481 enq: TS - contention                                 1116070
      2069          1       2113      24377 enq: TS - contention                                 1116070
      2069          1       2509      39935 enq: TS - contention                                 1116070
      1563          1       2245      54015 enq: TS - contention                                 1552232
      1563          1       2223      47061 enq: TS - contention                                 1552232
      1563          1       2135      21113 enq: TS - contention                                 1552232

     QCSID    INST_ID        SID    SERIAL# EVENT                          BLOCKING_SESSION LAST_CALL_ET
---------- ---------- ---------- ---------- ------------------------------ ---------------- ------------
      1563          1       2047      41769 enq: TS - contention                                 1552232
      1563          1       2267       1105 enq: TS - contention                                 1552232
      1563          1       1937      59185 enq: TS - contention                                 1552232
      1563          1       1827      38509 enq: TS - contention                                 1552232
      1563          1       1761      44009 enq: TS - contention                                 1552232
      1563          1       1629      60835 enq: TS - contention                                 1552232
      1563          1       1959      24697 enq: TS - contention                                 1552232

 

通过查看对应的SID执行的SQL如下，如下：

Insert /*+ append */ into sys.ora_temp_1_ds_350003 SELECT /*+  parallel(t,16) parallel_index(t,16) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)...

收集挂起的sid的10046事件，trc如下（从10046中可以看到进程其实并没有在做什么任务，和其处于waiting的状态保持一致。）

Trace file /oracle/app/oracle/diag/rdbms/adashw/ADASHW1/trace/ADASHW1_smon_23265484.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0.3/dbhome_1
System name:    AIX
Node name:      padasdb1
Release:        1
Version:        6
Machine:        00F977E44C00
Instance name: ADASHW1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 23265484, image: oracle@padasdb1 (SMON)


*** 2018-08-03 17:53:17.235
*** SESSION ID:(925.1) 2018-08-03 17:53:17.235
*** CLIENT ID:() 2018-08-03 17:53:17.235
*** SERVICE NAME:(SYS$BACKGROUND) 2018-08-03 17:53:17.235
*** MODULE NAME:() 2018-08-03 17:53:17.235
*** ACTION NAME:() 2018-08-03 17:53:17.235
 

*** TRACE FILE RECREATED AFTER BEING REMOVED ***

Parallel Transaction recovery caught exception 12801
Parallel Transaction recovery caught error 370 
SMON: Restarting fast_start parallel rollback

*** 2018-08-03 17:53:34.038
Dead transaction 0x0020.012.0005ce72 recovered by 1 server(s)
SMON: Parallel transaction recovery tried
Parallel Transaction recovery caught exception 12801

*** 2018-08-07 08:33:42.963
Parallel Transaction recovery caught error 370 
SMON: Restarting fast_start parallel rollback

*** 2018-08-07 08:35:38.755
Dead transaction 0x0052.018.000424d8 recovered by 1 server(s)
SMON: Parallel transaction recovery tried
Parallel Transaction recovery caught exception 12801

*** 2018-08-08 18:20:12.170
Parallel Transaction recovery caught error 370 
SMON: Restarting fast_start parallel rollback

*** 2018-08-08 18:22:22.192
Dead transaction 0x0018.00e.00053ddc recovered by 1 server(s)
SMON: Parallel transaction recovery tried
Parallel Transaction recovery caught exception 12801

*** 2018-08-10 16:52:51.813
Parallel Transaction recovery caught error 370 
SMON: Restarting fast_start parallel rollback

*** 2018-08-10 16:54:17.551
Dead transaction 0x005f.01c.0002129b recovered by 1 server(s)
SMON: Parallel transaction recovery tried

*** 2018-08-20 14:04:08.093
Received ORADEBUG command (#2) 'event 10046 trace name context forever,level 12' from process 'Unix process pid: 3
8076450, image: <none>'

*** 2018-08-20 14:04:08.224
Finished processing ORADEBUG command (#2) 'event 10046 trace name context forever,level 12'

*** 2018-08-20 14:04:10.242
WAIT #0: nam='smon timer' ela= 5000058 sleep time=300 failed=0 p3=0 obj#=1 tim=73478640165036

*** 2018-08-20 14:04:15.242
WAIT #0: nam='smon timer' ela= 4986465 sleep time=300 failed=0 p3=0 obj#=1 tim=73478645165087

*** 2018-08-20 14:04:20.242
WAIT #0: nam='smon timer' ela= 4999954 sleep time=300 failed=0 p3=0 obj#=1 tim=73478650165154

*** 2018-08-20 14:04:25.242
WAIT #0: nam='smon timer' ela= 4999971 sleep time=300 failed=0 p3=0 obj#=1 tim=73478655165226

*** 2018-08-20 14:04:30.242
WAIT #0: nam='smon timer' ela= 4999966 sleep time=300 failed=0 p3=0 obj#=1 tim=73478660165306

*** 2018-08-20 14:04:35.242
WAIT #0: nam='smon timer' ela= 4999951 sleep time=300 failed=0 p3=0 obj#=1 tim=73478665165386

*** 2018-08-20 14:04:40.242
WAIT #0: nam='smon timer' ela= 4999971 sleep time=300 failed=0 p3=0 obj#=1 tim=73478670165462

*** 2018-08-20 14:04:45.242
WAIT #0: nam='smon timer' ela= 4999987 sleep time=300 failed=0 p3=0 obj#=1 tim=73478675165547

*** 2018-08-20 14:04:50.242
WAIT #0: nam='smon timer' ela= 4999976 sleep time=300 failed=0 p3=0 obj#=1 tim=73478680165621

*** 2018-08-20 14:04:55.243
WAIT #0: nam='smon timer' ela= 4999968 sleep time=300 failed=0 p3=0 obj#=1 tim=73478685165700

*** 2018-08-20 14:05:00.243
WAIT #0: nam='smon timer' ela= 4999975 sleep time=300 failed=0 p3=0 obj#=1 tim=73478690165774
WAIT #0: nam='DFS lock handle' ela= 696 type|mode=1413545989 id1=3 id2=11 obj#=1 tim=73478690167321
WAIT #0: nam='DFS lock handle' ela= 621 type|mode=1413545989 id1=3 id2=12 obj#=1 tim=73478690168055
WAIT #0: nam='DFS lock handle' ela= 434 type|mode=1413545989 id1=3 id2=13 obj#=1 tim=73478690168598
WAIT #0: nam='DFS lock handle' ela= 429 type|mode=1413545989 id1=3 id2=14 obj#=1 tim=73478690169097
WAIT #0: nam='DFS lock handle' ela= 425 type|mode=1413545989 id1=3 id2=15 obj#=1 tim=73478690169615
WAIT #0: nam='DFS lock handle' ela= 483 type|mode=1413545989 id1=3 id2=16 obj#=1 tim=73478690170157
WAIT #0: nam='DFS lock handle' ela= 499 type|mode=1413545989 id1=3 id2=17 obj#=1 tim=73478690170721
WAIT #0: nam='DFS lock handle' ela= 464 type|mode=1413545989 id1=3 id2=18 obj#=1 tim=73478690171248
WAIT #0: nam='DFS lock handle' ela= 422 type|mode=1413545989 id1=3 id2=19 obj#=1 tim=73478690171793
WAIT #0: nam='DFS lock handle' ela= 474 type|mode=1413545989 id1=3 id2=20 obj#=1 tim=73478690172398
WAIT #0: nam='DFS lock handle' ela= 792 type|mode=1413545989 id1=3 id2=21 obj#=1 tim=73478690173288
WAIT #0: nam='DFS lock handle' ela= 622 type|mode=1413545989 id1=3 id2=22 obj#=1 tim=73478690174059
WAIT #0: nam='DFS lock handle' ela= 396 type|mode=1413545989 id1=3 id2=36 obj#=1 tim=73478690174548
WAIT #0: nam='DFS lock handle' ela= 519 type|mode=1413545989 id1=3 id2=37 obj#=1 tim=73478690175143
WAIT #0: nam='DFS lock handle' ela= 625 type|mode=1413545989 id1=3 id2=38 obj#=1 tim=73478690175923
WAIT #0: nam='DFS lock handle' ela= 695 type|mode=1413545989 id1=3 id2=39 obj#=1 tim=73478690176741
WAIT #0: nam='DFS lock handle' ela= 504 type|mode=1413545989 id1=3 id2=40 obj#=1 tim=73478690177340
WAIT #0: nam='DFS lock handle' ela= 1048 type|mode=1413545989 id1=3 id2=41 obj#=1 tim=73478690178521
WAIT #0: nam='DFS lock handle' ela= 634 type|mode=1413545989 id1=3 id2=42 obj#=1 tim=73478690179252
WAIT #0: nam='DFS lock handle' ela= 489 type|mode=1413545989 id1=3 id2=43 obj#=1 tim=73478690179854
WAIT #0: nam='DFS lock handle' ela= 1132 type|mode=1413545989 id1=3 id2=44 obj#=1 tim=73478690181056
WAIT #0: nam='DFS lock handle' ela= 718 type|mode=1413545989 id1=3 id2=45 obj#=1 tim=73478690181863
WAIT #0: nam='DFS lock handle' ela= 1111 type|mode=1413545989 id1=3 id2=46 obj#=1 tim=73478690183116
WAIT #0: nam='DFS lock handle' ela= 567 type|mode=1413545989 id1=3 id2=47 obj#=1 tim=73478690183767
WAIT #0: nam='DFS lock handle' ela= 584 type|mode=1413545989 id1=3 id2=48 obj#=1 tim=73478690184497
WAIT #0: nam='DFS lock handle' ela= 486 type|mode=1413545989 id1=3 id2=49 obj#=1 tim=73478690185063
WAIT #0: nam='DFS lock handle' ela= 728 type|mode=1413545989 id1=3 id2=50 obj#=1 tim=73478690185868
WAIT #0: nam='DFS lock handle' ela= 503 type|mode=1413545989 id1=3 id2=51 obj#=1 tim=73478690186448
WAIT #0: nam='DFS lock handle' ela= 782 type|mode=1413545989 id1=3 id2=54 obj#=1 tim=73478690187350
WAIT #0: nam='DFS lock handle' ela= 466 type|mode=1413545989 id1=3 id2=55 obj#=1 tim=73478690187909
WAIT #0: nam='DFS lock handle' ela= 540 type|mode=1413545989 id1=3 id2=64 obj#=1 tim=73478690188547
WAIT #0: nam='DFS lock handle' ela= 516 type|mode=1413545989 id1=3 id2=65 obj#=1 tim=73478690189132
WAIT #0: nam='DFS lock handle' ela= 520 type|mode=1413545989 id1=3 id2=66 obj#=1 tim=73478690189715
WAIT #0: nam='DFS lock handle' ela= 1036 type|mode=1413545989 id1=3 id2=108 obj#=1 tim=73478690190897
WAIT #0: nam='DFS lock handle' ela= 455 type|mode=1413545989 id1=3 id2=109 obj#=1 tim=73478690191504
WAIT #0: nam='DFS lock handle' ela= 472 type|mode=1413545989 id1=3 id2=110 obj#=1 tim=73478690192113
WAIT #0: nam='DFS lock handle' ela= 510 type|mode=1413545989 id1=3 id2=111 obj#=1 tim=73478690192762
WAIT #0: nam='DFS lock handle' ela= 381 type|mode=1413545989 id1=3 id2=112 obj#=1 tim=73478690193261
WAIT #0: nam='DFS lock handle' ela= 826 type|mode=1413545989 id1=3 id2=113 obj#=1 tim=73478690194191
WAIT #0: nam='DFS lock handle' ela= 625 type|mode=1413545989 id1=3 id2=114 obj#=1 tim=73478690194934
WAIT #0: nam='DFS lock handle' ela= 1084 type|mode=1413545989 id1=3 id2=115 obj#=1 tim=73478690196101
WAIT #0: nam='DFS lock handle' ela= 528 type|mode=1413545989 id1=3 id2=116 obj#=1 tim=73478690196740
WAIT #0: nam='DFS lock handle' ela= 730 type|mode=1413545989 id1=3 id2=117 obj#=1 tim=73478690197656
WAIT #0: nam='DFS lock handle' ela= 818 type|mode=1413545989 id1=3 id2=118 obj#=1 tim=73478690198586
WAIT #0: nam='DFS lock handle' ela= 507 type|mode=1413545989 id1=3 id2=119 obj#=1 tim=73478690199262
WAIT #0: nam='DFS lock handle' ela= 443 type|mode=1413545989 id1=3 id2=120 obj#=1 tim=73478690199802
WAIT #0: nam='DFS lock handle' ela= 445 type|mode=1413545989 id1=3 id2=121 obj#=1 tim=73478690200339
WAIT #0: nam='DFS lock handle' ela= 906 type|mode=1413545989 id1=3 id2=122 obj#=1 tim=73478690201332
WAIT #0: nam='DFS lock handle' ela= 523 type|mode=1413545989 id1=3 id2=123 obj#=1 tim=73478690202002
WAIT #0: nam='DFS lock handle' ela= 541 type|mode=1413545989 id1=3 id2=124 obj#=1 tim=73478690202626
WAIT #0: nam='DFS lock handle' ela= 583 type|mode=1413545989 id1=3 id2=125 obj#=1 tim=73478690203336
WAIT #0: nam='DFS lock handle' ela= 477 type|mode=1413545989 id1=3 id2=126 obj#=1 tim=73478690203925
WAIT #0: nam='DFS lock handle' ela= 500 type|mode=1413545989 id1=3 id2=127 obj#=1 tim=73478690204565
WAIT #0: nam='DFS lock handle' ela= 2133 type|mode=1413545989 id1=3 id2=128 obj#=1 tim=73478690206778
WAIT #0: nam='DFS lock handle' ela= 568 type|mode=1413545989 id1=3 id2=129 obj#=1 tim=73478690207466
WAIT #0: nam='DFS lock handle' ela= 492 type|mode=1413545989 id1=3 id2=130 obj#=1 tim=73478690208104
WAIT #0: nam='DFS lock handle' ela= 605 type|mode=1413545989 id1=3 id2=131 obj#=1 tim=73478690208834
WAIT #0: nam='DFS lock handle' ela= 677 type|mode=1413545989 id1=3 id2=132 obj#=1 tim=73478690209634
WAIT #0: nam='DFS lock handle' ela= 602 type|mode=1413545989 id1=3 id2=133 obj#=1 tim=73478690210308
WAIT #0: nam='DFS lock handle' ela= 704 type|mode=1413545989 id1=3 id2=134 obj#=1 tim=73478690211163
WAIT #0: nam='DFS lock handle' ela= 744 type|mode=1413545989 id1=3 id2=135 obj#=1 tim=73478690212014
WAIT #0: nam='DFS lock handle' ela= 537 type|mode=1413545989 id1=3 id2=136 obj#=1 tim=73478690212734
WAIT #0: nam='DFS lock handle' ela= 479 type|mode=1413545989 id1=3 id2=137 obj#=1 tim=73478690213288
WAIT #0: nam='DFS lock handle' ela= 772 type|mode=1413545989 id1=3 id2=138 obj#=1 tim=73478690214149
WAIT #0: nam='DFS lock handle' ela= 598 type|mode=1413545989 id1=3 id2=139 obj#=1 tim=73478690214814
WAIT #0: nam='DFS lock handle' ela= 798 type|mode=1413545989 id1=3 id2=140 obj#=1 tim=73478690215773
WAIT #0: nam='DFS lock handle' ela= 791 type|mode=1413545989 id1=3 id2=141 obj#=1 tim=73478690216682
WAIT #0: nam='DFS lock handle' ela= 442 type|mode=1413545989 id1=3 id2=142 obj#=1 tim=73478690217227
WAIT #0: nam='DFS lock handle' ela= 935 type|mode=1413545989 id1=3 id2=143 obj#=1 tim=73478690218271
WAIT #0: nam='DFS lock handle' ela= 748 type|mode=1413545989 id1=3 id2=144 obj#=1 tim=73478690219161
WAIT #0: nam='DFS lock handle' ela= 502 type|mode=1413545989 id1=3 id2=145 obj#=1 tim=73478690219806
WAIT #0: nam='DFS lock handle' ela= 386 type|mode=1413545989 id1=3 id2=146 obj#=1 tim=73478690220361
WAIT #0: nam='DFS lock handle' ela= 644 type|mode=1413545989 id1=3 id2=147 obj#=1 tim=73478690221140
WAIT #0: nam='DFS lock handle' ela= 419 type|mode=1413545989 id1=3 id2=148 obj#=1 tim=73478690221667
WAIT #0: nam='DFS lock handle' ela= 601 type|mode=1413545989 id1=3 id2=149 obj#=1 tim=73478690222347
WAIT #0: nam='DFS lock handle' ela= 511 type|mode=1413545989 id1=3 id2=150 obj#=1 tim=73478690222925
WAIT #0: nam='DFS lock handle' ela= 506 type|mode=1413545989 id1=3 id2=151 obj#=1 tim=73478690223557
WAIT #0: nam='DFS lock handle' ela= 476 type|mode=1413545989 id1=3 id2=159 obj#=1 tim=73478690224122
WAIT #0: nam='DFS lock handle' ela= 500 type|mode=1413545989 id1=3 id2=160 obj#=1 tim=73478690224711
WAIT #0: nam='DFS lock handle' ela= 467 type|mode=1413545989 id1=3 id2=161 obj#=1 tim=73478690225275
WAIT #0: nam='DFS lock handle' ela= 876 type|mode=1413545989 id1=3 id2=162 obj#=1 tim=73478690226293
WAIT #0: nam='DFS lock handle' ela= 915 type|mode=1413545989 id1=3 id2=163 obj#=1 tim=73478690227318

*** 2018-08-20 14:05:05.243
WAIT #0: nam='smon timer' ela= 4938493 sleep time=300 failed=0 p3=0 obj#=1 tim=73478695165886

*** 2018-08-20 14:05:10.244
WAIT #0: nam='smon timer' ela= 4999930 sleep time=300 failed=0 p3=0 obj#=1 tim=73478700165962

*** 2018-08-20 14:05:12.198
Received ORADEBUG command (#3) 'event 10046 trace name context off' from process 'Unix process pid: 38076450, imag
e: <none>'

*** 2018-08-20 14:05:12.199
Finished processing ORADEBUG command (#3) 'event 10046 trace name context off'

*** 2018-08-20 14:05:12.199
Received ORADEBUG command (#4) 'tracefile_name' from process 'Unix process pid: 38076450, image: <none>'

*** 2018-08-20 14:05:12.199
Finished processing ORADEBUG command (#4) 'tracefile_name

 

4、当单实例数据库中存在多个temp表空间时，当单个temp空间不够时，会使用另外的temp表空间么？

  • 创建大表
SQL> select BYTES/1024/1024/1024 from dba_segments where segment_name='TEMP_TEST';

BYTES/1024/1024/1024
--------------------
           1
  • 模拟大的排序动作，可以看到最终产生报错，这里没有立即报错的原因是因为参数resumable_timeout为非零值。
SQL> select object_id,object_name from TEMP_TEST order by object_id;
select object_id,object_name from TEMP_TEST order by object_id
                                  *
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

tempsegment的使用如下

SQL> @tempseg_usage.sql

     SID  SERIAL# LOGON_TIME         SQL_ID       SEGTYPE    SIZE_MB STATUS     STATE             PADDR
-------- -------- ------------------ ------------- --------- ---------- -------- ------------------- ----------------
       1    7 20190703 11:02:11  bvh9w9hf4tg4x SORT          79 ACTIVE     WAITING         000000009272BD20

等待事件如下：

       SID SQL_ID     EVENT
---------- ------------- ----------------------------------------------------------------
     1 g6yamzuzkkn1q statement suspended, wait error to be cleared
  •  添加新的temp表空间，重新进行大的排序查询
SQL> create temporary tablespace temp2 tempfile '/oracle/app/oracle/oradata/ocm/temp02.dbf' size 100M autoextend off;

Tablespace created.

虽然添加了新的temp表空间，但是在进行大的排序动作时，只使用一个temp

SQL>  @tempseg_usage.sql

     SID  SERIAL# LOGON_TIME         SQL_ID        SEGTYPE   SIZE_MB    STATUS   STATE               PADDR
-------- -------- ------------------ ------------- --------- ---------- -------- ------------------- ----------------
       1        7 20190703 11:02:11  g6yamzuzkkn1q SORT              79 ACTIVE   WAITING             000000009272BD20
  • 在查询用户的默认temp表空间时，也可以找到相应的结论，一个用户只能有一个默认的permanent tablespace和一个TEMPORARY_TABLESPACE
SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

USERNAME                        DEFAULT_TABLESPACE          TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
MGMT_VIEW                      SYSTEM                  TEMP
SYS                   SYSTEM                  TEMP
SYSTEM                   SYSTEM                  TEMP
DBSNMP                   SYSAUX                  TEMP
SYSMAN                   SYSAUX                  TEMP
DAYU                   USERS                  TEMP
OUTLN                   SYSTEM                  TEMP
FLOWS_FILES               SYSAUX                  TEMP
MDSYS                   SYSAUX                  TEMP
ORDSYS                   SYSAUX                  TEMP
EXFSYS                   SYSAUX                  TEMP
WMSYS                   SYSAUX                  TEMP
APPQOSSYS               SYSAUX                  TEMP
APEX_030200               SYSAUX                  TEMP
OWBSYS_AUDIT               SYSAUX                  TEMP
ORDDATA                SYSAUX                  TEMP
CTXSYS                   SYSAUX                  TEMP
ANONYMOUS               SYSAUX                  TEMP
XDB                   SYSAUX                  TEMP
ORDPLUGINS               SYSAUX                  TEMP
OWBSYS                   SYSAUX                  TEMP
SI_INFORMTN_SCHEMA           SYSAUX                  TEMP
OLAPSYS                SYSAUX                  TEMP
ORACLE_OCM               USERS                  TEMP
XS$NULL                USERS                  TEMP
MDDATA                   USERS                  TEMP
DIP                   USERS                  TEMP
APEX_PUBLIC_USER           USERS                  TEMP
SPATIAL_CSW_ADMIN_USR           USERS                  TEMP
SPATIAL_WFS_ADMIN_USR           USERS                  TEMP

30 rows selected.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

by dayu ：first time at 20180820

source：E:\TS\201808\dayu0820

reference：https://orainternals.wordpress.com/2012/02/13/temporary-tablespaces-in-rac/#comment-19674

 

转载于:https://www.cnblogs.com/dayu-liu/p/9508119.html

weixin_30725315
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
第一次遇到在awr top 5 event中出现enq:SS-contention 和enq:TS-contention
killvoon的专栏
08-06 488
开发人员跟我说有台测试环境数据库有问题，很卡，应用程序启动起来就卡住，根本无法使用。但没有报任何ora-相关的错误，让我瞧瞧T 我用plsql developer登录下，发现还好，看了下最近的alert日志，也没有异常。下意识的觉得不应该是数据库问题，可能是开发那边网络之类导致出现卡顿的情况。恰巧到饭点，无心恋战，开发人员就让我将数据库重启下，我内心不深处觉得重启是解决不了问题的，但开发给我的回复是重启后好了。 心里一直挂着这事，午休后上班就再次登录上去仔细查验一番。首先看了下最近几天awr DB Ti
BLOG_【AWR分析】Oracle_lhr_队列等待之enq IV - contention案例.pdf
08-08
BLOG_【AWR分析】Oracle_lhr_队列等待之enq IV - contention案例.pdfBLOG_【AWR分析】Oracle_lhr_队列等待之enq IV - contention案例.pdf
Merge 压缩表 有可能导致 enq:ts-contention事件
海鲨MYSQL精英架构师
07-09 1525
昨天把大部分大量数据的表给压缩了，其中有个用户信息表约250万条客户信息，每天都要全量MERGE比较更新和插入。今早一看该过程都跑了9个小时了，一直等待enq:ts-contention事件。计划说它要执行32个小时.而其他分区压缩表也有merge过程7-8分钟就跑完了。后来发现该BA用户使用的是STAT_TEMP临时表空间 把该用户的换成了TEMP空间就没问题了。
enq: TS - contention 等待事件
congjianqing1076的博客
04-30 293
OS:suse10 oracle 11g 4 RAC 节点 根据awr 报告 TOP 5 的等待事件EventWaitsTime(s)Avg wait (ms)% DB timeWait Clas...
enq: TS – contention
wll的专栏
06-03 1970
此事件在RAC中会更加常见，原因在于系统中临时表空间争用。 RAC里，所有节点共用同一个临时表空间，而temp extent一旦分配给某一个节点，其它节点将不可见； 一旦某个节点上分配的temp extent耗尽，则会发出cross instance call(CIC)向其它节点请求temp extent; 此时SMON就去回收所有节点的free temp extent，此过程会持有TS e
遇到 enq: TS - contention
ruilinruirui
06-17 345
今天我要做一个SKID translation(不要问我什么是SKID translation). 我用BW9518账号干活。处于习惯，我会监控等待事件 SQL&gt; select inst_id,sid,serial#,event,p1,p2,p3,program from gv$session where username='BW9518'; INST_ID SID SE...
enq：TX index contention.txt
11-01
cause:当插入新的索引条目时，发现索引块中没有足够的空间容纳新的索引条目，索引块就会产生分裂（分为5-5分裂...这时就会表现为enq: TX - index contention。本例中索引块分裂属于5-5 分裂，此分裂可以通过awr报告观察
enq PS - contention.pdf
08-01
enq PS - contention.pdf enq PS - contention.pdfenq PS - contention.pdf
BLOG_Oracle_lhr_ 【等待事件】等待事件系列（5.1）--Enqueue(队列等待) .pdf
08-06
BLOG_Oracle_lhr_ 【等待事件】等待事件系列（5.1）--Enqueue(队列等待) .pdfBLOG_Oracle_lhr_ 【等待事件】等待事件系列（5.1）--Enqueue(队列等待) .pdf
BLOG_【故障处理】Oracle_lhr_队列等待之TX - allocate ITL entry案例.pdf
08-06
BLOG_【故障处理】Oracle_lhr_队列等待之TX - allocate ITL entry案例.pdfBLOG_【故障处理】Oracle_lhr_队列等待之TX - allocate ITL entry案例.pdf
enq: TS - contention
congdiku5262的博客
06-01 135
查看数据库的时候发现系统存在以下等待事件enq: TS – contention 此事件在RAC中会更加常见，原因在于系统中临时表空间争用。 RAC里，所有节点共用同一个临时表空间，而temp extent一旦分配给某一个节...
oracle 系统drop 事件,drop tablespace temp~等待事件 enq: TS - contention
weixin_39573287的博客
04-06 225
ODM DATA:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/10.2.0...
切换TEMP表空间遭遇enq: TS - contention
cuicuntu1021的博客
08-29 167
一数据库出现TEMP表空间相关的ORA-060600[2662]报错，决定重新建立一个新的temp表空间，切换过去。在切换过程，会话长时间无响应，查看等待事件发现enq: TS - contention 事件占用大量时间。 S...
不要轻易的尝试在生产系统上切换temp表空间 enq: TS - contention
食肉动物的专栏
03-31 520
http://www.oracleblog.org/working-case/switch-temp-tablespace-cause-lot-ofenqueue/
使用V$LOCK解决enq: ST – contention的一个例子
云的后端
06-05 391
一个用来存储报表的数据库上，有一系列数据导入的进程，但在今天发现这些进程一直未执行结束，在数据导入端可以看到数据导入速度为零，查看数据库上的等待事件，发现它们的等待事件全部是enq: ST – contention（EXTENT分配或者回收的锁）。 [code="sql"] SID MACHINE HASH Event Name ...
RAC环境下所有数据库实例可以使用同一个Undo？
最新发布
jnrjian的博客
11-17 93
RAC下的每个节点实例需要有自己单独的Undo表空间。由初始化参数“UNDO_TABLESPACE”指定。同Redo一样，Undo表空间也需要部署到。
oracle的表空间的检查,oracle数据库检查所有表空间使用率的脚本
weixin_30139729的博客
04-11 148
oracle数据库检查所有表空间使用率的脚本REM tablespace reportset linesize 200select a.tablespace_name,round(a.bytes_alloc / 1024 / 1024) megs_alloc,round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,round((a.bytes_all...
Oracle数据库临时表（TEMP）空间的切换及数据文件的处理
lu200601051315的博客
11-07 2848
TEMP表空间的作用 临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当 oracle 里需要用到 sort 的时候， PGA 中 sort_area_size 大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间 , 正常来说，在完成 Select 语句、create index 等一...
案例|RAC 添加表空间误将数据文件放本地处理办法
JiekeXu的博客
11-16 445
作者 | JiekeXu来源 |JiekeXu之路（ID: JiekeXu_IT）转载请联系授权 |(微信ID：xxq1426321293)大家好，我是 JiekeXu,很高兴又和大...
enq: wf - contention
07-28
"enq: wf - contention" 是 Oracle 数据库中一个等待事件的名称，表示等待某个事务提交或回滚的争用（contention）情况。 在 Oracle 数据库中，当一个事务想要提交或回滚时，它必须等待其他事务的完成。如果有多个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值