Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Friday, September 08, 2006
You may need to analyze DUAL too
Encountered this weird issue on a 9.2.0.5 database recently. The following query was found to be the most expensive query. Do not concentrate too much on the query, just notice the presence od DUAL and then look at the explain plan.
Notice, how the cardinality of DUAL is estimated to be 8168! It is, therefore, Oracle choosing to do a very expensive "MERGE JOIN CARTESIAN" - since the DUAL part is not joined with anything else!
After calculating statistics of dual (logged in as SYS) the problem corrected and the explain plan looked so much better!
Now, the dual cardinality (number of rows) is (correctly) 1. The same behaviour can be achieved, other than analyzing dual, by setting OPTIMIZER_DYNAMIC_SAMPLING to 3 or higher.
Thankfully, Oracle 10g does not reproduce the same problem with the same set of data. This is because from 10g, SYS objects, too, are analyzed.
SELECT SUM (VALUE) AS VALUE, column_hidden AS column_hidden,
NVL (column_visible, '(No Request Status)') AS column_visible,
NVL (LENGTH (column_hidden), 0)
|| '.'
|| NVL (LENGTH (column_visible), 0)
|| '.'
|| column_hidden
|| '.'
|| column_visible AS column_codestring
FROM (SELECT COUNT (*) AS VALUE, rv.status_id AS column_hidden,
rv.status_name_for_group_by AS column_visible
FROM kcrt_requests_v rv,
KCRT_REQUESTS req,
(SELECT :1 AS user_id
FROM DUAL) sys_token_value
WHERE ( 1 = 1
AND (rv.batch_number = 1 OR rv.batch_number IS NULL)
AND rv.request_id = req.request_id
AND rv.assigned_to_group_id = :2
AND req.request_type_id IN (:3)
AND ( req.status_code NOT LIKE 'CLOSED%'
AND req.status_code NOT LIKE 'CANCEL%'
)
AND EXISTS (
SELECT pcv.request_id
FROM kcrt_participant_check_v pcv
WHERE pcv.request_id = req.request_id
AND pcv.user_id = :4)
)
GROUP BY rv.status_id, rv.status_name_for_group_by)
WHERE 1 = 1
GROUP BY column_hidden,
NVL (column_visible, '(No Request Status)'),
NVL (LENGTH (column_hidden), 0)
|| '.'
|| NVL (LENGTH (column_visible), 0)
|| '.'
|| column_hidden
|| '.'
|| column_visible
ORDER BY column_visible ASC
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56262 | 2142K| 18862 |
| 1 | SORT GROUP BY | | 56262 | 2142K| 18862 |
| 2 | VIEW | | 56262 | 2142K| 130K|
| 3 | SORT GROUP BY | | 56262 | 18M| 18474 |
| 4 | FILTER | | | | |
| 5 | MERGE JOIN CARTESIAN | | 613K| 200M| 1178 |
| 6 | NESTED LOOPS OUTER | | 75 | 25650 | 353 |
| 7 | NESTED LOOPS OUTER | | 75 | 22950 | 353 |
| 8 | NESTED LOOPS OUTER | | 75 | 20250 | 353 |
| 9 | NESTED LOOPS OUTER | | 75 | 17550 | 353 |
| 10 | NESTED LOOPS OUTER | | 75 | 14850 | 353 |
| 11 | FILTER | | | | |
| 12 | NESTED LOOPS OUTER | | | | |
| 13 | NESTED LOOPS OUTER | | 46 | 7084 | 261 |
| 14 | NESTED LOOPS OUTER | | 46 | 6854 | 261 |
| 15 | NESTED LOOPS OUTER | | 46 | 6670 | 261 |
| 16 | NESTED LOOPS OUTER | | 46 | 6486 | 261 |
| 17 | HASH JOIN OUTER | | 46 | 6302 | 261 |
| 18 | HASH JOIN OUTER | | 46 | 5106 | 257 |
| 19 | NESTED LOOPS OUTER | | 46 | 4692 | 250 |
| 20 | NESTED LOOPS OUTER | | 46 | 4462 | 250 |
| 21 | NESTED LOOPS OUTER | | 46 | 4232 | 250 |
| 22 | NESTED LOOPS OUTER | | 46 | 4002 | 250 |
| 23 | NESTED LOOPS | | 46 | 3772 | 250 |
| 24 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUESTS | 46 | 1150 | 204 |
| 25 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 1440 | | 5 |
| 26 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUESTS | 1 | 57 | 1 |
| 27 | INDEX UNIQUE SCAN | KCRT_REQUESTS_U1 | 1 | | |
| 28 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 29 | INDEX UNIQUE SCAN | KCRT_REQUEST_SUB_TYPES_U1 | 1 | 5 | |
| 30 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U1 | 1 | 5 | |
| 31 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | 5 | |
| 32 | TABLE ACCESS FULL | KCRT_CONTACTS | 347 | 3123 | 6 |
| 33 | TABLE ACCESS FULL | KCRT_STATUSES | 534 | 13884 | 3 |
| 34 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 35 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 36 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 37 | INDEX UNIQUE SCAN | KCRT_FLS_VISIBILITY_U2 | 1 | 5 | |
| 38 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_DETAILS | 2 | 16 | 2 |
| 39 | INDEX RANGE SCAN | KCRT_REQUEST_DETAILS_N1 | 2 | | 1 |
| 40 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 41 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 42 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 43 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 44 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 45 | BUFFER SORT | | 8168 | | 18474 |
| 46 | TABLE ACCESS FULL | DUAL | 8168 | | 11 |
| 47 | FILTER | | | | |
| 48 | NESTED LOOPS | | 1 | 24 | 2 |
| 49 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | 1 |
| 50 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 1 | 20 | 1 |
| 51 | INDEX UNIQUE SCAN | KCRT_REQUESTS_U1 | 1 | | |
| 52 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 53 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 54 | NESTED LOOPS | | 1 | 26 | 3 |
| 55 | NESTED LOOPS | | 2 | 34 | 3 |
| 56 | TABLE ACCESS FULL | KNTA_SECURITY_GROUPS | 124 | 868 | 3 |
| 57 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 58 | INDEX UNIQUE SCAN | KNTA_ACCESS_SECURITY_U2 | 1 | 9 | |
| 59 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 33 | 2 |
| 60 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N2 | 1 | | 1 |
| 61 | NESTED LOOPS | | 1 | 52 | 4 |
| 62 | NESTED LOOPS | | 1 | 45 | 3 |
| 63 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 35 | 3 |
| 64 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 65 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 66 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 67 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 68 | NESTED LOOPS | | 1 | 69 | 17 |
| 69 | NESTED LOOPS | | 1 | 62 | 16 |
| 70 | NESTED LOOPS | | 8 | 416 | 16 |
| 71 | MERGE JOIN CARTESIAN | | 6 | 258 | 10 |
| 72 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 73 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 74 | BUFFER SORT | | 10 | 120 | 7 |
| 75 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 10 | 120 | 7 |
| 76 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEPS_N1 | 19 | | 1 |
| 77 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 1 | 9 | 1 |
| 78 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 79 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 80 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 81 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEP_SECURITY | 1 | 7 | 2 |
| 82 | NESTED LOOPS | | 6 | 300 | 22 |
| 83 | MERGE JOIN CARTESIAN | | 6 | 258 | 10 |
| 84 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 85 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 86 | BUFFER SORT | | 10 | 120 | 7 |
| 87 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 10 | 120 | 7 |
| 88 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEPS_N1 | 19 | | 1 |
| 89 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 2 | | 1 |
| 90 | NESTED LOOPS | | 1 | 107 | 17 |
| 91 | NESTED LOOPS | | 1 | 100 | 16 |
| 92 | NESTED LOOPS | | 1 | 90 | 16 |
| 93 | NESTED LOOPS | | 1 | 81 | 15 |
| 94 | NESTED LOOPS | | 1 | 74 | 14 |
| 95 | NESTED LOOPS | | 1 | 64 | 7 |
| 96 | MERGE JOIN CARTESIAN | | 1 | 57 | 6 |
| 97 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 98 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 99 | BUFFER SORT | | 1 | 26 | 3 |
| 100 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 26 | 3 |
| 101 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 2 |
| 102 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOWS | 1 | 7 | 1 |
| 103 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | | |
| 104 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCE_STEPS | 25 | 250 | 7 |
| 105 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCE_STEP_N1 | 25 | | 2 |
| 106 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 1 | 7 | 1 |
| 107 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEPS_U1 | 1 | | |
| 108 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 1 | 9 | 1 |
| 109 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 110 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 111 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 112 | NESTED LOOPS | | 1 | 88 | 17 |
| 113 | NESTED LOOPS | | 1 | 81 | 16 |
| 114 | NESTED LOOPS | | 1 | 74 | 14 |
| 115 | NESTED LOOPS | | 1 | 64 | 7 |
| 116 | MERGE JOIN CARTESIAN | | 1 | 57 | 6 |
| 117 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 118 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 119 | BUFFER SORT | | 1 | 26 | 3 |
| 120 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 26 | 3 |
| 121 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 2 |
| 122 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOWS | 1 | 7 | 1 |
| 123 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | | |
| 124 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCE_STEPS | 25 | 250 | 7 |
| 125 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCE_STEP_N1 | 25 | | 2 |
| 126 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEP_SECURITY | 1 | 7 | 2 |
| 127 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 2 | | 1 |
| 128 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 1 | 7 | 1 |
| 129 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEPS_U1 | 1 | | |
| 130 | NESTED LOOPS | | 1 | 82 | 7 |
| 131 | NESTED LOOPS OUTER | | 1 | 77 | 7 |
| 132 | NESTED LOOPS OUTER | | 1 | 73 | 7 |
| 133 | NESTED LOOPS | | 1 | 68 | 7 |
| 134 | NESTED LOOPS | | 1 | 61 | 6 |
| 135 | MERGE JOIN CARTESIAN | | 1 | 51 | 6 |
| 136 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 137 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 138 | BUFFER SORT | | 1 | 20 | 3 |
| 139 | TABLE ACCESS BY INDEX ROWID | KWFL_RESOLVED_SEC_TOKENS | 1 | 20 | 3 |
| 140 | INDEX RANGE SCAN | KWFL_RESOLVED_SEC_TOKENS_N1 | 1 | | 2 |
| 141 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 142 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 143 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 144 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 145 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 146 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEP_SECURITY_U1 | 1 | 5 | |
| 147 | NESTED LOOPS | | 1 | 65 | 6 |
| 148 | NESTED LOOPS OUTER | | 1 | 60 | 6 |
| 149 | NESTED LOOPS OUTER | | 1 | 55 | 6 |
| 150 | MERGE JOIN CARTESIAN | | 1 | 51 | 6 |
| 151 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 152 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 153 | BUFFER SORT | | 1 | 20 | 3 |
| 154 | TABLE ACCESS BY INDEX ROWID | KWFL_RESOLVED_SEC_TOKENS | 1 | 20 | 3 |
| 155 | INDEX RANGE SCAN | KWFL_RESOLVED_SEC_TOKENS_N1 | 1 | | 2 |
| 156 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 157 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 158 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEP_SECURITY_U1 | 1 | 5 | |
| 159 | NESTED LOOPS | | 1 | 69 | 5 |
| 160 | NESTED LOOPS | | 1 | 62 | 4 |
| 161 | NESTED LOOPS | | 1 | 52 | 4 |
| 162 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 16 | 3 |
| 163 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 164 | INDEX RANGE SCAN | KNTA_RESOLVED_SEC_TOKENS_N2 | 1 | 36 | 1 |
| 165 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 166 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 167 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 168 | NESTED LOOPS | | 1 | 55 | 4 |
| 169 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 16 | 3 |
| 170 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 171 | INDEX RANGE SCAN | KNTA_RESOLVED_SEC_TOKENS_N1 | 1 | 39 | 1 |
| 172 | TABLE ACCESS BY INDEX ROWID | KWFL_APPROVAL_RECIPIENTS | 1 | 13 | 3 |
| 173 | NESTED LOOPS | | 1 | 45 | 15 |
| 174 | NESTED LOOPS | | 1 | 32 | 12 |
| 175 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 21 | 4 |
| 176 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 3 |
| 177 | TABLE ACCESS BY INDEX ROWID | KWFL_STEP_TRANSACTIONS | 11 | 121 | 8 |
| 178 | INDEX RANGE SCAN | KWFL_STEP_TRANSACTIONS_N2 | 11 | | 2 |
| 179 | INDEX RANGE SCAN | KWFL_APPROVAL_RECIPIENTS_N1 | 1 | | 2 |
----------------------------------------------------------------------------------------------------------------------
Notice, how the cardinality of DUAL is estimated to be 8168! It is, therefore, Oracle choosing to do a very expensive "MERGE JOIN CARTESIAN" - since the DUAL part is not joined with anything else!
| 45 | BUFFER SORT | | 8168 | | 18474 |
| 46 | TABLE ACCESS FULL | DUAL | 8168 | | 11 |
After calculating statistics of dual (logged in as SYS) the problem corrected and the explain plan looked so much better!
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75 | 2925 | 391 |
| 1 | SORT GROUP BY | | 75 | 2925 | 391 |
| 2 | VIEW | | 75 | 2925 | 529 |
| 3 | SORT GROUP BY | | 75 | 25650 | 379 |
| 4 | FILTER | | | | |
| 5 | NESTED LOOPS OUTER | | 75 | 25650 | 364 |
| 6 | NESTED LOOPS OUTER | | 75 | 22950 | 364 |
| 7 | NESTED LOOPS OUTER | | 75 | 20250 | 364 |
| 8 | NESTED LOOPS OUTER | | 75 | 17550 | 364 |
| 9 | NESTED LOOPS OUTER | | 75 | 14850 | 364 |
| 10 | FILTER | | | | |
| 11 | NESTED LOOPS OUTER | | | | |
| 12 | NESTED LOOPS OUTER | | 46 | 7084 | 272 |
| 13 | NESTED LOOPS OUTER | | 46 | 6854 | 272 |
| 14 | NESTED LOOPS OUTER | | 46 | 6670 | 272 |
| 15 | NESTED LOOPS OUTER | | 46 | 6486 | 272 |
| 16 | HASH JOIN OUTER | | 46 | 6302 | 272 |
| 17 | HASH JOIN OUTER | | 46 | 5106 | 268 |
| 18 | NESTED LOOPS OUTER | | 46 | 4692 | 261 |
| 19 | NESTED LOOPS OUTER | | 46 | 4462 | 261 |
| 20 | NESTED LOOPS OUTER | | 46 | 4232 | 261 |
| 21 | NESTED LOOPS OUTER | | 46 | 4002 | 261 |
| 22 | NESTED LOOPS | | 46 | 3772 | 261 |
| 23 | MERGE JOIN CARTESIAN | | 46 | 1150 | 215 |
| 24 | TABLE ACCESS FULL | DUAL | 1 | | 11 |
| 25 | BUFFER SORT | | 46 | 1150 | 204 |
| 26 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUESTS | 46 | 1150 | 204 |
| 27 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 1440 | | 5 |
| 28 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 1 | 57 | 1 |
| 29 | INDEX UNIQUE SCAN | KCRT_REQUESTS_U1 | 1 | | |
| 30 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 31 | INDEX UNIQUE SCAN | KCRT_REQUEST_SUB_TYPES_U1 | 1 | 5 | |
| 32 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U1 | 1 | 5 | |
| 33 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | 5 | |
| 34 | TABLE ACCESS FULL | KCRT_CONTACTS | 347 | 3123 | 6 |
| 35 | TABLE ACCESS FULL | KCRT_STATUSES | 534 | 13884 | 3 |
| 36 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 37 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 38 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 39 | INDEX UNIQUE SCAN | KCRT_FLS_VISIBILITY_U2 | 1 | 5 | |
| 40 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_DETAILS | 2 | 16 | 2 |
| 41 | INDEX RANGE SCAN | KCRT_REQUEST_DETAILS_N1 | 2 | | 1 |
| 42 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 43 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 44 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 45 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 46 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 36 | |
| 47 | FILTER | | | | |
| 48 | NESTED LOOPS | | 1 | 24 | 2 |
| 49 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | 1 |
| 50 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 1 | 20 | 1 |
| 51 | INDEX UNIQUE SCAN | KCRT_REQUESTS_U1 | 1 | | |
| 52 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 53 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 54 | NESTED LOOPS | | 1 | 26 | 3 |
| 55 | NESTED LOOPS | | 2 | 34 | 3 |
| 56 | TABLE ACCESS FULL | KNTA_SECURITY_GROUPS | 124 | 868 | 3 |
| 57 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 58 | INDEX UNIQUE SCAN | KNTA_ACCESS_SECURITY_U2 | 1 | 9 | |
| 59 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 33 | 2 |
| 60 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N2 | 1 | | 1 |
| 61 | NESTED LOOPS | | 1 | 52 | 4 |
| 62 | NESTED LOOPS | | 1 | 45 | 3 |
| 63 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 2 | 70 | 3 |
| 64 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 65 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 66 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 67 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 68 | NESTED LOOPS | | 1 | 69 | 21 |
| 69 | NESTED LOOPS | | 1 | 62 | 20 |
| 70 | NESTED LOOPS | | 13 | 676 | 20 |
| 71 | MERGE JOIN CARTESIAN | | 10 | 430 | 10 |
| 72 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 73 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 74 | BUFFER SORT | | 10 | 120 | 7 |
| 75 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 10 | 120 | 7 |
| 76 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEPS_N1 | 19 | | 1 |
| 77 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 1 | 9 | 1 |
| 78 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 79 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 80 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 81 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEP_SECURITY | 1 | 7 | 2 |
| 82 | NESTED LOOPS | | 10 | 500 | 30 |
| 83 | MERGE JOIN CARTESIAN | | 10 | 430 | 10 |
| 84 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 85 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 86 | BUFFER SORT | | 10 | 120 | 7 |
| 87 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 10 | 120 | 7 |
| 88 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEPS_N1 | 19 | | 1 |
| 89 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 2 | | 1 |
| 90 | NESTED LOOPS | | 1 | 107 | 17 |
| 91 | NESTED LOOPS | | 1 | 100 | 16 |
| 92 | NESTED LOOPS | | 1 | 90 | 16 |
| 93 | NESTED LOOPS | | 1 | 81 | 15 |
| 94 | NESTED LOOPS | | 1 | 74 | 14 |
| 95 | NESTED LOOPS | | 1 | 64 | 7 |
| 96 | MERGE JOIN CARTESIAN | | 1 | 57 | 6 |
| 97 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 98 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 99 | BUFFER SORT | | 1 | 26 | 3 |
| 100 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 26 | 3 |
| 101 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 2 |
| 102 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOWS | 1 | 7 | 1 |
| 103 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | | |
| 104 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCE_STEPS | 25 | 250 | 7 |
| 105 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCE_STEP_N1 | 25 | | 2 |
| 106 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 1 | 7 | 1 |
| 107 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEPS_U1 | 1 | | |
| 108 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 1 | 9 | 1 |
| 109 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 110 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 111 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 112 | NESTED LOOPS | | 1 | 88 | 17 |
| 113 | NESTED LOOPS | | 1 | 81 | 16 |
| 114 | NESTED LOOPS | | 1 | 74 | 14 |
| 115 | NESTED LOOPS | | 1 | 64 | 7 |
| 116 | MERGE JOIN CARTESIAN | | 1 | 57 | 6 |
| 117 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 118 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 119 | BUFFER SORT | | 1 | 26 | 3 |
| 120 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 26 | 3 |
| 121 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 2 |
| 122 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOWS | 1 | 7 | 1 |
| 123 | INDEX UNIQUE SCAN | KWFL_WORKFLOWS_U1 | 1 | | |
| 124 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCE_STEPS | 25 | 250 | 7 |
| 125 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCE_STEP_N1 | 25 | | 2 |
| 126 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEP_SECURITY | 1 | 7 | 2 |
| 127 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEP_SECURITY_N2 | 2 | | 1 |
| 128 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 1 | 7 | 1 |
| 129 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEPS_U1 | 1 | | |
| 130 | NESTED LOOPS | | 1 | 82 | 7 |
| 131 | NESTED LOOPS OUTER | | 1 | 77 | 7 |
| 132 | NESTED LOOPS OUTER | | 1 | 73 | 7 |
| 133 | NESTED LOOPS | | 1 | 68 | 7 |
| 134 | NESTED LOOPS | | 1 | 61 | 6 |
| 135 | MERGE JOIN CARTESIAN | | 1 | 51 | 6 |
| 136 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 137 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 138 | BUFFER SORT | | 1 | 20 | 3 |
| 139 | TABLE ACCESS BY INDEX ROWID | KWFL_RESOLVED_SEC_TOKENS | 1 | 20 | 3 |
| 140 | INDEX RANGE SCAN | KWFL_RESOLVED_SEC_TOKENS_N1 | 1 | | 2 |
| 141 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 142 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 143 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 144 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 145 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 146 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEP_SECURITY_U1 | 1 | 5 | |
| 147 | NESTED LOOPS | | 1 | 65 | 6 |
| 148 | NESTED LOOPS OUTER | | 1 | 60 | 6 |
| 149 | NESTED LOOPS OUTER | | 1 | 55 | 6 |
| 150 | MERGE JOIN CARTESIAN | | 1 | 51 | 6 |
| 151 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 31 | 3 |
| 152 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 153 | BUFFER SORT | | 1 | 20 | 3 |
| 154 | TABLE ACCESS BY INDEX ROWID | KWFL_RESOLVED_SEC_TOKENS | 1 | 20 | 3 |
| 155 | INDEX RANGE SCAN | KWFL_RESOLVED_SEC_TOKENS_N1 | 1 | | 2 |
| 156 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 4 | |
| 157 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | 5 | |
| 158 | INDEX UNIQUE SCAN | KWFL_WORKFLOW_STEP_SECURITY_U1 | 1 | 5 | |
| 159 | NESTED LOOPS | | 1 | 69 | 5 |
| 160 | NESTED LOOPS | | 1 | 62 | 4 |
| 161 | NESTED LOOPS | | 1 | 52 | 4 |
| 162 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 16 | 3 |
| 163 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 164 | INDEX RANGE SCAN | KNTA_RESOLVED_SEC_TOKENS_N2 | 1 | 36 | 1 |
| 165 | INDEX UNIQUE SCAN | KNTA_USER_SECURITY_U2 | 1 | 10 | |
| 166 | TABLE ACCESS BY INDEX ROWID | KNTA_SECURITY_GROUPS | 1 | 7 | 1 |
| 167 | INDEX UNIQUE SCAN | KNTA_SECURITY_GROUPS_U1 | 1 | | |
| 168 | NESTED LOOPS | | 1 | 55 | 4 |
| 169 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUEST_TYPE_SECURITY | 1 | 16 | 3 |
| 170 | INDEX RANGE SCAN | KCRT_REQUEST_TYPE_SECURITY_N1 | 12 | | 1 |
| 171 | INDEX RANGE SCAN | KNTA_RESOLVED_SEC_TOKENS_N1 | 1 | 39 | 1 |
| 172 | TABLE ACCESS BY INDEX ROWID | KWFL_APPROVAL_RECIPIENTS | 1 | 13 | 3 |
| 173 | NESTED LOOPS | | 1 | 45 | 15 |
| 174 | NESTED LOOPS | | 1 | 32 | 12 |
| 175 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_INSTANCES | 1 | 21 | 4 |
| 176 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N5 | 1 | | 3 |
| 177 | TABLE ACCESS BY INDEX ROWID | KWFL_STEP_TRANSACTIONS | 11 | 121 | 8 |
| 178 | INDEX RANGE SCAN | KWFL_STEP_TRANSACTIONS_N2 | 11 | | 2 |
| 179 | INDEX RANGE SCAN | KWFL_APPROVAL_RECIPIENTS_N1 | 1 | | 2 |
-----------------------------------------------------------------------------------------------------------------------
Now, the dual cardinality (number of rows) is (correctly) 1. The same behaviour can be achieved, other than analyzing dual, by setting OPTIMIZER_DYNAMIC_SAMPLING to 3 or higher.
| 23 | MERGE JOIN CARTESIAN | | 46 | 1150 | 215 |
| 24 | TABLE ACCESS FULL | DUAL | 1 | | 11 |
Thankfully, Oracle 10g does not reproduce the same problem with the same set of data. This is because from 10g, SYS objects, too, are analyzed.
Archives
January 2005 February 2005 March 2005 April 2005 May 2005 March 2006 April 2006 August 2006 September 2006 October 2006 January 2007 May 2008
