1.聯通現場狀態
時間：2012年8月17日
資料庫版本：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
作業系統平臺：HP-UX
告警日誌： more alert_mdsoss.log

2.定位問題
報錯現象：
Fri Aug 17 13:37:39 EAT 2012
ORA-1652: unable to extend temp segment by 128 in tablespace MDSTEMP 顯示不能擴充套件臨時段，說明臨時表空間已經被使用滿了，空間不夠。

說明：從metalink上官方解釋，沒有更多的空閒區分給這個臨時段了，可以給表空間新增資料檔案的方式來解決此問題，表面上是這樣，我們更加的深入瞭解，是什麼原因導致的臨時段沒有空間了呢，我們都知道臨時段是記錄排序和資料遷移的，現在深層次問題不是空間不夠，過一會再執行sql可能就不報錯了。是sql語句不夠最佳化。因為當sql在批次DML操作的時候，會突發性佔用大量臨時空間排序，就會報臨時段不夠用，新資料此時不能入庫！過一會空間釋放後又可以入庫了，要想解決此問題就需要sql最佳化。
The below is from metalink:
Error: ORA-1652
Text: unable to extend temp segment by %s in tablespace %s

------- -----------------------------------------------------------------------

Cause: Failed to allocate an extent for temp segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

files to the tablespace indicated or create the object in another

tablespace.

select * from gnwebbrw12081720; 此時是有資料的，說明空間已經釋放了

col file_name for a35
select file_name,file_id,bytes/1024/1024,status,autoextensible TABLESPACE_NAME from DBA_TEMP_FILES;

FILE_NAME                              FILE_ID BYTES/1024/1024 STATUS    TAB
----------------------------------- ---------- --------------- --------- ---
/oradata/mdsoss/temp01.dbf        1           24671          AVAILABLE   YES
/oradata/mdsoss/mdstmp.dbf       2           20000         AVAILABLE NO    MDSTEMP 不是自動擴充套件，如果是就沒有上述問題了，但我們不建議使用資料檔案自動擴充套件功能，不容易監控。看 24G + 20G 空間是沒有問題的，一般都是sql寫的不夠好導致不必要排序。

3.解決方案
（1）重啟例項，7*24 重啟例項 smon程式可以釋放sort段，但我們的庫是不能down的
（2）增加資料檔案，我的空間很緊張，不可以
（3）配置合理sort_area大小已經配置完畢了，現PGA 4G sort_area_size 208M
（4）sql optimization 最佳方案

（5）總結哪些操作會導致臨時表空間暴漲呢
什麼操作在使用temp
- 索引建立或重建.
- ORDER BY or GROUP BY
- DISTINCT 操作.
- UNION & INTERSECT & MINUS
- Sort-Merge joins.
- Analyze 操作
- 有些異常將會引起temp暴漲
當處理以上操作時候呢，dba需要加倍關注temp使用情況？我們現在來看看誰使用這些臨時段。

（5）臨時表空間使用情況
select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME     CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------- ------------- ------------ ----------- -----------
TEMP                            1      3157760         128     3157632
MDSTEMP                        24      2559872     2337152      222720      已經使用了92%

（6）誰在使用這些sort段
select username,session_addr,sqladdr,sqlhash from v$sort_usage;
USERNAME                       SESSION_ADDR     SQLADDR             SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS                         C0000008483ECFB8 C0000008512150B8 3342809064
SABOCOUSR                   C00000084B405E50 C00000033F867510 141205382
MDSOSS                         C00000084740E988 C0000008508AB1C0 409467952
MDSOSS                         C0000008483DE390 C00000033B8914F0 2951877480
MDSOSS                         C00000084A404460 C0000003404007A0 2584373469
MDSOSS                         C0000008483F5088 C00000033FA63E18 2245874020
MDSOSS                         C0000008483FFC48 C00000084D5B5F98 3000467390
MDSOSS                         C0000008483F5088 C00000033FA63E18 2245874020
MDSOSS                         C000000852404A60 C00000084DD6F598 1491833069
MDSOSS                         C0000008483EBA40 C00000084DE28990 1530468420
MDSOSS                         C0000008483FD158 C00000084E13A648 3459409074
MDSOSS                         C000000852404A60 C00000084DD6F598 1491833069
MDSOSS                         C000000852404A60 C00000084DD6F598 1491833069
MDSOSS                         C000000852404A60 C00000084DD6F598 1491833069
MDSOSS                         C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS                         C0000008474068B8 C00000084CCC7790 2982079417
MDSOSS                         C0000008494501E0 C00000085098B400 3836056470
MDSOSS                         C00000084B3E5B10 C00000084DA9B990 719858768
MDSOSS                         C0000008523F3348 C00000084D96F830 2434343698
MDSOSS                         C00000084740D410 C0000008174B3540 2103182003

