ADG切换后temp异常增长问题

来源：这里教程网时间：2026-03-03 19:58:32 作者：

1. 背景概述

问题描述：晋商新核心ADG切换演练之后，TEMP表空间异常增长至满

2. 故障分析

2.1. 故障现象

这时肯定有程序进程抓着temp 不放，让我们细找分析一下

SQL> set lines 200 pages 999

SQL> select * from (

select count(*), event,sql_id,program,inst_id

from gv$active_session_history

where sample_time > sysdate - 5/1440

group by event,sql_id,program,inst_id

order by 1 desc

) where rownum <21; 2 3 4 5 6 7

COUNT(*) EVENT SQL_ID PROGRAM INST_ID

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

193 8a584b77rtadj oracle@hxdba02 (PPA7) 2

148 8a584b77rtadj oracle@hxdba01 (PPA7) 1

=================SQL=================================

/* 格式化对象 2023/5/24 10:14:38 (QP5 v5.287) */

SELECT /*+LEADING(@"SEL$F3900594" "R"@"SEL$4" "GV$LOCK"@"SEL$19" "GV$_LOCK"@"SEL$5" "S"@"SEL$4")*/

MAX (h.CTIME) ACQUIRED_LOCK_TIME

FROM v$lock h

WHERE h.CTIME > 60

AND h.TYPE IN ('TX', 'TM')

AND NOT EXISTS

(SELECT NULL

FROM gv$lock b

WHERE b.request > 1

AND h.TYPE = b.TYPE

AND h.id1 = b.id1

AND h.id2 = b.id2)

SQL> set lines 500 pages 999

select plan_table_output from table(dbms_xplan.display_cursor('&sql_id'));SQL>

Enter value for sql_id: 8a584b77rtadj

old 1: select plan_table_output from table(dbms_xplan.display_cursor('&sql_id'))

new 1: select plan_table_output from table(dbms_xplan.display_cursor('8a584b77rtadj'))

PLAN_TABLE_OUTPUT

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

SQL_ID 8a584b77rtadj, child number 0

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

SELECT /*+LEADING(@"SEL$F3900594" "R"@"SEL$4" "GV$LOCK"@"SEL$19"

"GV$_LOCK"@"SEL$5" "S"@"SEL$4")*/ max(h.CTIME)

ACQUIRED_LOCK_TIME FROM v$lock h WHERE h.CTIME> 60

AND h.type IN ('TX', 'TM') AND NOT

EXISTS (SELECT NULL FROM gv$lock b

WHERE b.request>1 AND h.type=b.type AND

h.id1=b.id1 AND h.id2=b.id2)

Plan hash value: 4116497937

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| | | |

| 1 | SORT AGGREGATE | | 1 | 100 | | | | |

|* 2 | HASH JOIN | | 1 | 100 | 0 (0)| | | |

|* 3 | HASH JOIN | | 1 | 88 | 0 (0)| | | |

|* 4 | HASH JOIN ANTI | | 1 | 63 | 0 (0)| | | |

|* 5 | FIXED TABLE FULL | X$KSQRS | 1 | 21 | 0 (0)| | | |

|* 6 | PX COORDINATOR | | | | | | | |

| 7 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 55 | 0 (0)| Q1,00 | P->S | QC (RAND) |

|* 9 | HASH JOIN | | 1 | 55 | 0 (0)| Q1,00 | PCWP | |

|* 10 | HASH JOIN | | 1 | 34 | 0 (0)| Q1,00 | PCWP | |

| 11 | VIEW | GV$_LOCK | 10 | 250 | 0 (0)| Q1,00 | PCWP | |

| 12 | UNION-ALL | | | | | Q1,00 | PCWP | |

|* 13 | FILTER | | | | | Q1,00 | PCWP | |

| 14 | VIEW | GV$_LOCK1 | 2 | 50 | 0 (0)| Q1,00 | PCWP | |

| 15 | UNION-ALL | | | | | Q1,00 | PCWP | |

