Home » RDBMS Server » Performance Tuning » How tune the following query? (Oracle 9i)
How tune the following query? [message #569235] |
Tue, 23 October 2012 06:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/d8be4154bafb16fadc9f9e3c0281a709?s=64&d=mm&r=g) |
kumar_hi21@yahoo.com
Messages: 6 Registered: August 2012 Location: chennai
|
Junior Member |
|
|
Dear Experts,
I am very much new to SQL tuning . Here i have shown my and explain plan
SELECT ei.ROWID row_id, 'PROJECT' search_criteria, ei.expenditure_item_id,
SUBSTR (gcc.segment2, 1, 6) ACCOUNT,
SUBSTR (gcc.segment5, 1, 10) local_account,
SUBSTR (gcc.segment1, 1, 3) le, SUBSTR (gcc.segment3, 1, 5) pu,
SUBSTR (gcc.segment4, 1, 3) dept, ei.expenditure_id, p.project_id,
p.segment1 project_number, p.NAME project_name, pt.project_type,
pt.project_type_class_code, ei.task_id, t.task_number,
NVL (SUBSTR (t.task_number, 1, INSTR (t.task_number, '*', 1) - 1),
t.task_number
) work_stage,
DECODE (INSTR (t.task_number, '*', 1),
0, '',
SUBSTR (t.task_number, INSTR (t.task_number, '*', 1) + 1)
) activity_code,
t.task_name, ei.expenditure_item_date, ei.expenditure_type,
et.expenditure_category, et.revenue_category_code,
x.incurred_by_person_id, emp.full_name employee_name,
/*DECODE (p.org_id,
'103', DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
'107', DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
'2201', DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
emp.employee_number
) employee_number,*/
DECODE (p.org_id,
'103', NVL (DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
emp.employee_number
),
'107', NVL (DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
emp.employee_number
),
'2201', NVL (DECODE (emp.attribute7,
NULL, NULL,
-- 'DEN#' || emp.attribute7
emp.attribute7
),
emp.employee_number
),
emp.employee_number
) employee_number, ---- Ghansham 24-Nov-2009
emp.attribute7 den_number, ei.job_id, j.NAME job_name,
x.incurred_by_organization_id, ei.override_to_organization_id,
NVL (ei.override_to_organization_id,
x.incurred_by_organization_id
) expenditure_organization_id,
o1.NAME expenditure_organization_name, ei.non_labor_resource,
ei.organization_id nlr_organization_id,
o2.NAME nlr_organization_name, ei.system_linkage_function,
c.expenditure_comment,
ei.transaction_source || '' transaction_source,
ei.orig_transaction_reference, x.expenditure_group,
x.expenditure_status_code, x.expenditure_ending_date, cdl.quantity,
et.unit_of_measure,
(SELECT l2.meaning
FROM pa_lookups l2
WHERE 1 = 1
AND l2.lookup_type = 'UNIT'
AND l2.lookup_code = et.unit_of_measure
AND ROWNUM = 1) unit_of_measure_m,
DECODE (ei.system_linkage_function,
'ST', ei.raw_cost_rate,
'OT', ei.raw_cost_rate,
ei.raw_cost_rate
) raw_cost_rate,
ei.cost_distributed_flag, ei.cost_dist_rejection_code,
ei.labor_cost_multiplier_name,
DECODE (ei.system_linkage_function,
'VI', cdl.burdened_cost,
cdl.amount
) burdened_cost,
DECODE (ei.system_linkage_function,
'ST', ei.burden_cost_rate,
'OT', ei.burden_cost_rate,
ei.burden_cost_rate
) burdened_cost_rate,
ei.cost_burden_distributed_flag, ei.ind_cost_dist_rejection_code,
ei.cost_ind_compiled_set_id,
DECODE (pt.project_type_class_code,
'CAPITAL', ei.billable_flag,
CAST (NULL AS VARCHAR2 (10))
) capitalizable_flag,
ei.accrued_revenue, ei.accrual_rate, ei.raw_revenue,
ei.forecast_revenue,
SIGN (cdl.amount) * ABS (ei.bill_amount) bill_amount, ei.bill_rate,
ei.bill_rate_multiplier, ei.adjusted_revenue, ei.adjusted_rate,
DECODE (pt.project_type_class_code,
'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
cdl.billable_flag
) billable_flag,
DECODE (pt.project_type_class_code,
'CAPITAL', ei.revenue_distributed_flag,
CAST (NULL AS VARCHAR2 (10))
) grouped_cip_flag,
DECODE (pt.project_type_class_code,
'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
ei.revenue_distributed_flag
) revenue_distributed_flag,
ei.bill_hold_flag, ei.rev_dist_rejection_code, ei.bill_job_id,
ei.bill_job_billing_title, ei.bill_employee_billing_title,
ei.rev_ind_compiled_set_id, ei.inv_ind_compiled_set_id,
ei.event_num, ei.event_task_id, ei.adjusted_expenditure_item_id,
NVL (ei.net_zero_adjustment_flag, 'N') net_zero_adjustment_flag,
ei.transferred_from_exp_item_id, ei.converted_flag,
ei.source_expenditure_item_id, ei.last_update_login,
ei.last_update_date, ei.last_updated_by, ei.creation_date,
ei.created_by, ei.attribute_category, ei.attribute1, ei.attribute2,
ei.attribute3, ei.attribute4, ei.attribute5, ei.attribute6,
ei.attribute7, ei.attribute8, ei.attribute9, ei.attribute10,
DECODE (ei.org_id,
'1131', SUBSTR (ei.attribute8,
(INSTR (ei.attribute8, '[', 1, 5)
) + 1,
(( INSTR (ei.attribute8, ']', 1, 5)
- 1
- (INSTR (ei.attribute8, '[', 1, 5))
)
)
),
TO_CHAR (CAST (NULL AS VARCHAR2 (10)))
),
ei.org_id, ei.org_id expenditure_org_id,
tr.user_transaction_source || '' user_transaction_source,
tr.allow_adjustments_flag, cast('' as varchar2(1)) transferred_item_flag,
cdl.line_num cdl_line_num,
cdl.transfer_status_code cdl_xfr_status_code,
DECODE (ei.system_linkage_function,
'VI', cdl.amount,
(SIGN (cdl.amount) * ABS (ei.raw_cost))
) raw_cost,
cdl.function_transaction_code cdl_func_txn_code,
cdl.pa_date + 0 cdl_pa_date, cdl.dr_code_combination_id cdl_dr_ccid,
cdl.gl_date + 0 cdl_gl_date, cdl.accumulated_flag cdl_accum_flag,
cdl.reversed_flag cdl_reversed_flag,
cdl.line_num_reversed cdl_line_num_rev,
NVL (cdl.system_reference1, 0) cdl_sys_ref1,
cdl.system_reference2 cdl_sys_ref2,
cdl.system_reference3 cdl_sys_ref3,
cdl.cr_code_combination_id cdl_cr_ccid, cdl.line_type cdl_line_type,
cdl.code_combination_id cdl_ccid, et.attribute8 aorl_flag,
(SELECT hp.party_name
FROM pa_project_customers ppc,
hz_cust_accounts hca,
hz_parties hp
WHERE ppc.project_id = p.project_id
AND ppc.customer_bill_split = 100
AND hca.cust_account_id = ppc.customer_id
AND hp.party_id = hca.party_id
AND ROWNUM = 1) client,
(SELECT hca.account_number
FROM pa_project_customers ppc,
hz_cust_accounts hca
WHERE ppc.project_id = p.project_id
AND ppc.customer_bill_split = 100
AND hca.cust_account_id = ppc.customer_id
AND ROWNUM = 1) client_number,
ei.project_id event_project_id,
SUBSTR (ei.attribute2, 7, 4) office_site,
SUBSTR (ei.attribute2, 11, 3) hovensa_mrn,
SUBSTR (ei.attribute2, 14, 4) hovensa_crew,
SUBSTR (ei.attribute2, 18, 2) hovensa_craft,
SUBSTR (ei.attribute2, 20, 2) hovensa_class,
SUBSTR (ei.attribute2, 1, 1) hovensa_shift,
SUBSTR (emp.attribute6, 3, 8) hovensa_badge_number,
SUBSTR (emp.attribute6, 1, 2) hovensa_frequency,
pa_expenditures_utils.getorgtlname (ei.org_id) prvdr_org_name,
pa_expenditures_utils.getorgtlname (ei.recvr_org_id) recvr_org_name,
ei.receipt_currency_code, ei.receipt_exchange_rate,
ei.receipt_currency_amount, ei.denom_currency_code,
DECODE (ei.system_linkage_function,
'VI', cdl.denom_raw_cost,
(SIGN (cdl.amount) * ABS (ei.denom_raw_cost)
)
) denom_raw_cost,
DECODE (ei.system_linkage_function,
'VI', cdl.burdened_cost,
(SIGN (cdl.amount) * ABS (ei.denom_burdened_cost)
)
) denom_burdened_cost,
ei.acct_exchange_rounding_limit, ei.acct_currency_code,
ei.acct_rate_type, ei.acct_rate_date, ei.acct_exchange_rate,
DECODE (ei.system_linkage_function,
'VI', cdl.acct_raw_cost,
(SIGN (cdl.amount) * ABS (ei.acct_raw_cost)
)
) acct_raw_cost,
DECODE (ei.system_linkage_function,
'VI', cdl.burdened_cost,
(SIGN (cdl.amount) * ABS (ei.acct_burdened_cost)
)
) acct_burdened_cost,
ei.project_currency_code, ei.project_rate_type,
ei.project_rate_date, ei.project_exchange_rate,
DECODE (ei.system_linkage_function,
'VI', cdl.project_raw_cost,
(SIGN (cdl.amount) * ABS (ei.project_raw_cost)
)
) project_raw_cost,
DECODE (ei.system_linkage_function,
'VI', cdl.burdened_cost,
(SIGN (cdl.amount) * ABS (ei.project_burdened_cost)
)
) project_burdened_cost,
ei.cc_prvdr_organization_id, ei.cc_recvr_organization_id,
pa_expenditures_utils.getorgtlname
(ei.cc_prvdr_organization_id)
cc_prvdr_organization_name,
pa_expenditures_utils.getorgtlname
(ei.cc_recvr_organization_id)
cc_recvr_organization_name,
cc_cross_charge_code, cc_cross_charge_type, cc_rejection_code,
jeg_get_lookup ('CC_CROSS_CHARGE_CODE',
cc_cross_charge_code
) cc_cross_charge_code_m,
jeg_get_lookup ('CC_CROSS_CHARGE_TYPE',
cc_cross_charge_type
) cc_cross_charge_type_m,
jeg_get_lookup ('CC_REJECTION_CODE',
cc_rejection_code
) cc_rejection_code_m,
fnd_profile.VALUE ('GL_SET_OF_BKS_ID') set_of_books_id,
(SELECT ass_attribute3
FROM per_all_assignments_f paa
WHERE paa.person_id = emp.person_id
AND ei.expenditure_item_date BETWEEN effective_start_date
AND effective_end_date
AND ROWNUM = 1) jeg_assigned_pu,
(SELECT SUBSTR (SUBSTR (ei.attribute8,
1,
INSTR (ei.attribute8, '~', 1) - 1
),
c1.l_from1,
(c1.l_to1 - c1.l_from1) + 1
)
|| ','
|| SUBSTR (SUBSTR (ei.attribute8,
1,
INSTR (ei.attribute8, '~', 1) - 1
),
c1.l_from2,
(c1.l_to2 - c1.l_from2) + 1
)
|| ','
|| SUBSTR (SUBSTR (ei.attribute8,
1,
INSTR (ei.attribute8, '~', 1) - 1
),
c1.l_from3,
(c1.l_to3 - c1.l_from3) + 1
)
|| ','
|| SUBSTR (SUBSTR (ei.attribute8,
1,
INSTR (ei.attribute8, '~', 1) - 1
),
c1.l_from4,
(c1.l_to4 - c1.l_from4) + 1
)
FROM jeg_proj_mask_map c1
WHERE p.org_id = 3225 -- CA OU
AND c1.project_id = p.project_id
AND ei.expenditure_item_date BETWEEN c1.start_date
AND NVL (c1.end_date, SYSDATE)
AND ROWNUM = 1) jeg_exp_mask,
DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 11)),
'JOB SHOPPER', UPPER (SUBSTR (emp.last_name, 1, 4)),
DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 21)),
'SHIFT DIFFERENTIAL JS', UPPER
(SUBSTR (emp.last_name,
1,
4
)
),
INITCAP (SUBSTR (emp.last_name, 1, 2))
|| SUBSTR (emp.first_name, 1, 1)
)
) mailstop,
jeg_get_ar_inv (p.project_id, ei.expenditure_item_id) ar_inv_num,
---- Ghansham 21-JUL-2008 added foe GCC
jeg_check_service_centre_cc (ei.expenditure_item_id) service_center,
---- Ghansham 21-JUL-2008 added foe GCC
ei.acct_transfer_price ---- Ghansham 21-JUL-2008 added foe GCC
FROM pa_projects_all p,
pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_tasks t,
pa_project_types_all pt,
pa_expenditures_all x,
hr_all_organization_units o1,
hr_all_organization_units o2,
per_jobs j,
pa_expenditure_types et,
gl_code_combinations gcc,
pa_expenditure_comments c,
pa_transaction_sources tr,
per_all_people_f emp
WHERE 1 = 1
AND cdl.project_id = p.project_id
AND ei.expenditure_item_id = cdl.expenditure_item_id
--AND ei.expenditure_item_date BETWEEN TO_DATE('01-DEC-2007') AND TO_DATE('14-DEC-2007')
AND cdl.line_type || '' =
DECODE (ei.system_linkage_function,
'VI', 'R',
'D'
)
AND ( ei.cc_cross_charge_type || '' = 'IC'
OR ei.org_id = fnd_profile.VALUE ('ORG_ID')
)
AND t.task_id = ei.task_id
AND pt.project_type = p.project_type
AND pt.org_id = p.org_id
AND x.expenditure_id = ei.expenditure_id
AND o1.organization_id =
NVL (ei.override_to_organization_id,
x.incurred_by_organization_id
)
AND ei.organization_id = o2.organization_id(+)
AND ei.job_id = j.job_id(+)
AND ei.expenditure_type = et.expenditure_type
AND et.unit_of_measure IN
('DOLLARS', 'HOURS', 'MILEAGE', 'Plots', 'Samples')
AND gcc.code_combination_id = cdl.dr_code_combination_id
AND ei.expenditure_item_id = c.expenditure_item_id(+)
AND ei.transaction_source = tr.transaction_source(+)
AND x.incurred_by_person_id = emp.person_id(+)
AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
AND emp.employee_number(+) IS NOT NULL;
and explain plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21685820 Card=447922
39 Bytes=46539136321)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_VALUES' (Co
st=4 Card=1 Bytes=57)
3 2 INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1' (UNIQUE)
(Cost=3 Card=1)
4 0 COUNT (STOPKEY)
5 4 NESTED LOOPS (Cost=7 Card=1 Bytes=56)
6 5 NESTED LOOPS (Cost=5 Card=1 Bytes=24)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'PA_PROJECT_CUSTOME
RS' (Cost=4 Card=1 Bytes=13)
8 7 INDEX (RANGE SCAN) OF 'PA_PROJECT_CUSTOMERS_U1' (U
NIQUE) (Cost=3 Card=1)
9 6 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS'
(Cost=1 Card=1 Bytes=11)
10 9 INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (UNIQ
UE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (Cost=2
Card=1 Bytes=32)
12 11 INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (UNIQUE) (Cos
t=1 Card=1)
13 0 COUNT (STOPKEY)
14 13 NESTED LOOPS (Cost=5 Card=1 Bytes=24)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'PA_PROJECT_CUSTOMERS
' (Cost=4 Card=1 Bytes=13)
16 15 INDEX (RANGE SCAN) OF 'PA_PROJECT_CUSTOMERS_U1' (UNI
QUE) (Cost=3 Card=1)
17 14 TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS' (C
ost=1 Card=1 Bytes=11)
18 17 INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (UNIQUE
)
19 0 COUNT (STOPKEY)
20 19 TABLE ACCESS (BY INDEX ROWID) OF 'PER_ALL_ASSIGNMENTS_F'
(Cost=7 Card=1 Bytes=27)
21 20 INDEX (RANGE SCAN) OF 'PER_ASSIGNMENTS_F_N12' (NON-UNI
QUE) (Cost=3 Card=4)
22 0 COUNT (STOPKEY)
23 22 FILTER
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'JEG_PROJ_MASK_MAP' (
Cost=3 Card=1 Bytes=31)
25 24 INDEX (RANGE SCAN) OF 'JEG_PROJ_MASK_MAP_N1' (NON-UN
IQUE) (Cost=2 Card=1)
26 0 HASH JOIN (Cost=21685820 Card=44792239 Bytes=46539136321)
27 26 TABLE ACCESS (FULL) OF 'HR_ALL_ORGANIZATION_UNITS' (Cost
=8 Card=2291 Bytes=29783)
28 26 HASH JOIN (Cost=21666496 Card=44792239 Bytes=45956837214
)
29 28 TABLE ACCESS (FULL) OF 'PA_PROJECT_TYPES_ALL' (Cost=2
Card=138 Bytes=3312)
30 28 HASH JOIN (Cost=21663798 Card=44792239 Bytes=448818234
78)
31 30 TABLE ACCESS (FULL) OF 'PA_PROJECTS_ALL' (Cost=3246
Card=265730 Bytes=11957850)
32 30 HASH JOIN (Cost=21005440 Card=44483842 Bytes=4257103
6794)
33 32 TABLE ACCESS (FULL) OF 'GL_CODE_COMBINATIONS' (Cos
t=1773 Card=688770 Bytes=21351870)
34 32 HASH JOIN (OUTER) (Cost=20373686 Card=44175128 Byt
es=40906168528)
35 34 HASH JOIN (OUTER) (Cost=19509061 Card=44175128 B
ytes=39536739560)
36 35 HASH JOIN (Cost=18943246 Card=44175128 Bytes=3
6046904448)
37 36 TABLE ACCESS (FULL) OF 'PA_TASKS' (Cost=9738
2 Card=18021390 Bytes=360427800)
38 36 HASH JOIN (Cost=18294927 Card=44166353 Bytes
=35156416988)
39 38 TABLE ACCESS (FULL) OF 'PA_EXPENDITURES_AL
L' (Cost=84808 Card=28594800 Bytes=1401145200)
40 38 HASH JOIN (OUTER) (Cost=17413038 Card=4409
9473 Bytes=32942306331)
41 40 HASH JOIN (OUTER) (Cost=16913198 Card=44
099473 Bytes=32369013182)
42 41 HASH JOIN (OUTER) (Cost=16420291 Card=
44099473 Bytes=31883918979)
43 42 HASH JOIN (Cost=15948611 Card=440994
73 Bytes=30516835316)
44 43 TABLE ACCESS (FULL) OF 'PA_EXPENDI
TURE_TYPES' (Cost=7 Card=1290 Bytes=81270)
45 43 HASH JOIN (Cost=15928472 Card=4409
9473 Bytes=27738568517)
46 45 TABLE ACCESS (FULL) OF 'PA_EXPEN
DITURE_ITEMS_ALL' (Cost=1680109 Card=51616333 Bytes=26530795
162)
47 45 TABLE ACCESS (FULL) OF 'PA_COST_
DISTRIBUTION_LINES_ALL' (Cost=4105845 Card=309305110 Bytes=3
5570087650)
48 42 TABLE ACCESS (FULL) OF 'PA_TRANSACTI
ON_SOURCES' (Cost=3 Card=107 Bytes=3317)
49 41 TABLE ACCESS (FULL) OF 'PER_JOBS' (Cos
t=125 Card=49710 Bytes=546810)
50 40 TABLE ACCESS (FULL) OF 'HR_ALL_ORGANIZAT
ION_UNITS' (Cost=8 Card=2291 Bytes=29783)
51 35 TABLE ACCESS (FULL) OF 'PER_ALL_PEOPLE_F' (Cos
t=1770 Card=276740 Bytes=21862460)
52 34 TABLE ACCESS (FULL) OF 'PA_EXPENDITURE_COMMENTS'
(Cost=21998 Card=15029290 Bytes=465907990)
This process can take more than 6 hours,How can i reduce the running time ...
Kindly advice me...
Thanks in advance....
|
|
|
|
Re: How tune the following query? [message #569575 is a reply to message #569235] |
Mon, 29 October 2012 22:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
SQLTRACE and TKPROF may be more correct, but they are also a pain in the neck for many of us to generate.
What version of Oracle are you running? I assume 9/10/11? A simpler more practical approach is to use DBMS_XPLAN to generate your plan. You need more than just the query executpion plan output. You also need the other plan data, in particular the PREDICATE INFORMATION section.
Tuning this query will take several hours to a couple of days. Are you willing to invest this time? Here are some things you can do if you are serious:
) create a query diagram so you can see how the tables are joined visually (1/2 hour to 3 hours depends upon if you use pen an paper or some drawing tool)
) create/run COUNT QUERIES and FILTER QUERIES to determine drivng table and join order based on intial row filtering and compare to current plan order (one hour + runtime)
) create/run RECONSTRUCTION QUERIES to validate cardinality of various steps (three to four hours maybe more if this is your first time + runtime)
) check predicate information for FILTER PREDICATE LISTS that show inefficient fetching or joining and determine if it is because of problem's nature or wasted effort because of physical data model
) consider if NESTED LOOPS JOIN is being done where HASH JOIN should be done
) detemermine optimum set of indexes (indexes may not be the right solution for all steps) using query diagram/driving table/join order/and other info learned
) apply what you have learned and try again (check the plan, check the runtime stats)
Once you know the plan you want, make Oracle give it to you using / the Basics (constraints,datatypes,design) / Indexes / Statistics / Rewrites / <only if necessary> Hints and Plan Stability.
Divide and Conquer. You need to figure out where your time is going in this query so you know what to tune.
Good luck, Kevin
[Updated on: Mon, 29 October 2012 22:30] Report message to a moderator
|
|
|
|
Re: How tune the following query? [message #569578 is a reply to message #569576] |
Mon, 29 October 2012 23:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Plan is only half useful without predicate information. Stole this from TOM. Was the first one I saw that had predicate info.
ops$tkyte@ORA9IR2> delete from plan_table;
2 rows deleted.
ops$tkyte@ORA9IR2> explain plan for select * from dual where dummy in ( 'X', 'Y' );
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUAL"."DUMMY"='X' OR "DUAL"."DUMMY"='Y')
Note: rule based optimization
14 rows selected.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:40:26 CDT 2024
|