1 2 Previous Next 15 Replies Latest reply: Feb 6, 2013 6:51 AM by Nikolay Savvinov RSS

    Query Tuning:

    user12090988
      Hi all,

      How can I tune the below query for better performance.

      My oracle database is on 11.2.0.3
      SELECT COUNT(1) 
      FROM AFMS_RELT_FEEDBK
      WHERE
      (CSE_CD IN (SELECT CSE_CD FROM FMS_RELT_FEEDBK)) OR
      (RELT_CSE_CD IN (SELECT RELT_CSE_CD FROM FMS_RELT_FEEDBK));
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3722087114
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT       |                     |     1 |    20 |   175M  (1)|584:08:35 |
      |   1 |  SORT AGGREGATE        |                     |     1 |    20 |            |          |
      |*  2 |   FILTER               |                     |       |       |            |          |
      |   3 |    INDEX FAST FULL SCAN| AFMS_RELT_FEEDBK_PK |    44M|   851M| 62751   (1)| 00:12:34 |
      |*  4 |    INDEX RANGE SCAN    | FMS_RELT_FEEDBK_PK  |     2 |    28 |     4   (0)| 00:00:01 |
      |*  5 |    INDEX FAST FULL SCAN| FMS_RELT_FEEDBK_PK  |     2 |    26 |   764   (1)| 00:00:10 |
      ----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter( EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK" WHERE
                    "CSE_CD"=:B1) OR  EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK"
                    WHERE "RELT_CSE_CD"=:B2))
         4 - access("CSE_CD"=:B1)
         5 - filter("RELT_CSE_CD"=:B1)
      
      21 rows selected.
        • 1. Re: Query Tuning:
          NSK2KSN
          wrong entry

          Edited by: NSK2KSN on Feb 5, 2013 10:43 AM
          • 2. Re: Query Tuning:
            Karthick_Arp
            NSK2KSN wrote:
            check with below query,

            this way you can reduce the usage of accessing table one time less
            SELECT COUNT (1)
            FROM AFMS_RELT_FEEDBK
            WHERE (CSE_CD || RELT_CSE_CD) IN
            (SELECT CSE_CD || RELT_CSE_CD FROM FMS_RELT_FEEDBK)
            Edited by: NSK2KSN on Feb 5, 2013 10:21 AM
            This query is not the same as that of OP, correct? because in OP query CSE_CD and RELT_CSE_CD are checked seperately for existance in FMS_RELT_FEEDBK. But yours does a combination check.

            To OP,

            You need to offer lot more details that what you have provided. I would suggest you read {message:id=9360003} and update your post accordingly.
            • 3. Re: Query Tuning:
              jeneesh
              Try EXISTS - which will use only one scan on FMS_RELT_FEEDBK
              SELECT COUNT(1) 
              FROM AFMS_RELT_FEEDBK afms
              WHERE  exists
                 (
                   select null
                   from FMS_RELT_FEEDBK fms
                   where fms.CSE_CD = afms.CSE_CD 
                   or fms.RELT_CSE_CD = afms.RELT_CSE_CD
                 );
              And have a look at {message:id=9360003}

              Edited by: jeneesh on Feb 5, 2013 10:31 AM
              As already mentioned :)
              • 4. Re: Query Tuning:
                NSK2KSN
                karthik missed, that thanks' for correcting
                • 5. Re: Query Tuning:
                  Purvesh K
                  --Removed.

                  I missed the OR condition. Thanks for correcting Jeneesh and Karthick.

                  Edited by: Purvesh K on Feb 5, 2013 11:00 AM
                  --Removed.                                                                                                                                                                                                                                                                                           
                  • 6. Re: Query Tuning:
                    Karthick_Arp
                    Purvesh K wrote:
                    Another way of writing query:
                    SELECT COUNT(1) 
                    FROM AFMS_RELT_FEEDBK
                    WHERE
                    (CSE_CD, RELT_CSE_CD) IN (SELECT CSE_CD, RELT_CSE_CD FROM FMS_RELT_FEEDBK)
                    Though not as efficient as Jeneesh suggested, but you might want to give it a try if Jeneesh's suggestion does not work.
                    This is incorrect, check my previous post and my comment on NSK2KSN post.
                    • 7. Re: Query Tuning:
                      jeneesh
                      Purvesh K wrote:
                      Another way of writing query:
                      SELECT COUNT(1) 
                      FROM AFMS_RELT_FEEDBK
                      WHERE
                      (CSE_CD, RELT_CSE_CD) IN (SELECT CSE_CD, RELT_CSE_CD FROM FMS_RELT_FEEDBK)
                      This is not same as the OR condition given by OP..
                      • 8. Re: Query Tuning:
                        user12090988
                        Thanks Jeneesh,Karthick, Purvesh & NSK2KSN !

                        I tried the below query and I get the explain plan as below. But it seems to be taking more time(999:59:59 ) as shown in the explain plan?
                        SQL> explain plan for
                          2  SELECT COUNT(1)
                          3       FROM AFMS_RELT_FEEDBK afms
                          4            WHERE  exists
                          5             ( select null
                          6                 from FMS_RELT_FEEDBK fms
                          7                 where fms.CSE_CD = afms.CSE_CD
                          8                      or fms.RELT_CSE_CD = afms.RELT_CSE_CD
                          9              );
                        
                        Explained.
                        
                        
                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        Plan hash value: 133917811
                        
                        ----------------------------------------------------------------------------------------------
                        | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
                        ----------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT       |                     |     1 |    20 |    14G  (2)|999:59:59 |
                        |   1 |  SORT AGGREGATE        |                     |     1 |    20 |            |          |
                        |*  2 |   FILTER               |                     |       |       |            |          |
                        |   3 |    INDEX FAST FULL SCAN| AFMS_RELT_FEEDBK_PK |    44M|   851M| 62702   (1)| 00:12:33 |
                        |*  4 |    INDEX FAST FULL SCAN| FMS_RELT_FEEDBK_PK  |     3 |    81 |   339   (2)| 00:00:05 |
                        ----------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                           2 - filter( EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS" WHERE
                                      "FMS"."CSE_CD"=:B1 OR "FMS"."RELT_CSE_CD"=:B2))
                           4 - filter("FMS"."CSE_CD"=:B1 OR "FMS"."RELT_CSE_CD"=:B2)
                        
                        18 rows selected.
                        • 9. Re: Query Tuning:
                          user12090988
                          And the explain plan for the below query is:
                          SQL> explain plan for
                            2  SELECT COUNT (1)
                            3    FROM AFMS_RELT_FEEDBK
                            4   WHERE (CSE_CD || RELT_CSE_CD) IN
                            5              (SELECT CSE_CD || RELT_CSE_CD FROM FMS_RELT_FEEDBK);
                          
                          Explained.
                          
                          PLAN_TABLE_OUTPUT
                          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          Plan hash value: 133917811
                          
                          ----------------------------------------------------------------------------------------------
                          | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
                          ----------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT       |                     |     1 |    20 |    89M  (1)|298:04:47 |
                          |   1 |  SORT AGGREGATE        |                     |     1 |    20 |            |          |
                          |*  2 |   FILTER               |                     |       |       |            |          |
                          |   3 |    INDEX FAST FULL SCAN| AFMS_RELT_FEEDBK_PK |    44M|   851M| 62702   (1)| 00:12:33 |
                          |*  4 |    INDEX FAST FULL SCAN| FMS_RELT_FEEDBK_PK  |     1 |    27 |     2   (0)| 00:00:01 |
                          ----------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             2 - filter( EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK" WHERE
                                        "CSE_CD"||"RELT_CSE_CD"=:B1||:B2))
                             4 - filter("CSE_CD"||"RELT_CSE_CD"=:B1||:B2)
                          Would this query give me the same result or output as my original query?

                          Edited by: user12090988 on Feb 4, 2013 10:45 PM
                          • 10. Re: Query Tuning:
                            SomeoneElse
                            CSE_CD || RELT_CSE_CD
                            You don't want to use logic like this.

                            A || BC and AB || C will both result in ABC giving you a false match.
                            • 11. Re: Query Tuning:
                              riedelme
                              user12090988 wrote:
                              My oracle database is on 11.2.0.3
                              SELECT COUNT(1) 
                              FROM AFMS_RELT_FEEDBK
                              WHERE
                              (CSE_CD IN (SELECT CSE_CD FROM FMS_RELT_FEEDBK)) OR
                              (RELT_CSE_CD IN (SELECT RELT_CSE_CD FROM FMS_RELT_FEEDBK));
                              
                              
                              PLAN_TABLE_OUTPUT
                              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                              Plan hash value: 3722087114
                              
                              ----------------------------------------------------------------------------------------------
                              | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT       |                     |     1 |    20 |   175M  (1)|584:08:35 |
                              |   1 |  SORT AGGREGATE        |                     |     1 |    20 |            |          |
                              |*  2 |   FILTER               |                     |       |       |            |          |
                              |   3 |    INDEX FAST FULL SCAN| AFMS_RELT_FEEDBK_PK |    44M|   851M| 62751   (1)| 00:12:34 |
                              |*  4 |    INDEX RANGE SCAN    | FMS_RELT_FEEDBK_PK  |     2 |    28 |     4   (0)| 00:00:01 |
                              |*  5 |    INDEX FAST FULL SCAN| FMS_RELT_FEEDBK_PK  |     2 |    26 |   764   (1)| 00:00:10 |
                              ----------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                              2 - filter( EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK" WHERE
                              "CSE_CD"=:B1) OR  EXISTS (SELECT 0 FROM "FMS"."FMS_RELT_FEEDBK" "FMS_RELT_FEEDBK"
                              WHERE "RELT_CSE_CD"=:B2))
                              4 - access("CSE_CD"=:B1)
                              5 - filter("RELT_CSE_CD"=:B1)
                              
                              21 rows selected.
                              Some possiblities exits.

                              First, use the execution plan as a tool to examine possiblities. The metrics list - particularly cost and time but really all of them - are estimates and may not be accurate.

                              Second, the full index scans might be helped - if you have the licence - by running the SQL in parallel, wither with using the PARALLEL() hint or PARALLEL_INDEX() hints. Alternately you can set the degree for tables and indexes.
                              • 12. Re: Query Tuning:
                                chris227
                                Just brainstorming
                                SELECT
                                1 
                                FROM
                                 AFMS_RELT_FEEDBK afms
                                ,FMS_RELT_FEEDBK fms
                                where
                                fms.CSE_CD = afms.CSE_CD 
                                and rownum < 2
                                union
                                select
                                1
                                FROM
                                 AFMS_RELT_FEEDBK afms
                                ,FMS_RELT_FEEDBK fms
                                where
                                fms.RELT_CSE_CD = afms.RELT_CSE_CD
                                and rownum < 2
                                • 13. Re: Query Tuning:
                                  Nikolay Savvinov
                                  Hi,

                                  I suggest that rather than blindly "try" things you try and address the issue in a systematic manner.
                                  What is the root cause of the poor performance? Obviously, it's optimizer's inability to transform the IN
                                  subqueries into a semi-join. As a result, they are executed per each row in AFMS_RELT_FEEDBK, and given
                                  that the table has 44M rows (according to the optimizer estimates) the total cost comes up to a substantial
                                  number.

                                  The most likely reason why the optimizer fails to transform subqueries into a join is the OR. So I would recommend
                                  the following approach:

                                  1) Run the subqueries separately, i.e.
                                  SELECT COUNT(1) 
                                  FROM AFMS_RELT_FEEDBK
                                  WHERE
                                  (CSE_CD IN (SELECT CSE_CD FROM FMS_RELT_FEEDBK))
                                  and
                                  SELECT COUNT(1) 
                                  FROM AFMS_RELT_FEEDBK
                                  WHERE
                                  (RELT_CSE_CD IN (SELECT RELT_CSE_CD FROM FMS_RELT_FEEDBK))
                                  2) if the queries above run fast, then you can achieve the goal by transforming the
                                  original query manually using logical equivalency. E.g. "A or B" is equivalent to "not ((not A) and (not B))".

                                  Best regards,
                                  Nikolay
                                  • 14. Re: Query Tuning:
                                    chris227
                                    Nikolay Savvinov wrote:
                                    The most likely reason why the optimizer fails to transform subqueries into a join is the OR. So I would recommend
                                    the following approach:

                                    1) Run the subqueries separately, i.e.
                                    That was the intention of my post above, was it wrong way of thinking?
                                    1 2 Previous Next