用gv$tempseg_usage查询此SQL分配临时段达30G，但是此字典表V$RMAN_BACKUP_JOB_DETAILS只有20多条数据。
请问为何为占用如此多的临时表空间，导致日志经常报错ORA-1652。
SQL_ID ac00gajgz0v7b, child number 0
-------------------------------------
SELECT TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS'),
ROUND((SYSDATE-END_TIME)*24,2), INPUT_BYTES, SESSION_KEY,
SESSION_RECID, SESSION_STAMP FROM (SELECT END_TIME, INPUT_BYTES,
SESSION_KEY, SESSION_RECID, SESSION_STAMP FROM
V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS LIKE 'COMPLETED%' AND
INPUT_TYPE='DB INCR' AND OUTPUT_DEVICE_TYPE IN ('SBT_TAPE', '*') ORDER
BY END_TIME DESC) WHERE ROWNUM = 1
Plan hash value: 2247927750
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | | | | |
|* 3 | SORT ORDER BY STOPKEY | | 0 (0)| | | |
| 4 | VIEW | | | | | |
| 5 | SORT UNIQUE | | | | | |
|* 6 | VIEW | | | | | |
| 7 | WINDOW SORT | | 0 (0)| | | |
| 8 | MERGE JOIN OUTER | | | | | |
| 9 | SORT JOIN | | | | | |
| 10 | VIEW | | | | | |
| 11 | SORT UNIQUE | | | | | |
| 12 | WINDOW SORT | | 0 (0)| | | |
| 13 | WINDOW SORT | | 0 (0)| | | |
| 14 | MERGE JOIN | | | | | |
| 15 | MERGE JOIN | | | | | |
| 16 | SORT JOIN | | | | | |
|* 17 | FILTER | | | | | |
| 18 | MERGE JOIN OUTER | | | | | |
| 19 | SORT JOIN | | | | | |
| 20 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 21 | SORT JOIN | | | | | |
| 22 | PX COORDINATOR | | | | | |
| 23 | PX SEND QC (RANDOM) | :TQ10000 | | Q1,00 | P->S | QC (RAND) |
| 24 | VIEW | GV$RMAN_STATUS_CURRENT | | Q1,00 | PCWP | |
|* 25 | FIXED TABLE FULL | X$KRBMRST | | Q1,00 | PCWP | |
|* 26 | SORT JOIN | | | | | |
| 27 | VIEW | | | | | |
| 28 | SORT UNIQUE | | | | | |
| 29 | MERGE JOIN OUTER | | | | | |
| 30 | SORT JOIN | | | | | |
| 31 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 32 | SORT JOIN | | | | | |
| 33 | VIEW | | | | | |
| 34 | WINDOW SORT | | 0 (0)| | | |
|* 35 | PX COORDINATOR | | | | | |
| 36 | PX SEND QC (RANDOM)| :TQ20000 | | Q2,00 | P->S | QC (RAND) |
|* 37 | VIEW | GV$KSFQP | | Q2,00 | PCWP | |
|* 38 | FIXED TABLE FULL | X$KSFQP | | Q2,00 | PCWP | |
|* 39 | SORT JOIN | | | | | |
| 40 | VIEW | | | | | |
| 41 | SORT GROUP BY | | | | | |
| 42 | MERGE JOIN OUTER | | | | | |
| 43 | SORT JOIN | | | | | |
| 44 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 45 | SORT JOIN | | | | | |
| 46 | VIEW | | | | | |
| 47 | SORT GROUP BY | | | | | |
| 48 | PX COORDINATOR | | | | | |
| 49 | PX SEND QC (RANDOM) | :TQ30000 | | Q3,00 | P->S | QC (RAND) |
| 50 | VIEW | GV$KSFQP | | Q3,00 | PCWP | |
| 51 | FIXED TABLE FULL | X$KSFQP | | Q3,00 | PCWP | |
|* 52 | SORT JOIN | | | | | |
| 53 | VIEW | | | | | |
| 54 | SORT GROUP BY | | | | | |
| 55 | MERGE JOIN | | | | | |
| 56 | MERGE JOIN | | | | | |
| 57 | SORT JOIN | | | | | |
|* 58 | FILTER | | | | | |
| 59 | MERGE JOIN OUTER | | | | | |
| 60 | SORT JOIN | | | | | |
| 61 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 62 | SORT JOIN | | | | | |
| 63 | PX COORDINATOR | | | | | |
| 64 | PX SEND QC (RANDOM) | :TQ40000 | | Q4,00 | P->S | QC (RAND) |
| 65 | VIEW | GV$RMAN_STATUS_CURRENT | | Q4,00 | PCWP | |
|* 66 | FIXED TABLE FULL | X$KRBMRST | | Q4,00 | PCWP | |
|* 67 | SORT JOIN | | | | | |
| 68 | VIEW | | | | | |
| 69 | SORT UNIQUE | | | | | |
| 70 | MERGE JOIN OUTER | | | | | |
| 71 | SORT JOIN | | | | | |
| 72 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 73 | SORT JOIN | | | | | |
| 74 | VIEW | | | | | |
| 75 | WINDOW SORT | | 0 (0)| | | |
|* 76 | PX COORDINATOR | | | | | |
| 77 | PX SEND QC (RANDOM) | :TQ50000 | | Q5,00 | P->S | QC (RAND) |
|* 78 | VIEW | GV$KSFQP | | Q5,00 | PCWP | |
|* 79 | FIXED TABLE FULL | X$KSFQP | | Q5,00 | PCWP | |
|* 80 | SORT JOIN | | | | | |
| 81 | VIEW | | | | | |
| 82 | SORT GROUP BY | | | | | |
| 83 | MERGE JOIN OUTER | | | | | |
| 84 | SORT JOIN | | | | | |
| 85 | FIXED TABLE FULL | X$KCCRSR | | | | |
|* 86 | SORT JOIN | | | | | |
| 87 | VIEW | | | | | |
| 88 | SORT GROUP BY | | | | | |
| 89 | PX COORDINATOR | | | | | |
| 90 | PX SEND QC (RANDOM) | :TQ60000 | | Q6,00 | P->S | QC (RAND) |
| 91 | VIEW | GV$KSFQP | | Q6,00 | PCWP | |
| 92 | FIXED TABLE FULL | X$KSFQP | | Q6,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
6 - filter((("A"."OUTPUT_DEVICE_TYPE"='SBT_TAPE' OR "A"."OUTPUT_DEVICE_TYPE"='*') AND
DECODE("OBJECT_TYPE_WEIGHT",9,'DB FULL',8,'RECVR AREA',7,'DB INCR',6,'DATAFILE FULL',5,'DATAFILE
INCR',4,'ARCHIVELOG',3,'CONTROLFILE',2,'SPFILE',1,'BACKUPSET',NULL)='DB INCR' AND
DECODE("STATUS_WEIGHT",2000,'FAILED',1900,'RUNNING WITH ERRORS',1500,'RUNNING WITH
WARNINGS',1001,'RUNNING',900,'COMPLETED WITH ERRORS',500,'COMPLETED WITH WARNINGS',1,'COMPLETED','FAILED')
LIKE 'COMPLETED%'))
17 - filter((NVL("GV$RMAN_STATUS_CURRENT"."ROW_LEVEL","R2"."RSRLV")=1 AND
NVL("GV$RMAN_STATUS_CURRENT"."OPERATION",UPPER("R2"."RSROP")) LIKE 'BACKUP%'))
21 - access("R2"."RSRRID"="GV$RMAN_STATUS_CURRENT"."RECID" AND
"R2"."RSRTST"="GV$RMAN_STATUS_CURRENT"."STAMP")
filter(("R2"."RSRTST"="GV$RMAN_STATUS_CURRENT"."STAMP" AND
"R2"."RSRRID"="GV$RMAN_STATUS_CURRENT"."RECID"))
25 - filter(("STATUS_KRBMRST"=1 OR "STATUS_KRBMRST"=9 OR "STATUS_KRBMRST"=17 OR "STATUS_KRBMRST"=25))
26 - access("ODEV"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID") AND
"ODEV"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST"))
filter(("ODEV"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST") AND
"ODEV"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID")))
32 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
filter(("R"."RSRTST"="RS"."RMAN_STATUS_STAMP" AND "R"."RSRRID"="RS"."RMAN_STATUS_RECID"))
35 - filter("TYPE"=2)
37 - filter("TYPE"=2)
38 - filter("TYPE"=2)
39 - access("HH"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID") AND
"HH"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST"))
filter(("HH"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST") AND
"HH"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID")))
45 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
filter(("R"."RSRTST"="RS"."RMAN_STATUS_STAMP" AND "R"."RSRRID"="RS"."RMAN_STATUS_RECID"))
52 - access("A"."SESSION_RECID"="B"."SESSION_RECID" AND "A"."SESSION_STAMP"="B"."SESSION_STAMP")
filter(("A"."SESSION_STAMP"="B"."SESSION_STAMP" AND "A"."SESSION_RECID"="B"."SESSION_RECID"))
58 - filter((NVL("GV$RMAN_STATUS_CURRENT"."ROW_LEVEL","R2"."RSRLV")>1 AND
NVL("GV$RMAN_STATUS_CURRENT"."OPERATION",UPPER("R2"."RSROP")) LIKE '%AUTOBACKUP%'))
62 - access("R2"."RSRRID"="GV$RMAN_STATUS_CURRENT"."RECID" AND
"R2"."RSRTST"="GV$RMAN_STATUS_CURRENT"."STAMP")
filter(("R2"."RSRTST"="GV$RMAN_STATUS_CURRENT"."STAMP" AND
"R2"."RSRRID"="GV$RMAN_STATUS_CURRENT"."RECID"))
66 - filter(("STATUS_KRBMRST"=1 OR "STATUS_KRBMRST"=9 OR "STATUS_KRBMRST"=17 OR "STATUS_KRBMRST"=25))
67 - access("ODEV"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID") AND
"ODEV"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST"))
filter(("ODEV"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST") AND
"ODEV"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID")))
73 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
filter(("R"."RSRTST"="RS"."RMAN_STATUS_STAMP" AND "R"."RSRRID"="RS"."RMAN_STATUS_RECID"))
76 - filter("TYPE"=2)
78 - filter("TYPE"=2)
79 - filter("TYPE"=2)
80 - access("HH"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID") AND
"HH"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST"))
filter(("HH"."STAMP"=NVL("GV$RMAN_STATUS_CURRENT"."STAMP","R2"."RSRTST") AND
"HH"."RECID"=NVL("GV$RMAN_STATUS_CURRENT"."RECID","R2"."RSRRID")))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
86 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND "R"."RSRTST"="RS"."RMAN_STATUS_STAMP")
filter(("R"."RSRTST"="RS"."RMAN_STATUS_STAMP" AND "R"."RSRRID"="RS"."RMAN_STATUS_RECID"))
Queries from V$BACKUP_PIECE_DETAILS or V$RMAN_STATUS Return Error ORA-01652 (文档 ID 2119607.1)
貌似是这个
评论
有用 0