|* 16 | FIXED TABLE FULL| X$KDNSSF | 1 | 15 | 0 (0)| Q1,00 | PCWP | |

|* 17 | FIXED TABLE FULL| X$KSQEQ | 1 | 17 | 0 (0)| Q1,00 | PCWP | |

|* 18 | FIXED TABLE FULL | X$KTADM | 1 | 15 | 0 (0)| Q1,00 | PCWP | |

|* 19 | FIXED TABLE FULL | X$KTATRFIL | 1 | 13 | 0 (0)| Q1,00 | PCWP | |

|* 20 | FIXED TABLE FULL | X$KTATRFSL | 1 | 13 | 0 (0)| Q1,00 | PCWP | |

|* 21 | FIXED TABLE FULL | X$KTATL | 1 | 13 | 0 (0)| Q1,00 | PCWP | |

|* 22 | FIXED TABLE FULL | X$KTSTUSC | 1 | 13 | 0 (0)| Q1,00 | PCWP | |

|* 23 | FIXED TABLE FULL | X$KTSTUSS | 1 | 21 | 0 (0)| Q1,00 | PCWP | |

|* 24 | FIXED TABLE FULL | X$KTSTUSG | 1 | 13 | 0 (0)| Q1,00 | PCWP | |

|* 25 | FIXED TABLE FULL | X$KTCXB | 1 | 24 | 0 (0)| Q1,00 | PCWP | |

| 26 | FIXED TABLE FULL | X$KSUSE | 100 | 900 | 0 (0)| Q1,00 | PCWP | |

|* 27 | FIXED TABLE FULL | X$KSQRS | 1 | 21 | 0 (0)| Q1,00 | PCWP | |

| 28 | VIEW | GV$_LOCK | 11 | 275 | 0 (0)| | | |

| 29 | UNION-ALL | | | | | | | |

|* 30 | FILTER | | | | | | | |

| 31 | VIEW | GV$_LOCK1 | 2 | 50 | 0 (0)| | | |

| 32 | UNION-ALL | | | | | | | |

|* 33 | FIXED TABLE FULL | X$KDNSSF | 1 | 18 | 0 (0)| | | |

|* 34 | FIXED TABLE FULL | X$KSQEQ | 1 | 20 | 0 (0)| | | |

|* 35 | FIXED TABLE FULL | X$KTADM | 1 | 18 | 0 (0)| | | |

|* 36 | FIXED TABLE FULL | X$KTATRFIL | 1 | 15 | 0 (0)| | | |

|* 37 | FIXED TABLE FULL | X$KTATRFSL | 1 | 15 | 0 (0)| | | |

|* 38 | FIXED TABLE FULL | X$KTATL | 1 | 15 | 0 (0)| | | |

|* 39 | FIXED TABLE FULL | X$KTSTUSC | 1 | 15 | 0 (0)| | | |

|* 40 | FIXED TABLE FULL | X$KTSTUSS | 2 | 48 | 0 (0)| | | |

|* 41 | FIXED TABLE FULL | X$KTSTUSG | 1 | 15 | 0 (0)| | | |

|* 42 | FIXED TABLE FULL | X$KTCXB | 1 | 26 | 0 (0)| | | |

|* 43 | FIXED TABLE FULL | X$KSUSE | 100 | 1200 | 0 (0)| | | |

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

Predicate Information (identified by operation id):

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

2 - access("SADDR"="S"."ADDR")

3 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADD

R"))

4 - access("R"."KSQRSIDT"="TYPE" AND "R"."KSQRSID1"="ID1" AND "R"."KSQRSID2"="ID2")

5 - filter(("R"."KSQRSIDT"='TM' OR "R"."KSQRSIDT"='TX'))

6 - filter(("REQUEST">1 AND INTERNAL_FUNCTION("TYPE")))

8 - filter(("REQUEST">1 AND INTERNAL_FUNCTION("TYPE")))

9 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADD

R"))

10 - access("SADDR"="S"."ADDR")

13 - filter(USERENV('INSTANCE') IS NOT NULL)

16 - filter(("KSQLKREQ">1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND