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
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 #329225 is a reply to message #329224] Tue, 24 June 2008 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know of any other info that would be useful

Of course all these information would be useful as well as a correctly formated query.
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Nasty query re-write [message #329231 is a reply to message #329224] Tue, 24 June 2008 11:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
In addition to what @Michel has suggested, could you please post the explain plan without the hints.

Regards

Raj
Re: Nasty query re-write [message #329234 is a reply to message #329224] Tue, 24 June 2008 11:15 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 #329236 is a reply to message #329234] Tue, 24 June 2008 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gather statistics on your objects, set optimizer_goal to choose and replay.

Something strange for me: VIEW PUSHED PREDICATE, HASH JOIN, Cost along with "Optimizer=RULE".

Regards
Michel
Re: Nasty query re-write [message #329237 is a reply to message #329234] Tue, 24 June 2008 11:21 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

And one more thing I forgot to include, the full version of Oracle:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
Re: Nasty query re-write [message #329242 is a reply to message #329236] Tue, 24 June 2008 11:54 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Ok, I ran statistics on the t_fbsco and t_fbsos tables, and I set the optimizer_mode to CHOOSE. Then I did the explain plan again. Here's the semi-formatted output:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=197715 Card=63110 Bytes=44492550)

   1    0   SORT (ORDER BY) (Cost=197715 Card=63110 Bytes=44492550)
   2    1     NESTED LOOPS (OUTER) (Cost=191443 Card=63110 Bytes=44492550)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2113 Card=63110 Bytes=22845820)

   4    3         INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=270 Card=63110)

   5    2       VIEW PUSHED PREDICATE (Cost=3 Card=1 Bytes=343)
   6    5         HASH JOIN (Cost=161295 Card=1 Bytes=372)
   7    6           NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=353)
   8    7             TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSCO_CHAN_ORD' (Cost=4 Card=1 Bytes=314)

   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' (UNIQUE) (Cost=1 Card=1)

  12    6           VIEW (Cost=161288 Card=63110 Bytes=1199090)
  13   12             SORT (GROUP BY) (Cost=161288 Card=63110 Bytes=1199090)

  14   13               HASH JOIN (Cost=161039 Card=63110 Bytes=1199090)

  15   14                 TABLE ACCESS (BY INDEX ROWID) OF 'T_FBSOS_OFFR_STG' (Cost=2113 Card=63110 Bytes=631100)

  16   15                   INDEX (RANGE SCAN) OF 'FPPBSOSNN01' (NON-UNIQUE) (Cost=270 Card=1)

  17   14                 TABLE ACCESS (FULL) OF 'T_FBSCO_CHAN_ORD' (Cost=123284 Card=94357231 Bytes=849215079)
Re: Nasty query re-write [message #329275 is a reply to message #329242] Tue, 24 June 2008 21:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What proportion of the data contains the selected :RUN_ID?

Less than 1%? More than 10%? Somewhere in-between?

Ross Leishman
Re: Nasty query re-write [message #329296 is a reply to message #329275] Tue, 24 June 2008 23:46 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Great question. It can vary depending on the size of the "run", but the ones usually in question, that have the longest run-times, typically contain 1-2% of the entire data-set. Usually no more than that.
Re: Nasty query re-write [message #329317 is a reply to message #329296] Wed, 25 June 2008 01:05 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 #330073 is a reply to message #329492] Fri, 27 June 2008 09:08 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Anyone have any further advice on how this query can be sped up or re-formatted further? Adding the NO_PUSH_PRED definitely helps the query, especially if you compare the run-time to when it runs with no hints at all, but it still runs for about 20-25 minutes (which is lengthy for the amount of data we're talking about).

Any additional advice would be greatly appreciated, and thank you all for your continued help!
Re: Nasty query re-write [message #330469 is a reply to message #330073] Mon, 30 June 2008 04:22 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 messageGo to next message
BlackSwan
Messages: 25033
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 messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 #330665 is a reply to message #329224] Mon, 30 June 2008 13:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Why is RBO being used & not CBO?

What happens when you force use of CBO?

How many rows in these tables 'T_FBSCO_CHAN_ORD' & 'S_FBPC'?
Re: Nasty query re-write [message #330680 is a reply to message #330665] Mon, 30 June 2008 15:15 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 messageGo to next message
rleishman
Messages: 3724
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 #330710 is a reply to message #330707] Mon, 30 June 2008 23:54 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Hmm, strange. I've engaged the DBAs to get their thoughts on why the rowcount is now showing up in that column. I'm probably doing something wrong in my session, not sure. I'm basically logging into sql-PLUS, altering the session to trace, changing it to optimizer-CHOOSE, setting the head off, then running the query. Then I exit SQLplus and run the tkprof against the trace.

I'll let you know if they get me an answer on why they aren't showing. Please let me know of any thoughts that come to mind as to what could be wrong with my above steps. Thanks for all your help!
Re: Nasty query re-write [message #330924 is a reply to message #330710] Tue, 01 July 2008 15:55 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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 #331738 is a reply to message #331506] Sat, 05 July 2008 02:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The MERGE join will be slowing it down some. Suggest you add a USE_HASH hint to force a hash join.

Ross Leishman
Re: Nasty query re-write [message #333573 is a reply to message #329224] Sat, 12 July 2008 21:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

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!
Previous Topic: query performance
Next Topic: Oracle Query Tuning
Goto Forum:
  


Current Time: Sat Dec 03 11:44:10 CST 2016

Total time taken to generate the page: 0.12582 seconds