Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.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 |
------------------------------------------------------------------------------------------------------------------------------------
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
