9 Replies Latest reply: Jun 1, 2010 7:32 AM by 776357 RSS

    Including parallel in your queries.

    776357
      Hi All,

      I am new to Oracle and i have a problem, actually i have a query in which i need to add parallel hints, so i added three kind of parallels:-

      1) /*+ parallel(al1,4) parallel(al2,4) parallel(al3,4) parallel(al4,4) */
      2) /*+ optimizer_features_enable('10.0.0') */
      3) /*+ optimizer_features_enable('9.2.0') */

      I got the best result with the third query i.e it ran successfully in the minimum time. i.e it too 23:58 mins

      My question is that is there any other way to reduce the runtime of the query i.e give results in less than 23:58 mins.
        • 1. Re: Including parallel in your queries.
          stellios
          No doubt you read somewhere that making a query parallel can reduce the running time?

          Some comments on what you have done so far - was this test in a controlled environment? Are you sure you acquired the same number of parallel servers per test? For example, if there are say 10 parallel query slaves and it happens to be another session used some and you only got 2 and next test you ran you got 8 then your test is not really proper test.

          Why are you changing the optimizer feature versions, do you have a specific reason? There are some instances after an upgrade why you might do this but do these apply here?

          Have you looked at the execution plan of the original SQL to find other improvements? Using parallel is a licensed option, do you have licenses to use it better yet is it even worth getting a license if there is a better less expensive way (dual use for expensive - $ cost and execution cost)?
          • 2. Re: Including parallel in your queries.
            776357
            Well frankly speaking this is something new to me, but yes it was tested in a control env.

            Thats true that i might not get tha same number of processors every time too.

            Changing the version of the optimiser was a friends idea so i implemented that.

            And no i have not seen the execution plan yet, told u that i am new to oracle.
            • 3. Re: Including parallel in your queries.
              Marko Živković
              Hi,

              That hint is dangerous if you don't use it in right way. You can block your whole server if you use parallel on all processors with bad query. You need to give explain plan, statspack and your query if you want to someone help you. 24 min of server time is too long if you take all processors.

              Regards
              • 4. Re: Including parallel in your queries.
                776357
                Here is the query i am trying to optimise.....


                /* Formatted on 2010/06/01 11:19 (Formatter Plus v4.8.8) */
                SELECT /*+ optimizer_features_enable('9.2.0') */
                al2.asr_full_name, al2.party_account_number, al2.area,
                SUBSTR (TO_CHAR (al1.sftwr_authn_cd), 1, 150),
                SUBSTR (TO_CHAR (al13.brm_item_cd), 1, 50),
                SUBSTR (TO_CHAR (al13.brm_bx_typ_nm), 1, 50),
                SUBSTR (TO_CHAR (al13.brm_recon_item_cd), 1, 50),
                SUBSTR (TO_CHAR (al13.bus_grp_cd), 1, 50),
                al2.cs_specific_customer_name,
                SUBSTR (TO_CHAR (al1.direct_nest_instnc_id), 1, 40),
                SUBSTR (TO_CHAR (al1.connect_home_refusal_rsn), 1, 100),
                SUBSTR (TO_CHAR (al1.connect_home_refuser), 1, 100),
                SUBSTR (TO_CHAR (al1.connect_in_refusal_reason), 1, 100),
                SUBSTR (TO_CHAR (al1.connect_in_refuser), 1, 100),
                SUBSTR (TO_CHAR (al1.cnctd_sn), 1, 50),
                SUBSTR (TO_CHAR (al15.cnctn_typ_nm), 1, 40), al2.contact_first_name,
                al2.contact_last_name, al2.contract_customer_type,
                DECODE (TO_CHAR (al22.cntrc_end_dt, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al22.cntrc_end_dt, 'dd-Mon-yyyy')
                ),
                SUBSTR (TO_CHAR (al23.be_name), 1, 50),
                SUBSTR (TO_CHAR (al22.cntrc_ln_cvrd_lvl_nm), 1, 10),
                SUBSTR (TO_CHAR (al22.cntrc_mod_num), 1, 50),
                SUBSTR (TO_CHAR (al22.cntrc_num_cd), 1, 10),
                SUBSTR (TO_CHAR (al22.cntrc_subln_stat_cd), 1, 20),
                DECODE (TO_CHAR (al22.cntrc_subln_end_dt, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al22.cntrc_subln_end_dt, 'dd-Mon-yyyy')
                ),
                DECODE (TO_CHAR (al22.cntrc_subln_strt_dt, 'dd-Mon-yyyy'),
                '31-Dec-4711', '-',
                TO_CHAR (al22.cntrc_subln_strt_dt, 'dd-Mon-yyyy')
                ),
                SUBSTR (TO_CHAR (al22.cntrc_cvrg_typ_cd), 1, 10),
                al19.location11i_market_name,
                DECODE (UPPER (SUBSTR (TO_CHAR (al13.instl_src_cd), 1, 5)),
                'YES', 'YES',
                'NO', 'NO',
                NULL
                ),
                SUBSTR (TO_CHAR (al11.dial_in_cond_nm), 1, 40),
                al2.district_service_manager, al2.district, al2.division,
                al2.domestic_duns_number, al2.domestic_duns_name,
                al2.entity_identifier,
                SUBSTR (TO_CHAR (al13.fld_replbl_unt_nm), 1, 50),
                SUBSTR (TO_CHAR (al1.flovr_authn_cd), 1, 40),
                SUBSTR (TO_CHAR (al1.flovr_host_id), 1, 50), al2.geo,
                al2.global_duns_number, al2.global_duns_name,
                SUBSTR (TO_CHAR (al13.item_grp_nm), 1, 50), al2.customer_city,
                al2.customer_country, al2.customer_address_line_1,
                al2.customer_address_line_2, al2.customer_address_line_3,
                al2.customer_address_line_4, al2.customer_number,
                al2.customer_postal_code, al2.customer_province, al2.customer_state,
                SUBSTR (TO_CHAR (al18.instl_bs_instnc_stat_nm), 1, 30), al1.item_qty,
                SUBSTR (TO_CHAR (al1.item_sn), 1, 40),
                SUBSTR (TO_CHAR (al1.instlr_nm), 1, 80),
                SUBSTR (DECODE (TO_CHAR (al1.instld_on_instnc_id),
                '$NA', '-',
                TO_CHAR (al1.instld_on_instnc_id)
                ),
                1,
                50
                ),
                SUBSTR (TO_CHAR (al1.instance_description), 1, 100),
                SUBSTR (TO_CHAR (al1.instnc_num), 1, 40), al2.internal_customer,
                DECODE (TO_CHAR (al9.per_of_tm_day, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al9.per_of_tm_day, 'dd-Mon-yyyy')
                ),
                SUBSTR (TO_CHAR (al13.item_desc), 1, 50),
                DECODE (TO_CHAR (al8.per_of_tm_day, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al8.per_of_tm_day, 'dd-Mon-yyyy')
                ),
                SUBSTR (DECODE (TO_CHAR (al13.item_num),
                '$NA', '-',
                TO_CHAR (al13.item_num)
                ),
                1,
                50
                ),
                SUBSTR (TO_CHAR (al13.item_typ_cd), 1, 40),
                SUBSTR (TO_CHAR (al1.mach_id_1), 1, 50),
                SUBSTR (TO_CHAR (al1.mach_id_2), 1, 50),
                SUBSTR (TO_CHAR (al13.microcd_flg_nm), 1, 40),
                SUBSTR (DECODE (TO_CHAR (al13.item_num),
                '$NA', '-',
                TO_CHAR (al13.item_num)
                ),
                1,
                50
                ),
                al1.cntrc_mdl_qty, SUBSTR (TO_CHAR (al1.disp_nest_lvl), 1, 10),
                DECODE (TO_CHAR (al1.top_lvl_mdl_instnc_id),
                TO_CHAR (al22.instnc_id), 'Y',
                'N'
                ),
                al2.parent_duns_number, al2.parent_duns_name,
                SUBSTR (DECODE (TO_CHAR (al24.item_num),
                '$NA', '-',
                TO_CHAR (al24.item_num)
                ),
                1,
                50
                ),
                SUBSTR (DECODE (TO_CHAR (al25.item_num),
                '$NA', '-',
                TO_CHAR (al25.item_num)
                ),
                1,
                50
                ),
                SUBSTR (TO_CHAR (al1.prnt_part_sn), 1, 40), al2.customer_name,
                al2.pref_install_at, SUBSTR (TO_CHAR (al21.be_name), 1, 100),
                al2.primary_ce_full_name, al2.primary_contact_indicator,
                SUBSTR (TO_CHAR (al13.item_cd_nm), 1, 40),
                SUBSTR (TO_CHAR (al13.item_fmly_cd), 1, 40),
                SUBSTR (TO_CHAR (al13.prod_typ_cd), 1, 20),
                SUBSTR (TO_CHAR (al13.item_fmly_vrsn_nm), 1, 40), al2.region,
                DECODE (TO_CHAR (al12.per_of_tm_day, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al12.per_of_tm_day, 'dd-Mon-yyyy')
                ),
                SUBSTR (TO_CHAR (al11.cnctn_meth_nm), 1, 60),
                SUBSTR (TO_CHAR (al1.remot_suprt_rfusd_rsn), 1, 100),
                SUBSTR (TO_CHAR (al1.remot_suprt_rfusr), 1, 50),
                SUBSTR (TO_CHAR (al5.item_rvsn_nm), 1, 100),
                SUBSTR (TO_CHAR (al5.item_rvsn_desc), 1, 100), al2.sam_full_name,
                al1.syr_lst_dl_hm_dt, SUBSTR (TO_CHAR (al6.sls_chnl_cd), 1, 50),
                SUBSTR (TO_CHAR (al1.sls_ord_dl_num), 1, 40),
                SUBSTR (TO_CHAR (al1.sls_ord_num), 1, 40),
                SUBSTR (TO_CHAR (al14.sls_ord_typ_nm), 1, 50),
                SUBSTR (TO_CHAR (al26.rsrc_nm), 1, 100), al2.secondary_ce_full_name,
                SUBSTR (TO_CHAR (al1.sec_dspch_grp_nm), 1, 60), al4.customer_name,
                DECODE (TO_CHAR (al10.per_of_tm_day, 'dd-Mon-yyyy'),
                '31-Dec-4712', '-',
                TO_CHAR (al10.per_of_tm_day, 'dd-Mon-yyyy')
                ),
                al3.sold_by, SUBSTR (TO_CHAR (al13.top_lvl_flg_nm), 1, 5),
                SUBSTR (TO_CHAR (al16.item_num), 1, 40),
                SUBSTR (DECODE (TO_CHAR (al17.item_num),
                '$NA', '-',
                TO_CHAR (al17.item_num)
                ),
                1,
                50
                ),
                SUBSTR (TO_CHAR (al1.top_lvl_part_sn), 1, 40), al2.theater,
                SUBSTR (TO_CHAR (al1.top_lvl_mdl_instnc_id), 1, 50),
                SUBSTR (TO_CHAR (al1.unq_id), 1, 60),
                SUBSTR (TO_CHAR (al13.item_pri_uom_nm), 1, 30),
                SUBSTR (TO_CHAR (al1.sftwr_updt_authn_cd), 1, 50),
                SUBSTR (TO_CHAR (al1.sftwr_updt_enblr_cd), 1, 50)
                FROM whsusr.dim_customer al2,
                whsusr.dim_customer al3,
                whsusr.dim_customer al4,
                whsusr.dim_item_revision al5,
                whsusr.dim_sales_channel al6,
                whsusr.dim_time al8,
                whsusr.dim_time al9,
                whsusr.dim_time al10,
                whsusr.dim_connection_method al11,
                whsusr.dim_time al12,
                whsusr.dim_product al13,
                whsusr.dim_sales_order_type al14,
                whsusr.dim_connection_method al15,
                whsusr.dim_product al16,
                whsusr.dim_product al17,
                whsusr.dim_instl_bs_instnc_stat al18,
                whsusr.att_customer_site al19,
                whsusr.att_customer_site al20,
                whsusr.att_grp al21,
                whsusr.dim_product al24,
                whsusr.dim_product al25,
                whsusr.dim_resource al26,
                whsusr.dm_install_base_tla al1 LEFT OUTER JOIN transdata.ib_to_contract_wrnty al22
                ON (al1.top_lvl_mdl_instnc_id = al22.instnc_id AND al22.primary_flag(+) =
                'Y')
                LEFT OUTER JOIN whsusr.att_cntrc_hdr_typ al23
                ON (al22.cntrc_hdr_typ_cd = al23.cntrc_hdr_typ_cd)
                WHERE ( al19.customer_id = al2.be_id
                AND al20.customer_id = al3.be_id
                AND al1.slsrep_1103 = al26.slsrep_1103_key
                AND al1.srvc_provdr_co_key = al4.be_id
                AND al1.sls_chnl_key = al6.be_id
                AND al1.item_item_rvsn_key = al5.be_id
                AND al1.instl_bs_instnc_stat_key = al18.be_id
                AND al1.tl_item_item_key = al17.be_id
                AND al1.prnt_item_item_key = al24.be_id
                AND al1.instl_at_co_site_key = al19.be_id
                AND al1.bill_to_co_site_key = al20.be_id
                AND al1.sls_ord_typ_key = al14.be_id
                AND al1.prfrd_dspch_grp_key = al21.be_id
                AND al1.instld_on_day_key = al8.be_id
                AND al1.sw_item_rgstrd_on_day_key = al12.be_id
                AND al1.remt_suprt_cnctn_meth_key = al11.be_id
                AND al1.cnct_hm_cnctn_meth_key = al15.be_id
                AND al1.dinstld_on_day_key = al9.be_id
                AND al1.shpd_on_day_key = al10.be_id
                AND al1.mdl_item_key = al13.be_id
                AND al1.prnt_mdl_item_key = al25.be_id
                AND al1.tl_mdl_item_key = al16.be_id
                )
                AND ( al14.start_date <= SYSDATE
                AND al14.end_date > SYSDATE
                AND al21.start_date <= SYSDATE
                AND al21.end_date > SYSDATE
                AND al6.start_date <= SYSDATE
                AND al6.end_date > SYSDATE
                AND al5.start_date <= SYSDATE
                AND al5.end_date > SYSDATE
                AND al17.start_date <= SYSDATE
                AND al17.end_date > SYSDATE
                AND al24.start_date <= SYSDATE
                AND al24.end_date > SYSDATE
                AND al8.start_date <= SYSDATE
                AND al8.end_date > SYSDATE
                AND al12.start_date <= SYSDATE
                AND al12.end_date > SYSDATE
                AND al11.start_date <= SYSDATE
                AND al11.end_date > SYSDATE
                AND al15.start_date <= SYSDATE
                AND al15.end_date > SYSDATE
                AND al13.start_date <= SYSDATE
                AND al13.end_date > SYSDATE
                AND al9.start_date <= SYSDATE
                AND al9.end_date > SYSDATE
                AND al10.start_date <= SYSDATE
                AND al10.end_date > SYSDATE
                AND al16.start_date <= SYSDATE
                AND al16.end_date > SYSDATE
                AND al25.start_date <= SYSDATE
                AND al25.end_date > SYSDATE
                AND al18.start_date <= SYSDATE
                AND al18.end_date > SYSDATE
                AND al26.start_date <= SYSDATE
                AND al26.end_date > SYSDATE
                AND al18.instl_bs_instnc_stat_nm IN
                ('Competitor Maintained',
                'CREATED',
                'Defective Returns',
                'Deinstall - Rtn to Mfg',
                'Deinstall - Cust Site',
                'Deinstall - Mech Rep',
                'Deinstall - Rtn to Mfg',
                'EXPIRED',
                'EXPORT NON-COMPLIANT',
                'Install',
                'Install - T'||'&'||'M',
                'Reseller Maintained',
                'Shipped',
                'Terminate',
                'Terminated'
                )
                AND al13.item_typ_nm = 'MODEL'
                AND al13.prod_typ_cd = 'HARDWARE'
                AND al8.per_of_tm_day >= TO_DATE (' 1-Jan-2010', 'dd-Mon-yyyy')
                AND al8.per_of_tm_day <= TO_DATE (' 1-Mar-2010', 'dd-Mon-yyyy')
                )
                • 5. Re: Including parallel in your queries.
                  Billy~Verreynne
                  user13224347 wrote:

                  I got the best result with the third query i.e it ran successfully in the minimum time. i.e it too 23:58 mins

                  My question is that is there any other way to reduce the runtime of the query i.e give results in less than 23:58 mins.
                  Use the WARP_DRIVE hint. Here's how it work. You run a SQL:
                  SQL> select count(*) from all_objects;
                  
                    COUNT(*)
                  ----------
                       69465
                  
                  Elapsed: 00:00:04.96
                  Next, you contact Scotty telling him that you need every last scrap of power from the warp reactor:
                  SQL> select /*+ WARP_DRIVE(9) */ count(*) from all_objects;
                  
                    COUNT(*)
                  ----------
                       69465
                  
                  Elapsed: 00:00:02.88
                  An almost 50% improvement in elapsed execution time. You can ask for a higher warp factor, but as every Star Trek fanboi knows, warp 9 is the limit on the Federation's best warp reactors. (except perhaps when you have Wesley Crusher as the DBA).

                  Of course, the fact that there is no such hint and the so-called performance improvement is merely that the 2nd query benefited from logical I/O, courtesy of the physical I/O of the 1st query, is purely coincidental.

                  We do not want that to spoil our erroneous measurement of performance using elapsed execution time.. or interfere with our proof that Larry Elison is also a Star Trek fanboi as the WARP_DRIVE hint proves.

                  ;-)
                  • 6. Re: Including parallel in your queries.
                    Marko Živković
                    Hi,

                    Try to make it simpler.

                    You have 25 tables in from clause i think that is tooooo much... Most of the tables are repeating, try to use for example dim_customer table only one time. You have more passes to one table then it is needed.

                    When you have 25 tables, you have 25! combinations that optimizer need to lookup. 25x24x23...x2x1 that is too much combinations and only for order of tables.

                    Why do you use below code twice
                    FROM whsusr.dim_customer al2, whsusr.dim_customer al3, 
                     whsusr.att_customer_site al19, whsusr.att_customer_site al20
                    WHERE (    al19.customer_id = al2.be_id
                           AND al20.customer_id = al3.be_id
                    This is odd sintax you use LEFT OUTER JOIN and (+) in same time.
                    LEFT OUTER JOIN transdata.ib_to_contract_wrnty al22
                         ON (al1.top_lvl_mdl_instnc_id = al22.instnc_id AND al22.primary_flag(+) = 'Y')
                    Can you rewrite that query in that way that you don't have duplicated tables.

                    Regards
                    • 7. Re: Including parallel in your queries.
                      Marko Živković
                      Hi,

                      Wow! This is better then fast_sql=true.

                      Regards
                      • 8. Re: Including parallel in your queries.
                        Fahd.Mirza
                        And please dont tell him about the hit rations. Leave that to.....Guess who?

                        regards
                        • 9. Re: Including parallel in your queries.
                          776357
                          hey thanks a lot for help...would try to implement it ans see what happens.