13 Replies Latest reply: Jul 7, 2014 8:13 AM by chris227 RSS

    Performance tuning help.

    Ranganathan -Oracle

      Hi All,

       

      Need help on tuning the below query ..

      with    visib as (SELECT VIS.ENTITY_TYPE_VALUE,
                                         VIS.ENTITY_TYPE_VALUE || ' - ' ||
                                         vis.client_nm AS DISP
                                    FROM (SELECT GEID,
                                                 BRANCH_CD,
                                                 RM_NAME,
                                                 EG_TITLE,
                                                 MKTNG_TEAM,
                                                 ENTITY_TYPE_NBR,
                                                 ENTITY_TYPE_VALUE,
                                                 ENTITY_GROUP_VALUE,
                                                 RM_GEID,
                                                 CLIENT_NM,
                                                 EXIT_STATUS,
                                                 BASE_SEC_WAIVER as BASE_SECRECY_WAIVE_IND,
                                                 lcl_acct_ofcr as LOCAL_ACCOUNT_OFFICER,
                                                 entity_type_value as entity_number_action
                                            FROM VISIBILITY_TABLE
                                           WHERE ENTITY_TYPE_NBR != 4
                                             AND GEID = '1000498065') VIS
                                   WHERE VIS.ENTITY_TYPE_NBR = 3
                                     AND VIS.GEID = '1000498065'
                                     AND VIS.RM_GEID = '0000629043'
                                   ORDER BY VIS.ENTITY_TYPE_VALUE),
             alert_data as (select alert_category_nbr
                              from category_table
                             where alert_type_nbr = 3)
              select /*+ INDEX(t ALT_ALERT_CAT_MAST_IND)*/
                 distinct visib.entity_type_value, visib.DISP
                from visib, alert_data a, alerts_txnmst t
               where t.alert_category_nbr = a.alert_category_nbr
                 and t.entity_type_value in
                     (select entity_type_value
                        from alt_visibility_cache_dref v
                       START WITH v.entity_type_value = visib.entity_type_value
                              and v.geid = '1000498065'
                      CONNECT BY PRIOR v.entity_type_value = v.entity_group_value
                             and v.geid = '1000498065');

       

      The visib with clause will retrieve a set of parent rows for the provided geid and rm_geid. This table will have around 50K rows and all the conditions used in this query is indexed.

       

      The alert_data with clause will retrieve 8 rows of data which are needed for the logic. The total records in this table is only 40 rows.

       

      The requirement was to take all the rows from visib with clause and fetch the child records of it. then they needs to be checked in alerts_txnmst table for the categories retrieved from alert_data.

      For this condition the alerts_txnmst table will have around 60K rows and the hierarchical query will retrieve the parent to child records. around 100 rows.

       

      So if any matched rows is found in the alerts_txnmst table then we need show the details of its Parent retrieved from Visib_data with clause.

       

      Please find the below Explain Plan of this query. There is no Table Full scan. Made the query to use the indexes properly. But still the query is executing for around 15 to 20 seconds. Expectation is around 2 to 3 seconds.

       

      SELECT STATEMENT, GOAL = ALL_ROWS    Id=0   Cost=744576    Cardinality=1    Bytes=217
      HASH UNIQUE    Id=1   Cost=744576    Cardinality=1    Bytes=217
        FILTER    Id=2  
         NESTED LOOPS    Id=3  
      NESTED LOOPS    Id=4   Cost=1638    Cardinality=76519    Bytes=16604623
      MERGE JOIN CARTESIAN    Id=5   Cost=34    Cardinality=68    Bytes=13532
        VIEW    Id=6    Object owner=ALERTSASIA   Cost=30    Cardinality=9    Bytes=1728
         SORT ORDER BY    Id=7   Cost=30    Cardinality=9    Bytes=576
          TABLE ACCESS BY INDEX ROWID    Id=8    Object owner=ALERTSASIA    Object name=ALT_VISIBILITY_CACHE_DREF    Cost=29    Cardinality=9    Bytes=576
           BITMAP CONVERSION TO ROWIDS    Id=9  
            BITMAP AND    Id=10  
             BITMAP CONVERSION FROM ROWIDS    Id=11  
              INDEX RANGE SCAN    Id=12    Object owner=ALERTSASIA    Object name=ALT_VISCACHE_RMGEID_IDX    Cost=1    Cardinality=124   
             BITMAP CONVERSION FROM ROWIDS    Id=13  
              INDEX RANGE SCAN    Id=14    Object owner=ALERTSASIA    Object name=ALT_VISCACHE_GEID_TYP_NBR_IDX    Cost=26    Cardinality=124   
        BUFFER SORT    Id=15   Cost=34    Cardinality=8    Bytes=56
         INDEX FAST FULL SCAN    Id=16    Object owner=ALERTSASIA    Object name=ALT_ALERTCATEGORY_TYPENBR_IDX    Cost=0    Cardinality=8    Bytes=56
      INDEX RANGE SCAN    Id=17    Object owner=ALERTSASIA    Object name=ALT_ALERT_CAT_MAST_IND    Cost=2    Cardinality=1422   
      TABLE ACCESS BY INDEX ROWID    Id=18    Object owner=ALERTSASIA    Object name=ALT_ALERTS_TXNMST    Cost=29    Cardinality=1134    Bytes=20412
         FILTER    Id=19  
      CONNECT BY WITH FILTERING (UNIQUE)    Id=20  
      TABLE ACCESS BY INDEX ROWID    Id=21    Object owner=ALERTSASIA    Object name=ALT_VISIBILITY_CACHE_DREF    Cost=3    Cardinality=1    Bytes=38
        INDEX RANGE SCAN    Id=22    Object owner=ALERTSASIA    Object name=ALT_VISCACHE_ENTITYVALUE_IDX    Cost=1    Cardinality=2   
      NESTED LOOPS    Id=23   Cost=7    Cardinality=2    Bytes=230
        CONNECT BY PUMP    Id=24  
        TABLE ACCESS BY INDEX ROWID    Id=25    Object owner=ALERTSASIA    Object name=ALT_VISIBILITY_CACHE_DREF    Cost=4    Cardinality=2    Bytes=76
         INDEX RANGE SCAN    Id=26    Object owner=ALERTSASIA    Object name=ALT_VISCACHE_ENTITYGROUP_IDX    Cost=1    Cardinality=4   

       

       

      Please suggest some idea to fine tune this query further.

        • 2. Re: Performance tuning help.
          Hoek

          What happens if you run the query WITHOUT the index hint?

          Why the DISTINCT? It is an expensive operation and usually the query can be rewritten in a way without the need for a DISTINCT.

          Also, post the result of: select * from v$version;

          • 3. Re: Performance tuning help.
            Ranganathan -Oracle

            Hi,

            If i dint use the Index Hint and if the query is NOT returning any rows then it is using the Index Scan by rowid on alerts_txnmst and if the query is returning rows then explain plan shows Table Full Scan for the alerts_txnmst table.

             

            If i dint use distinct then it goes cartesian join somehow and returns lot of rows with same value

             

            Result of v$version

            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

            PL/SQL Release 11.2.0.2.0 - Production

            CORE    11.2.0.2.0    Production

            TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

            NLSRTL Version 11.2.0.2.0 - Production

            • 4. Re: Performance tuning help.
              chris227

              First of all you should rewrite your query and narrow it to the less necessary:

               

              with visib as (
              SELECT ENTITY_TYPE_VALUE
              ,      ENTITY_TYPE_VALUE || ' - ' ||
                     client_nm AS DISP
              FROM VISIBILITY_TABLE
              WHERE ENTITY_TYPE_NBR = 3
                AND GEID = '1000498065'
                AND RM_GEID = '0000629043'
              )
              , alert_data as (
              select alert_category_nbr
              from category_table
              where alert_type_nbr = 3)

              select /*+ INDEX(t ALT_ALERT_CAT_MAST_IND)*/
                distinct
                visib.entity_type_value
              , visib.DISP
              from visib
              ,    alert_data a
              ,    alerts_txnmst t
              where t.alert_category_nbr = a.alert_category_nbr
                and t.entity_type_value in
                             (select entity_type_value
                                from alt_visibility_cache_dref v
                               START WITH v.entity_type_value = visib.entity_type_value
                                      and v.geid = '1000498065'
                               CONNECT BY PRIOR v.entity_type_value = v.entity_group_value
                                     and v.geid = '1000498065');

              First question that arises from this is did you forget a join predicate on visib?

              And wyh you join alert_data since you dont have any predicates or projection columns on it?

              • 5. Re: Performance tuning help.
                Ranganathan -Oracle

                Hi Chris,

                There is no Join predicate for visib is because the visib returns only the parent rows and i want to retrieve all the child rows of it and then compare them against the alerts_txnmst table. If i put a join on visib and alerts_txnmst on the entity_type_value column, then the result will be for only the parent rows.

                 

                Alert_data is used to search only the alert_categoy_nbr it gives in the alerts_txnmst table.

                 

                for example the visib might return a Relation R1. and that R1 might be present or not in the alerts_txnmst table. then in that case i should see the child of it, which are say EG1 and its child PF1 and PF2. So now my hierarchical query gives R1, EG1, PF1 and PF2. So these 4 rows needs to be searched in alerts_txnmst table and if any matching rows is found for any value then the Relation R1 should be returned in the result.

                • 6. Re: Performance tuning help.
                  David Berger

                  Hello Ranganathan-Oracle

                   

                  I have simplified your query.. for example you do not need to have an ORDER BY clause.. and maybe you need the MATERIALIZE hint.. it depends..

                  WITH VISIB

                    AS (SELECT /*+ MATERIALIZE NO_MERGE */

                               entity_type_value

                             , entity_type_value || ' - ' || client_nm AS DISP

                          FROM VISIBILITY_TABLE

                         WHERE entity_type_nbr = 3

                           AND geid            = '1000498065' 

                           AND rm_geid         = '0000629043'    

                        )

                  , ALERT_DATA

                    AS (SELECT alert_category_nbr

                          FROM CATEGORY_TABLE

                         WHERE alert_type_nbr = 3

                       )

                  SELECT /*+ INDEX(ALT ALT_ALERT_CAT_MAST_IND)*/

                         DISTINCT

                         VIS.entity_type_value

                       , VIS.DISP

                    FROM

                         VISIB         VIS

                       , ALERT_DATA    ALA

                       , ALERTS_TXNMST ALT

                  WHERE

                         ALA.alert_category_nbr = ALT.alert_category_nbr

                     AND ALT.entity_type_value

                              IN (SELECT entity_type_value

                                    FROM ALT_VISIBILITY_CACHE_DREF V

                                   START WITH v.entity_type_value = visib.entity_type_value

                                          AND v.geid = '1000498065'

                                 CONNECT BY PRIOR v.entity_type_value = v.entity_group_value

                                              AND v.geid = '1000498065'

                                 );

                   

                  What we can see firstly that you do not have any join-predicate on the table VISIB... This could be the reason why you get multiple rows back..

                   

                  Maybe this help you to make your query better.. I hope it.

                   

                  Regards, David

                  • 7. Re: Performance tuning help.
                    David Berger
                    If i put a join on visib and alerts_txnmst on the entity_type_value column, then the result will be for only the parent rows.


                    It seems that you should use in this case an OUTER-JOIN... Do you know this Join-Type?

                    • 8. Re: Performance tuning help.
                      chris227

                      I think we need some test data (create table or with clause see FAQ) since i doubt your whole approach.

                      • 9. Re: Performance tuning help.
                        Ranganathan -Oracle

                        If i use the MATERIALIZE  hint then the visib with clause is not using the merge join cartesian, but the main query again goes into the merge join.

                         

                        if i rewrite the below condition as

                           AND ALT.entity_type_value

                                    IN (SELECT entity_type_value

                                          FROM ALT_VISIBILITY_CACHE_DREF V

                                         START WITH v.entity_type_value = visib.entity_type_value

                                                AND v.geid = '1000498065'

                                       CONNECT BY PRIOR v.entity_type_value = v.entity_group_value

                                                    AND v.geid = '1000498065'

                                       );

                        rewriting as


                        AND ALT.entity_type_value = visib.entity_type_value


                        Then there is no merge join cartesian occurs and cost get much lower.

                        Is there any option to avoid cartesian product in my query .

                        • 10. Re: Performance tuning help.
                          chris227

                          Ranganathan -Oracle wrote:


                          AND ALT.entity_type_value = visib.entity_type_value


                          Then there is no merge join cartesian occurs and cost get much lower.

                          Is there any option to avoid cartesian product in my query .

                          That's what i told you already.

                          The materialze hint doesnt change anything on this.

                          First aim has to be to find a appropriate query for your requirements.

                          So if you come up with some test data we might start ...

                          • 11. Re: Performance tuning help.
                            Ranganathan -Oracle

                            Assume below is the result from the visib with clause

                             

                            Relation1

                             

                            And the list of child records in it from the visibility table in connect by clause is

                             

                            Relation1

                            EG1

                            PF1

                            PF2

                            EG2

                            PF3

                             

                            In the alerts_txnmst table we will have a lot of data around 150,000 records.

                            In that we are going to see only the alert_category_nbr retrieved from the alert_data with clause.

                            So assume these records would be around 50000 records.

                             

                            The structure of alerts_txnmst table is

                            alert_category_nbr   alert_type_nbr   entity_type_value

                               1                       3                Relation1

                               1                       3                Relation2

                               1                       3                EG1

                               2                       3                PF3

                               3                       3                EG3

                              

                            Here from our above result of hierarchical query, Relation1, EG1, PF3 are present in the alerts_txnmst table.

                            So i should provide the Relation1 in the fetch list.

                             

                            Does this sample values help  you ?

                            • 12. Re: Performance tuning help.
                              Hoek

                              It would help more if you'd post CREATE TABLE and a few INSERT INTO statements...see: Re: 2. How do I ask a question on the forums?

                              • 13. Re: Performance tuning help.
                                chris227

                                Ranganathan -Oracle wrote:

                                 

                                Does this sample values help  you ?

                                No, sorry, see Hoek reply.

                                Without this my advise would be:

                                Do the connect by on the join of visib and ALT_VISIBILITY_CACHE_DREF first (e.g. in the first with clause).

                                Afterwards you join the other tables to the outcome.