Home » RDBMS Server » Performance Tuning » Nasty query re-write (Oracle 9i)
Nasty query re-write [message #329224] |
Tue, 24 June 2008 10:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Ok, just a forewarning that this query is huge, ugly and horribly built.. Please note that I did not write it; it was unfortunately "passed down" to our team at work. I have been working hard at re-writing this thing, and I've made some headway thanks to great help from these forums. However, the query is still not up to speed, so I figured instead of breaking it up and asking questions about each individual part, I'd go ahead and post the whole thing.
I would never expect anyone to re-write this horrible thing for me, but any words of wisdom for helping consolidate this thing into something that might perform better would be VERY much appreciated. Some initial background about the tables:
t_fbsos_offr_stg: 40-50 million rows
t_fbsco_chan_ord: 80-100 million rows
These 2 tables are joined by "ord_id" field, and there are typically 2 entries on t_fbsco_chan_ord for each entry on t_fbsos_offr_stg. Primary key on fbsos is "ord_id" and primary key on fbsco is "ord_id,seq_n".
The "Beast" Query:
SELECT
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os
LEFT OUTER JOIN (SELECT /*+ NO_MERGE(OSC1) ORDERED FULL(OSC1) INDEX(OSC2) USE_NL(OSC1,OSC2) +*/
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select /*+
parallel(os,8,1)
parallel(co,8,1)
+*/
co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = :RUN_ID
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = :RUN_ID
ORDER BY os.dlvr_mode_c, os.ord_id
Thanks in advance!! Please let me know of any other info that would be useful (i.e. explain plans, index info, etc). FYI we DO have the ability to ask our DBA's to add additional indexes if needed.
[Mod-edit: Frank added code-tags to improve readability]
[Updated on: Tue, 24 June 2008 11:59] by Moderator Report message to a moderator
|
|
|
|
|
Re: Nasty query re-write [message #329234 is a reply to message #329224] |
Tue, 24 June 2008 11:15 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
So sorry, I didn't realize it would left-align the query. Here is the newly formatted query with no hints:
SELECT
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os
LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = :RUN_ID
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = :RUN_ID
ORDER BY os.dlvr_mode_c, os.ord_id
Here is the explain plan:
12:06:56 SQL> set autotrace traceonly explain;
12:07:21 SQL> /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=197519 Card=63075 Byte
s=44026350)
1 0 SORT (ORDER BY) (Cost=197519 Card=63075 Bytes=44026350)
2 1 NESTED LOOPS (OUTER) (Cost=191322 Card=63075 Bytes=44026
350)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (C
ost=2097 Card=63075 Bytes=22833150)
4 3 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Co
st=271 Card=63075)
5 2 VIEW PUSHED PREDICATE (Cost=3 Card=1 Bytes=336)
6 5 HASH JOIN (Cost=160605 Card=1 Bytes=365)
7 6 NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=346)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSCO_CHAN_O
RD' (Cost=4 Card=1 Bytes=307)
9 8 INDEX (RANGE SCAN) OF 'FPPBSCOPK00' (UNIQUE) (
Cost=3 Card=1)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'S_FBPC' (Cost=
2 Card=1 Bytes=39)
11 10 INDEX (RANGE SCAN) OF 'PK_T_FEPC_PREF_CTGY' (U
NIQUE) (Cost=1 Card=1)
12 6 VIEW (Cost=160598 Card=63075 Bytes=1198425)
13 12 SORT (GROUP BY) (Cost=160598 Card=63075 Bytes=11
98425)
14 13 HASH JOIN (Cost=160349 Card=63075 Bytes=119842
5)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OF
FR_STG' (Cost=2097 Card=63075 Bytes=630750)
16 15 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-U
NIQUE) (Cost=271 Card=1)
17 14 TABLE ACCESS (FULL) OF 'T_FBSCO_CHAN_ORD' (C
ost=122349 Card=95049112 Bytes=855442008)
Here are the constraint/indexes of the tables at hand:
Table: t_fbsos_offr_stg
Constraints/Indexes:
Name Type Index Parameters
---------- ---- ----- -------------------------------------------------------------------------------------------
c @@@@sospk00 pk yes (ord_id)
c @@@@sosnn01 ni yes (run_id,cpgn_st_c,schd_dlvr_d,dlvr_mode_c)
c @@@@sosnn02 ni yes (sel_d)
Table: t_fbsco_chan_ord
Constraints/Indexes:
Name Type Index Parameters
---------- ---- ----- -------------------------------------------------------------------------------------------
c @@@@scopk00 pk yes (ord_id,seq_n)
c @@@@scofk01 fk no (ord_id) references t_fbsos_offr_stg (ord_id)
Thanks for you guys' quick responses, and sorry again for not including this info in the original post! Can't wait to hear you guys' suggestions, etc. Please let me know what other info would be useful. Thanks again
|
|
|
|
|
|
|
|
Re: Nasty query re-write [message #329317 is a reply to message #329296] |
Wed, 25 June 2008 01:05 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
The Pushed Predicate is causing the entire greater sub-query to be run for every row returned. That's too many times.
You want to run that sub-query once only and then hash-join it to the outer table.
Try a /*+ NO_PUSH_PRED*/ at the very top level SELECT to prevent the predicate push - this should also discourage the Nested Loops join in favour of a hash join, which in turn will favour an access path on the os.run_id = :RUN_ID rather than the ON os.ord_id = osc.ord_id join predicate.
If that doesn't work, you will have to get specific. Make the top-level select:SELECT /*+ ORDERED USE_HASH(osc)*/ ...
Post the new query and explain plan.
Ross Leishman
|
|
|
Re: Nasty query re-write [message #329492 is a reply to message #329317] |
Wed, 25 June 2008 10:27 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Thanks for that info, I've added the hints to the very top of the query and the following explain plans resulted:
With NO_PUSH_PRED:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=238199 Card=62622 Bytes=63874440)
1 0 SORT (ORDER BY) (Cost=238199 Card=62622 Bytes=63874440)
2 1 HASH JOIN (OUTER) (Cost=229241 Card=62622 Bytes=63874440)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2024 Card=61766 Bytes=22668122)
4 3 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=271 Card=61766)
5 2 VIEW (Cost=223029 Card=62623 Bytes=40892819)
6 5 HASH JOIN (OUTER) (Cost=223029 Card=62623 Bytes=23671494)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSCO_CHAN_ORD' (Cost=2 Card=1 Bytes=313)
8 7 NESTED LOOPS (Cost=222815 Card=62623 Bytes=21229197)
9 8 VIEW (Cost=160987 Card=61766 Bytes=1605916)
10 9 SORT (GROUP BY) (Cost=160987 Card=61766 Bytes=1173554)
11 10 HASH JOIN (Cost=160743 Card=61766 Bytes=1173554)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2024 Card=61766 Bytes=617660)
13 12 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=271 Card=1)
14 11 TABLE ACCESS (FULL) OF 'T_FBSCO_CHAN_ORD' (Cost=123751 Card=94804809 Bytes=853243281)
15 8 INDEX (RANGE SCAN) OF 'FPPBSCOPK00' (UNIQUE) (Cost=1 Card=1)
16 6 TABLE ACCESS (FULL) OF 'S_FBPC' (Cost=3 Card=7 Bytes=273)
With USE_HASH(osc):
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=238199 Card=62622 Bytes=63874440)
1 0 SORT (ORDER BY) (Cost=238199 Card=62622 Bytes=63874440)
2 1 HASH JOIN (OUTER) (Cost=229241 Card=62622 Bytes=63874440)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2024 Card=61766 Bytes=22668122)
4 3 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=271 Card=61766)
5 2 VIEW (Cost=223029 Card=62623 Bytes=40892819)
6 5 HASH JOIN (OUTER) (Cost=223029 Card=62623 Bytes=23671494)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSCO_CHAN_ORD' (Cost=2 Card=1 Bytes=313)
8 7 NESTED LOOPS (Cost=222815 Card=62623 Bytes=21229197)
9 8 VIEW (Cost=160987 Card=61766 Bytes=1605916)
10 9 SORT (GROUP BY) (Cost=160987 Card=61766 Bytes=1173554)
11 10 HASH JOIN (Cost=160743 Card=61766 Bytes=1173554)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2024 Card=61766 Bytes=617660)
13 12 INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=271 Card=1)
14 11 TABLE ACCESS (FULL) OF 'T_FBSCO_CHAN_ORD' (Cost=123751 Card=94804809 Bytes=853243281)
15 8 INDEX (RANGE SCAN) OF 'FPPBSCOPK00' (UNIQUE) (Cost=1 Card=1)
16 6 TABLE ACCESS (FULL) OF 'S_FBPC' (Cost=3 Card=7 Bytes=273)
If needed, here is the new query (with the no-push-pred hint):
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = :RUN_ID
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = :RUN_ID
ORDER BY os.dlvr_mode_c, os.ord_id
I see that the predicate push has been eliminated, which is a good thing. The query run-time is still lengthy unfortunately, but I suspect there are other things at hand here. Thanks again for all your help! Let me know your thoughts.
|
|
|
|
Re: Nasty query re-write [message #330469 is a reply to message #330073] |
Mon, 30 June 2008 04:22 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Can you please post the TKPROF output of a SQL Trace. That will give us the row count of each step in the plan.
I suspect one of your INDEX RANGE SCANS is scanning lots more rows than you think (especially the 2nd last row in the plan) - but need TKPROF to tell for sure.
Ross Leishman
|
|
|
Re: Nasty query re-write [message #330649 is a reply to message #330469] |
Mon, 30 June 2008 13:10 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Here is the TKPROF output from the trace. Please let me know if this is sufficient. To get this, I just turned tracing on, ran the query, and then ran "tkprof <tracefile> <outputfile>". Thanks!
TKPROF: Release 9.2.0.7.0 - Production on Mon Jun 30 14:06:23 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2224542.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 4508
********************************************************************************
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16668 220.97 1659.66 2372118 2731160 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16670 220.97 1659.66 2372118 2731160 49 250000
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 4508
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 16668 220.97 1659.66 2372118 2731160 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16671 220.97 1659.66 2372118 2731160 49 250000
Misses in library cache during parse: 0
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2224542.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
16758 lines in trace file.
|
|
|
Re: Nasty query re-write [message #330654 is a reply to message #329224] |
Mon, 30 June 2008 13:20 ![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/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>ran "tkprof <tracefile> <outputfile>".
Close.
please do again with
tkprof <tracefile> <outputfile> explain=<username>/<password>
Also any explanation as to why "Optimizer goal: RULE"?
What happens after collecting statistics & using the CBO?
[Initially mis-typed RBO in line above when I really meant CBO]
[Updated on: Mon, 30 June 2008 13:42] by Moderator Report message to a moderator
|
|
|
Re: Nasty query re-write [message #330659 is a reply to message #330654] |
Mon, 30 June 2008 13:29 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Here is the updated TKPROF output with the "explain=user/pw" tag. I know the statistics are collected on a nightly basis. I can collect them and run again if needed, but I know not much has been added/altered to these tables since the last stats run. As for the optimizer being set to RULE, I'm not sure about this. I can ping our DBA's for more info on this if needed, though. Thanks again for your help, and let me know what other info I can gather to help!
TKPROF: Release 9.2.0.7.0 - Production on Mon Jun 30 14:23:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2224542.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 4508 (FPRSAPPP)
********************************************************************************
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16668 220.97 1659.66 2372118 2731160 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16670 220.97 1659.66 2372118 2731160 49 250000
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 4508 (FPRSAPPP)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 SORT (ORDER BY)
0 HASH JOIN (OUTER)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_FBSOS_OFFR_STG'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FPPBSOSNN01'
(NON-UNIQUE)
0 VIEW
0 HASH JOIN (OUTER)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_FBSCO_CHAN_ORD'
0 NESTED LOOPS
0 VIEW
0 SORT (GROUP BY)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_FBSOS_OFFR_STG'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FPPBSOSNN01' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'T_FBSCO_CHAN_ORD'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FPPBSCOPK00'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'S_FBPC'
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 16668 220.97 1659.66 2372118 2731160 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16671 220.97 1659.66 2372118 2731160 49 250000
Misses in library cache during parse: 0
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2224542.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
FPPBCOL1.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
16760 lines in trace file.
|
|
|
|
Re: Nasty query re-write [message #330680 is a reply to message #330665] |
Mon, 30 June 2008 15:15 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Thanks for responding so quickly. Number of rows are as follows:
T_FBSCO_CHAN_ORD: 92,893,952
S_FBPC: 7
Here is the TKPROF output when I force to CBO:
TKPROF: Release 9.2.0.7.0 - Production on Mon Jun 30 16:10:58 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2216744.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 4508 (FPRSAPPP)
********************************************************************************
select node,owner,name
from
syn$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.04 4 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 0.04 4 8 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,spare2,spare6, decode(i.pctthres$,null,
null, mod(trunc(i.pctthres$/256),256))
from
ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))
deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=
:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=
:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 6 0.01 0.04 7 17 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.05 7 17 0 4
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.02 4 24 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.00 0.02 4 24 0 8
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 101 0.00 0.00 0 73 0 99
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 104 0.00 0.00 0 73 0 99
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 1 6 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 38 0.00 0.01 2 10 0 36
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 0.00 0.01 2 10 0 36
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 31 0.00 0.00 0 39 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 35 0.00 0.00 0 39 0 28
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select intcol#,nvl(pos#,0),col#
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 28 0.00 0.00 0 0 0 0
Fetch 58 0.01 0.03 5 144 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 87 0.01 0.04 5 144 0 30
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16668 214.65 1708.55 2366063 2717157 45 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16670 214.68 1708.60 2366063 2717157 45 250000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 4508 (FPRSAPPP)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 HASH JOIN (OUTER)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_FBSOS_OFFR_STG'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FPPBSOSNN01'
(NON-UNIQUE)
0 VIEW
0 HASH JOIN (OUTER)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_FBSCO_CHAN_ORD'
0 NESTED LOOPS
0 VIEW
0 SORT (GROUP BY)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_FBSOS_OFFR_STG'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FPPBSOSNN01' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'T_FBSCO_CHAN_ORD'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FPPBSCOPK00'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'S_FBPC'
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 16668 214.65 1708.55 2366063 2717157 45 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16671 214.68 1708.60 2366063 2717157 45 250000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.00 0.01 0 0 0 0
Execute 48 0.00 0.00 0 0 0 0
Fetch 253 0.04 0.17 23 327 0 207
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 310 0.04 0.19 23 327 0 207
Misses in library cache during parse: 9
2 user SQL statements in session.
9 internal SQL statements in session.
11 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_2216744.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
9 internal SQL statements in trace file.
11 SQL statements in trace file.
11 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
FPPBCOL1.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
17106 lines in trace file.
|
|
|
Re: Nasty query re-write [message #330707 is a reply to message #330680] |
Mon, 30 June 2008 23:40 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
For some reason your trace is not generating row-level statistics for the plan. Where the plan is shown, there should be row counts beside each line.
I don't know why that is the case. Sometimes it happens when you do not CLOSE the cursor. Otherwise, it might be some initialisation parameter in Oracle.
First, make sure that your application is closing the cursor. Otherwise, you will need to take this up with your DBA.
Ross Leishman
|
|
|
|
Re: Nasty query re-write [message #330924 is a reply to message #330710] |
Tue, 01 July 2008 15:55 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
FYI, I think I have the rowcount figured out now. The things I did differently this time are 1) setting timed_statistics=true, 2) turning OFF sql_trace after the query completed, and 3) adding "table=sys.plan_table" to the end of my tkprof command (which may not have worked according to the output, lol).
Anyway, below is the new output with rows included. Please let me know what you guys think, thanks!
TKPROF: Release 9.2.0.7.0 - Production on Tue Jul 1 16:48:54 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_4084476.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Error in CREATE TABLE of EXPLAIN PLAN table: sys.plan_table
ORA-01031: insufficient privileges
EXPLAIN PLAN option disabled.
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 4508
********************************************************************************
alter session set timed_statistics=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 4508
********************************************************************************
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16668 231.82 1725.52 2379266 2736383 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16670 231.82 1725.52 2379266 2736383 49 250000
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 4508
Rows Row Source Operation
------- ---------------------------------------------------
250000 SORT ORDER BY
250000 HASH JOIN OUTER
250000 TABLE ACCESS BY INDEX ROWID T_FBSOS_OFFR_STG
250000 INDEX RANGE SCAN FPPBSOSNN01 (object id 14075874)
250000 VIEW
250000 HASH JOIN OUTER
250000 TABLE ACCESS BY INDEX ROWID T_FBSCO_CHAN_ORD
500001 NESTED LOOPS
250000 VIEW
250000 SORT GROUP BY
500000 HASH JOIN
250000 TABLE ACCESS BY INDEX ROWID T_FBSOS_OFFR_STG
250000 INDEX RANGE SCAN FPPBSOSNN01 (object id 14075874)
93073133 TABLE ACCESS FULL T_FBSCO_CHAN_ORD
250000 INDEX RANGE SCAN FPPBSCOPK00 (object id 13975636)
7 TABLE ACCESS FULL S_FBPC
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 4508
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 16668 231.82 1725.52 2379266 2736383 49 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16675 231.82 1725.53 2379266 2736383 49 250000
Misses in library cache during parse: 2
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_4084476.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
16785 lines in trace file.
|
|
|
Re: Nasty query re-write [message #330949 is a reply to message #329224] |
Tue, 01 July 2008 22:20 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
For comparison, could you try the following version that will replace the Hash Join / Full Table Scan with Indexed Nested Loops:
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM t_fbsos_offr_stg os LEFT OUTER JOIN (SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM (select /*+ ORDERED USE_NL(co)*/ co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
t_fbsco_chan_ord osc2
LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id
WHERE os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
This assumes that there is an index on t_fbsco_chan_ord.ord_id
Do an Explain Plan first to make sure the plan is otherwise identical except for the FULL scan on the third last line (replaced with 2 lines: an index range scan + table access by rowid) and the 6th last line (replace HASH with NESTED LOOPS).
This will greatly reduce your disk IO, but will disproportionately increase your buffer cache reads. If it improves at all, it may only be by 10-15%. I would be stunned to any improvement over 30%.
I think you need to brace yourself for the possibility that SQL tuning has peaked.
Your next avenues would be instance tuning (eg. check if you hash joins are spilling over and adjust your Hash Area Size) and segment tuning (eg. hash partitioning t_fbsco_chan_ord and t_fbsos_offr_stg).
Ross Leishman
|
|
|
Re: Nasty query re-write [message #331506 is a reply to message #330949] |
Thu, 03 July 2008 12:39 ![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) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Thanks to all for the great suggestions and tips! I have taken the last few days to re-organize things, add your suggestions, and play with the query structure to eliminate full-table scans. I have gotten the query down to about 1-3 minutes, which isn't bad at all for our application. I'd like to get it down further if possible, but I understand if this is the limit.
If interested, I've attached the new TKPROF analysis, which includes my re-structured query. I've confirmed it returns the same data. I kept the optimizer=RULE because when I switch it to CHOOSE, it does full table scans.. Feel free to comment if you see something that could potentially be tuned further, etc. Thanks again for all your help!
TKPROF: Release 9.2.0.7.0 - Production on Thu Jul 3 13:29:45 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_1081628.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Error in CREATE TABLE of EXPLAIN PLAN table: sys.plan_table
ORA-01031: insufficient privileges
EXPLAIN PLAN option disabled.
********************************************************************************
ALTER SESSION SET SQL_TRACE = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 4508
********************************************************************************
alter session set timed_statistics=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 4508
********************************************************************************
SELECT /*+ NO_PUSH_PRED +*/
os.ord_id, os.cpgn_c, os.run_id, os.part_id,
os.client_id_n, os.plan_n, os.prod_id_c,
os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c,
os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c,
os.insrt_bck_slp_i, os.nigo_i, os.orig_c,
os.crea_corp_id, os.mod_corp_id, os.sel_d,
os.lst_mod_d, os.crea_src_c, os.offr_c,
os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n,
os.run_ops_chan_x, os.fprs_rules_c,
os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm,
os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm,
os.call_ctr_tele_n, os.client_id_logo_x,
os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm,
os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm,
os.lst_nm, os.life_evnt_wndw_beg_d,
os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x,
os.plan_id_logo_x, os.plan_list_html_x,
os.plan_list_x, os.pref_plan_nm, os.proj_nm,
os.room_loc_x, os.shrt_plan_nm, os.site_nm,
os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x,
os.user_def2_x, os.user_def3_x, os.user_def4_x,
os.user_def5_x, os.user_def6_x, os.user_def7_x,
os.user_def8_x, os.user_def9_x, os.user_def10_x,
os.user_def11_x, os.user_def12_x, os.user_def13_x,
os.user_def14_x, os.user_def15_x, os.user_def16_x,
os.user_def17_x, os.user_def18_x, os.user_def19_x,
os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n,
osc.chan_type_c, osc.actv_chan_i,
osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c,
osc.cntnt_key_val2_c, osc.email_addr_x,
osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x,
osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x,
osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c,
osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i,
osc.pref_sel_i
FROM (
SELECT
osc2.ord_id,osc2.seq_n,osc2.chan_type_c,
osc2.actv_chan_i,osc2.chan_cntnt_key_type_c,
osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c,
osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,
osc2.line_3_ad_x,osc2.city_nm,osc2.st_c,
osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i,
osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x,
osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i
FROM
(select co.ord_id, max(co.seq_n)
keep(dense_rank first order by co.actv_chan_i
desc,co.seq_n desc) seq_n
from t_fbsos_offr_stg os,
t_fbsco_chan_ord co
where co.ord_id = os.ord_id
and os.run_id = 100654
group by co.ord_id) osc1,
(SELECT * from t_fbsco_chan_ord where ord_id in
(select ord_id from t_fbsos_offr_stg where run_id = 100654)) osc2,
s_fbpc pc
WHERE osc1.ord_id = osc2.ord_id
AND osc1.seq_n = osc2.seq_n
AND pc.pref_ctgy_c (+) = osc2.ctgy_c) osc, t_fbsos_offr_stg os
WHERE osc.ord_id (+) = os.ord_id
AND os.run_id = 100654
ORDER BY os.dlvr_mode_c, os.ord_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16668 33.50 148.12 112016 2515960 30 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16670 33.51 148.13 112016 2515960 30 250000
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 4508
Rows Row Source Operation
------- ---------------------------------------------------
250000 SORT ORDER BY
250000 MERGE JOIN OUTER
250000 SORT JOIN
250000 TABLE ACCESS BY INDEX ROWID T_FBSOS_OFFR_STG
250000 INDEX RANGE SCAN FPPBSOSNN01 (object id 14075874)
250000 SORT JOIN
250000 VIEW
250000 NESTED LOOPS OUTER
250000 NESTED LOOPS
250000 NESTED LOOPS
250000 VIEW
250000 SORT GROUP BY
500000 TABLE ACCESS BY INDEX ROWID T_FBSCO_CHAN_ORD
750001 NESTED LOOPS
250000 TABLE ACCESS BY INDEX ROWID T_FBSOS_OFFR_STG
250000 INDEX RANGE SCAN FPPBSOSNN01 (object id 14075874)
500000 INDEX RANGE SCAN FPPBSCOPK00 (object id 13975636)
250000 TABLE ACCESS BY INDEX ROWID T_FBSCO_CHAN_ORD
250000 INDEX UNIQUE SCAN FPPBSCOPK00 (object id 13975636)
250000 TABLE ACCESS BY INDEX ROWID T_FBSOS_OFFR_STG
250000 INDEX UNIQUE SCAN FPPBSOSPK00 (object id 13975395)
0 TABLE ACCESS BY INDEX ROWID S_FBPC
0 INDEX RANGE SCAN PK_T_FEPC_PREF_CTGY (object id 8305741)
********************************************************************************
ALTER SESSION SET SQL_TRACE = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 4508
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.01 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 16668 33.50 148.12 112016 2515960 30 250000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16675 33.51 148.13 112016 2515960 30 250000
Misses in library cache during parse: 3
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: /fprsp1/oracle/admin/FPP1/udump/fpp1_ora_1081628.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
16798 lines in trace file.
|
|
|
|
Re: Nasty query re-write [message #333573 is a reply to message #329224] |
Sat, 12 July 2008 21:42 ![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 |
|
|
I have some doubts about the correctness of the query. For example, why is run_id referenced twice? Seems to me it is only needed once. Additionally, what is with the MAX, it seems not to be used at all.
Inspite of these simple flaws however, it may well be that the query is correct which you should know by looking at the output and by understanding what it is supposed to do.
That said, forget tuning it like this. Re-write it using the WITH clause. This will simplify it and help ohter people see what it is doing. I took a first quick stab at it and it looks something like this:
create or replace view this_is_nice
as
with
osc1 as (
SELECT co.Ord_Id,
MAX(co.seq_n) {why is there no column alias for this?}{what is this column even for?}
keep(dense_rank first order by co.actv_chan_i desc,co.seq_n desc) seq_n {is this really correct?}
FROM t_fbsco_chAn_Ord co
group by co.Ord_Id
)
, osc as (
SELECT osc2.ord_id,...
FROM t_fbsco_chAn_Ord osc2,
s_fbpc pc,
osc1
WHERE osc2.ctgy_c = pc.pref_ctgy_c(+)
AND osc2.Ord_Id = osc1.Ord_Id
and osc2.seq_n = osc1.seq_n
)
SELECT os.run_id,...
FROM t_fbSos_Offr_stg os,
osc
WHERE os.Ord_Id = osc.Ord_Id(+)
/
select *
from this_is_nice
where run_id = :run_id
ORDER BY os.dlvr_Mode_c,
os.Ord_Id
/
Ideally I think the following accesses would be made:
1) RUN_ID is used to access t_fbSos_Offr_stg
2) ORD_ID is used to access t_fbsco_chAn_Ord
3) ORD_ID is used to access t_fbsco_chAn_Ord (where analytic is done)
4) filter operation using SEQ_N to find specific rows
5) ctgy_c is used o access s_fbpc
You should validate this against the original query for correctness as I am sure I have made a mistake somewhere. But you should be getting the idea that the WITH clause can help. It will organize your query and give you more control over presenting your thoughts in the query.
There are distinct advantages in turning this code into a view and/or organizing it using the WITH clause:
1) you can query anything you want, not just for a specific run_id
2) you can test the query independently of any application the uses it
3) you can test the pieces of the query as you biuld them
4) you can tune the pieces of the query just as easily
5) you can tune the whole query without changing applications that use it
Here is another version of the query using additional techniques you might want to consider. I am sure I messed up syntax somewhere but the trick of course is the correlated IN used to get the SEQ_N for the corresponding ORD_ID.
create or replace view is_smaller_nicer
as
with
osc as (
SELECT osc2.ord_id,...
FROM t_fbsco_chAn_Ord osc2,
s_fbpc pc,
osc1
WHERE osc2.ctgy_c = pc.pref_ctgy_c(+)
AND osc2.Ord_Id = osc1.Ord_Id
and osc2.seq_n = (
SELECT keep(dense_rank first order by co.actv_chan_i desc,co.seq_n desc) seq_n
FROM t_fbsco_chAn_Ord co
where co.ord_id = osc2.ord_id
)
)
SELECT os.run_id,...
FROM t_fbSos_Offr_stg os,
osc
WHERE os.Ord_Id = osc.Ord_Id(+)
/
Good performance of these query formulations will rely heavily on Oracle's innate optimizing intelligence. I find it is prettry damned good in 10g. It seems to like the WITH formulations of queryies too, having less problems doing predicate pushing and the like. This is of course my totally unscientific observation.
And PLEASE PLEASE load up some comments into the view text will you! When you do the hand-me-down thing to the next guy/gal they will appreciate your personal thoughts on how you reformulated the query, what work you did to make it go fast (which using the WITH clause like this should be nothing), and as always a well articulated explanation of the purpose of the query would be good.
Good luck, Kevin
|
|
|
Re: Nasty query re-write [message #333846 is a reply to message #333573] |
Mon, 14 July 2008 10:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
![svguerin3](/forum/theme/orafaq/images/yahoo.png)
|
|
Excellent suggestion(s)!! I like the look and feel of using the WITH clause. This idea has been tossed around a few times, and I think this is a prime opportunity to use it.
I'll let you guys know the results, and thank you all again for your continued assistance!
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:25:37 CDT 2024
|