Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Tuesday, May 27, 2008
How to avoid "ORA-02016: cannot use a subquery in a START WITH on a remote database" or generally "workaround" Oracle query issues
This morning, I was forwarded an interesting problem. A hierarchical SQL query (i.e., with CONNECT BY - START WITH clause) operating on a view based on remote database table (DB Link) was failing on an Oracle 10.2.0.2 database.
The query looked like -
Where the lineitem_vw is a view defined on remote table(s) accessed with database link(s).
The way I solved this problem -
(1) Find out what the ORA-2016 error is all about. Google is the usual favorite, though I really chasten myself to use Metalink for researching such errors. The main reason behind going to Metalink is, Google cannot search Metalink and thus cannot show me if this error is, say, because of a bug in a particular database version OR if there is already a workaround for this available.
The result was a crisp one record just stating the description of the error -
(2) Now that I knew the problem is because of the particular interaction between "CONNECT BY" and "Accessing remote data using DB link" happening together, my objective was to separate these two so each happens in a separate "thread" from the other.
Subquery-factoring or WITH clause came to use again. I took out and "materialized" the remote dataset ahead of the query execution so the CONNECT BY gets a ready-made dataset to build the tree upon.
The revised query looked like -
To make sure that the subquery is indeed factored ahead, I added the MATERIALIZE hint.
The revised query worked like a charm!
The query looked like -
SELECT a.*
FROM (SELECT lineitem.*,
ROW_NUMBER () OVER (PARTITION BY lineitem.tran_id ORDER BY LEVEL) sequence_number
FROM (SELECT *
FROM lineitem_vw
WHERE ID = 1122) lineitem
START WITH lineitem.ID IN (SELECT ID
FROM header_vw
WHERE company_id = 1122)
CONNECT BY PRIOR lineitem.next_id = lineitem.target_id) a;
Where the lineitem_vw is a view defined on remote table(s) accessed with database link(s).
The way I solved this problem -
(1) Find out what the ORA-2016 error is all about. Google is the usual favorite, though I really chasten myself to use Metalink for researching such errors. The main reason behind going to Metalink is, Google cannot search Metalink and thus cannot show me if this error is, say, because of a bug in a particular database version OR if there is already a workaround for this available.
The result was a crisp one record just stating the description of the error -
Error: ORA 2016
Text: cannot use a subquery in a START WITH on a remote database
-------------------------------------------------------------------------------
Cause: An attempt was made to use a subquery in a START WITH clause.
Such a subquery is not currently permitted if the table in the FROM
clause of the tree query is in a remote database.
Action: It is necessary to connect directly to the remote database before
using a subquery in the START WITH clause.
(2) Now that I knew the problem is because of the particular interaction between "CONNECT BY" and "Accessing remote data using DB link" happening together, my objective was to separate these two so each happens in a separate "thread" from the other.
Subquery-factoring or WITH clause came to use again. I took out and "materialized" the remote dataset ahead of the query execution so the CONNECT BY gets a ready-made dataset to build the tree upon.
The revised query looked like -
with prebuilt_remote_data as (SELECT /*+ MATERIALIZE */ *
FROM lineitem_vw
WHERE ID = 1122)
SELECT a.*
FROM (SELECT lineitem.*,
ROW_NUMBER () OVER (PARTITION BY lineitem.tran_id ORDER BY LEVEL) sequence_number
FROM (select * from prebuilt_remote_data) lineitem
START WITH lineitem.ID IN (SELECT ID
FROM header_vw
WHERE company_id = 1122)
CONNECT BY PRIOR lineitem.next_id = lineitem.target_id) a;
To make sure that the subquery is indeed factored ahead, I added the MATERIALIZE hint.
The revised query worked like a charm!
Wednesday, January 10, 2007
Replace "Not Exists" by Outer Join (with NULL)
Often, tuning is just iterative. You try on a few possible options till it clicks. Then, you often analyze to learn why something worked or why the other didn't. Replacing NOT EXISTS by OUTER JOIN (like EXISTS with INNER JOIN) can be one such iterative option --
The code
is equivalent to -
In the old pre-ANSI OUTER JOIN syntax this looks like --
It's TA is OUTER JOINED with A -- and for every record where TA doesn't have a value for AU_ID! Same as NOT EXISTS.
The code
SELECT au_lname, au_fname
FROM authors AS a
WHERE NOT EXISTS (
SELECT *
FROM titleauthor AS ta
WHERE ta.au_id = a.au_id)
is equivalent to -
SELECT a.au_lname, a.au_fname
FROM authors AS a
LEFT JOIN titleauthor AS ta
ON ta.au_id = a.au_id
WHERE ta.au_id IS NULL
In the old pre-ANSI OUTER JOIN syntax this looks like --
SELECT a.au_lname, a.au_fname
FROM authors AS a,
titleauthor AS ta
WHERE ta.au_id IS NULL
and ta.au_id(+) = a.au_id
It's TA is OUTER JOINED with A -- and for every record where TA doesn't have a value for AU_ID! Same as NOT EXISTS.
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.
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
