1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| SELECT P.HASH_VALUE HV,
P.CHILD_NUMBER "cn",
TO_CHAR(P.ID, '990') || DECODE(ACCESS_PREDICATES, NULL, NULL, 'A') ||
DECODE(FILTER_PREDICATES, NULL, NULL, 'F') ID,
P.COST "cost",
P.CARDINALITY "card",
LPAD(' ', DEPTH) || P.OPERATION || ' ' || P.OPTIONS || ' ' ||
P.OBJECT_NAME || DECODE(P.PARTITION_START, NULL, ' ', ':') ||
TRANSLATE(P.PARTITION_START, '(NRUMBE', '(NR') ||
DECODE(P.PARTITION_STOP, NULL, ' ', '-') ||
TRANSLATE(P.PARTITION_STOP, '(NRUMBE', '(NR') "operation",
P.POSITION "pos",
(SELECT S.LAST_OUTPUT_ROWS
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "ROWS",
(SELECT ROUND(S.LAST_ELAPSED_TIME / 1000000, 2)
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "ELAPSED",
(SELECT S.LAST_CR_BUFFER_GETS
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "CR_GETS",
(SELECT S.LAST_CU_BUFFER_GETS
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "CU_GETS",
(SELECT S.LAST_DISK_READS
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "READS",
(SELECT S.LAST_DISK_WRITES
FROM V$SQL_PLAN_STATISTICS S
WHERE S.ADDRESS = P.ADDRESS AND S.HASH_VALUE = P.HASH_VALUE AND
S.CHILD_NUMBER = P.CHILD_NUMBER AND S.OPERATION_ID = P.ID) "WRITES"
FROM V$SQL_PLAN P
WHERE P.plan_HASH_VALUE = 3733925778
ORDER BY P.CHILD_NUMBER, P.ID |
Partager