Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Monday, September 25, 2006
IMP-00058 with LOB columns
While importing a table with CLOB the error obtained was -
Import: Release 10.1.0.3.0 - Production ON Mon Sep 25 18:04:39 2006
Copyright (c) 1982, 2004, Oracle. ALL rights reserved.
PASSWORD:
Connected TO: Oracle DATABASE 10g Release 10.1.0.3.0 - 64bit Production
Export FILE created BY EXPORT:V10.01.00 via conventional path
import done IN US7ASCII CHARACTER SET AND AL16UTF16 NCHAR CHARACTER SET
import server uses UTF8 CHARACTER SET (possible charset conversion)
. importing ITGAPP's objects into ITGAPP
. . importing TABLE "DSH_GENERATED_IMAGES"
IMP-00058: ORACLE error 942 encountered
ORA-00942: TABLE OR VIEW does NOT exist 0 ROWS imported
Import terminated successfully WITH warnings.
This typically happens when CLOB was created in a tablespace that's the new schema cannot "see" or access.
To get rid of -
(1) Either use IMPDP / EXPDP (it rocks! faster, better, easier!)
(2) Precreate the CLOB-ed tables exactly as it were in the exporting schema as follows and then import the table individually -
(a) RUN the following command -
imp
(b) RUN the tablecreate.SQL script FROM (a) in the importing SCHEMA. You may need TO CREATE a small TABLESPACE OR two (temporarily, you can get the name FROM the SQL script)
(c) Now import the TABLES -
imp/ @ FILE= buffer=1000000 TABLES= ignore=Y LOG= show=y COMMIT=N
COMMIT=N is better for large tables with LOB data.
Impending OR
Another piece of SQL in the "Top 100" SQL list -
Here's the statistics from V$SQLAREA for this -
CPU_TIME EXECUTIONS BUFFER_GETS
18,635,370,000 479,770.00 809,709,593
i.e., for about 500,000 executions - it took about 5 hrs. 15 mins doing 1700 LIO per exec!
The explain plan shows -
Glancing at the SQL with "blink" problem solving mode - I see the "OR".
Which can be rewritten as -
I tried running the SQL with either of the OR-ed parts and it returned faster. So I rewrote the SQL to -
Notice, how I put the other value in a UNION so we do not have to use OR in the outside loop. Now, workflow_id can be equated just once with this inner-view (which, knowing the data model, should be sufficiently faster).
The explain plan of un-OR-ed SQL -
Though it's difficult to tell from explain plan -- but the "bytes" has decreased at the expense of "cost" (weird! But a plan could indeed be the better one with an increased cost!).
The LIO for this was tested to be 1/3rd as the original one. This is just a case of "doing the work in another way and found improvement", not necessarily one way is better than the other.
SELECT wss.token,
MIN (wss.workflow_step_security_id) workflow_step_security_id
FROM kwfl_workflow_step_security wss
WHERE wss.workflow_step_id IN (
SELECT ws.workflow_step_id
FROM kwfl_workflow_steps ws
WHERE ws.workflow_id = :b2
OR ws.workflow_id IN (
SELECT workflow_id
FROM kwfl_workflow_instances wi
WHERE wi.top_instance_source_id = :b10
AND wi.top_instance_source_type_code =DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL)))
AND wss.token IS NOT NULL
GROUP BY wss.token,
DECODE (UPPER (SUBSTR (wss.token, 1, 3)),:b1, wss.workflow_step_id,0)
Here's the statistics from V$SQLAREA for this -
CPU_TIME EXECUTIONS BUFFER_GETS
18,635,370,000 479,770.00 809,709,593
i.e., for about 500,000 executions - it took about 5 hrs. 15 mins doing 1700 LIO per exec!
The explain plan shows -
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 214 | 5136 | 37 |
| 1 | SORT GROUP BY | | 214 | 5136 | 37 |
| 2 | FILTER | | | | |
| 3 | HASH JOIN | | 214 | 5136 | 25 |
| 4 | TABLE ACCESS FULL | KWFL_WORKFLOW_STEPS | 207 | 2070 | 13 |
| 5 | TABLE ACCESS FULL | KWFL_WORKFLOW_STEP_SECURITY | 652 | 9128 | 11 |
| 6 | TABLE ACCESS BY INDEX ROWID| KWFL_WORKFLOW_INSTANCES | 1 | 12 | 4 |
| 7 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N6 | 1 | | 3 |
----------------------------------------------------------------------------------------------
Glancing at the SQL with "blink" problem solving mode - I see the "OR".
ws.workflow_id = :b2
OR ws.workflow_id IN (
SELECT workflow_id
FROM kwfl_workflow_instances wi
WHERE wi.top_instance_source_id = :b10
AND wi.top_instance_source_type_code =DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL)))
Which can be rewritten as -
WHERE ws.workflow_id IN (
SELECT workflow_id
FROM kwfl_workflow_instances wi
WHERE wi.top_instance_source_id = :b10
AND wi.top_instance_source_type_code =
DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL))
UNION
SELECT TO_NUMBER(:b2) FROM dual )
I tried running the SQL with either of the OR-ed parts and it returned faster. So I rewrote the SQL to -
explain plan for SELECT wss.token,
MIN (wss.workflow_step_security_id) workflow_step_security_id
FROM kwfl_workflow_step_security wss
WHERE
wss.workflow_step_id IN (SELECT ws.workflow_step_id
FROM kwfl_workflow_steps ws
WHERE ws.workflow_id IN (
SELECT workflow_id
FROM kwfl_workflow_instances wi
WHERE wi.top_instance_source_id = :b10
AND wi.top_instance_source_type_code =
DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL))
UNION
SELECT TO_NUMBER(:b2) FROM dual )
AND wss.token IS NOT NULL
GROUP BY wss.token,DECODE (UPPER (SUBSTR (wss.token, 1, 3)),:b1, wss.workflow_step_id,0) ;
Notice, how I put the other value in a UNION so we do not have to use OR in the outside loop. Now, workflow_id can be equated just once with this inner-view (which, knowing the data model, should be sufficiently faster).
The explain plan of un-OR-ed SQL -
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 54 | 61 |
| 1 | SORT GROUP BY | | 2 | 54 | 61 |
| 2 | HASH JOIN | | 2 | 54 | 49 |
| 3 | VIEW | VW_NSO_1 | 13 | 169 | 37 |
| 4 | SORT UNIQUE | | 13 | 264 | 37 |
| 5 | UNION-ALL | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | KWFL_WORKFLOW_STEPS | 19 | 190 | 7 |
| 7 | NESTED LOOPS | | 12 | 264 | 11 |
| 8 | TABLE ACCESS BY INDEX ROWID| KWFL_WORKFLOW_INSTANCES | 1 | 12 | 4 |
| 9 | INDEX RANGE SCAN | KWFL_WORKFLOW_INSTANCES_N6 | 1 | | 3 |
| 10 | INDEX RANGE SCAN | KWFL_WORKFLOW_STEPS_N1 | 19 | | 1 |
| 11 | TABLE ACCESS FULL | DUAL | 1 | | 2 |
| 12 | TABLE ACCESS FULL | KWFL_WORKFLOW_STEP_SECURITY | 652 | 9128 | 11 |
---------------------------------------------------------------------------------------------------
Though it's difficult to tell from explain plan -- but the "bytes" has decreased at the expense of "cost" (weird! But a plan could indeed be the better one with an increased cost!).
The LIO for this was tested to be 1/3rd as the original one. This is just a case of "doing the work in another way and found improvement", not necessarily one way is better than the other.
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.
NESTED_TABLE_GET_REFS hint
Learned something new today. It never stops, does it?
I was looking into one DB to find out most expensive SQL statements. That's my principal "tuning arsenal". i.e., the query -
Many a time, you won't get full SQL_text from this query. But then you can easily modify this one and join it with V$SQL (or V$SQL_WITH_NEWLINES). Many of my collegues here - wanting to start their career in performance tuning - ask me for opinion on 'performance tools'. I always tell them about the query above first. This, along with Statspack or AWRSTATS report gathered at calculated interval, can give you pretty much all the data to pinpoint the problem.
However, as I was scanning the V$SQLAREA - I found queries like following taking about 20% response time of the "Top 100" queries. (Another tactic I use is build a histogram with Top 10-25-100 queries. Typically 80 to 90% of database "work" is done by these. I then generate pie chart for external reports to show how much each single query - sometimes bottomed up to application transaction level - consume in the DB.).
It looks so much like those Oracle recursive queries, doesn't it? I found out that Oracle "exp" (export) issues the query with the above hint for every table! This is an undocumented hint, supposed to work with "Nested Tables" (though the table above is not a Nested one). More info about the hint can be found here and here. Apparently, the hint will just be ignored if the table is not a nested table.
I was looking into one DB to find out most expensive SQL statements. That's my principal "tuning arsenal". i.e., the query -
SELECT sql_text, buffer_gets, executions, disk_reads, cpu_time, elapsed_time
FROM (SELECT *
FROM v$sqlarea
ORDER BY elapsed_time desc)
WHERE ROWNUM < 25
Many a time, you won't get full SQL_text from this query. But then you can easily modify this one and join it with V$SQL (or V$SQL_WITH_NEWLINES). Many of my collegues here - wanting to start their career in performance tuning - ask me for opinion on 'performance tools'. I always tell them about the query above first. This, along with Statspack or AWRSTATS report gathered at calculated interval, can give you pretty much all the data to pinpoint the problem.
However, as I was scanning the V$SQLAREA - I found queries like following taking about 20% response time of the "Top 100" queries. (Another tactic I use is build a histogram with Top 10-25-100 queries. Typically 80 to 90% of database "work" is done by these. I then generate pie chart for external reports to show how much each single query - sometimes bottomed up to application transaction level - consume in the DB.).
SELECT /*+NESTED_TABLE_GET_REFS+*/
"SCH"."KWFL_STEP_TRANSACTIONS".*
FROM "SCH"."KWFL_STEP_TRANSACTIONS"
It looks so much like those Oracle recursive queries, doesn't it? I found out that Oracle "exp" (export) issues the query with the above hint for every table! This is an undocumented hint, supposed to work with "Nested Tables" (though the table above is not a Nested one). More info about the hint can be found here and here. Apparently, the hint will just be ignored if the table is not a nested table.
Wednesday, September 06, 2006
Leaving transitivity closure to Oracle is not always good
Today, I was working with one of the "big six" DBA groups who were bothered about the performance of this query on a 9.2.05 DB -
This was taking over a minute to execute. The tables, apparently, were indexed well, analyzed recently and was not too big (less than million records).
I took a look at the explain plan -
So, what's wrong? Let's see where Oracle is doing most work.
2316760 SORT JOIN
2316760 MERGE JOIN CARTESIAN
Looking at the query logically, we see the table KNTA_PERIODS is not joined with any other table than one in the IN-clause. So, effectively Oracle has two parts in this query -
(a) Every other table in the FROM clause other than KNTA_PERIODS
(b) KNTA_PERIODS joined with the IN-list.
Now, take a look at both the bind variables -
These two project_ids are related. In fact, PROJECT_ID in kdrv_cost_metrics_by_period table (IN-clause) is a foreign key to PROJECT_ID in kdrv_projects. So, we expected Oracle to use "transitive closure" -- i.e.,
if, a= b and b=c
Oracle automatically will use a=c for the query optimization.
For some reason, here (sub-query, disabled foreign-key constraints?) it was not doing so.
So I changed the query to
The response time was less than 0.1 sec (down from 62 sec).
The new explain plan shows how every table is well joined this time.
SELECT top_proj.project_id AS project_id, kpw.period_id AS period_id
FROM kdrv_project_costs kpc,
kdrv_projects kp,
kdrv_projects top_proj,
knta_periods kpw
WHERE top_proj.project_id = :b1
AND kpc.project_id = kp.project_id
AND kp.project_path_id_list LIKE top_proj.project_path_id_list || '%'
AND kp.project_type_code = 'TASK'
AND kpw.period_type_id = 3
AND kpw.period_id IN (SELECT period_id
FROM kdrv_cost_metrics_by_period
WHERE project_id = :b1);
This was taking over a minute to execute. The tables, apparently, were indexed well, analyzed recently and was not too big (less than million records).
I took a look at the explain plan -
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY
0 HASH JOIN
1201 TABLE ACCESS FULL KNTA_PERIODS
0 MERGE JOIN
2316760 SORT JOIN
2316760 MERGE JOIN CARTESIAN
68 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID KDRV_PROJECTS
1 INDEX UNIQUE SCAN KDRV_PROJECTS_U1 (object id 33383)
68 SORT UNIQUE
68 INDEX RANGE SCAN KDRV_COST_METRICS_BY_PERIOD_U2 (object id 33358)
2316760 BUFFER SORT
34070 TABLE ACCESS FULL KDRV_PROJECT_COSTS
0 FILTER
1687420 SORT JOIN
91622 TABLE ACCESS FULL KDRV_PROJECTS
So, what's wrong? Let's see where Oracle is doing most work.
2316760 SORT JOIN
2316760 MERGE JOIN CARTESIAN
Looking at the query logically, we see the table KNTA_PERIODS is not joined with any other table than one in the IN-clause. So, effectively Oracle has two parts in this query -
(a) Every other table in the FROM clause other than KNTA_PERIODS
(b) KNTA_PERIODS joined with the IN-list.
Now, take a look at both the bind variables -
WHERE project_id = :b1 -- IN-clause
WHERE top_proj.project_id = :b1 -- main WHERE clause
These two project_ids are related. In fact, PROJECT_ID in kdrv_cost_metrics_by_period table (IN-clause) is a foreign key to PROJECT_ID in kdrv_projects. So, we expected Oracle to use "transitive closure" -- i.e.,
if, a= b and b=c
Oracle automatically will use a=c for the query optimization.
For some reason, here (sub-query, disabled foreign-key constraints?) it was not doing so.
So I changed the query to
SELECT /*+ */ top_proj.project_id AS project_id, kpw.period_id AS period_id
FROM kdrv_project_costs kpc,
kdrv_projects kp,
kdrv_projects top_proj,
knta_periods kpw
WHERE top_proj.project_id = :b1
AND kpc.project_id = kp.project_id
AND kp.project_path_id_list LIKE top_proj.project_path_id_list || '%'
AND kp.project_type_code = 'TASK'
AND kpw.period_type_id = 3
AND kpw.period_id IN (SELECT period_id
FROM kdrv_cost_metrics_by_period
WHERE project_id = top_proj.project_id)
The response time was less than 0.1 sec (down from 62 sec).
The new explain plan shows how every table is well joined this time.
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 694 | 1 |
| 1 | NESTED LOOPS | | 1 | 694 | 1 |
| 2 | NESTED LOOPS | | 1 | 686 | 1 |
| 3 | NESTED LOOPS | | 1 | 660 | 1 |
| 4 | NESTED LOOPS | | 1 | 328 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS | 1 | 315 | 1 |
|* 6 | INDEX UNIQUE SCAN | KDRV_PROJECTS_U1 | 1 | | |
| 7 | INDEX FULL SCAN | KDRV_PROJECT_COSTS_U2 | 1 | 13 | |
|* 8 | TABLE ACCESS BY INDEX ROWID | KDRV_PROJECTS | 1 | 332 | |
|* 9 | INDEX UNIQUE SCAN | KDRV_PROJECTS_U1 | 1 | | |
| 10 | SORT UNIQUE | | | | |
|* 11 | INDEX RANGE SCAN | KDRV_COST_METRICS_BY_PERIOD_U2 | 1 | 26 | |
|* 12 | TABLE ACCESS BY INDEX ROWID | KNTA_PERIODS | 1 | 8 | |
|* 13 | INDEX UNIQUE SCAN | KNTA_PERIODS_U1 | 1 | | |
---------------------------------------------------------------------------------------------------
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