USERNAME                       SESSION_ADDR     SQLADDR             SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS                         C00000084A3FD908 C00000084C944888 3846639713
MDSOSS                         C0000008523D5AF8 C00000084DC6BB30 3158920754
MDSOSS                         C0000008483FA668 C00000032FCDF3D8 1691040305
MDSOSS                         C00000084943BFD8 C00000084CE4CA70 2036150049
MDSOSS                         C00000084A4170F0 C00000084D75FF68 2058192145
MDSOSS                         C0000008483CF768 C00000084DC096B8 3375505524
MDSOSS                         C000000849452CD0 C00000031E5A32F8 346930689
MDSOSS                         C000000849433F08 C000000340106C48 2344782277
MDSOSS                         C0000008484067A0 C000000344414D88 4097260861
MDSOSS                         C00000084B3C57D0 C000000850BF8528 3690121153
MDSOSS                         C00000084740D410 C0000008174B3540 2103182003
MDSOSS                         C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS                         C0000008483EBA40 C00000084DE28990 1530468420
MDSOSS                         C0000008483FD158 C00000084E13A648 3459409074
MDSOSS                         C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS                         C0000008523F73B0 C00000034046B670 3944536657
MDSOSS                         C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS                         C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS                         C0000008473E3A88 C00000084F13BD98 1450489357
MDSOSS                         C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS                         C0000008473E2510 C0000008518A3DA0 2446627624

USERNAME                       SESSION_ADDR     SQLADDR             SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS                         C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS                         C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS                         C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS                         C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS                         C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS                         C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS                         C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS                         C00000084941A720 C00000084F2EB838 1495689429
MDSOSS                         C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS                         C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS                         C00000084941A720 C00000084F2EB838 1495689429
MDSOSS                         C00000084941A720 C00000084F2EB838 1495689429
MDSOSS                         C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS                         C00000084941A720 C00000084F2EB838 1495689429
MDSOSS                         C00000084941A720 C00000084F2EB838 1495689429
MDSOSS                         C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS                         C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS                         C00000084A3F8328 C000000851A28740   87302580
MDSOSS                         C00000084A3F8328 C000000851A28740   87302580
MDSOSS                         C00000084A3F8328 C000000851A28740   87302580

61 rows selected

此時我們知道MDSOSS使用者的sql是導致臨時段爆滿的罪魁禍首

（7）找出哪些SQL語句在使用sort段，利用多表關聯查詢
select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v

$parameter p,v$session se,v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and

s.address=su.sqladdr order by se.username,se.sid;

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT

MDSOSS 840 1 1048576 MDSTEMP DATA insert into tmp1768202 select * from

TPA_F_EMAIL_SMTP_SUM_5 where first_result
MDSOSS 840 1 1048576 MDSTEMP INDEX insert into tmp1768202 select * from

TPA_F_EMAIL_SMTP_SUM_5 where first_result
MDSOSS 840 1 1048576 MDSTEMP DATA insert into tmp1768202 select * from

TPA_F_EMAIL_SMTP_SUM_5 where first_result
MDSOSS 877 14 14680064 MDSTEMP DATA CREATE GLOBAL TEMPORARY TABLE tmp708304 AS SELECT * from

TPA_S_SP_SUM_5 where
MDSOSS 879 21 22020096 MDSTEMP DATA insert into tmp521803

