临时表空间最优大小问题

ShineCQY

问题前言：我想诊断一个关于临时表空间老是报出空间不足问题，老大的action plan是做一个error statck。但是客户不让随便运行导致临时表空间不足的存储过程，只能使用JOB 早上7点运行。
  我的想法是这样：修改下存储过程
create or replace procedure test01 as
test varchar2(100);
begin
execute immediate q'[alter session set tracefile_identifier = errorstackfile]';
execute immediate q'[alter session set events='1438 trace name errorstack forever,level 10']';
select * into test from dual; --执行的SQL语句
execute immediate q'[alter session set tracefile_identifier = errorstack]';
end;
不知道这个方法能不能达到目的？

问题：临时表空间不足的问题已经报错过3次，客户也烦了，前两次都是同事添加5G的数据文件，目前已经达到40G，占用临时表空间主要是distinct 和group by 以及Union all (表的数据量蛮大的单个表的排序可以耗用4G左右的临时表空间）。
问题是不能一直增加数据文件，但又担心临时表空间会不足，怎么确定最优的临时表空间大小？

代码见附件
希望热心的老大以及网友提提意见

Maclean Liu(刘相兵

运行一下 SQL，并给出结果

1. REM SORT ACTIVITY
   
2. 
   
3. set linesize 150 pagesize 1400;
   
4. 
   
5.     SELECT d.tablespace_name "Name",
   
6.                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
   
7.                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
   
8.                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
   
9.                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
   
10.                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
    
11.            FROM sys.dba_tablespaces d,
    
12.                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
    
13.                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
    
14.           WHERE d.tablespace_name = a.tablespace_name(+)
    
15.             AND d.tablespace_name = t.tablespace_name(+)
    
16.             AND d.extent_management like 'LOCAL'
    
17.             AND d.contents like 'TEMPORARY'
    
18. /
    
19. 
    
20. alter session set nls_date_format='dd-mon-yy';
    
21. set lines 160 pages 1000 echo off feedback off
    
22. col stat_name for a25
    
23. col date_time for a40
    
24. col BEGIN_INTERVAL_TIME for a20
    
25. col END_INTERVAL_TIME for a20
    
26. prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
    
27. 
    
28. WITH sysstat AS
    
29. (select sn.begin_interval_time begin_interval_time,
    
30.          sn.end_interval_time end_interval_time,
    
31.          ss.stat_name stat_name,
    
32.          ss.value e_value,
    
33.          lag(ss.value, 1) over(order by ss.snap_id) b_value
    
34.     from dba_hist_sysstat ss, dba_hist_snapshot sn
    
35.    where trunc(sn.begin_interval_time) >= sysdate-7
    
36.      and ss.snap_id = sn.snap_id
    
37.      and ss.dbid = sn.dbid
    
38.      and ss.instance_number = sn.instance_number
    
39.      and ss.dbid = (select dbid from v$database)
    
40.      and ss.instance_number = (select instance_number from v$instance)
    
41.      and ss.stat_name = 'sorts (disk)')
    
42. select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
    
43. stat_name,
    
44. round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
    
45. + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
    
46. + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
    
47. from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
    
48. /
    
49. 
    
50. select temp_space/1024/1024,SQL_ID  from DBA_HIST_SQL_PLAN where temp_space>0 order by 1 asc;
    
51. 
    
52. --For 8.1.7 to 9.2:

复制代码

ShineCQY

Maclean Liu(刘相兵 发表于 2013-2-18 15:03
运行一下 SQL，并给出结果

回老大：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as datacore@10.24.128.80

SQL>

Name                           Size (M)        HWM (M)       HWM %   Using (M)     Using %
------------------------------ --------------- ------------- ------- ------------- -------
TEMP                                 1,024.000        12.000    1.17          .000    0.00
UPRR_TEMP                           41,600.000     26165.000   62.90          .000    0.00

Session altered
Cannot SET LINES
"Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."

DATE_TIME                                STAT_NAME                    PER_SEC
---------------------------------------- ------------------------- ----------
02/17/13_06_00_07_00                     sorts (disk)                       0

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
   0.992774963378906 16ba97fyd3hfr
    1.04713439941406 2h18c838s8hb4
    1.15680694580078 67kxduz2mgkpu
    1.16443634033203 g5m0bnvyy37b1
    1.16443634033203 g5m0bnvyy37b1
    1.35993957519531 86wgmf9b6vjna
    1.45339965820313 as5mspaqs5bzp
    1.57833099365234 1v2y10pvdsjb2
     1.6021728515625 7b2wwfb7wwru7
    1.75857543945313 20x3w1r9rqa21
    1.85203552246094 3j1yrf7qqr8rb
    1.89876556396484 19urtpq6bg9xn
    1.99222564697266 2h18c838s8hb4
    2.08663940429688 d3xdy4pvzpv0v
    2.17247009277344 f0wnvfw5x89n8
    2.17247009277344 f0wnvfw5x89n8
    2.17247009277344 22kmhbskja8v7
    2.17247009277344 22kmhbskja8v7
    2.17247009277344 22kmhbskja8v7
    2.26593017578125 dvx12aaaqrkqq

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
    2.31266021728516 1atp3cjw0f2j3
    2.35939025878906 fprqvdfdg1c8g
    2.35939025878906 958k7h53hmnjs
    2.35939025878906 bfaucrspyst4v
    2.38323211669922 aqgrgavwc5x8q
    2.39849090576172 7b2wwfb7wwru7
    2.87532806396484 8n58uzssgttyk
    2.87532806396484 8n58uzssgttyk
    2.87628173828125 dff0qfp1mrzsq
    2.89154052734375 79gdj3amhss5f
    2.97737121582031 0anxvs0g87x7n
    2.98500061035156 a17tfh5kz2vc6
    3.16524505615234 5zcca8buuqfk3
    3.16524505615234 as5mspaqs5bzp
    3.16524505615234 19urtpq6bg9xn
    3.16524505615234 3j1yrf7qqr8rb
       3.23486328125 d3xdy4pvzpv0v
    3.28922271728516 7b2wwfb7wwru7
    3.46946716308594 708wj1nqfmywg
    3.53145599365234 20x3w1r9rqa21
    3.60965728759766 1atp3cjw0f2j3

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
    3.75080108642578 f0wnvfw5x89n8
    3.75080108642578 f0wnvfw5x89n8
    3.91483306884766 5qf3bp4kp9xvw
    4.52423095703125 6j9jwrbh3z51b
    4.53948974609375 1sdbtkgqg32tm
    4.56333160400391 dazfmxvc7uw5c
    4.65679168701172 7j8dj9aypp66y
    4.66442108154297 7b2wwfb7wwru7
     5.8441162109375 d3xdy4pvzpv0v
    5.95378875732422 f2v33zqq8zjaa
    6.00051879882813 8n58uzssgttyk
    6.96945190429688 f0wnvfw5x89n8
     7.4615478515625 f0wnvfw5x89n8
    7.51590728759766 f2v33zqq8zjaa
    7.51590728759766 f2v33zqq8zjaa
    7.57122039794922 1sdbtkgqg32tm
    7.61032104492188 f0wnvfw5x89n8
    8.46958160400391 f0wnvfw5x89n8
    8.91494750976563 g5m0bnvyy37b1
    8.98456573486328 1atp3cjw0f2j3
    9.04750823974609 79urjnasyhf71

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
    9.07039642333984 1sdbtkgqg32tm
    9.07039642333984 1sdbtkgqg32tm
    9.31262969970703 9nzb7a4pxykna
    9.31262969970703 69mnq4jf15tyd
    9.31262969970703 7ctcrwh6uv2c2
    9.31262969970703 czvw3dax6j0wm
    9.31262969970703 gvd487c022sfv
    9.58633422851563 64dtubcq1y4c3
    10.1022720336914 6v8gq8q0fwc1h
    10.1022720336914 7975jcrn9v7j5
    10.1022720336914 6zxqh7wrfwcnp
    10.1022720336914 7qj2x8yqkfvqx
    10.4923248291016 fcgwjs1212jjc
    11.3677978515625 493vxq4t9vaaa
    11.3677978515625 64dtubcq1y4c3
    11.3677978515625 a90vrsvmqtc1w
    11.3677978515625 4zrrgg52zydsn
    11.9924545288086 8n58uzssgttyk
    12.5160217285156 bffbx7rr5k6th
    13.5631561279297 bn4b3vjw2mj3u
    13.6098861694336 bn4b3vjw2mj3u

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
    18.0940628051758 4r0m311k8ta7c
    20.9770202636719 fcgwjs1212jjc
    23.7512588500977 f0wnvfw5x89n8
    23.7512588500977 f0wnvfw5x89n8
    23.7817764282227 48hzggh7ptv1j
    23.7817764282227 48hzggh7ptv1j
    23.9067077636719 f0wnvfw5x89n8
    23.9067077636719 22kmhbskja8v7
    23.9067077636719 22kmhbskja8v7
    24.2891311645508 64dtubcq1y4c3
    24.2891311645508 64dtubcq1y4c3
    25.3524780273438 4rhnxzmu87t9r
    25.3524780273438 4rhnxzmu87t9r
    26.6256332397461 awv06a0pydwmu
    27.1177291870117 f0wnvfw5x89n8
    28.6331176757813 9htg1rk788dr2
    28.7351608276367 avrafc206tyvs
    28.7351608276367 6ahf2k7s2337w
    31.8441390991211 awv06a0pydwmu
    33.2508087158203 gxrp1rw44f5v2
    33.2813262939453 gxrp1rw44f5v2

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
    34.3360900878906 dvx12aaaqrkqq
    34.3360900878906 6j9jwrbh3z51b
    34.3360900878906 4r0m311k8ta7c
    34.3360900878906 6ahf2k7s2337w
    34.3360900878906 4dg9jn9n1j3a3
    34.3360900878906 avrafc206tyvs
    34.3360900878906 79urjnasyhf71
    34.3360900878906 gkx50v44q1drf
    35.7503890991211 awv06a0pydwmu
     38.578987121582 avrafc206tyvs
     38.578987121582 6ahf2k7s2337w
     38.578987121582 gkx50v44q1drf
     38.578987121582 4r0m311k8ta7c
     38.578987121582 79urjnasyhf71
     38.578987121582 4dg9jn9n1j3a3
     38.578987121582 dvx12aaaqrkqq
     38.578987121582 6j9jwrbh3z51b
    47.7581024169922 48hzggh7ptv1j
    50.9147644042969 4rhnxzmu87t9r
    87.3050689697266 f0wnvfw5x89n8
    87.3050689697266 f0wnvfw5x89n8

TEMP_SPACE/1024/1024 SQL_ID
-------------------- -------------
         129.8828125 64dtubcq1y4c3
     444.89860534668 7f4qavcb4qv93

127 rows selected

SQL>

Maclean Liu(刘相兵

execute immediate q'[alter session set events='1438 trace name errorstack forever,level 10']';?


为什么是1438？

oracle@localhost:~$ oerr ora 1438
01438, 00000, "value larger than specified precision allowed for this column"
// *Cause: When inserting or updating records, a numeric value was entered
//         that exceeded the precision defined for the column.
// *Action: Enter a value that complies with the numeric column's precision,
//          or use the MODIFY option with the ALTER TABLE command to expand
//          the precision.


UPRR_TEMP                           41,600.000     26165.000   62.90          .000    0.00

这个临时空间 高水位只到过 62.9%， 为什么你说总是temp不足？

你temp不足的时候到底报了什么错误？

ShineCQY

Maclean Liu(刘相兵 发表于 2013-2-18 16:03
execute immediate q'[alter session set events='1438 trace name errorstack forever,level 10']';?

错误代码1438只是测试时候用的，生产环境中的存储过程中已经改成6512，但不知道能不能抓取到错误？
错误日志信息：执行存储过程ACC_ETL_DM_BASE_CHANGES(2013-02-16)失败.使用的数据源是：udl,调用存储过程失败 ... call ACC_ETL_DM_BASE_CHANGES(?) class java.sql.SQLException:ORA-01652: 无法通过 128 (在表空间 UPRR_TEMP 中) 扩展 temp 段 ORA-06512: 在 "DATACORE.ACC_ETL_DM_BASE_CHANGES", line 33 请检查该数据源配置或确认该存储过程定义是否正确
alert.log 中也是ORA-06512错误信息不多。

ShineCQY

附件中是errorstatck ,在测试环境中同样的存储过程 报空间不足，SQL语句问题的可能性比较大，还请老大鉴定

Maclean Liu(刘相兵

思路1：

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)
----- Error Stack Dump -----
ORA-01652: 无法通过 128 (在表空间 UPRR_TEMP 中) 扩展 temp 段
ORA-06512: 在 "DATACORE.ACC_ETL_DM_BASE_CHANGES", line 48
----- Current SQL Statement for this session (sql_id=81jm7baz8m9un) ----


DATACORE.ACC_ETL_DM_BASE_CHANGES的 第48行是什么SQL?

Maclean Liu(刘相兵

思路2
重新触发 ORA-1652并立即查询下面的SQL:

1. REM SORT ACTIVITY
   
2. 
   
3. set linesize 150 pagesize 1400;
   
4. 
   
5.     SELECT d.tablespace_name "Name",
   
6.                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
   
7.                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
   
8.                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
   
9.                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
   
10.                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
    
11.            FROM sys.dba_tablespaces d,
    
12.                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
    
13.                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
    
14.           WHERE d.tablespace_name = a.tablespace_name(+)
    
15.             AND d.tablespace_name = t.tablespace_name(+)
    
16.             AND d.extent_management like 'LOCAL'
    
17.             AND d.contents like 'TEMPORARY'
    
18. /
    
19. 
    
20. --For 10.1 and above:
    
21. 
    
22. SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
    
23. FROM v$session a, v$tempseg_usage b, v$sqlarea c
    
24. WHERE a.saddr = b.session_addr
    
25. AND c.address= a.sql_address
    
26. AND c.hash_value = a.sql_hash_value
    
27. ORDER BY b.tablespace, b.blocks;
    
28. 
    
29. SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
    
30. P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    
31. COUNT(*) statements
    
32. FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
    
33. WHERE T.session_addr = S.saddr
    
34. AND S.paddr = P.addr
    
35. AND T.tablespace = TBS.tablespace_name
    
36. GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
    
37. P.program, TBS.block_size, T.tablespace
    
38. ORDER BY sid_serial;
    
39. 
    
40. SELECT *
    
41.   FROM (SELECT matching_criteria,
    
42.                TO_CHAR(force_matching_signature) force_matching_signature,
    
43.                sql_id,
    
44.                child_number,
    
45.                sql_text,
    
46.                executions,
    
47.                elapsed_time / 1000,
    
48.                operation_type,
    
49.                policy,
    
50.                estimated_optimal_size,
    
51.                last_memory_used,
    
52.                last_execution,
    
53.                active_time / 1000,
    
54.                num_sort_operations,
    
55.                tot_active_time / 1000,
    
56.                tot_optimal_executions,
    
57.                tot_onepass_executions,
    
58.                tot_multipasses_executions,
    
59.                all_tot_active_time / 1000,
    
60.                max_tempseg_size,
    
61.                parsing_schema_name
    
62.           FROM (SELECT force_matching_signature,
    
63.                        sql_id,
    
64.                        child_number,
    
65.                        sql_text,
    
66.                        matching_criteria,
    
67.                        SUM(executions) OVER(PARTITION BY matching_criteria) executions,
    
68.                        SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time,
    
69.                        operation_type,
    
70.                        policy,
    
71.                        estimated_optimal_size,
    
72.                        last_memory_used,
    
73.                        last_execution,
    
74.                        active_time,
    
75.                        num_sort_operations,
    
76.                        SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time,
    
77.                        SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions,
    
78.                        SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions,
    
79.                        SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions,
    
80.                        MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size,
    
81.                        SUM(tot_active_time) OVER() all_tot_active_time,
    
82.                        ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum,
    
83.                        parsing_schema_name
    
84.                   FROM (SELECT s.sql_id,
    
85.                                s.child_number,
    
86.                                s.sql_text,
    
87.                                s.executions,
    
88.                                s.elapsed_time,
    
89.                                s.force_matching_signature,
    
90.                                CASE
    
91.                                  WHEN s.force_matching_signature > 0 THEN
    
92.                                   TO_CHAR(s.force_matching_signature)
    
93.                                  ELSE
    
94.                                   s.sql_id
    
95.                                END matching_criteria,
    
96.                                ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum,
    
97.                                sw.operation_type,
    
98.                                sw.policy,
    
99.                                sw.estimated_optimal_size,
    
100.                                sw.last_memory_used,
     
101.                                sw.last_execution,
     
102.                                MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size,
     
103.                                sw.active_time * sw.total_executions active_time,
     
104.                                SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time,
     
105.                                COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations,
     
106.                                SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions,
     
107.                                SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions,
     
108.                                SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions,
     
109.                                NVL(u.username, s.parsing_schema_name) parsing_schema_name
     
110.                           FROM v$sql s, v$sql_workarea sw, all_users u
     
111.                          WHERE sw.sql_id = s.sql_id
     
112.                            AND sw.child_number = s.child_number
     
113.                            AND u.user_id(+) = s.parsing_user_id)
     
114.                  WHERE rnum = 1)
     
115.          WHERE rnum = 1
     
116.          ORDER BY tot_multipasses_executions DESC,
     
117.                   tot_onepass_executions     DESC,
     
118.                   last_memory_used           DESC)
     
119. WHERE ROWNUM <= 200
     
120. /
     
121. 
     
122. SELECT *
     
123.   FROM (SELECT s.sid,
     
124.                s.machine,
     
125.                s.program,
     
126.                s.module,
     
127.                s.osuser,
     
128.                NVL(DECODE(TYPE,
     
129.                           'BACKGROUND',
     
130.                           'SYS (' || b.ksbdpnam || ')',
     
131.                           s.username),
     
132.                    SUBSTR(p.program, INSTR(p.program, '('))) username,
     
133.                NVL(SUM(CASE
     
134.                          WHEN sn.name = 'sorts (memory)' THEN
     
135.                           ss.VALUE
     
136.                          ELSE
     
137.                           0
     
138.                        END),
     
139.                    0) sorts_memory,
     
140.                NVL(SUM(CASE
     
141.                          WHEN sn.name = 'sorts (disk)' THEN
     
142.                           ss.VALUE
     
143.                          ELSE
     
144.                           0
     
145.                        END),
     
146.                    0) sorts_disk,
     
147.                NVL(SUM(CASE
     
148.                          WHEN sn.name = 'sorts (rows)' THEN
     
149.                           ss.VALUE
     
150.                          ELSE
     
151.                           0
     
152.                        END),
     
153.                    0) sorts_rows,
     
154.                NVL(SUM(CASE
     
155.                          WHEN sn.name = 'physical reads direct temporary tablespace' THEN
     
156.                           ss.VALUE
     
157.                          ELSE
     
158.                           0
     
159.                        END),
     
160.                    0) reads_direct_temp,
     
161.                NVL(SUM(CASE
     
162.                          WHEN sn.name = 'physical writes direct temporary tablespace' THEN
     
163.                           ss.VALUE
     
164.                          ELSE
     
165.                           0
     
166.                        END),
     
167.                    0) writes_direct_temp,
     
168.                NVL(SUM(CASE
     
169.                          WHEN sn.name = 'workarea executions - optimal' THEN
     
170.                           ss.VALUE
     
171.                          ELSE
     
172.                           0
     
173.                        END),
     
174.                    0) workarea_exec_optimal,
     
175.                NVL(SUM(CASE
     
176.                          WHEN sn.name = 'workarea executions - onepass' THEN
     
177.                           ss.VALUE
     
178.                          ELSE
     
179.                           0
     
180.                        END),
     
181.                    0) workarea_exec_onepass,
     
182.                NVL(SUM(CASE
     
183.                          WHEN sn.name = 'workarea executions - multipass' THEN
     
184.                           ss.VALUE
     
185.                          ELSE
     
186.                           0
     
187.                        END),
     
188.                    0) workarea_exec_multipass
     
189.           FROM v$session  s,
     
190.                v$sesstat  ss,
     
191.                v$statname sn,
     
192.                v$process  p,
     
193.                x$ksbdp    b
     
194.          WHERE s.paddr = p.addr
     
195.            AND b.inst_id(+) = USERENV('INSTANCE')
     
196.            AND p.addr = b.ksbdppro(+)
     
197.            AND s.TYPE = 'USER'
     
198.            AND s.sid = ss.sid
     
199.            AND ss.statistic# = sn.statistic#
     
200.            AND sn.name IN ('sorts (memory)',
     
201.                            'sorts (disk)',
     
202.                            'sorts (rows)',
     
203.                            'physical reads direct temporary tablespace',
     
204.                            'physical writes direct temporary tablespace',
     
205.                            'workarea executions - optimal',
     
206.                            'workarea executions - onepass',
     
207.                            'workarea executions - multipass')
     
208.          GROUP BY s.sid,
     
209.                   s.machine,
     
210.                   s.program,
     
211.                   s.module,
     
212.                   s.osuser,
     
213.                   NVL(DECODE(TYPE,
     
214.                              'BACKGROUND',
     
215.                              'SYS (' || b.ksbdpnam || ')',
     
216.                              s.username),
     
217.                       SUBSTR(p.program, INSTR(p.program, '(')))
     
218.          ORDER BY workarea_exec_multipass DESC,
     
219.                   workarea_exec_onepass DESC,
     
220.                   reads_direct_temp + writes_direct_temp DESC,
     
221.                   sorts_rows DESC)
     
222. WHERE ROWNUM <= 200
     
223. /
     
224. 
     
225. SELECT rawtohex(workarea_address) workarea_address,
     
226.        sql_id,
     
227.        sql_text,
     
228.        operation_type,
     
229.        policy,
     
230.        sid,
     
231.        active_time,
     
232.        work_area_size,
     
233.        expected_size,
     
234.        actual_mem_used,
     
235.        max_mem_used,
     
236.        number_passes,
     
237.        tempseg_size,
     
238.        tablespace,
     
239.        complete_ratio,
     
240.        elapsed,
     
241.        time_remaining,
     
242.        opname,
     
243.        machine,
     
244.        program,
     
245.        module,
     
246.        osuser,
     
247.        username
     
248.   FROM (SELECT swa.workarea_address,
     
249.                swa.sql_id,
     
250.                sa.sql_text,
     
251.                swa.operation_type,
     
252.                swa.policy,
     
253.                swa.sid,
     
254.                swa.active_time / 1000 active_time,
     
255.                swa.work_area_size,
     
256.                swa.expected_size,
     
257.                swa.actual_mem_used,
     
258.                swa.max_mem_used,
     
259.                swa.number_passes,
     
260.                swa.tempseg_size,
     
261.                swa.tablespace,
     
262.                (CASE
     
263.                  WHEN sl.totalwork <> 0 THEN
     
264.                   sl.sofar / sl.totalwork
     
265.                  ELSE
     
266.                   NULL
     
267.                END) complete_ratio,
     
268.                sl.elapsed_seconds * 1000 elapsed,
     
269.                sl.time_remaining * 1000 time_remaining,
     
270.                sl.opname,
     
271.                s.machine,
     
272.                s.program,
     
273.                s.module,
     
274.                s.osuser,
     
275.                NVL(DECODE(TYPE,
     
276.                           'BACKGROUND',
     
277.                           'SYS (' || b.ksbdpnam || ')',
     
278.                           s.username),
     
279.                    SUBSTR(p.program, INSTR(p.program, '('))) username,
     
280.                ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
     
281.           FROM v$sql_workarea_active swa,
     
282.                v$sqlarea sa,
     
283.                (SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
     
284.                v$session s,
     
285.                v$process p,
     
286.                x$ksbdp b
     
287.          WHERE sl.sid(+) = swa.sid
     
288.            AND sl.sql_id(+) = swa.sql_id
     
289.            AND swa.sid <> USERENV('sid')
     
290.            AND sa.sql_id = swa.sql_id
     
291.            AND s.sid = swa.sid
     
292.            AND s.paddr = p.addr
     
293.            AND b.inst_id(+) = USERENV('INSTANCE')
     
294.            AND p.addr = b.ksbdppro(+)
     
295.          ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
     
296. WHERE rnum = 1
     
297. /

复制代码

ShineCQY

Maclean Liu(刘相兵 发表于 2013-2-18 22:23
思路1：

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)

昨天在测试库上发现了同样的问题，晚上9点多找到了原因：是一个SQL引起的 select 'A',
           d.explanation, --金融机构标识码
           c.account_no, --交易账号
           to_date(a.batchentrydate, 'yyyy-mm-dd'), --发生日期
           c.currencycode, --币种
           SUM(decode(A.Creditdebit, 'C', a.transactionamount, 0)), --当日贷方发生额
           SUM(decode(A.Creditdebit, 'D', a.transactionamount, 0)), --当日借方发生额
           case
             when C.Currencycode = 'JPY' Then
              Round(c.Ccyledgerbalance, 0)
             else
              c.ccyledgerbalance
           End Balance, --账户余额
           --b.instcode instcode, --系统虚拟机构代号
           1 datastatus, --前台对应的数据状态
           c.account_no || c.currencycode || '2013-01-04',
           to_date('2013-01-04', 'yyyy-mm-dd')
      from df_cust C
      left join (select distinct ACCOUNTBRANCH,
                                 DESCRIPTION,
                                 MASTERNO,
                                 CURRENCYCODE,
                                 ACCOUNT_NUMBER,
                                 SEQNO,
                                 ACCT_CLASS_CODE,
                                 PRODUCTCODE,
                                 VALUEDT_YYYY,
                                 VALUEDT_MM,
                                 VALUEDT_DD,
                                 BATCHENTRYDATE,
                                 VALUEDT_YYYYMMDD,
                                 NARRATIONPOST,
                                 TRANSACTIONAMOUNT,
                                 CREDITDEBIT,
                                 ACCOUNTBRANCH1,
                                 SEGMENTCODE,
                                 REFERENCENUMBER,
                                 NARRATIONTRAN,
                                 BATCHNUMBER,
                                 GLDEPTID,
                                 ARMCODE,
                                 EXTREFNO,
                                 MAKERID,
                                 CHECKERID,
                                 CHANNELID,
                                 TRANSACTION_AMT_IN_USD,
                                 ACCSHORTNAME,
                                 ARMNAME,
                                 SEGNAME,
                                 TXNCODE,
                                 REVERSALFLAG,
                                 EBBSREFERENCE,
                                 TRANSTYPECODE,
                                 CUSTOMERRATE,
                                 ADVTREASURYFLAG,
                                 VA_FLAG
                   from df_acmov_today
                  where Creditdebit in ('C', 'D')) a on a.account_number =
                                                        c.account_no
   
      Left Join Da_Mid_Acc_Gl_Dic D On D.Source = A.Accountbranch
   
     Where exists (select 1
              from acc.t_base_account b
             where b.account = c.account_no
               and b.currence_code = c.currencycode)
       and a.account_number is not null
       and c.account_no like '0%'
     group by d.explanation, --金融机构标识码
              c.account_no, --交易账号
              a.batchentrydate, --发生日期
              c.currencycode, --币种
              C.Ccyledgerbalance--系统机构代号
单独运行此SQL 临时表空间耗用 100%左右。但是郁闷的是，今天早上 准备调优SQL时，发现此问题消失了。我把AWR报告传上来。

ShineCQY

18518-18520 昨天出问题时。18531-18532今天早上的状况。很郁闷

ShineCQY

问题原因找到：表df_acmov_today，在执行计划中基数不对，导致执行计划错误，从而造成临时表空间爆掉。 使用crontab 在凌晨2:30对表做分析，但是早上6点。其他任务对表做了，truncate 和Insert into 从而导致改原因。谢谢老大和网友们的热心帮助！！！