一条简单SQL大量占用临时表空间
本帖最后由 iniestandroid 于 2013-12-26 15:15 编辑Solaris10
11.2.0.1
业务用户临时表空间晚上用完42G的临时文件后报错，脚本监控了当时临时段的使用情况<font size="1">USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
---------- ----- ---------------- ----------- ---------------- ---------- ------------- ---------- --------- --------- ---------- ---------- ---------- ---------- ----------
ARDS ARDS 0000000848DB7B18 23081 00 0 TS_ARDS_TE TEMPORARY SORT 1002 153728 1064 136192 1
MP
ARDS ARDS 000000085CE11BB8 52028 00 0 TS_ARDS_TE TEMPORARY SORT 1002 314752 1388 177664 1
MP
ARDS ARDS 0000000848E69920 29914 00 0 TS_ARDS_TE TEMPORARY SORT 1002 493056 1635 209280 1
MP
ARDS ARDS 0000000850F97F40 5128 00 0 TS_ARDS_TE TEMPORARY SORT 1002 700288 2297 294016 1
MP
ARDS ARDS 000000084912E298 26193 00 0 TS_ARDS_TE TEMPORARY SORT 1002 1946240 3237 414336 1
MP
ARDS ARDS 0000000850EE6138 26457 00 0 TS_ARDS_TE TEMPORARY SORT 1003 61184 4596 588288 2
MP
ARDS ARDS 0000000854EB84A8 <font color="#ff0000">43184 </font>00 0 TS_ARDS_TE TEMPORARY SORT 1002 <font color="#ff0000">4174208 </font> 28559 3655552 1
MP</font>
serial#为43184的会话当时占用了4174208个block 差不多32G，找到了这个SQL<font size="1">SELECT </font>order by 是ETL模块自己加上去的，后来把order by改了但是问题还是存在，执行计划:<font size="1">Plan hash value: 596693200
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4547M(100)| | | |
| 1 | SORT ORDER BY | | 16G| 12T| 4547M (1)|999:59:59 | | |
| 2 | CONCATENATION | | | | | | | |
| 3 | MERGE JOIN CARTESIAN | | 16G| 12T| 567M (1)|999:59:59 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| BJ_ZLCLB | 353K| 281M| 21449 (1)| 00:04:18 | ROWID | ROWID |
| 5 | INDEX RANGE SCAN | IDX_BJ_ZLCLB_06 | 63594 | | 332 (1)| 00:00:04 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | WF_SRC_CTL | 1 | 57 | 1 (0)| 00:00:01 | | |
| 7 | INDEX UNIQUE SCAN | SYS_C0011770 | 1 | | 0 (0)| | | |
| 8 | BUFFER SORT | | 46647 | 592K| 567M (1)|999:59:59 | | |
| 9 | TABLE ACCESS FULL | BJ_ZLCLB_TMP | 46647 | 592K| 1607 (1)| 00:00:20 | | |
| 10 | HASH JOIN | | 16782 | 13M| 10782 (1)| 00:02:10 | | |
| 11 | INDEX FAST FULL SCAN | IDX_BJ_ZLCLB_TMP_06 | 46647 | 592K| 55 (2)| 00:00:01 | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| BJ_ZLCLB | 16782 | 13M| 10726 (1)| 00:02:09 | ROWID | ROWID |
| 13 | INDEX RANGE SCAN | IDX_BJ_ZLCLB_06 | 31797 | | 167 (1)| 00:00:03 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | WF_SRC_CTL | 1 | 57 | 1 (0)| 00:00:01 | | |
| 15 | INDEX UNIQUE SCAN | SYS_C0011770 | 1 | | 0 (0)| | | |
| 16 | TABLE ACCESS BY INDEX ROWID | WF_SRC_CTL | 1 | 53 | 1 (0)| 00:00:01 | | |
| 17 | INDEX UNIQUE SCAN | SYS_C0011770 | 1 | | 0 (0)| | | |
| 18 | TABLE ACCESS BY INDEX ROWID | WF_SRC_CTL | 1 | 53 | 1 (0)| 00:00:01 | | |
| 19 | INDEX UNIQUE SCAN | SYS_C0011770 | 1 | | 0 (0)| | | |
-----------------------------------------------------------------------------------------------------------------------------</font>
这个SQL白天手动执行时间是秒级的，但是晚上15分钟也没跑完，各位大神儿帮忙看看为什么这个SQL会占用这么多的临时段？ SELECT T.ZLLYZH AS ZLLYZH,
T.ZLFLZH AS ZLFLZH,
T.ZLFLJC AS ZLFLJC,
T.ZLDSZH AS ZLDSZH,
T.ZLDSJC AS ZLDSJC,
T.ZLYWLB AS ZLYWLB,
T.ZLYWLM AS ZLYWLM,
T.ZLJYFX AS ZLJYFX,
T.ZLJYFM AS ZLJYFM,
T.ZLSJFS AS ZLSJFS,
T.ZLSJMS AS ZLSJMS,
T.ZLDJFS AS ZLDJFS,
T.ZLDJMS AS ZLDJMS,
T.ZLSJGR AS ZLSJGR,
T.ZLDJGR AS ZLDJGR,
T.ZLSZJE AS ZLSZJE,
T.ZLDZJE AS ZLDZJE,
T.ZLSCJG AS ZLSCJG,
T.ZLDQJG AS ZLDQJG,
T.ZLMEHJ AS ZLMEHJ,
T.ZLZQSM AS ZLZQSM,
T.ZLYWBS AS ZLYWBS,
T.ZLHGLL AS ZLHGLL,
T.ZLGYLL AS ZLGYLL,
T.ZLHTH1 AS ZLHTH1,
T.ZLHTH2 AS ZLHTH2,
T.ZLLXZE AS ZLLXZE,
T.ZLYQTS AS ZLYQTS,
T.ZLFLBZ AS ZLFLBZ,
T.ZLDSBZ AS ZLDSBZ,
T.ZLFBXH AS ZLFBXH,
T.ZLFBDM AS ZLFBDM,
T.ZLFBJC AS ZLFBJC,
T.ZLFBME AS ZLFBME,
T.ZLFBZJ AS ZLFBZJ,
T.ZLDBXH AS ZLDBXH,
T.ZLDBDM AS ZLDBDM,
T.ZLDBJC AS ZLDBJC,
T.ZLDBME AS ZLDBME,
T.ZLDBZJ AS ZLDBZJ,
T.ZLMEXX AS ZLMEXX,
T.ZLZTFS AS ZLZTFS,
T.ZLZCYY AS ZLZCYY,
T.ZLSYBH AS ZLSYBH,
T.ZLZXYY AS ZLZXYY,
T.ZLSYMS AS ZLSYMS,
T.ZLZTSQ AS ZLZTSQ,
T.ZLDYHT AS ZLDYHT,
T.ZLDYZL AS ZLDYZL,
T.ZLXDQR AS ZLXDQR,
T.ZLZRZH AS ZLZRZH,
T.ZLZCZH AS ZLZCZH,
T.ZLCZY AS ZLCZY,
T.ZLFHY AS ZLFHY,
T.ZLQRY AS ZLQRY,
T.ZLZLLY AS ZLZLLY,
T.ZLJSSJ AS ZLJSSJ,
T.ZLFHSJ AS ZLFHSJ,
T.ZLQRSJ AS ZLQRSJ,
T.ZLZT AS ZLZT,
T.ZLFLQR AS ZLFLQR,
T.ZLDSQR AS ZLDSQR,
T.ZLCWCS AS ZLCWCS,
T.ZLBY1 AS ZLBY1,
T.ZLBY2 AS ZLBY2,
T.ZLQLBZ AS ZLQLBZ,
T.ZLGXSJ AS ZLGXSJ,
T.ZLFBGD AS ZLFBGD,
T.ZLSBGD AS ZLSBGD,
T.S_JZSJ AS S_JZSJ,
T.S_SCBS AS S_SCBS,
T.S_YWSJ AS S_YWSJ,
T.ZLBH AS ZLBH
FROM BJ_ZLCLB T, BJ_ZLCLB_TMP T1
WHERE T.ZLBH = T1.ZLBH
AND (T.ZLSJGR >= (SELECT TO_CHAR(BEGIN_DATE, 'YYYYMMDD')
FROM WF_SRC_CTL
WHERE FD_NAME = 'mapping_day_opds_ards'
AND WF_NAME = 'WF_JYZW_ZL1_DAY_OPAR') AND
T.ZLSJGR <= (SELECT TO_CHAR(END_DATE, 'YYYYMMDD')
FROM WF_SRC_CTL
WHERE FD_NAME = 'mapping_day_opds_ards'
AND WF_NAME = 'WF_JYZW_ZL1_DAY_OPAR'))
OR T.ZLSJGR <= (SELECT TO_CHAR(CLEAR_DATE, 'YYYYMMDD')
FROM WF_SRC_CTL
WHERE FD_NAME = 'mapping_day_opds_ards'
AND WF_NAME = 'WF_JYZW_ZL1_DAY_OPAR'
AND CLEAR_FLAG = 1
AND STATUS = 0)
ORDER BY ZLBH,
ZLLYZH,
ZLFLZH,
ZLFLJC,
ZLDSZH,
ZLDSJC,
ZLYWLB,
ZLYWLM,
ZLJYFX,
ZLJYFM,
ZLSJFS,
ZLSJMS,
ZLDJFS,
ZLDJMS,
ZLSJGR,
ZLDJGR,
ZLSZJE,
ZLDZJE,
ZLSCJG,
ZLDQJG,
ZLMEHJ,
ZLZQSM,
ZLYWBS,
ZLHGLL,
ZLGYLL,
ZLHTH1,
ZLHTH2,
ZLLXZE,
ZLYQTS,
ZLFLBZ,
ZLDSBZ,
ZLFBXH,
ZLFBDM,
ZLFBJC,
ZLFBME,
ZLFBZJ,
ZLDBXH,
ZLDBDM,
ZLDBJC,
ZLDBME,
ZLDBZJ,
ZLMEXX,
ZLZTFS,
ZLZCYY,
ZLSYBH,
ZLZXYY,
ZLSYMS,
ZLZTSQ,
ZLDYHT,
ZLDYZL,
ZLXDQR,
ZLZRZH,
ZLZCZH,
ZLCZY,
ZLFHY,
ZLQRY,
ZLZLLY,
ZLJSSJ,
ZLFHSJ,
ZLQRSJ,
ZLZT,
ZLFLQR,
ZLDSQR,
ZLCWCS,
ZLBY1,
ZLBY2,
ZLQLBZ,
ZLGXSJ,
ZLFBGD,
ZLSBGD,
S_JZSJ,
S_SCBS,
S_YWSJ SQL> select count(*) from BJ_ZLCLB;
COUNT(*)
----------
7112940
SQL> select * from user_ind_columns where table_name='BJ_ZLCLB';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------ --------------- ------------- ----------- -------
IDX_BJ_ZLCLB_01 BJ_ZLCLB ZLBH 1 12 12 ASC
IDX_BJ_ZLCLB_01 BJ_ZLCLB S_YWSJ 2 7 0 ASC
IDX_BJ_ZLCLB_02 BJ_ZLCLB S_YWSJ 1 7 0 ASC
IDX_BJ_ZLCLB_06 BJ_ZLCLB ZLSJGR 1 8 8 ASC
IDX_BJ_ZLCLB_06 BJ_ZLCLB ZLBH 2 12 12 ASC
SQL> SELECT COUNT(*) FROM BJ_ZLCLB_TMP;
COUNT(*)
----------
67040
SQL> select * from user_ind_columns where table_name='BJ_ZLCLB_TMP';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------ --------------- ------------- ----------- -------
IDX_BJ_ZLCLB_TMP_01 BJ_ZLCLB_TMP ZLBH 1 12 12 ASC
IDX_BJ_ZLCLB_TMP_01 BJ_ZLCLB_TMP S_YWSJ 2 7 0 ASC
IDX_BJ_ZLCLB_TMP_06 BJ_ZLCLB_TMP ZLSJGR 1 8 8 ASC
IDX_BJ_ZLCLB_TMP_06 BJ_ZLCLB_TMP ZLBH 2 12 12 ASC
SQL> SELECT COUNT(*) FROM WF_SRC_CTL;
COUNT(*)
----------
97
SQL> select * from user_ind_columns where table_name='WF_SRC_CTL';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ ------------ --------------- ------------- ----------- -------
SYS_C0011770 WF_SRC_CTL FD_NAME 1 50 50 ASC
SYS_C0011770 WF_SRC_CTL WF_NAME 2 50 50 ASC
SQL> SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL
WHERE sql_id = '&A'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC;运行上面的脚本 并输入SQL_ID 给出结果 Liu Maclean(刘相兵 发表于 2013-12-26 13:37 static/image/common/back.gif
运行上面的脚本 并输入SQL_ID 给出结果
SOURCE SQL_ID PLAN_HASH_VALUE BEGIN_TIME END_TIME No. of exec LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec
1 1.v$sql实例号:1 08andxfxgpws9 596693200 2013-12-21/02:21:50 在cursor cache中 1 20974 339.14 1000.08319 3795 0
2 4.dba_hist_sqlstat实例号:1 08andxfxgpws9 596693200 2013-12-25 02:00:05 2013-12-25 03:00:19 1 20974 339.14 1000.08319 3795 0
3 4.dba_hist_sqlstat实例号:1 08andxfxgpws9 596693200 2013-12-24 10:01:00 2013-12-24 11:00:03 1 16331 1.64 1.68865 0 66993
4 4.dba_hist_sqlstat实例号:1 08andxfxgpws9 596693200 2013-12-24 02:00:16 2013-12-24 03:00:32 1 8997 275.54 858.940176 90 0
5 4.dba_hist_sqlstat实例号:1 08andxfxgpws9 596693200 2013-12-23 14:00:40 2013-12-23 15:00:44 1 13702 1.72 9.512561 8249 46600
6 4.dba_hist_sqlstat实例号:1 08andxfxgpws9 596693200 2013-12-21 02:00:02 2013-12-21 03:00:16 1 15801 221.42 476.332696 1077 0
就结果看 执行计划都一样 都是596693200
但执行时间有较大的区别 长的时候是1000秒，短的是1秒
逻辑读 和物理读都不高 需要ASH数据
select * from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='08andxfxgpws9';
看以一下sql>@?/rdbms/admin/sqltrpt.sql这个条sql的报告，另外BJ_ZLCLB_TMP这张是不是临时表？ 本帖最后由 iniestandroid 于 2013-12-26 15:17 编辑
Liu Maclean(刘相兵 发表于 2013-12-26 14:45 static/image/common/back.gif
需要ASH数据
不知道会不会跟 MERGE JOIN CARTESIAN 有关，这个SQL的ASH已经传附件了，谢谢ML！
附件放1L了 Derek 发表于 2013-12-26 15:06 static/image/common/back.gif
看以一下sql>@?/rdbms/admin/sqltrpt.sql这个条sql的报告，另外BJ_ZLCLB_TMP这张是不是临时表？ ...
BJ_ZLCLB_TMP 不是临时表，这张表会每天truncate再insert，不明白既然执行计划没变，白天和晚上执行为什么会有这么大的差别 每天truncate 是几点？
显然 晚上仍使用哪个执行计划可能有问题
另 你有128g内存 ，ORACLE SGA+PGA 只用了 6.47%
Begin End
Host Mem (MB): 131,072.0 131,072.0
SGA use (MB): 7,424.0 7,424.0
PGA use (MB): 1,054.5 1,131.5
% Host Mem used for SGA+PGA: 6.47 6.53
PGA用的太少了 iniestandroid 发表于 2013-12-26 15:19 static/image/common/back.gif
BJ_ZLCLB_TMP 不是临时表，这张表会每天truncate再insert，不明白既然执行计划没变，白天和晚上执行为什 ...
@?/rdbms/admin/awrsqrpt.sql 上面打错了，分别上传这条sql白天和晚上的报告，看看 Liu Maclean(刘相兵 发表于 2013-12-26 15:23 static/image/common/back.gif
每天truncate 是几点？
显然 晚上仍使用哪个执行计划可能有问题
truncate和insert操作就在这个这个SQL之前，数据量基本没有变化
机器上有5个库，总共给了差不多90G内存，这个库给了20g memory_target... 1、order by 涉及的字段过多了
2、同样的执行计划 在半天合适 但到了晚上就不合适 这说明数据量还是发生了变化
3、没有充分使用PGA
action plan:
考虑优化该SQL
不建议用 memory target
具体观察数据的变化 Liu Maclean(刘相兵 发表于 2013-12-26 16:01 static/image/common/back.gif
1、order by 涉及的字段过多了
2、同样的执行计划 在半天合适 但到了晚上就不合适 这说明数据量还是发生了 ...
好的，我先从这几个方面优化看看效果，非常感谢ML和各位的帮助，TKS！ 楼主解决完 贴下总结哈 关注中。。。 同关注。 一般有较大数据量变化时要重新收集统计信息
页:
[1]