(ne_id,ne_type,first_result,sum_level,compress_date,regio
MDSOSS 879 1 1048576 MDSTEMP DATA insert into tmp521803

(ne_id,ne_type,first_result,sum_level,compress_date,regio
MDSOSS 879 1 1048576 MDSTEMP INDEX insert into tmp521803

(ne_id,ne_type,first_result,sum_level,compress_date,regio
MDSOSS 879 1 1048576 MDSTEMP DATA insert into tmp521803

(ne_id,ne_type,first_result,sum_level,compress_date,regio
MDSOSS 922 389 407896064 MDSTEMP DATA insert into tmp2489701 select * from GNWEBBRW12081720 s

where capturetime >= to
MDSOSS 946 6 6291456 MDSTEMP DATA select * from dual

MDSOSS 948 417 437256192 MDSTEMP DATA insert into tmp2462001 SELECT s.*,decode(m.flag,1,0,1)

result1_flag FROM HTTP_
MDSOSS 1028 1 1048576 MDSTEMP DATA CREATE GLOBAL TEMPORARY TABLE tmp1804004 AS SELECT * from

TPA_S_SP_SUM_5 where
MDSOSS 1050 28 29360128 MDSTEMP DATA insert into tmp2407302 select substr(imei,1,8)

imei,useragent brand from GnWebb
MDSOSS 1050 434 455081984 MDSTEMP DATA insert into tmp2407302 select substr(imei,1,8)

imei,useragent brand from GnWebb
MDSOSS 1066 1 1048576 MDSTEMP INDEX update tmp1737203 t set apn= nvl(apn,'-1'),lac= nvl

(lac,'-1'),ci= nvl(ci,'-1')
MDSOSS 1066 2 2097152 MDSTEMP DATA update tmp1737203 t set apn= nvl(apn,'-1'),lac= nvl

(lac,'-1'),ci= nvl(ci,'-1')
MDSOSS 1066 1 1048576 MDSTEMP DATA update tmp1737203 t set apn= nvl(apn,'-1'),lac= nvl

(lac,'-1'),ci= nvl(ci,'-1')
MDSOSS 1066 2 2097152 MDSTEMP DATA update tmp1737203 t set apn= nvl(apn,'-1'),lac= nvl

(lac,'-1'),ci= nvl(ci,'-1')

MDSOSS 1066 1 1048576 MDSTEMP INDEX update tmp1737203 t set apn= nvl(apn,'-1'),lac= nvl

(lac,'-1'),ci= nvl(ci,'-1')
MDSOSS 1074 1 1048576 MDSTEMP DATA insert into tmp2336801 SELECT s.*,decode(m.flag,1,0,1)

result1_flag FROM WAP_R
MDSOSS 1074 34 35651584 MDSTEMP DATA insert into tmp2336801 SELECT s.*,decode(m.flag,1,0,1)

result1_flag FROM WAP_R
MDSOSS 1086 4 4194304 MDSTEMP DATA insert into tmp1261403

(ne_id,ne_type,first_result,sum_level,compress_date,regi
MDSOSS 1086 4 4194304 MDSTEMP DATA insert into tmp1261403

(ne_id,ne_type,first_result,sum_level,compress_date,regi
MDSOSS 1086 2 2097152 MDSTEMP INDEX insert into tmp1261403

(ne_id,ne_type,first_result,sum_level,compress_date,regi
MDSOSS 1086 900 943718400 MDSTEMP DATA insert into tmp1261403

(ne_id,ne_type,first_result,sum_level,compress_date,regi

我把佔用sort段空間最多的幾個SQL列舉出來，請看SID：922 948 434 會話ID 不外乎都與gnwebbrw http XDR資料有關，當用gnweb資料插入臨時表（資料分析）時產生了大量的排序從而佔用大量排序區，此時我們要分析了這種排序是否是有必要的。

（8）排序區分配
排序區域的分配 - 專用伺服器分配sort area，排序區域在PGA！ - 共享伺服器分配sort area，排序區域在UGA. (UGA在shared_pool中分配).
我們採用的全是dedicated server 模式，pga_aggregate_target引數決定sort_area的大於,這時sort_area應該是pga總記憶體的5%，我們PGA=4G，

sort_area=4G*5%=204.8M 和我們從spotlight上監控的結果一樣。

小結,如何從根本上降低臨時表空間的膨脹呢？方法有2個：
1 設定合理的pga或sort_area_size
2 最佳化引起disk sort的sql語句

最後我把發現佔用sort段較多的process kill掉，來臨時緩解排序段的使用，提交給研發，調整SQL

select sid,paddr from v$session where sid=1177; 查詢程式地址

select p.spid,se.sid,se.username,se.machine from v$sort_usage su,v$process p,v$session se,v$sql s where se.paddr='C0000008482BF9B0' and p.ADDR='C0000008482BF9B0'; 透過程式地址查詢程式號，kill 程式

] kill -9 29944
] ps -ef | grep 15072
oracle 10587 8420 1 22:58:58 pts/tb    0:00 grep 15072
oracle 15072     1 250 20:40:04 ?        127:20 oraclemdsoss (LOCAL=NO)
] kill -9 15072
] ps -ef | grep 23259
oracle 11625 8420 1 23:02:02 pts/tb    0:00 grep 23259
oracle 23259     1 253 21:00:15 ?        53:31 oraclemdsoss (LOCAL=NO)
] kill -9 23259

Leonarding
2012.8.17
天津&autumn
分享技術~收穫快樂
Blog：http://space.itpub.net/26686207

ORA-1652 臨時表空間滿了導致新會話資料不能入庫診斷案例

相關文章