2016-1-8 ORA-1652: unable to extend temp segment by 128 in tablespace解决方案

╰+哭是因爲堅強的太久メ 2022-08-19 08:49 165阅读 0赞

一次ORA-1652的诊断过程，系统不能使用，重启后可以使用。

**weblogic日志：**

\#\#\#\#<2016-1-8 上午09时40分27秒 CST> <Error> <WebLogicServer> <zc-sc-app01> <fwms\_app01\_8003> <\[ACTIVE\] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1452217227682> <BEA-000337> <\[STUCK\] ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "603" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@5d4d331d\[GET /web/defect/extend-attr/5b469596984e4c53ae0f4cdafd3fd39b\]

", which is more than the configured time (StuckThreadMaxTime) of "600" seconds in "server-failure-trigger". Stack trace:  
java.net.SocketInputStream.socketRead0(Native Method)  
java.net.SocketInputStream.read(SocketInputStream.java:152)  
java.net.SocketInputStream.read(SocketInputStream.java:122)  
oracle.net.ns.Packet.receive(Packet.java:300)

oracle.net.ns.DataPacket.receive(DataPacket.java:106)

\#\#\#\#<2016-1-8 上午09时56分53秒 CST> <Error> <WebLogicServer> <zc-tzjh-app1> <IPMS\_APP01\_8101> <\[STANDBY\] ExecuteThread: '114' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1452218213276> <BEA-000337> <\[STUCK\] ExecuteThread: '9' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "607" seconds working on the request "weblogic.servlet.internal.ServletRequestImpl@56d8af13\[  
GET /web/menu\_icon/zongheguanli.png?tSession=1452217537514 HTTP/1.1  
X-Forwarded-For: 10.119.124.109  
Accept: \*/\*  
Referer: http://10.121.180.109/web/app/MyApp.jsp  
Accept-Language: zh-CN  
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; GWX:DOWNLOADED)  
Accept-Encoding: gzip, deflate  
Connection: Keep-Alive  
Cookie: DWRSESSIONID=UmKxc9wQCOJBrOHXa$Zx1oNVu9l; JSESSIONIDMINI=BtcLWPJprp2xpBhqbsgv7NzKmJ8yLZhvxFLpThyGcz50JzGyh4vH!2102636630; ComtopSessionSID=<SNAID>BtcLWPJprp2xpBhqbsgv7NzKmJ8yLZhvxFLpThyGcz50JzGyh4vH!2102636630!1452217406173</SNAID>; cs\_cStorage\_=1  
\]", which is more than the configured time (StuckThreadMaxTime) of "600" seconds. Stack trace:  
weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:385)  
weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:342)  
weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:329)  
weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:417)  
weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:324)   
**alert日志：**  
Fri Jan 08 09:41:39 2016  
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP   
Fri Jan 08 09:41:39 2016  
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP   
Fri Jan 08 10:01:36 2016  
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP   
Fri Jan 08 10:11:35 2016  
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

**AWR:**

