Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Thursday, October 12, 2006
Fast == Not repeating yourself
As someone said, the fastest way to do something is to not do it. Nothing can beat zero!
Take the following piece of code -
I was asked by one of our customers if adding an ORDERED hint is OK. It seems they've gotten the performance of the above SQL down to 4.5 sec by adding the hint. No logic was given on why adding the ORDERED hint would make it fast. Or, better - what's actually wrong with the above query. I took a look and found ( I needed to take a look at the data model as well)-
(1) Information - The only attribute that needs to be SUMmed over PROJECT_ID is TSLD.ACTUAL_TIME. ACTUAL_RATE remains constant throughout a project.
How it can help performance - We can remove the GROUP BY. We can just use SUM() analytical function on TSLD.ACTUAL_RATE over (partition by project_id)
(2) What's bad - SUM(NVL(column,0)) is redundant! Whenever SUMming -- all NULLs are ignored (i.e., treated as zero) anyways.
(3) Information - The PL/SQL function kcst_cost_util.get_currency_exchange_rate is called too many times. Let's see the logic behind -
Now, isn't (for most of the cases -- there're exceptions though!) --
Thus we can simplify the part from the SQL above from -
To -
(I checked that the function will return the same value for same variable. i.e., deterministic. In other words, I would not be able to transform the following without a look at the source code.
The final optimized version of the query looked like (I actually did not check in the immediate above transformation since the gain was minute, but the logic still holds) --
The improvements -
(1) I evaluated the PL/SQL function values in the sub-query itself.
(2) I removed the group by and used Oracle Analytic Function SUM() instead (Great when you really are grouping by just a few of many columns present in the SELECT clause).
(3) I removed the redundant (sum(nvl(column,0))) to sum(column)
Final version of the query ran, as expected, beating the hinted version by a whole 2 seconds!
Take the following piece of code -
SELECT p.project_id project_id,
NVL(SUM(NVL (tsld.actual_time, 0)), 0) actuals_to_date,
NVL(SUM(NVL (tsld.actual_time, 0) * NVL (tsl.actual_rate, 0) / (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)),0) cost_to_date_bse,
NVL(SUM(NVL (tsld.actual_time, 0) * NVL (tsl.actual_rate, 0) * DECODE((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL),
(SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL), 1,
((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL)
/ (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)))),0
) cost_to_date_lcl
FROM kdrv_projects p,
kdrv_projects mp,
kdrv_project_setups ps,
knta_regions r,
ktmg_time_sheet_lines tsl,
ktmg_time_sheets tts,
ktmg_time_sheet_line_details tsld
WHERE tts.status_code != 'CANCELLED'
AND tsl.time_sheet_id = tts.time_sheet_id
AND ( tsl.work_item_type_code = 'TASK'
OR tsl.work_item_type_code = 'PROJECT'
)
AND tsld.time_sheet_line_id = tsl.time_sheet_line_id
AND tsld.activity_id = -1
AND (p.project_id IN
(397198, 68164, 351378, 280015, 281991, 68194)
)
AND tsl.work_item_id = p.project_id
AND tsl.work_item_set_id = p.master_project_id
AND mp.project_id = p.master_project_id
AND mp.project_id = ps.source_id
AND ps.source_entity_id = 6
AND ps.region_id = r.region_id
GROUP BY p.project_id
I was asked by one of our customers if adding an ORDERED hint is OK. It seems they've gotten the performance of the above SQL down to 4.5 sec by adding the hint. No logic was given on why adding the ORDERED hint would make it fast. Or, better - what's actually wrong with the above query. I took a look and found ( I needed to take a look at the data model as well)-
(1) Information - The only attribute that needs to be SUMmed over PROJECT_ID is TSLD.ACTUAL_TIME. ACTUAL_RATE remains constant throughout a project.
How it can help performance - We can remove the GROUP BY. We can just use SUM() analytical function on TSLD.ACTUAL_RATE over (partition by project_id)
(2) What's bad - SUM(NVL(column,0)) is redundant! Whenever SUMming -- all NULLs are ignored (i.e., treated as zero) anyways.
(3) Information - The PL/SQL function kcst_cost_util.get_currency_exchange_rate is called too many times. Let's see the logic behind -
NVL(SUM(NVL (tsld.actual_time, 0) * NVL (tsl.actual_rate, 0) * DECODE((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL),
(SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL), 1,
((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL)
/ (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)))),0
) cost_to_date_lcl
if
(SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) = (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)
then nvl((SUM(tsld.actual_time) * tsl.actual_rate),0)
else
NVL( SUM(tsld.actual_time) * tsl.actual_rate * (SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL)
/ (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)), 0)
end if;
Now, isn't (for most of the cases -- there're exceptions though!) --
if F(x) = F(y) then
x = y;
end if;
Thus we can simplify the part from the SQL above from -
DECODE((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL),
(SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL), 1,
((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL)
/ (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)))
To -
(I checked that the function will return the same value for same variable. i.e., deterministic. In other words, I would not be able to transform the following without a look at the source code.
DECODE(r.currency_id,tsl.currency_id, 1,
((SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL)
/ (SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL)))
The final optimized version of the query looked like (I actually did not check in the immediate above transformation since the gain was minute, but the logic still holds) --
SELECT project_id,
NVL(actd, 0) actuals_to_date,
NVL((actd * actual_rate/ tslc),0) cost_to_date_bse,
NVL((actd * actual_rate * DECODE(rcri, tslc, 1,(rcri / tslc))),0) cost_to_date_lcl
FROM
(SELECT p.project_id, SUM(tsld.actual_time) over (PARTITION BY p.project_id) actd , NVL(tsl.actual_rate, 0) actual_rate,
(SELECT kcst_cost_util.get_currency_exchange_rate(tsl.currency_id,p.scheduled_start_date) FROM DUAL) tslc,
(SELECT kcst_cost_util.get_currency_exchange_rate(r.currency_id,p.scheduled_start_date) FROM DUAL) rcri
FROM kdrv_projects p,
kdrv_projects mp,
kdrv_project_setups ps,
knta_regions r,
ktmg_time_sheet_lines tsl,
ktmg_time_sheets tts,
ktmg_time_sheet_line_details tsld
WHERE tts.status_code != 'CANCELLED'
AND tsl.time_sheet_id = tts.time_sheet_id
AND ( tsl.work_item_type_code = 'TASK'
OR tsl.work_item_type_code = 'PROJECT'
)
AND tsld.time_sheet_line_id = tsl.time_sheet_line_id
AND tsld.activity_id = -1
AND (p.project_id IN
(397198, 68164, 351378, 280015, 281991, 68194)
)
AND tsl.work_item_id = p.project_id
AND tsl.work_item_set_id = p.master_project_id
AND mp.project_id = p.master_project_id
AND mp.project_id = ps.source_id
AND ps.source_entity_id = 6
AND ps.region_id = r.region_id)
The improvements -
(1) I evaluated the PL/SQL function values in the sub-query itself.
(2) I removed the group by and used Oracle Analytic Function SUM() instead (Great when you really are grouping by just a few of many columns present in the SELECT clause).
(3) I removed the redundant (sum(nvl(column,0))) to sum(column)
Final version of the query ran, as expected, beating the hinted version by a whole 2 seconds!
Thursday, October 05, 2006
One version is faster, but why?
The query runs in about 4 sec with about 180K LIO -
But when I remove the outer "IN" clause, use that as a data source and change the IN to "=" it executes in 0.4 sec with barely 4K LIO. Why? The change in plan is also observed if I remove -
(Well, I know this is redundant -- but in this web application this is a part 'manipulated by token')
There's a distinct change in explain plan. I need to run a 10053 level trace to find out why!
SELECT DISTINCT r.user_id, users.full_name, users.username,
(SELECT lookups.meaning
FROM knta_lookups lookups
WHERE lookups.lookup_type = 'DEPT'
AND lookups.lookup_code = users.department_code),
users.email_address
FROM rsc_resources r,
rsc_rp_distribution_entries de,
wp_tasks tasks,
wp_task_schedule sched,
knta_users_v users
WHERE de.resource_id = r.resource_id
AND de.distribution_percent > 0
-- show resources from the associated Staffing Profile + users managed by the current user
AND ( DECODE ('N', 'Y', 1, 'N', 2) = 1
OR r.resource_id IN (
SELECT dist.resource_id
FROM rsc_rp_distribution_entries dist
WHERE dist.resource_pool_id IN (
SELECT resource_pool_id
FROM rsc_resource_pools rp
START WITH rp.resource_pool_id IN (
-- get resource pools directly managed by user
SELECT resource_pool_id
FROM rsc_resource_pool_managers
WHERE manager_user_id = 30030)
CONNECT BY rp.parent_resource_pool_id =
PRIOR rp.resource_pool_id)
UNION
SELECT rsc_assign.resource_id
FROM pm_work_plans wp,
pm_projects projects,
rsc_staffing_profiles sp,
rsc_positions p,
rsc_resource_assignments rsc_assign
WHERE rsc_assign.position_id = p.position_id
AND p.staffing_profile_id = sp.staffing_profile_id
AND sp.container_entity_type_code = 1
-- project type
AND sp.container_entity_id = projects.project_id
AND projects.project_id = wp.project_id
AND 30002 = wp.work_plan_id)
)
AND r.user_id = users.user_id
AND tasks.task_id = 30000
AND sched.task_schedule_id = tasks.task_schedule_id
AND NVL (users.end_date, sched.sched_start_date) >=
sched.sched_start_date
AND ( ( de.start_time <= sched.sched_finish_date AND de.finish_time >= sched.sched_start_date
)
OR ( de.finish_time IS NULL
AND de.start_time <= sched.sched_finish_date ) ) ORDER BY users.full_name
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 6765 | 444 (2)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID | KNTA_LOOKUPS | 1 | 47 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 15 | 6765 | 443 (2)| 00:00:06 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS OUTER | | 300 | 132K| 442 (2)| 00:00:06 |
| 6 | NESTED LOOPS OUTER | | 300 | 122K| 442 (2)| 00:00:06 |
| 7 | NESTED LOOPS OUTER | | 300 | 112K| 442 (2)| 00:00:06 |
| 8 | NESTED LOOPS OUTER | | 300 | 102K| 442 (2)| 00:00:06 |
| 9 | NESTED LOOPS OUTER | | 300 | 94500 | 442 (2)| 00:00:06 |
| 10 | NESTED LOOPS OUTER | | 300 | 84300 | 442 (2)| 00:00:06 |
| 11 | NESTED LOOPS OUTER | | 300 | 82800 | 442 (2)| 00:00:06 |
|* 12 | HASH JOIN OUTER | | 300 | 81300 | 442 (2)| 00:00:06 |
| 13 | NESTED LOOPS OUTER | | 300 | 78300 | 368 (2)| 00:00:05 |
|* 14 | HASH JOIN RIGHT OUTER | | 300 | 77100 | 367 (1)| 00:00:05 |
| 15 | TABLE ACCESS FULL | KNTA_REGIONS | 51 | 408 | 2 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 300 | 74700 | 365 (1)| 00:00:05 |
|* 17 | HASH JOIN | | 300 | 70800 | 365 (1)| 00:00:05 |
|* 18 | HASH JOIN | | 6000 | 339K| 90 (2)| 00:00:02 |
| 19 | NESTED LOOPS | | 6000 | 281K| 16 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID| WP_TASKS | 1 | 10 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SYS_C00298557 | 1 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID| WP_TASK_SCHEDULE | 366K| 7510K| 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SYS_C00298562 | 1 | | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | RSC_RP_DISTRIBUTION_ENTRIES | 6000 | 99K| 13 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | RSC_RESOURCES | 21008 | 205K| 73 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | KNTA_USERS | 21008 | 3651K| 274 (1)| 00:00:04 |
|* 28 | INDEX UNIQUE SCAN | KACC_ACCELERATORS_U1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | KCST_CURRENCIES_U1 | 1 | 4 | 0 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | RSC_RESOURCES | 21008 | 205K| 73 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | SYS_C00298535 | 1 | 5 | 0 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 5 | 0 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
| 38 | SORT UNIQUE | | 2 | 71 | 12 (67)| 00:00:01 |
| 39 | UNION-ALL | | | | | |
|* 40 | HASH JOIN | | 1 | 23 | 5 (20)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | RSC_RP_DISTRIBUTION_ENTRIES | 1 | 10 | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | FK63EC4E39A1B1A5A3_I | 1 | | 1 (0)| 00:00:01 |
| 43 | VIEW | VW_NSO_1 | 3 | 39 | 2 (0)| 00:00:01 |
|* 44 | CONNECT BY WITH FILTERING | | | | | |
| 45 | TABLE ACCESS BY INDEX ROWID | RSC_RESOURCE_POOLS | | | | |
| 46 | NESTED LOOPS SEMI | | 1 | 14 | 1 (0)| 00:00:01 |
| 47 | INDEX FULL SCAN | SYS_C00298531 | 201 | 1005 | 1 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | SYS_C00298532 | 1 | 9 | 0 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | | | | |
| 50 | BUFFER SORT | | | | | |
| 51 | CONNECT BY PUMP | | | | | |
| 52 | TABLE ACCESS BY INDEX ROWID | RSC_RESOURCE_POOLS | 3 | 30 | 2 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | FK6AF57803760C2955_I | 3 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS FULL | RSC_RESOURCE_POOLS | 3 | 30 | 2 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | SYS_C00298532 | 1 | 9 | 0 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 48 | 5 (0)| 00:00:01 |
| 57 | MERGE JOIN CARTESIAN | | 1 | 38 | 4 (0)| 00:00:01 |
| 58 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
| 59 | NESTED LOOPS | | 1 | 15 | 1 (0)| 00:00:01 |
| 60 | TABLE ACCESS BY INDEX ROWID | PM_WORK_PLANS | 1 | 10 | 1 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | SYS_C00298525 | 1 | | 0 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | SYS_C00298505 | 150 | 750 | 0 (0)| 00:00:01 |
| 63 | TABLE ACCESS BY INDEX ROWID | RSC_STAFFING_PROFILES | 1 | 13 | 1 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | RSC_STAFFING_PROFILES_N1 | 1 | | 0 (0)| 00:00:01 |
| 65 | BUFFER SORT | | 1 | 10 | 3 (0)| 00:00:01 |
| 66 | TABLE ACCESS BY INDEX ROWID | RSC_RESOURCE_ASSIGNMENTS | 1 | 10 | 2 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | FKA1F58E72A1B1A5A3_I | 1 | | 1 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID | RSC_POSITIONS | 1 | 10 | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | SYS_C00298527 | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
But when I remove the outer "IN" clause, use that as a data source and change the IN to "=" it executes in 0.4 sec with barely 4K LIO. Why? The change in plan is also observed if I remove -
DECODE ('N', 'Y', 1, 'N', 2) = 1
(Well, I know this is redundant -- but in this web application this is a part 'manipulated by token')
SELECT DISTINCT r.user_id, users.full_name, users.username,
(SELECT lookups.meaning
FROM knta_lookups lookups
WHERE lookups.lookup_type = 'DEPT'
AND lookups.lookup_code = users.department_code),
users.email_address
FROM rsc_resources r,
rsc_rp_distribution_entries de,
wp_tasks tasks,
wp_task_schedule sched,
knta_users_v users,
( SELECT dist.resource_id
FROM rsc_rp_distribution_entries dist
WHERE dist.resource_pool_id IN (
SELECT resource_pool_id
FROM rsc_resource_pools rp
START WITH rp.resource_pool_id IN (
-- get resource pools directly managed by user
SELECT resource_pool_id
FROM rsc_resource_pool_managers
WHERE manager_user_id = 30030)
CONNECT BY rp.parent_resource_pool_id = PRIOR rp.resource_pool_id)
UNION
SELECT rsc_assign.resource_id
FROM pm_work_plans wp,
pm_projects projects,
rsc_staffing_profiles sp,
rsc_positions p,
rsc_resource_assignments rsc_assign
WHERE rsc_assign.position_id = p.position_id
AND p.staffing_profile_id = sp.staffing_profile_id
AND sp.container_entity_type_code = 1 -- project type
AND sp.container_entity_id = projects.project_id
AND projects.project_id = wp.project_id
AND 30002 = wp.work_plan_id
) inner
WHERE de.resource_id = r.resource_id
AND de.distribution_percent > 0
-- show resources from the associated Staffing Profile + users managed by the current user
AND ( DECODE ('N', 'Y', 1, 'N', 2)= 1 OR r.resource_id =inner.resource_id)
AND r.user_id = users.user_id
AND tasks.task_id = 30000
AND sched.task_schedule_id = tasks.task_schedule_id
AND NVL (users.end_date, sched.sched_start_date) >= sched.sched_start_date
AND ( ( de.start_time <= sched.sched_finish_date AND de.finish_time >= sched.sched_start_date)
OR ( de.finish_time IS NULL AND de.start_time <= sched.sched_finish_date)) ORDER BY users.full_name;
There's a distinct change in explain plan. I need to run a 10053 level trace to find out why!
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 928 | 164 (4)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | KNTA_LOOKUPS | 1 | 47 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 928 | 163 (3)| 00:00:02 |
| 4 | NESTED LOOPS OUTER | | 2 | 928 | 163 (3)| 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 2 | 860 | 163 (3)| 00:00:02 |
| 6 | NESTED LOOPS OUTER | | 2 | 792 | 163 (3)| 00:00:02 |
| 7 | NESTED LOOPS OUTER | | 2 | 724 | 163 (3)| 00:00:02 |
| 8 | NESTED LOOPS OUTER | | 2 | 656 | 163 (3)| 00:00:02 |
| 9 | NESTED LOOPS OUTER | | 2 | 588 | 163 (3)| 00:00:02 |
| 10 | NESTED LOOPS OUTER | | 2 | 578 | 163 (3)| 00:00:02 |
| 11 | NESTED LOOPS OUTER | | 2 | 558 | 159 (3)| 00:00:02 |
| 12 | NESTED LOOPS OUTER | | 2 | 548 | 159 (3)| 00:00:02 |
| 13 | NESTED LOOPS OUTER | | 2 | 540 | 159 (3)| 00:00:02 |
| 14 | NESTED LOOPS OUTER | | 2 | 524 | 157 (3)| 00:00:02 |
| 15 | NESTED LOOPS | | 2 | 498 | 157 (3)| 00:00:02 |
|* 16 | HASH JOIN | | 37 | 2627 | 120 (4)| 00:00:02 |
|* 17 | HASH JOIN | | 130 | 7020 | 107 (4)| 00:00:02 |
| 18 | NESTED LOOPS | | 130 | 5720 | 33 (10)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | WP_TASKS | 1 | 10 | 2 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | SYS_C00298557 | 1 | | 1 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | WP_TASK_SCHEDULE | 366K| 7510K| 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C00298562 | 1 | | 0 (0)| 00:00:01 |
| 24 | VIEW | | 130 | 1690 | 30 (10)| 00:00:01 |
| 25 | SORT UNIQUE | | 130 | 3990 | 30 (74)| 00:00:01 |
| 26 | UNION-ALL | | | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | RSC_RP_DISTRIBUTION_ENTRIES | 30 | 300 | 2 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 90 | 2070 | 8 (0)| 00:00:01 |
| 29 | VIEW | VW_NSO_1 | 3 | 39 | 2 (0)| 00:00:01 |
|* 30 | CONNECT BY WITH FILTERING | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | RSC_RESOURCE_POOLS | | | | |
| 32 | NESTED LOOPS | | 1 | 14 | 1 (0)| 00:00:01 |
|* 33 | INDEX FULL SCAN | SYS_C00298532 | 1 | 9 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | SYS_C00298531 | 1 | 5 | 0 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | | | | |
| 36 | BUFFER SORT | | | | | |
| 37 | CONNECT BY PUMP | | | | | |
| 38 | TABLE ACCESS BY INDEX ROWID| RSC_RESOURCE_POOLS | 3 | 30 | 2 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | FK6AF57803760C2955_I | 3 | | 1 (0)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | RSC_RESOURCE_POOLS | 3 | 30 | 2 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | SYS_C00298532 | 1 | 9 | 0 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | FK63EC4E39A85CA660_I | 30 | | 1 (0)| 00:00:01 |
|* 43 | HASH JOIN | | 40 | 1920 | 20 (5)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | RSC_POSITIONS | 13 | 130 | 2 (0)| 00:00:01 |
| 45 | NESTED LOOPS | | 13 | 494 | 4 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
| 47 | NESTED LOOPS | | 1 | 15 | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID| PM_WORK_PLANS | 1 | 10 | 1 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | SYS_C00298525 | 1 | | 0 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | SYS_C00298505 | 150 | 750 | 0 (0)| 00:00:01 |
| 51 | TABLE ACCESS BY INDEX ROWID | RSC_STAFFING_PROFILES | 1 | 13 | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | RSC_STAFFING_PROFILES_N1 | 1 | | 0 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | FK4C98D6AD3F9006BA_I | 13 | | 1 (0)| 00:00:01 |
| 54 | TABLE ACCESS FULL | RSC_RESOURCE_ASSIGNMENTS | 6000 | 60000 | 15 (0)| 00:00:01 |
| 55 | TABLE ACCESS FULL | RSC_RESOURCES | 21008 | 205K| 73 (0)| 00:00:01 |
|* 56 | TABLE ACCESS FULL | RSC_RP_DISTRIBUTION_ENTRIES | 6000 | 99K| 13 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | KNTA_USERS | 1 | 178 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | | 0 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | KACC_ACCELERATORS_U1 | 1 | 13 | 0 (0)| 00:00:01 |
| 60 | TABLE ACCESS BY INDEX ROWID | KNTA_REGIONS | 1 | 8 | 1 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | KNTA_REGIONS_U1 | 1 | | 0 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | KCST_CURRENCIES_U1 | 1 | 4 | 0 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | KNTA_USERS_U1 | 1 | 5 | 0 (0)| 00:00:01 |
| 64 | TABLE ACCESS BY INDEX ROWID | RSC_RESOURCES | 1 | 10 | 2 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | FK70A50868C55496FA_I | 1 | | 1 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | SYS_C00298535 | 1 | 5 | 0 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 68 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | KNTA_LOOKUPS_U1 | 1 | 34 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------
New V$SQLAREA in Oracle 10g-R2 (10.2)
I was working on an Oracle 10.2 database yesterday and learnt that V$SQLAREA -- the view that I primarily use to analyze DB performance issues from a high level -- has been well enhanced in this release.
To compare -- 10.2 definition of this view looks like -
10.1 version definition -
I'm much excited about the 10.2 view because I can do the following --
(1) Find out what are the most expensive SQLs. And, to get the full version of it, I do not need to go to V$SQL anymore -- there's SQL_FULLTEXT column on 10.2!
(2) I can get the PLAN_HASH_VALUE from V$SQLAREA and query V$SQL_PLAN to get the explain plan of the SQL!
Note - for a PL/SQL code block, PLAN_HASH_VALUE will be 0!
(3) I don't have to join with DBA_USERS to get the name of the parsing user - 10.2 has a column called PARSING_SCHEMA_NAME.
Yesterday, actually I found out a SQL - got it's plan - saw that it's doing "index skip scan" - and then analyzed the table 'well' to get rid of the skip scan. The whole process is highly doable with 10.1 or a before version, but it was really a cinch with 10.2.
I am off to buy more Oracle stocks! The database really rocks.
To compare -- 10.2 definition of this view looks like -
| Column | Datatype | Description |
|---|---|---|
SQL_TEXT | VARCHAR2(1000) | First thousand characters of the SQL text for the current cursor |
SQL_ID | VARCHAR2(13) | SQL identifier of the parent cursor in the library cache |
SHARABLE_MEM | NUMBER | Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors. |
PERSISTENT_MEM | NUMBER | Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors. |
RUNTIME_MEM | NUMBER | Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed sum of all memory required during execution of all the child cursors. |
SORTS | NUMBER | Sum of the number of sorts that were done for all the child cursors |
VERSION_COUNT | NUMBER | Number of child cursors that are present in the cache under this parent |
LOADED_VERSIONS | NUMBER | Number of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded |
OPEN_VERSIONS | NUMBER | The number of child cursors that are currently open under this current parent |
USERS_OPENING | NUMBER | Number of users that have any of the child cursors open |
FETCHES | NUMBER | Number of fetches associated with the SQL statement |
EXECUTIONS | NUMBER | Total number of executions, totalled over all the child cursors |
END_OF_FETCH_COUNT | NUMBER | Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column. |
USERS_EXECUTING | NUMBER | Total number of users executing the statement over all child cursors |
LOADS | NUMBER | Number of times the object was loaded or reloaded |
FIRST_LOAD_TIME | VARCHAR2(19) | Timestamp of the parent creation time |
INVALIDATIONS | NUMBER | Total number of invalidations over all the child cursors |
PARSE_CALLS | NUMBER | Sum of all parse calls to all the child cursors under this parent |
DISK_READS | NUMBER | Sum of the number of disk reads over all child cursors |
DIRECT_WRITES | NUMBER | Sum of the number of direct writes over all child cursors |
BUFFER_GETS | NUMBER | Sum of buffer gets over all child cursors |
APPLICATION_WAIT_TIME | NUMBER | Application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Cluster wait time |
USER_IO_WAIT_TIME | NUMBER | User I/O Wait Time |
PLSQL_EXEC_TIME | NUMBER | PL/SQL execution time |
JAVA_EXEC_TIME | NUMBER | Java execution time |
ROWS_PROCESSED | NUMBER | Total number of rows processed on behalf of this SQL statement |
COMMAND_TYPE | NUMBER | Oracle command type definition |
OPTIMIZER_MODE | VARCHAR2(25) | Mode under which the SQL statement was executed |
PARSING_USER_ID | NUMBER | User ID of the user that has parsed the very first cursor under this parent |
PARSING_SCHEMA_ID | NUMBER | Schema ID that was used to parse this child cursor |
KEPT_VERSIONS | NUMBER | Number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package |
ADDRESS | RAW(4 | 8) | Address of the handle to the parent for this cursor |
HASH_VALUE | NUMBER | Hash value of the parent statement in the library cache |
OLD_HASH_VALUE | NUMBER | Old SQL hash value |
MODULE | VARCHAR2(64) | Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE |
MODULE_HASH | NUMBER | Hash value of the module that is named in the MODULE column |
ACTION | VARCHAR2(64) | Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION |
ACTION_HASH | NUMBER | Hash value of the action that is named in the ACTION column |
SERIALIZABLE_ABORTS | NUMBER | Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors |
CPU_TIME | NUMBER | CPU time (in microseconds) used by this cursor for parsing/executing/fetching |
ELAPSED_TIME | NUMBER | Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching |
IS_OBSOLETE | VARCHAR2(1) | Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large. |
CHILD_LATCH | NUMBER | Child latch number that is protecting the cursor |
PROGRAM_ID | NUMBER | Program identifier |
10.1 version definition -
| Column | Datatype | Description |
|---|---|---|
SQL_TEXT | VARCHAR2(1000) | First thousand characters of the SQL text for the current cursor |
SQL_ID | VARCHAR2(13) | SQL identifier of the parent cursor in the library cache |
SHARABLE_MEM | NUMBER | Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors. |
PERSISTENT_MEM | NUMBER | Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors. |
RUNTIME_MEM | NUMBER | Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed sum of all memory required during execution of all the child cursors. |
SORTS | NUMBER | Sum of the number of sorts that were done for all the child cursors |
VERSION_COUNT | NUMBER | Number of child cursors that are present in the cache under this parent |
LOADED_VERSIONS | NUMBER | Number of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded |
OPEN_VERSIONS | NUMBER | The number of child cursors that are currently open under this current parent |
USERS_OPENING | NUMBER | Number of users that have any of the child cursors open |
FETCHES | NUMBER | Number of fetches associated with the SQL statement |
EXECUTIONS | NUMBER | Total number of executions, totalled over all the child cursors |
END_OF_FETCH_COUNT | NUMBER | Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column. |
USERS_EXECUTING | NUMBER | Total number of users executing the statement over all child cursors |
LOADS | NUMBER | Number of times the object was loaded or reloaded |
FIRST_LOAD_TIME | VARCHAR2(19) | Timestamp of the parent creation time |
INVALIDATIONS | NUMBER | Total number of invalidations over all the child cursors |
PARSE_CALLS | NUMBER | Sum of all parse calls to all the child cursors under this parent |
DISK_READS | NUMBER | Sum of the number of disk reads over all child cursors |
DIRECT_WRITES | NUMBER | Sum of the number of direct writes over all child cursors |
BUFFER_GETS | NUMBER | Sum of buffer gets over all child cursors |
APPLICATION_WAIT_TIME | NUMBER | Application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Cluster wait time |
USER_IO_WAIT_TIME | NUMBER | User I/O Wait Time |
PLSQL_EXEC_TIME | NUMBER | PL/SQL execution time |
JAVA_EXEC_TIME | NUMBER | Java execution time |
ROWS_PROCESSED | NUMBER | Total number of rows processed on behalf of this SQL statement |
COMMAND_TYPE | NUMBER | Oracle command type definition |
OPTIMIZER_MODE | VARCHAR2(25) | Mode under which the SQL statement was executed |
PARSING_USER_ID | NUMBER | User ID of the user that has parsed the very first cursor under this parent |
PARSING_SCHEMA_ID | NUMBER | Schema ID that was used to parse this child cursor |
KEPT_VERSIONS | NUMBER | Number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package |
ADDRESS | RAW(4 | 8) | Address of the handle to the parent for this cursor |
HASH_VALUE | NUMBER | Hash value of the parent statement in the library cache |
OLD_HASH_VALUE | NUMBER | Old SQL hash value |
MODULE | VARCHAR2(64) | Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE |
MODULE_HASH | NUMBER | Hash value of the module that is named in the MODULE column |
ACTION | VARCHAR2(64) | Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION |
ACTION_HASH | NUMBER | Hash value of the action that is named in the ACTION column |
SERIALIZABLE_ABORTS | NUMBER | Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors |
CPU_TIME | NUMBER | CPU time (in microseconds) used by this cursor for parsing/executing/fetching |
ELAPSED_TIME | NUMBER | Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching |
IS_OBSOLETE | VARCHAR2(1) | Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large. |
CHILD_LATCH | NUMBER | Child latch number that is protecting the cursor |
PROGRAM_ID | NUMBER | Program identifier |
I'm much excited about the 10.2 view because I can do the following --
(1) Find out what are the most expensive SQLs. And, to get the full version of it, I do not need to go to V$SQL anymore -- there's SQL_FULLTEXT column on 10.2!
(2) I can get the PLAN_HASH_VALUE from V$SQLAREA and query V$SQL_PLAN to get the explain plan of the SQL!
Note - for a PL/SQL code block, PLAN_HASH_VALUE will be 0!
(3) I don't have to join with DBA_USERS to get the name of the parsing user - 10.2 has a column called PARSING_SCHEMA_NAME.
Yesterday, actually I found out a SQL - got it's plan - saw that it's doing "index skip scan" - and then analyzed the table 'well' to get rid of the skip scan. The whole process is highly doable with 10.1 or a before version, but it was really a cinch with 10.2.
I am off to buy more Oracle stocks! The database really rocks.
Wednesday, October 04, 2006
Do you really need a UNION - more example of bad coding!
Today I was reviewing a code for a materialized view (part of) that looked like this -
Now, look at that code carefully. All the UNION parts are bringing basically the same data (same WHERE clause etc) except that each part merges the data with a different month, there being 12 UNIONed parts for 12 months in a year.
The plan for this SQL is horrendous (actual SQL was far worse, being different UNION parts for different request types as well!) -
Look at how many times the large table KCRT_REQUEST_DETAILS is full-scanned. Each UNIONed part has FOUR full-table scans for that table and there are total of 12 UNION-ed parts. - totalling a whooping 48 full-scans of KCRT_REQUEST_DETAILS tables.
Now, let's create a "static" data table that will hold the months in a year.
Now, rewrite the SQL as -
The explain plan of which is much more friendly than before -
So, from 48 full-scan we're now back to 4! The response time, predictably, is about 15 times faster!
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'January' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER17 V0,
RD4.VISIBLE_PARAMETER20 D0,
RD5.VISIBLE_PARAMETER48 V1,
RD6.VISIBLE_PARAMETER1 D1,
RD7.VISIBLE_PARAMETER26 V2,
RD7.VISIBLE_PARAMETER29 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'February' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER23 V0,
RD4.VISIBLE_PARAMETER25 D0,
RD6.VISIBLE_PARAMETER4 V1,
RD6.VISIBLE_PARAMETER7 D1,
RD7.VISIBLE_PARAMETER32 V2,
RD7.VISIBLE_PARAMETER35 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'March' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER28 V0,
RD4.VISIBLE_PARAMETER31 D0,
RD6.VISIBLE_PARAMETER10 V1,
RD6.VISIBLE_PARAMETER13 D1,
RD7.VISIBLE_PARAMETER38 V2,
RD7.VISIBLE_PARAMETER41 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'April' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER34 V0,
RD4.VISIBLE_PARAMETER37 D0,
RD6.VISIBLE_PARAMETER16 V1,
RD6.VISIBLE_PARAMETER19 D1,
RD7.VISIBLE_PARAMETER44 V2,
RD7.VISIBLE_PARAMETER47 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'May' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER40 V0,
RD4.VISIBLE_PARAMETER46 D0,
RD6.VISIBLE_PARAMETER22 V1,
RD6.VISIBLE_PARAMETER25 D1,
RD7.VISIBLE_PARAMETER50 V2,
RD8.VISIBLE_PARAMETER3 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'June' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER49 V0,
RD5.VISIBLE_PARAMETER2 D0,
RD6.VISIBLE_PARAMETER28 V1,
RD6.VISIBLE_PARAMETER31 D1,
RD8.VISIBLE_PARAMETER6 V2,
RD8.VISIBLE_PARAMETER9 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'July' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER5 V0,
RD5.VISIBLE_PARAMETER8 D0,
RD6.VISIBLE_PARAMETER34 V1,
RD6.VISIBLE_PARAMETER37 D1,
RD8.VISIBLE_PARAMETER12 V2,
RD8.VISIBLE_PARAMETER15 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'August' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER11 V0,
RD5.VISIBLE_PARAMETER14 D0,
RD6.VISIBLE_PARAMETER40 V1,
RD6.VISIBLE_PARAMETER43 D1,
RD8.VISIBLE_PARAMETER18 V2,
RD8.VISIBLE_PARAMETER21 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'September' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER17 V0,
RD5.VISIBLE_PARAMETER20 D0,
RD6.VISIBLE_PARAMETER46 V1,
RD6.VISIBLE_PARAMETER49 D1,
RD8.VISIBLE_PARAMETER24 V2,
RD8.VISIBLE_PARAMETER27 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'October' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER23 V0,
RD5.VISIBLE_PARAMETER26 D0,
RD7.VISIBLE_PARAMETER2 V1,
RD7.VISIBLE_PARAMETER5 D1,
RD8.VISIBLE_PARAMETER30 V2,
RD8.VISIBLE_PARAMETER33 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'November' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER29 V0,
RD5.VISIBLE_PARAMETER32 D0,
RD7.VISIBLE_PARAMETER8 V1,
RD7.VISIBLE_PARAMETER11 D1,
RD8.VISIBLE_PARAMETER36 V2,
RD8.VISIBLE_PARAMETER39 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'December' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER35 V0,
RD5.VISIBLE_PARAMETER38 D0,
RD7.VISIBLE_PARAMETER14 V1,
RD7.VISIBLE_PARAMETER17 D1,
RD8.VISIBLE_PARAMETER42 V2,
RD8.VISIBLE_PARAMETER45 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
Now, look at that code carefully. All the UNION parts are bringing basically the same data (same WHERE clause etc) except that each part merges the data with a different month, there being 12 UNIONed parts for 12 months in a year.
The plan for this SQL is horrendous (actual SQL was far worse, being different UNION parts for different request types as well!) -
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76656 | 37M| 19864 |
| 1 | SORT UNIQUE | | 76656 | 37M| 19864 |
| 2 | UNION-ALL | | | | |
| 3 | HASH JOIN | | 6388 | 3493K| 1324 |
| 4 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 5 | HASH JOIN | | 5750 | 3099K| 1279 |
| 6 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 7 | HASH JOIN | | 5175 | 2749K| 1236 |
| 8 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 661K| 177 |
| 9 | HASH JOIN | | 4657 | 2392K| 1029 |
| 10 | HASH JOIN | | 4192 | 1408K| 760 |
| 11 | HASH JOIN | | 3773 | 854K| 544 |
| 12 | HASH JOIN | | 3395 | 397K| 348 |
| 13 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 14 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 15 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 16 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 17 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 18 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 19 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 20 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 21 | HASH JOIN | | 6388 | 2838K| 1095 |
| 22 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 23 | HASH JOIN | | 5750 | 2510K| 1053 |
| 24 | HASH JOIN | | 5175 | 1339K| 788 |
| 25 | HASH JOIN | | 4657 | 704K| 579 |
| 26 | HASH JOIN | | 4192 | 176K| 392 |
| 27 | HASH JOIN | | 3773 | 98098 | 212 |
| 28 | HASH JOIN | | 3395 | 61110 | 189 |
| 29 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 30 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 31 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 32 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 33 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 34 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 35 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 625K| 177 |
| 36 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 37 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 38 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 39 | HASH JOIN | | 6388 | 3892K| 1203 |
| 40 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 41 | HASH JOIN | | 5750 | 3458K| 1156 |
| 42 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 43 | HASH JOIN | | 5175 | 3072K| 1111 |
| 44 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 45 | HASH JOIN | | 4657 | 2728K| 1068 |
| 46 | HASH JOIN | | 4192 | 1711K| 780 |
| 47 | HASH JOIN | | 3773 | 869K| 545 |
| 48 | HASH JOIN | | 3395 | 397K| 348 |
| 49 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 50 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 51 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 52 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 53 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 54 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4265K| 177 |
| 55 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 56 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 57 | HASH JOIN | | 6388 | 3867K| 1199 |
| 58 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 59 | HASH JOIN | | 5750 | 3436K| 1152 |
| 60 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 61 | HASH JOIN | | 5175 | 3052K| 1107 |
| 62 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 63 | HASH JOIN | | 4657 | 2710K| 1064 |
| 64 | HASH JOIN | | 4192 | 1694K| 778 |
| 65 | HASH JOIN | | 3773 | 854K| 544 |
| 66 | HASH JOIN | | 3395 | 397K| 348 |
| 67 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 68 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 69 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 70 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 71 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 72 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 73 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 74 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 75 | HASH JOIN | | 6388 | 3880K| 1357 |
| 76 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 77 | HASH JOIN | | 5750 | 3447K| 1310 |
| 78 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 367K| 177 |
| 79 | HASH JOIN | | 5175 | 3052K| 1107 |
| 80 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 81 | HASH JOIN | | 4657 | 2710K| 1064 |
| 82 | HASH JOIN | | 4192 | 1694K| 778 |
| 83 | HASH JOIN | | 3773 | 854K| 544 |
| 84 | HASH JOIN | | 3395 | 397K| 348 |
| 85 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 86 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 87 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 88 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 89 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 90 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 91 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 92 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 93 | HASH JOIN | | 6388 | 3337K| 1296 |
| 94 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 588K| 177 |
| 95 | HASH JOIN | | 5750 | 2914K| 1087 |
| 96 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 97 | HASH JOIN | | 5175 | 2582K| 1045 |
| 98 | HASH JOIN | | 4657 | 1496K| 771 |
| 99 | HASH JOIN | | 4192 | 601K| 553 |
| 100 | HASH JOIN | | 3773 | 136K| 368 |
| 101 | HASH JOIN | | 3395 | 61110 | 189 |
| 102 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 103 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 104 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 105 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 106 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 107 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 698K| 177 |
| 108 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 109 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 110 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 111 | HASH JOIN | | 6388 | 2857K| 1098 |
| 112 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 113 | HASH JOIN | | 5750 | 2526K| 1056 |
| 114 | HASH JOIN | | 5175 | 1354K| 790 |
| 115 | HASH JOIN | | 4657 | 718K| 580 |
| 116 | HASH JOIN | | 4192 | 188K| 392 |
| 117 | HASH JOIN | | 3773 | 98098 | 212 |
| 118 | HASH JOIN | | 3395 | 61110 | 189 |
| 119 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 120 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 121 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 122 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 123 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 124 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 125 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 735K| 177 |
| 126 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 127 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 128 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 129 | HASH JOIN | | 6388 | 3268K| 1132 |
| 130 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 131 | HASH JOIN | | 5750 | 2897K| 1088 |
| 132 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 133 | HASH JOIN | | 5175 | 2567K| 1046 |
| 134 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 588K| 177 |
| 135 | HASH JOIN | | 4657 | 2237K| 842 |
| 136 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 137 | HASH JOIN | | 4192 | 1981K| 803 |
| 138 | HASH JOIN | | 3773 | 1112K| 553 |
| 139 | HASH JOIN | | 3395 | 397K| 348 |
| 140 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 141 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 142 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 143 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 144 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 145 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 146 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 147 | HASH JOIN | | 6388 | 3431K| 1158 |
| 148 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 149 | HASH JOIN | | 5750 | 3043K| 1113 |
| 150 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 151 | HASH JOIN | | 5175 | 2698K| 1070 |
| 152 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 153 | HASH JOIN | | 4657 | 2392K| 1029 |
| 154 | HASH JOIN | | 4192 | 1408K| 760 |
| 155 | HASH JOIN | | 3773 | 854K| 544 |
| 156 | HASH JOIN | | 3395 | 397K| 348 |
| 157 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 158 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 159 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 160 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 161 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 162 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 163 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 164 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 165 | HASH JOIN | | 6388 | 2850K| 1097 |
| 166 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 167 | HASH JOIN | | 5750 | 2521K| 1055 |
| 168 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 169 | HASH JOIN | | 5175 | 2228K| 1015 |
| 170 | HASH JOIN | | 4657 | 1505K| 772 |
| 171 | HASH JOIN | | 4192 | 609K| 553 |
| 172 | HASH JOIN | | 3773 | 136K| 368 |
| 173 | HASH JOIN | | 3395 | 61110 | 189 |
| 174 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 175 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 176 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 177 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 178 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 179 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 698K| 177 |
| 180 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 181 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 182 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 183 | HASH JOIN | | 6388 | 2252K| 1038 |
| 184 | HASH JOIN | | 5750 | 1005K| 793 |
| 185 | HASH JOIN | | 5175 | 348K| 598 |
| 186 | HASH JOIN | | 4657 | 209K| 416 |
| 187 | HASH JOIN | | 4192 | 155K| 392 |
| 188 | HASH JOIN | | 3773 | 110K| 368 |
| 189 | HASH JOIN | | 3395 | 61110 | 189 |
| 190 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 191 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 192 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 193 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 194 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 195 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 441K| 177 |
| 196 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 197 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 198 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 845K| 177 |
| 199 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 200 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 201 | HASH JOIN | | 6388 | 2807K| 1091 |
| 202 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 203 | HASH JOIN | | 5750 | 2481K| 1049 |
| 204 | HASH JOIN | | 5175 | 1313K| 786 |
| 205 | HASH JOIN | | 4657 | 673K| 578 |
| 206 | HASH JOIN | | 4192 | 155K| 392 |
| 207 | HASH JOIN | | 3773 | 110K| 368 |
| 208 | HASH JOIN | | 3395 | 61110 | 189 |
| 209 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 210 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 211 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 212 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 213 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 214 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 441K| 177 |
| 215 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 216 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 217 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 218 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
---------------------------------------------------------------------------------------------------
Look at how many times the large table KCRT_REQUEST_DETAILS is full-scanned. Each UNIONed part has FOUR full-table scans for that table and there are total of 12 UNION-ed parts. - totalling a whooping 48 full-scans of KCRT_REQUEST_DETAILS tables.
Now, let's create a "static" data table that will hold the months in a year.
CREATE TABLE stat_months(month_name VARCHAR2(20));
INSERT INTO stat_months (SELECT DISTINCT(TO_CHAR(creation_date,'MONTH')) FROM kcrt_requests);
-- check if ITG_MONTHS have 12 months. If not, manually insert them.
Now, rewrite the SQL as -
SELECT R.REQUEST_ID "REQUEST_ID",
'Proposal' "PROMPT",
month_name "MONTH",
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER17 V0,
RD4.VISIBLE_PARAMETER20 D0,
RD5.VISIBLE_PARAMETER48 V1,
RD6.VISIBLE_PARAMETER1 D1,
RD7.VISIBLE_PARAMETER26 V2,
RD7.VISIBLE_PARAMETER29 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
STAT_MONTHS itg_m,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (SELECT request_type_id FROM kcrt_request_types WHERE request_type_name IN ('MKTG - IB Request'))
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
The explain plan of which is much more friendly than before -
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76661 | 41M| 4227 |
| 1 | HASH JOIN | | 76661 | 41M| 4227 |
| 2 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 3 | HASH JOIN | | 68996 | 25M| 3520 |
| 4 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 5 | HASH JOIN | | 62097 | 16M| 3001 |
| 6 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 7 | HASH JOIN | | 55888 | 8896K| 2604 |
| 8 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 9 | HASH JOIN | | 50300 | 7613K| 2531 |
| 10 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 11 | HASH JOIN | | 45271 | 6498K| 2464 |
| 12 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 661K| 177 |
| 13 | HASH JOIN | | 40744 | 5132K| 2230 |
| 14 | MERGE JOIN CARTESIAN | | 36670 | 680K| 2006 |
| 15 | TABLE ACCESS FULL | STAT_MONTHS | 12 | 108 | 2 |
| 16 | BUFFER SORT | | 3056 | 30560 | 2004 |
| 17 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 18 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 19 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 20 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 21 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
---------------------------------------------------------------------------------------------------
So, from 48 full-scan we're now back to 4! The response time, predictably, is about 15 times faster!
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
