3 Replies Latest reply: Mar 18, 2014 2:06 AM by K.S.I. RSS

    Sql Query Tunning

    2618432

      Hi,

      I am new to oracle performance tunning, now i have situation to tune the below query

      SELECT  DISTINCT eod_structure_definition_id,
      p_eod_run_id,
      p_publish_group,
      pg.JMSDBUS_DESTINATION,
      pg.dbaxbuild_location,
      pg.LOCATION,
      sd.DATA_TYPE
      FROM
      PUBLISH_GROUP pg,
      STRUCTURE_EOD_MAPPING sem,
      WATCH_LIST_STRUCTURE wls,
      STRUCTURE_DEFINITION sd
      WHERE  pg.publish_group_name = sem.publish_group_name
      ANDsem.publish_group_name = p_publish_group
      ANDwls.structure_definition_id = sem.structure_definition_id
      ANDwls.watch_list_id IN ( SELECT watch_list_id FROM TMP_WATCHLIST)
      ANDsd.structure_definition_id = sem.structure_definition_id
      AND(sd.defcurve_name IS NULL OR sd.defcurve_name IN (SELECT curve_shortname FROM DEFCURVE_CURRENT WHERE CURVE_STATUS = 'live'))
      -- if the structure is a CREDITBASKET generic, make sure it is linked to a live defcurve
      AND (sd.generic_class_name IS NULL OR sd.generic_class_name <> 'CREDIT' OR( sd.generic_class_name = 'CREDIT'
      and generic_name in
      (
      select generic_name
      from
      analytic_object ao,
      analytic_object_instance aoi,
      analytic_object_property aop,
      defcurve_current dc
      where ao.analytic_object_id = aoi.analytic_object_id
      and aop.analytic_object_instance_id = aoi.analytic_object_instance_id
      and aop.PROPERTY_NAME = 'CreditObjectName'
      and aop.prop_value1 = dc.curve_shortname
      and aop.effective_to > systimestamp
      and aop.effective_from < systimestamp
      and dc.curve_status = 'live'
      and aoi.analytic_object_instance_id in
      (select analytic_object_instance_id
      from
      analytic_object_property
      where property_name = 'CreditObjectType'
      )
      )
      )
      );

      i have indexces on all the key columns, can some one plsase help me out to resolve this.

        • 1. Re: Sql Query Tunning
          SKP

          It is difficult  to make comments on performance just looking at the query.

           

          Please read the thread  HOW TO: Post a SQL statement tuning request - template posting

          • 2. Re: Sql Query Tunning
            K.S.I.

            Hi.

             

            For a start study the current plan of your request:

            connection order, type of connection, access methods to objects (to FTS, INDEX RS/FFS/FS/SS, RowID) ,the quantitative estimates of CBO ,statistics run-time etc.

             

            Select candidate units in the plan for refactoring.

             

            Rewrite your SQL query (for example: using factorization of subqueries), after analyze the SQL query,

             

            In most cases optimization of logic of request brings result .

             

            You can show the execution plan of your request?

             

             

            SELECT  DISTINCT eod_structure_definition_id,
            p_eod_run_id,
            p_publish_group,
            pg.JMSDBUS_DESTINATION,
            pg.dbaxbuild_location,
            pg.LOCATION,
            sd.DATA_TYPE
            FROM
            PUBLISH_GROUP pg,
            STRUCTURE_EOD_MAPPING sem,
            WATCH_LIST_STRUCTURE wls,
            STRUCTURE_DEFINITION sd
            WHERE  pg.publish_group_name = sem.publish_group_name
            ANDsem.publish_group_name = p_publish_group
            ANDwls.structure_definition_id = sem.structure_definition_id
            ANDwls.watch_list_id IN ( SELECT watch_list_id FROM TMP_WATCHLIST)
            ANDsd.structure_definition_id = sem.structure_definition_id
            AND(sd.defcurve_name IS NULL OR sd.defcurve_name IN (SELECT curve_shortname FROM DEFCURVE_CURRENT WHERE CURVE_STATUS = 'live'))
            -- if the structure is a CREDITBASKET generic, make sure it is linked to a live defcurve
            AND (sd.generic_class_name IS NULL OR sd.generic_class_name <> 'CREDIT' OR( sd.generic_class_name = 'CREDIT'
            and generic_name in
            (
            select generic_name
            from
            analytic_object ao,
            analytic_object_instance aoi,
            analytic_object_property aop,
            defcurve_current dc
            where ao.analytic_object_id = aoi.analytic_object_id
            and aop.analytic_object_instance_id = aoi.analytic_object_instance_id
            and aop.PROPERTY_NAME = 'CreditObjectName'
            and aop.prop_value1 = dc.curve_shortname
            and aop.effective_to > systimestamp
            and aop.effective_from < systimestamp
            and dc.curve_status = 'live'
            and aoi.analytic_object_instance_id in
            (select analytic_object_instance_id
            from
            analytic_object_property
            where property_name = 'CreditObjectType'
            )
            )
            )
            );

             

            • 3. Re: Sql Query Tunning
              Karthick_Arp

              The code in red looks unnecessory. Check that one.

               

              select generic_name

                from analytic_object ao

                   , analytic_object_instance aoi

                   , analytic_object_property aop

                   , defcurve_current dc

              where ao.analytic_object_id = aoi.analytic_object_id

                 and aop.analytic_object_instance_id = aoi.analytic_object_instance_id

                 and aop.property_name = 'CreditObjectType'

                 and aop.prop_value1 = dc.curve_shortname

                 and aop.effective_to > systimestamp

                 and aop.effective_from < systimestamp

                 and dc.curve_status = 'live'

                 and aoi.analytic_object_instance_id in

                     (

                       select analytic_object_instance_id

                         from analytic_object_property

                        where property_name = 'CreditObjectType'

                     )

               

              Next why do you have DISTINCT in the query? Its "Just for safety" purpose? In that case remove it. DISTINCT could be very expensive when used when its not necessary.

               

              select distinct eod_structure_definition_id

                   , p_eod_run_id

                   , p_publish_group

                   , pg.jmsdbus_destination

                   , pg.dbaxbuild_location

                   , pg.location