RAC环境等待事件enq: TS - Contention

prclxzc

Maclean您好：


环境：
OS: 64bit RHEL 5.4
Oracle: 11.2.0.1.0
RAC: 2 nodes


有一句创建中间表的sql语句，涉及到很多表的join，hanging了很长时间，通过EM里面看到主要的等待事件是enq: TS - Contention. AWR的Top 5里面也是该事件排在第一位。我Google了一下， 可以通过下面的语句来释放临时表空间
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
正好看到刘大你的那篇《了解你所不知道的SMON功能(一):清理临时段》的文章里提到了这个方法。但是我执行
select * from dba_segments where segment_type='TEMPORARY';
却没有任何行返回回来，请问这个应该怎么解决？



03:41:00 SQL> select inst_id, tablespace_name TSNAME, TOTAL_EXTENTS TOTAL, USED_EXTENTS USED, FREE_EXTENTS FREE
03:41:16   2  from gv$sort_segment
03:41:25   3  order by 1;

   INST_ID TSNAME                               TOTAL       USED       FREE
---------- ------------------------------- ---------- ---------- ----------
         1 TEMPORARY_DATA                      672234         15     672219
         1 TEMP_TBLSP_GROUP                         0          0          0
         2 TEMPORARY_DATA                      673439     673439          0
         2 TEMP_TBLSP_GROUP                         0          0          0



03:44:56 SQL> select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"
03:45:06   2  from gv$sort_segment
03:45:08   3  order by 1;

   INST_ID TABLESPACE_NAME                  Space(GB)
---------- ------------------------------- ----------
         1 TEMPORARY_DATA                      326.48
         1 TEMP_TBLSP_GROUP                         0
         2 TEMPORARY_DATA                      330.62
         2 TEMP_TBLSP_GROUP                         0



SELECT d.tablespace_name "Name",
                TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
                TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
                TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
                TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
                TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
           FROM sys.dba_tablespaces d,
                (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
          WHERE d.tablespace_name = a.tablespace_name(+)
            AND d.tablespace_name = t.tablespace_name(+)
            AND d.extent_management like 'LOCAL'
            AND d.contents like 'TEMPORARY'

Name                          Size(M)                     HWM(M)          HWM %          Using(M)      Using %
TEMPORARY_DATA          1,345,784.188           662634.000          49.24               15.000           0.00


另附上过去一小时的AWR report

Maclean Liu(刘相兵

oradebug setmypid
@?/rdbms/admin/hangdiag
oradebug tracefile_name

上传生成的TRACE

prclxzc

Maclean Liu(刘相兵 发表于 2013-9-3 16:17
oradebug setmypid
@?/rdbms/admin/hangdiag
oradebug tracefile_name

刘大，刚才在按照你给的方法生成trace，最后那个oradebug dump systemstate 267跑了很长时间，最后出错了，ORA-48113: unable to write to stream file because of out of space condition。我看了一下生成的trace文件有17个G。。。现在应该怎么办？

harryzhang

Elapsed Time (s)        Executions        Elapsed Time per Exec (s)        %Total        %CPU        %IO         SQL Id        SQL Module        SQL Text
47,223.40        0                 99.92        2.70        5.47        0ubpwpxtakuhm         DDTEK ODBC Oracle        create table ZZTWVYXJNGIMD000 ...

Maclean Liu(刘相兵

你是CTAS 且 select 设计到众多group by 操作

考虑以下几个方案：

1、 先创建表 且alter table nologging，之后可以分批 将数据INSERT APPEND 进去
2、 调优你的查询SELECT 语句
3、 考虑并行
4、 使用独立的TEMP TABLESPACE 以避免争用

prclxzc

Maclean Liu(刘相兵 发表于 2013-9-3 22:34
你是CTAS 且 select 设计到众多group by 操作

考虑以下几个方案：

谢谢刘大，这句CTAS的sql跑了一天多了都还没跑出结果，EM里面看到主要的wait_class都是CPU+CPU Wait，从v$session里面可以看到以下这些session都还是active的，这能不能判断出这句sql还在执行？

select SID, SERIAL#, STATUS, SQL_ID, EVENT, P1TEXT, P2TEXT, P3TEXT from v$session where username = 'RETAILMARTNEW';

   SID SERIAL# STATUS  SQL_ID          EVENT                        P1TEXT               P2TEXT     P3TEXT
------ ------- ------- --------------- ---------------------------- -------------------- ---------- ----------------------------------------------------------------
     3     143 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
     4       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
    97     389 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
    98       9 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
    99       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   193      13 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   194       7 ACTIVE  0ubpwpxtakuhm   latch: cache buffers chains  address              number     tries
   195       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   289       5 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   290       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
   291       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   384     101 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   385      35 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
   477      33 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   478      22 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   575      13 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   576       8 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
   667       3 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   668       2 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   763       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   858       2 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   859       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
   953      17 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
   955    2201 ACTIVE  gvx0u675zubn9   SQL*Net message to client    driver id            #bytes
  1049       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1050       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
  1143       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1144       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1239       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1240       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
  1334       3 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1335       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1430      15 ACTIVE  0ubpwpxtakuhm   latch: row cache objects     address              number     tries
  1522    1801 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1523      42 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1619       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1620       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
  1713       7 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1714       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1808   58599 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1809       4 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1904       1 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1905       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  1998       3 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  1999       1 ACTIVE  0ubpwpxtakuhm   kfk: async disk IO           count                intr       timeout
  2092     375 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  2093     261 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries
  2187     361 ACTIVE  0ubpwpxtakuhm   PX Deq: Execution Msg        sleeptime/senderid   passes
  2190       1 ACTIVE  0ubpwpxtakuhm   latch free                   address              number     tries

49 rows selected.

Maclean Liu(刘相兵

11g 你可以用SQL MONITOR

set long 9999999
set linesize 200 pagesize 1400
select dbms_sqltune.report_sql_monitor from dual;