Hello Listers, Its version 11.2.0.4 of oracle exadata. And we are facing
an issue in which a reporting query(part of plsql procedure) which normally
finishes within ~1-2minutes runs for ~1-2hrs at times. This happens when
the same query is submitted from 5-6 multiple sessions at the same time and
is accessing the same customer data. When we kill them and rerun them in
serial they run fine without any issue and finish in the same 1-2 minutes
duration.
Few things we observed is , when all the session submitted at same time and
the query runs long , the event its showing for the session is "latch:
cache buffers chains" but active session history is not showing up any
significant activity for that session and also the sql monitor is not
getting logged for that query. Which means it's not doing significant
activity while this issue occurs but kind of stuck. Why is it so? And also
due to that , I am not able to capture the current object on which it's
actually holding that latch.
The query is an INSERT query which inserts data into a global temporary
table. It has ~17 UNION clauses of which most look similar. So i am
wondering if by someway we can rewrite this query which will help us in
fixing this issue or making the situation better?
Attached is the sample INSERT query with UNION clauses(I have removed a few
of the UNIONS to make it look simple) and its plan which suffers from
"latch: cache buffers chains".
Regards
Pap
INSERT INTO GTT1
SELECT ...
FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) <= 1
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR) FROM TAD)
GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT
UNION
SELECT ..
FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 1
AND (TRUNC ( :B2) - ADT) <= 2
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR) FROM TAD)
GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT
UNION
SELECT ...
FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 2
AND (TRUNC ( :B2) - ADT) <= 3
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR) FROM TAD)
GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT
UNION
SELECT ....
FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 3
AND (TRUNC ( :B2) - ADT) <= 4
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR)
FROM TAD)
GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT
UNION
SELECT ....
FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 4
AND (TRUNC ( :B2) - ADT) <= 9
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR)
FROM TAD)
GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT
UNION
SELECT...
FROM TAD TAD,
TMMC TMMC,
TMTD TMTD,
TMCT TMCT,
TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 9
AND (TRUNC ( :B2) - ADT) <= 14
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR)
FROM TAD)
GROUP BY TMTD.PMENT,
TAD.MCD,
TMMC.PMCDVAL,
TMCT.PDCMLPNT
........
................
...........
UNION
SELECT ....
FROM TAD TAD,
TMMC TMMC,
TMTD TMTD,
TMCT TMCT,
TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 60
AND (TRUNC ( :B2) - ADT) <= 90
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR)
FROM TAD)
GROUP BY TMTD.PMENT,
TAD.MCD,
TMMC.PMCDVAL,
TMCT.PDCMLPNT
UNION
SELECT ....
FROM TAD TAD,
TMMC TMMC,
TMTD TMTD,
TMCT TMCT,
TNI TNI
WHERE TAD.MCD = TMMC.PTMCD
AND TMTD.PMENT = TAD.PMENT
AND TMTD.CID = TMCT.SWCTID
AND TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
AND (TRUNC ( :B2) - ADT) > 90
AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
AND TAD.DT_CR IN (SELECT MAX (DT_CR)
FROM TAD)
GROUP BY TMTD.PMENT,
TAD.MCD,
TMMC.PMCDVAL,
TMCT.PDCMLPNT
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| | 9361 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | |
| | | |
| 2 | SORT UNIQUE | |
18 | 1566 | 9361 (1)| 00:01:53 |
| 3 | UNION-ALL | |
| | | |
| 4 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 5 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 6 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 7 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 8 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 9 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 12 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 14 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 15 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 16 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | |
1 | 8 | | |
| 18 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 19 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 20 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 21 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 22 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 23 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 24 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 25 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 26 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 27 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 29 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 31 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 32 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 33 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 34 | SORT AGGREGATE | |
1 | 8 | | |
| 35 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 36 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 37 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 38 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 39 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 40 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 41 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 42 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 43 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 44 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 46 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 48 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 49 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 50 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 51 | SORT AGGREGATE | |
1 | 8 | | |
| 52 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 53 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 54 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 55 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 56 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 57 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 58 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 59 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 60 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 61 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 63 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 64 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 65 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 66 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 67 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 68 | SORT AGGREGATE | |
1 | 8 | | |
| 69 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 70 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 71 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 72 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 73 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 74 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 75 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 76 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 77 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 78 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 79 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 80 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 81 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 82 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 83 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 84 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 85 | SORT AGGREGATE | |
1 | 8 | | |
| 86 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 87 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 88 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 89 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 90 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 91 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 92 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 93 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 94 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 95 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 96 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 97 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 98 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 99 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 100 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 101 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 102 | SORT AGGREGATE | |
1 | 8 | | |
| 103 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 104 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 105 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 106 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 107 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 108 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 109 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 110 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 111 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 112 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 113 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 114 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 115 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 116 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 117 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 118 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 119 | SORT AGGREGATE | |
1 | 8 | | |
| 120 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 121 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 122 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 123 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 124 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 125 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 126 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 127 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 128 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 129 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 130 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 131 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 132 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 133 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 134 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 135 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 136 | SORT AGGREGATE | |
1 | 8 | | |
| 137 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 138 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 139 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 140 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 141 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 142 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 143 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 144 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 145 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 146 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 147 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 148 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 149 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 150 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 151 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 152 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 153 | SORT AGGREGATE | |
1 | 8 | | |
| 154 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 155 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 156 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 157 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 158 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 159 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 160 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 161 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 162 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 163 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 164 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 165 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 166 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 167 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 168 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 169 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 170 | SORT AGGREGATE | |
1 | 8 | | |
| 171 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 172 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 173 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 174 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 175 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 176 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 177 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 178 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 179 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 180 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 181 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 182 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 183 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 184 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 185 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 186 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 187 | SORT AGGREGATE | |
1 | 8 | | |
| 188 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 189 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 190 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 191 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 192 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 193 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 194 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 195 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 196 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 197 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 198 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 199 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 200 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 201 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 202 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 203 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 204 | SORT AGGREGATE | |
1 | 8 | | |
| 205 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 206 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 207 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 208 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 209 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 210 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 211 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 212 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 213 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 214 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 215 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 216 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 217 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 218 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 219 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 220 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 221 | SORT AGGREGATE | |
1 | 8 | | |
| 222 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 223 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 224 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 225 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 226 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 227 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 228 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 229 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 230 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 231 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 232 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 233 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 234 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 235 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 236 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 237 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 238 | SORT AGGREGATE | |
1 | 8 | | |
| 239 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 240 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 241 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 242 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 243 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 244 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 245 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 246 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 247 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 248 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 249 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 250 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 251 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 252 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 253 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 254 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 255 | SORT AGGREGATE | |
1 | 8 | | |
| 256 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 257 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 258 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 259 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 260 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 261 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 262 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 263 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 264 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 265 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 266 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 267 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 268 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 269 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 270 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 271 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 272 | SORT AGGREGATE | |
1 | 8 | | |
| 273 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 274 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 275 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 276 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 277 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 278 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 279 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 280 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 281 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 282 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 283 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 284 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 285 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 286 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 287 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 288 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 289 | SORT AGGREGATE | |
1 | 8 | | |
| 290 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 291 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 292 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
| 293 | HASH GROUP BY | |
1 | 87 | 520 (1)| 00:00:07 |
| 294 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 295 | NESTED LOOPS | |
1 | 87 | 518 (1)| 00:00:07 |
| 296 | NESTED LOOPS | |
1 | 68 | 517 (1)| 00:00:07 |
| 297 | NESTED LOOPS | |
1 | 39 | 5 (0)| 00:00:01 |
| 298 | NESTED LOOPS | |
1 | 32 | 4 (0)| 00:00:01 |
| 299 | TABLE ACCESS STORAGE FULL | TNI |
1 | 16 | 2 (0)| 00:00:01 |
| 300 | TABLE ACCESS BY INDEX ROWID| TMTD |
1 | 16 | 2 (0)| 00:00:01 |
| 301 | INDEX UNIQUE SCAN | MTD_PK |
1 | | 1 (0)| 00:00:01 |
| 302 | TABLE ACCESS BY INDEX ROWID | TMCT |
1 | 7 | 1 (0)| 00:00:01 |
| 303 | INDEX UNIQUE SCAN | MCT_PK |
1 | | 0 (0)| |
| 304 | TABLE ACCESS BY INDEX ROWID | TAD |
1 | 29 | 512 (1)| 00:00:07 |
| 305 | INDEX RANGE SCAN | TAD_IX2 |
21183 | | 81 (0)| 00:00:01 |
| 306 | SORT AGGREGATE | |
1 | 8 | | |
| 307 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 |
1 | 8 | 3 (0)| 00:00:01 |
| 308 | INDEX RANGE SCAN | TMMC_IX1 |
1 | | 0 (0)| |
| 309 | TABLE ACCESS BY INDEX ROWID | TMMC |
1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
