7 Replies Latest reply: Jun 12, 2014 10:53 AM by Dom Brooks RSS

    Slow Query Performance

    933257

      I am using Release 11.2.0.3.0 of oracle.


      I have a sql query which is taking ~1hr 10 minutes for complete execution. Then i ran cardinality estimation for same and found that the most time consuming part of the query is the index_fast_full scan of index a_pk. So below is the portion of the query plan i have removed some part of the plan purposely. The index details is as below. Its unique b-tree index having BLEVEL as '2', DISTINCT_KEYS - 2591426, clustering_factor-1895980, num_rows-2591426

       

      table size A - 1.54 GB , total rows - 2604469
      table size B - 762 MB , total rows - 2614041
      index size A_PK - 97 MB

       

      So my question is, the plan looks fine with cardinality estimation, so is it expected the fast full scan of index to take ~1hr, or there is some other issue with same?

       

       

       

      ------------------------------- cardinality estimation -------------------
        
        
        ----------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
      ----------------------------------------------------------------------------------------------------------------------------------------------------------
      --------------------------------------- Purposely removed some portion -----------------------------
      
      |*  6 |       FILTER                                |              |      1 |        |   7981 |01:04:54.81 |      53M|   5115 |       |       |          |
      |   7 |        NESTED LOOPS                         |              |      1 |        |   7981 |00:00:02.62 |   38109 |    716 |       |       |          |
      |   8 |         NESTED LOOPS                        |              |      1 |   1194 |   7981 |00:00:01.30 |   30069 |    289 |       |       |          |
      |   9 |          NESTED LOOPS                       |              |      1 |   1194 |   7981 |00:00:00.60 |    1095 |    244 |       |       |          |
      |  10 |           FAST DUAL                         |              |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          |
      |* 11 |           TABLE ACCESS BY GLOBAL INDEX ROWID| A            |      1 |   1194 |   7981 |00:00:00.59 |    1095 |    244 |       |       |          |
      |* 12 |            INDEX RANGE SCAN                 | A_FK_05      |      1 |      1 |   7981 |00:00:00.11 |      27 |     27 |       |       |          |
      |  13 |          PARTITION REFERENCE ITERATOR       |              |   7981 |      1 |   7981 |00:00:00.65 |   28974 |     45 |       |       |          |
      |* 14 |           INDEX UNIQUE SCAN                 | B_PK         |   7981 |      1 |   7981 |00:00:00.36 |    5031 |     42 |       |       |          |
      |  15 |         TABLE ACCESS BY LOCAL INDEX ROWID   | B            |   7981 |      1 |   7981 |00:00:01.30 |    8040 |    427 |       |       |          |
      |* 16 |        FILTER                               |              |   7981 |        |      0 |00:00:00.02 |       0 |      0 |       |       |          |
      |* 17 |         INDEX UNIQUE SCAN                   | A_PK         |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
      |* 18 |        INDEX FAST FULL SCAN                 | A_PK         |   7981 |      2 |   7981 |01:04:51.97 |      53M|   4399 |       |       |          |
      ----------------------------------------------------------------------------------------------------------------------------------------------------------
      
       
       
      Now the part of the query is as below for which i am able to get similar plan and also its too taking same amount of time for completion.
      select *
      FROM  USER1.A 
             INNER JOIN USER1.B 
                ON A.C1 = b.c1
             INNER JOIN (select 158214002 C2, 3 scp_cd from dual ) gtt -- this is a global temp table so modified the query
                ON A.C2 = gtt.C2
                WHERE  A.C3= 4385002
            and  (EXISTS
                          (SELECT   1
             FROM USER1.A 
             WHERE A.c1 = b.c1
               OR (UPPER (b.C4) =
                '0083')
              ) 
               );
       
       
         
      ----------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ----------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                      |              |     1 |   910 |  3600   (1)| 00:00:19 |       |       |
      |*  1 |  FILTER                               |              |       |       |            |          |       |       |
      |   2 |   NESTED LOOPS                        |              |  1194 |  1061K|  2403   (1)| 00:00:13 |       |       |
      |   3 |    NESTED LOOPS                       |              |  1194 |   741K|    12   (0)| 00:00:01 |       |       |
      |   4 |     FAST DUAL                         |              |     1 |       |     2   (0)| 00:00:01 |       |       |
      |*  5 |     TABLE ACCESS BY GLOBAL INDEX ROWID| A            |  1194 |   741K|    10   (0)| 00:00:01 |     1 |     1 |
      |*  6 |      INDEX RANGE SCAN                 | A_FK_05      |     1 |       |     2   (0)| 00:00:01 |       |       |
      |   7 |    PARTITION REFERENCE ITERATOR       |              |     1 |   274 |     2   (0)| 00:00:01 |   KEY |   KEY |
      |   8 |     TABLE ACCESS BY LOCAL INDEX ROWID | B            |     1 |   274 |     2   (0)| 00:00:01 |   KEY |   KEY |
      |*  9 |      INDEX UNIQUE SCAN                | B_PK         |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
      |* 10 |   INDEX FAST FULL SCAN                | A_PK         |     2 |    16 |     2   (0)| 00:00:01 |       |       |
      ----------------------------------------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         1 - filter( EXISTS (SELECT 0 FROM "USER1"."A"  WHERE "A"."C1"=:B1 OR
                    UPPER(:B2)=U'0083'))
         5 - filter("A"."C3"=4385002)
         6 - access("A"."C2"=158214002)
         9 - access("A"."C1"="B"."C1")
        10 - filter("A"."C1"=:B1 OR UPPER(:B2)=U'0083')
      
      
      
        • 1. Re: Slow Query Performance - Index fast full Scan
          sybrand_b

          Apparently there is no index on A.C3.

          The only equality predicate on the driving table isn't indexed.

          As the table has a PK, Oracle will use a FFS on the PK.

          Otherwise you would have had FTS.

          Index C3

           

          ------------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Slow Query Performance - Index fast full Scan
            Dom Brooks

            It's hard to comment on just a portion of a query.

            It's much better to comment on a whole query and a complete execution plan with the predicates section (with object/columns names sanitised as necessary).

             

            SQL performance issues often manifest themselves in poorly expressed queries

             

             

            It's hard to tell whether commenting on just a reproduction of a part of the execution plan is even relevant.

            Certainly your query which you've written to reproduce part of the execution plan doesn't make efficient sense.

            You've already got an INNER JOIN between USER1.A and USER1.B on A.c1 = b.C1 so to then have an existence query on

             

            (EXISTS

                                (SELECT   1

                   FROM USER1.A 

                   WHERE A.c1 = b.c1

                     OR (UPPER (b.C4) =

                      '0083')

                    )

            doesn't make sense as this will always be true.

             

            Revisit the entire SQL statement causing the problem and review whether it is efficiently asking the question it is meant to be asking.

            Review any OR predicates, particularly in the subquery, and see if they might be expressed better, possibly in a more expanded fashion.

            • 3. Re: Slow Query Performance - Index fast full Scan
              Balazs Papp

              On top of the above: doing a single Index Fast Full Scan may or may not take 1 hour.

              But doing it 7981 times during a single execution (as indicated by the 'Starts' column) will definitely take longer.

              • 4. Re: Slow Query Performance - Index fast full Scan
                Martin Preiss

                if I am not missing something important then I would say the problem is the existence filter in step 1 of the explained plan (step 6 in the plan with rowsource statistics): your main query returns 7981 rows and for each of them a lookup is done in table A (or more precise in the index A_PK). Since the correlating OR predicate in step 10 (step 18 in the rowsource plan) does not allow an index range scan on an index for table A you get 53M LIOs - and this is taking some time.

                 

                I would suggest to avoid the part: OR (UPPER (b.C4) = '0083') - but I am not sure what exactly you are trying to achieve with this predicate.

                • 5. Re: Slow Query Performance - Index fast full Scan
                  Cherif bh

                  Hi ,

                   

                  Please try this one

                   

                  select *

                    FROM USER1.A A

                  INNER JOIN USER1.B B ON A.C1 = b.c1

                  INNER JOIN (select 158214002 C2, 3 scp_cd from dual) gtt -- this is a global temp table so modified the query 

                  ON A.C2 = gtt.C2

                  WHERE A.C3 = 4385002

                     and (

                     (UPPER(b.C4) = '0083')

                     OR

                     EXISTS (SELECT 1

                                    FROM USER1.A D

                                   WHERE D.c1 = b.c1

                                       )

                                       );

                  and send execution plan

                  • 6. Re: Slow Query Performance - Index fast full Scan
                    933257

                    1)Yes, there exists composite index on a(c3,c2).

                     

                    2)This sql is getting formed dynamically (in Java) by users when they introduce theri search criteria and thats why it looks bit odd. All the 'best cases' run within ~5 sec but the worst case is taking ~1hr+ which i mentioned above. and the best cases were having format of the query such that they are not doing index fast full scan of the index A_pk, best case doesnot have the EXIST part of the query. given below is the plan for best case


                    I am just trying to understand where exactly this much time spent on in the query, is it on index fast full scan only, or i am missing something?

                     

                     

                     

                     

                    --------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                    --------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                           |                   |       |       |    17 (100)|          |       |       |
                    |*  1 |  COUNT STOPKEY                             |                   |       |       |            |          |       |       |
                    |   2 |   VIEW                                     |                   |     1 |    13 |    17  (18)| 00:00:01 |       |       |
                    |*  3 |    SORT ORDER BY STOPKEY                   |                   |     1 |  2167 |    17  (18)| 00:00:01 |       |       |
                    |*  4 |     VIEW                                   |                   |     1 |  2167 |    16  (13)| 00:00:01 |       |       |
                    |*  5 |      WINDOW SORT PUSHED RANK               |                   |     1 |   419 |    16  (13)| 00:00:01 |       |       |
                    |   6 |       NESTED LOOPS                         |                   |       |       |            |          |       |       |
                    |   7 |        NESTED LOOPS                        |                   |     1 |   419 |    15   (7)| 00:00:01 |       |       |
                    |*  8 |         HASH JOIN                          |                   |     1 |   384 |    13   (8)| 00:00:01 |       |       |
                    |   9 |          PARTITION HASH SINGLE             |                   |     1 |   358 |    10   (0)| 00:00:01 |   KEY |   KEY |
                    |* 10 |           TABLE ACCESS BY LOCAL INDEX ROWID| A                 |     1 |   358 |    10   (0)| 00:00:01 |   KEY |   KEY |
                    |* 11 |            INDEX RANGE SCAN                | A_FK_05           |     7 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
                    |  12 |          TABLE ACCESS FULL                 | GTT               |   161 |  4186 |     2   (0)| 00:00:01 |       |       |
                    |  13 |         PARTITION REFERENCE ITERATOR       |                   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
                    |* 14 |          INDEX UNIQUE SCAN                 | B_PK              |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
                    |  15 |        TABLE ACCESS BY LOCAL INDEX ROWID   | B                 |     1 |    35 |     2   (0)| 00:00:01 |     1 |     1 |
                    --------------------------------------------------------------------------------------------------------------------------------
                    
                     
                     
                    
                    
                    • 7. Re: Slow Query Performance - Index fast full Scan
                      Dom Brooks

                      You've correctly identified the index fast full scan as being the source of the slowness, others having clearly stated that it's done many times as a filter operation.

                       

                      The query is badly written/generated.

                       

                      The AND (EXISTS ... OR (EXISTS)) does not make logical sense.

                      It will always evaluate to true in this example