<table> 
 <tbody> 
  <tr> 
   <th>&nbsp;</th> 
   <th>Snap Id</th> 
   <th>Snap Time</th> 
   <th>Sessions</th> 
   <th>Cursors/Session</th> 
   <th>Instances</th> 
  </tr> 
  <tr> 
   <td>Begin Snap:</td> 
   <td align="right">5732</td> 
   <td align="middle">08-Jan-16 09:00:48</td> 
   <td align="right">548</td> 
   <td align="right">73.3</td> 
   <td align="right">2</td> 
  </tr> 
  <tr> 
   <td>End Snap:</td> 
   <td align="right">5733</td> 
   <td align="middle">08-Jan-16 10:00:59</td> 
   <td align="right">545</td> 
   <td align="right">73.0</td> 
   <td align="right">2</td> 
  </tr> 
  <tr> 
   <td>Elapsed:</td> 
   <td>&nbsp;</td> 
   <td align="middle">60.18 (mins)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>DB Time:</td> 
   <td>&nbsp;</td> 
   <td align="middle">126.97 (mins)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>Event</th> 
   <th>Waits</th> 
   <th>Total Wait Time (sec)</th> 
   <th>Wait Avg(ms)</th> 
   <th>% DB time</th> 
   <th>Wait Class</th> 
  </tr> 
  <tr> 
   <td>DB CPU</td> 
   <td align="right">&nbsp;</td> 
   <td align="right">6414.3</td> 
   <td align="right">&nbsp;</td> 
   <td align="right">84.2</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>direct path write temp</td> 
   <td align="right">323,000</td> 
   <td align="right">479.8</td> 
   <td align="right">1</td> 
   <td align="right">6.3</td> 
   <td>User I/O</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>Elapsed Time (s)</th> 
   <th>Executions</th> 
   <th>Elapsed Time per Exec (s)</th> 
   <th>%Total</th> 
   <th>%CPU</th> 
   <th>%IO</th> 
   <th>SQL Id</th> 
   <th>SQL Module</th> 
   <th>SQL Text</th> 
  </tr> 
  <tr> 
   <td align="right">3,579.67</td> 
   <td align="right">1,666</td> 
   <td align="right">2.15</td> 
   <td align="right">46.99</td> 
   <td align="right">99.76</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">ga37qxa3p485g</a></td> 
   <td>JDBC Thin Client</td> 
   <td>select tddakentit0_.ID as ID66...</td> 
  </tr> 
  <tr> 
   <td align="right">2,917.88</td> 
   <td align="right">6</td> 
   <td align="right">486.31</td> 
   <td align="right">38.30</td> 
   <td align="right">81.96</td> 
   <td align="right">16.41</td> 
   <td><a rel="nofollow">dxcdycgfr1w5n</a></td> 
   <td>&nbsp;</td> 
   <td>select nvl(round(avg(ctime), 2...</td> 
  </tr> 
  <tr> 
   <td align="right">190.66</td> 
   <td align="right">6</td> 
   <td align="right">31.78</td> 
   <td align="right">2.50</td> 
   <td align="right">14.15</td> 
   <td align="right">85.29</td> 
   <td><a rel="nofollow">8yavyxsts8hu9</a></td> 
   <td>&nbsp;</td> 
   <td>select a.tablespace_name table...</td> 
  </tr> 
  <tr> 
   <td align="right">28.21</td> 
   <td align="right">6</td> 
   <td align="right">4.70</td> 
   <td align="right">0.37</td> 
   <td align="right">99.74</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">brb01q8dygvy6</a></td> 
   <td>&nbsp;</td> 
   <td>select SQL_TEXT d_maxtime_sql ...</td> 
  </tr> 
  <tr> 
   <td align="right">16.66</td> 
   <td align="right">6</td> 
   <td align="right">2.78</td> 
   <td align="right">0.22</td> 
   <td align="right">99.74</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">45b35s83zwmah</a></td> 
   <td>&nbsp;</td> 
   <td>select round(max(ELAPSED_TIME)...</td> 
  </tr> 
  <tr> 
   <td align="right">11.57</td> 
   <td align="right">2</td> 
   <td align="right">5.79</td> 
   <td align="right">0.15</td> 
   <td align="right">58.51</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">4x91zq0fkd05y</a></td> 
   <td>&nbsp;</td> 
   <td>DECLARE job BINARY_INTEGER := ...</td> 
  </tr> 
  <tr> 
   <td align="right">11.57</td> 
   <td align="right">6</td> 
   <td align="right">1.93</td> 
   <td align="right">0.15</td> 
   <td align="right">58.51</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">4snzdp176n9wv</a></td> 
   <td>&nbsp;</td> 
   <td>CALL C_DATA_SYNCH_MAI...</td> 
  </tr> 
  <tr> 
   <td align="right">11.34</td> 
   <td align="right">2</td> 
   <td align="right">5.67</td> 
   <td align="right">0.15</td> 
   <td align="right">59.11</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">5k565njgcd272</a></td> 
   <td>&nbsp;</td> 
   <td>CALL G_DATA_SYNCH('MM...</td> 
  </tr> 
  <tr> 
   <td align="right">7.74</td> 
   <td align="right">246</td> 
   <td align="right">0.03</td> 
   <td align="right">0.10</td> 
   <td align="right">97.86</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">2h1xrpzcygsdz</a></td> 
   <td>JDBC Thin Client</td> 
   <td>select distinct p.portlet_id, ...</td> 
  </tr> 
  <tr> 
   <td align="right">6.70</td> 
   <td align="right">1,729</td> 
   <td align="right">0.00</td> 
   <td align="right">0.09</td> 
   <td align="right">87.11</td> 
   <td align="right">0.00</td> 
   <td><a rel="nofollow">dfb1dmsvm3nx8</a></td> 
   <td>JDBC Thin Client</td> 
   <td>select m.media_id, m.storage_p...</td> 
  </tr> 
 </tbody> 
</table>

检查temp表空间select tablespace\_name,file\_name,bytes/1024/1024 file\_size,autoextensible from dba\_temp\_files;

发现有32G，且是自动扩展的。

select sql\_id, count(\*) from gv$session where event = 'direct path write temp'  
 group by sql\_id  order by count(\*) desc;

\--查询无数据  
   
select sql\_id, count(\*) from dba\_hist\_active\_sess\_history  
 where event = 'direct path write temp'  
 group by sql\_id  order by count(\*) desc;

SQL\_ID          COUNT(\*)  
\------------- ----------

dxcdycgfr1w5n   71739  
3v1z2vmmzadm4 16  
bmndrgc7xkmjb 12  
3mnjv5g06mv0x 7  
看sql\_id为dxcdycgfr1w5n就是AWR中那条执行慢的SQL

<table> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><span style="font-family:SimSun">dxcdycgfr1w5n</span></td> 
   <td>select nvl(round(avg(ctime), 2), 0) d_lock_wait_time from v$lock where REQUEST &gt; 0</td> 
  </tr> 
 </tbody> 
</table>

先看下这条SQL的执行计划：

select nvl(round(avg(ctime),2),0) d\_lock\_wait\_time from v$lock where  
REQUEST > 0  
Plan hash value: 2399206389  
\---------------------------------------------------------------------------  
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)|  
\---------------------------------------------------------------------------  
|   0 | SELECT STATEMENT        |            |       |       |     1 (100)|  
|   1 |  SORT AGGREGATE         |            |     1 |    63 |            |  
|\*  2 |   HASH JOIN             |            |     1 |    63 |     0   (0)|  
|   3 |    MERGE JOIN CARTESIAN |            |   100 |  3800 |     0   (0)|  
|\*  4 |     FIXED TABLE FULL    | X$KSUSE    |     1 |    19 |     0   (0)|  
|   5 |     BUFFER SORT         |            |   100 |  1900 |     0   (0)|  
|   6 |      FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)|  
|   7 |    VIEW                 | GV$\_LOCK   |    10 |   250 |     0   (0)|  
|   8 |     UNION-ALL           |            |       |       |            |  
|\*  9 |      FILTER             |            |       |       |            |  
|  10 |       VIEW              | GV$\_LOCK1  |     2 |    50 |     0   (0)|  
|  11 |        UNION-ALL        |            |       |       |            |  
|\* 12 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    77 |     0   (0)|  
|\* 13 |         FIXED TABLE FULL| X$KSQEQ    |     1 |    77 |     0   (0)|  
|\* 14 |      FIXED TABLE FULL   | X$KTADM    |     1 |    77 |     0   (0)|  
|\* 15 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    77 |     0   (0)|  
|\* 16 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    77 |     0   (0)|  
|\* 17 |      FIXED TABLE FULL   | X$KTATL    |     1 |    77 |     0   (0)|  
|\* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    77 |     0   (0)|  
|\* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    77 |     0   (0)|  
|\* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    77 |     0   (0)|  
|\* 21 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    77 |     0   (0)|

\---------------------------------------------------------------------------

实际测试：

SQL> select nvl(round(avg(ctime), 2), 0) d\_lock\_wait\_time from v$lock where REQUEST > 0;  
  select nvl(round(avg(ctime), 2), 0) d\_lock\_wait\_time from v$lock where REQUEST > 0  
  
ERROR at line 1:  
ORA-01652: unable to extend tem segment by 128 in tablespace TEMP  
**Elapsed:00:07:50.70  **

SQL>exec dbms\_stats.gather\_fixed\_objects\_stats();

再次执行只需要0.3s，执行计划不是笛卡尔积了。

对于RAC的情况下，两个节点都要收集，不然，还有问题。