13 Replies Latest reply: Mar 17, 2013 12:34 PM by Shaps RSS

    Query Performance

    Shaps
      Hi Guys,

      I have the below query:
      SELECT   NVL (CONCAT ('', pros_od_flight_dimensions.flight_numb),
                    pros_od_flight_dimensions.flight_sequence
                   ),
               pros_od_flight_dimensions.onl_origin,
               pros_od_flight_dimensions.onl_destination,
               common_dimensions_dept_day.day_date,
               DECODE (common_dimensions.countries.iata_code,
                       'ZZ', '**',
                       common_dimensions.countries.iata_code
                      ),
               common_dimensions.CLASS.class_name,
               SUM (pros_od_availability_fact.seat_availability),
               SUM (pros_od_availability_fact.bid_price),
               SUM (pros_od_availability_fact.fare),
               common_dimensions.cabin.cabin_name,
               pros_od_reporting_capture_date.days_prior
          FROM pros_od_flight_dimensions,
               common_dimensions.DAY common_dimensions_dept_day,
               common_dimensions.countries,
               common_dimensions.CLASS,
               pros_od_availability_fact,
               common_dimensions.cabin,
               pros_od_reporting_capture_date
         WHERE (common_dimensions.cabin.cabin_id =
                                                  pros_od_availability_fact.cabin_id
               )
           AND (common_dimensions.CLASS.class_id =
                                                  pros_od_availability_fact.class_id
               )
           AND (common_dimensions.countries.country_id =
                   DECODE (pros_od_availability_fact.pos_id,
                           -1, 1,
                           pros_od_availability_fact.pos_id
                          )
               )
           AND (pros_od_reporting_capture_date.capture_date_id =
                                           pros_od_availability_fact.capture_date_id
               )
           AND (common_dimensions_dept_day.day_id =
                                         pros_od_availability_fact.departure_date_id
               )
           AND (pros_od_availability_fact.pros_flight_id =
                                            pros_od_flight_dimensions.pros_flight_id
               )
           AND (    NVL (CONCAT ('', pros_od_flight_dimensions.flight_numb),
                         pros_od_flight_dimensions.flight_sequence
                        ) IN (3, 4)
                AND common_dimensions_dept_day.day_date BETWEEN '01-FEB-2013'
                                                            AND '01-FEB-2013'
                AND common_dimensions.CLASS.class_name IN ('J', 'W', 'Y')
                AND (    pros_od_reporting_capture_date.days_prior IN
                                                                 (5, 4, 3, 2, 1, 0)
                     AND pros_od_reporting_capture_date.departure_date =
                                              (common_dimensions_dept_day.day_date
                                              )
                    )
                AND DECODE (common_dimensions.countries.iata_code,
                            'ZZ', '**',
                            common_dimensions.countries.iata_code
                           ) IN ('GB')
               )
      GROUP BY NVL (CONCAT ('', pros_od_flight_dimensions.flight_numb),
                    pros_od_flight_dimensions.flight_sequence
                   ),
               pros_od_flight_dimensions.onl_origin,
               pros_od_flight_dimensions.onl_destination,
               common_dimensions_dept_day.day_date,
               DECODE (common_dimensions.countries.iata_code,
                       'ZZ', '**',
                       common_dimensions.countries.iata_code
                      ),
               common_dimensions.CLASS.class_name,
               common_dimensions.cabin.cabin_name,
               pros_od_reporting_capture_date.days_prior
      Explain Plan as below:
      SELECT STATEMENT  CHOOSECost: 64  Bytes: 249  Cardinality: 1                                                    
           21 SORT GROUP BY  Cost: 64  Bytes: 249  Cardinality: 1                                               
                20 NESTED LOOPS  Cost: 63  Bytes: 249  Cardinality: 1                                          
                     17 HASH JOIN  Cost: 62  Bytes: 244  Cardinality: 1                                     
                          15 HASH JOIN  Cost: 59  Bytes: 229  Cardinality: 1                                
                               13 NESTED LOOPS  Cost: 56  Bytes: 214  Cardinality: 1                           
                                    10 NESTED LOOPS  Cost: 55  Bytes: 166  Cardinality: 1                      
                                         6 NESTED LOOPS  Cost: 5  Bytes: 49  Cardinality: 1                 
                                              2 TABLE ACCESS BY INDEX ROWID COMMON_DIMENSIONS.DAY Cost: 3  Bytes: 14  Cardinality: 1            
                                                   1 INDEX RANGE SCAN NON-UNIQUE COMMON_DIMENSIONS.IDX_DAY_DATE Cost: 2  Cardinality: 1       
                                              5 INLIST ITERATOR            
                                                   4 TABLE ACCESS BY INDEX ROWID RM_MART.PROS_OD_REPORTING_CAPTURE_DATE Cost: 3  Bytes: 35  Cardinality: 1       
                                                        3 INDEX RANGE SCAN UNIQUE RM_MART.PROS_OD_REPORTING_CPT_ID_PK Cost: 2  Cardinality: 1  
                                         9 PARTITION LIST ITERATOR  Partition #: 13                 
                                              8 TABLE ACCESS BY LOCAL INDEX ROWID RM_MART.PROS_OD_AVAILABILITY_FACT Cost: 51  Bytes: 117  Cardinality: 1  Partition #: 13            
                                                   7 INDEX RANGE SCAN NON-UNIQUE RM_MART.PROS_OD_AVAILABILITY_FACT_IDX Cost: 50  Cardinality: 1  Partition #: 13       
                                    12 TABLE ACCESS BY INDEX ROWID RM_MART.PROS_OD_FLIGHT_DIMENSIONS Cost: 2  Bytes: 48  Cardinality: 1                      
                                         11 INDEX UNIQUE SCAN UNIQUE RM_MART.SYS_C00382289 Cost: 1  Cardinality: 1                 
                               14 TABLE ACCESS FULL COMMON_DIMENSIONS.CLASS Cost: 3  Bytes: 30  Cardinality: 2                           
                          16 TABLE ACCESS FULL COMMON_DIMENSIONS.CABIN Cost: 3  Bytes: 2,460  Cardinality: 164                                
                     19 TABLE ACCESS BY INDEX ROWID COMMON_DIMENSIONS.COUNTRIES Cost: 2  Bytes: 5  Cardinality: 1                                     
                          18 INDEX UNIQUE SCAN UNIQUE COMMON_DIMENSIONS.CNTRY_ID_IDX Cost: 1  Cardinality: 1 
      When we have one of two days prior parameters the query runs ok but if we increase the days prior in the query the performace detoriates and it take ages to complete.

      Size of PROS_OD_AVAILABILITY_FACT is 68 GB and below is the indexing details on the table:
      ALTER TABLE RM_MART.PROS_OD_FLIGHT_DIMENSIONS
       DROP PRIMARY KEY CASCADE;
      DROP TABLE RM_MART.PROS_OD_FLIGHT_DIMENSIONS CASCADE CONSTRAINTS;
      
      CREATE TABLE RM_MART.PROS_OD_FLIGHT_DIMENSIONS
      (
        PROS_FLIGHT_ID    NUMBER,
        FLIGHT_SEQUENCE   VARCHAR2(25 BYTE)           NOT NULL,
        ONL_ORIGIN        VARCHAR2(4 BYTE)            NOT NULL,
        ONL_DESTINATION   VARCHAR2(4 BYTE)            NOT NULL,
        TRIP_ORIGIN       VARCHAR2(4 BYTE),
        TRIP_DESTINATION  VARCHAR2(4 BYTE),
        FLIGHT_NUMB       NUMBER
      )
      TABLESPACE AVAILABILITY_DATA
      PCTUSED    40
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  FREELISTS        1
                  FREELIST GROUPS  1
                  BUFFER_POOL      DEFAULT
                 )
      LOGGING 
      NOCOMPRESS 
      NOCACHE
      NOPARALLEL
      NOMONITORING;
      
      
      CREATE INDEX RM_MART.PROS_OD_FLT_SEQ_IDX ON RM_MART.PROS_OD_FLIGHT_DIMENSIONS
      (FLIGHT_SEQUENCE)
      LOGGING
      TABLESPACE AVAILABILITY_IDX
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  FREELISTS        1
                  FREELIST GROUPS  1
                  BUFFER_POOL      DEFAULT
                 )
      NOPARALLEL;
      
      
      CREATE INDEX RM_MART.PROS_OD_ONL_ORG_IDX ON RM_MART.PROS_OD_FLIGHT_DIMENSIONS
      (ONL_ORIGIN)
      LOGGING
      TABLESPACE AVAILABILITY_IDX
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  FREELISTS        1
                  FREELIST GROUPS  1
                  BUFFER_POOL      DEFAULT
                 )
      NOPARALLEL;
      
      
      CREATE INDEX RM_MART.PROS_OD_ONL_DEST_IDX ON RM_MART.PROS_OD_FLIGHT_DIMENSIONS
      (ONL_DESTINATION)
      LOGGING
      TABLESPACE AVAILABILITY_IDX
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  FREELISTS        1
                  FREELIST GROUPS  1
                  BUFFER_POOL      DEFAULT
                 )
      NOPARALLEL;
      
      
      ALTER TABLE RM_MART.PROS_OD_FLIGHT_DIMENSIONS ADD (
        PRIMARY KEY
       (PROS_FLIGHT_ID)
          USING INDEX 
          TABLESPACE AVAILABILITY_DATA
          PCTFREE    10
          INITRANS   2
          MAXTRANS   255
          STORAGE    (
                      INITIAL          64K
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      FREELISTS        1
                      FREELIST GROUPS  1
                     ));
      Please can you guys suggest of any improvements that we can implement.

      Cheers,
      Shaz
        • 1. Re: Query Performance
          696547
          Hi,

          Could you please further format Explain plan (like this sample) to make it more readable?
          Sample Explain Plan
          
          -----------------------------------------------------------------------------------------
          | Id  | Operation                          | Name               | Rows  | Pstart| Pstop |
          -----------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                   |                    |  1348 |       |       |
          |*  1 |  FILTER                            |                    |       |       |       |
          |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| EF_ACTL_REV        |    95 |       |       |
          |   3 |    NESTED LOOPS                    |                    |  1348 |       |       |
          |*  4 |     HASH JOIN                      |                    |    14 |       |       |
          |   5 |      MAT_VIEW ACCESS BY INDEX ROWID| PD_PROJ            |     1 |       |       |
          |*  6 |       INDEX RANGE SCAN             | PD_PROJ_PROJNUM_IX |     1 |       |       |
          |   7 |      INDEX FAST FULL SCAN          | ED_PRJMAP_PK       |   237K|       |       |
          |   8 |     PARTITION LIST ITERATOR        |                    | 37867 |   KEY |   KEY |
          |*  9 |      INDEX RANGE SCAN              | EF_AREV_PRJMAP_IX  | 37867 |   KEY |   KEY |
          -----------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
             2 - filter("AREV"."EFF_CMTH_SK">=TO_NUMBER(:X) AND "AREV"."EFF_CMTH_SK"<=TO_NUMBER(:Y))
             4 - access("PRJMAP"."PROJ_SK"="B"."PROJ_SK")
             6 - access("B"."PROJ_NUM"=:B)
             9 - access("AREV"."PRJMAP_SK"="PRJMAP"."PRJMAP_SK")
          Thanks,
          Ankit Rathi
          • 2. Re: Query Performance
            Shaps
            Hi,

            Don't have access to DB server so will not be able to pull out the format requested.

            Cheers,
            Shaz
            • 3. Re: Query Performance
              sb92075
              Sh**** wrote:
              Hi,

              Don't have access to DB server so will not be able to pull out the format requested.

              Cheers,
              Shaz
              Without access to DB, then you don't require any posted solution since you can't apply it.
              • 4. Re: Query Performance
                Shaps
                Hi,

                I am using Toad and have privledges to alter structure and add new objects in the database. Using Toad the explain planI got was in the given mentioned. If using Toad we can extract explain plan in the requested format please let me know and can post the same. What I saw on internet this will require access to DB server which I don't have.

                Cheers,
                Shaz
                • 5. Re: Query Performance
                  SomeoneElse
                  You don't need any special access to the server. I don't use Toad, but here's how it works with SQL Plus:
                  SQL> explain plan for
                    2  select *
                    3  from employees
                    4  where manager_id = 100;
                  
                  Explained.
                  
                  SQL> select * from table(dbms_xplan.display());
                  
                  PLAN_TABLE_OUTPUT
                  ----------------------------------------------------------------------------------------------
                  Plan hash value: 2843387772
                  
                  ----------------------------------------------------------------------------------------------
                  | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                  ----------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT            |                |    14 |   966 |     3   (0)| 00:00:01 |
                  |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |    14 |   966 |     3   (0)| 00:00:01 |
                  |*  2 |   INDEX RANGE SCAN          | EMP_MANAGER_IX |    14 |       |     1   (0)| 00:00:01 |
                  ----------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     2 - access("MANAGER_ID"=100)
                  • 6. Re: Query Performance
                    Shaps
                    Hi Guys,

                    Thanks a lot for your help. Here you go with the details:
                    PLAN_TABLE_OUTPUT
                     
                    ---------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                                |  Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
                    ---------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                         |                                 |     1 |   249 |    64   (7)|       |       |
                    |   1 |  SORT GROUP BY                           |                                 |     1 |   249 |    64   (7)|       |       |
                    |   2 |   NESTED LOOPS                           |                                 |     1 |   249 |    63   (5)|       |       |
                    |   3 |    HASH JOIN                             |                                 |     1 |   244 |    62   (5)|       |       |
                    |   4 |     HASH JOIN                            |                                 |     1 |   229 |    59   (4)|       |       |
                    |   5 |      NESTED LOOPS                        |                                 |     1 |   214 |    56   (2)|       |       |
                    |   6 |       NESTED LOOPS                       |                                 |     1 |   166 |    55   (2)|       |       |
                    |   7 |        NESTED LOOPS                      |                                 |     1 |    49 |     5  (20)|       |       |
                    |   8 |         TABLE ACCESS BY INDEX ROWID      | DAY                             |     1 |    14 |     3  (34)|       |       |
                    |   9 |          INDEX RANGE SCAN                | IDX_DAY_DATE                    |     1 |       |     2  (50)|       |       |
                    |  10 |         INLIST ITERATOR                  |                                 |       |       |            |       |       |
                    |  11 |          TABLE ACCESS BY INDEX ROWID     | PROS_OD_REPORTING_CAPTURE_DATE  |     1 |    35 |     3  (34)|       |       |
                    |  12 |           INDEX RANGE SCAN               | PROS_OD_REPORTING_CPT_ID_PK     |     1 |       |     2  (50)|       |       |
                    |  13 |        PARTITION LIST ITERATOR           |                                 |       |       |            |   KEY |   KEY |
                    |  14 |         TABLE ACCESS BY LOCAL INDEX ROWID| PROS_OD_AVAILABILITY_FACT       |     1 |   117 |    51   (2)|   KEY |   KEY |
                    |  15 |          INDEX RANGE SCAN                | PROS_OD_AVAILABILITY_FACT_IDX   |     1 |       |    50   (2)|   KEY |   KEY |
                    |  16 |       TABLE ACCESS BY INDEX ROWID        | PROS_OD_FLIGHT_DIMENSIONS       |     1 |    48 |     2  (50)|       |       |
                    |  17 |        INDEX UNIQUE SCAN                 | SYS_C00382289                   |     1 |       |            |       |       |
                    |  18 |      TABLE ACCESS FULL                   | CLASS                           |     2 |    30 |     3  (34)|       |       |
                    |  19 |     TABLE ACCESS FULL                    | CABIN                           |   164 |  2460 |     3  (34)|       |       |
                    |  20 |    TABLE ACCESS BY INDEX ROWID           | COUNTRIES                       |     1 |     5 |     2  (50)|       |       |
                    |  21 |     INDEX UNIQUE SCAN                    | CNTRY_ID_IDX                    |     1 |       |            |       |       |
                    ---------------------------------------------------------------------------------------------------------------------------------
                    Cheers,
                    Shaz
                    • 7. Re: Query Performance
                      sb92075
                      do the displayed ROW count accurately reflect reality?
                      • 8. Re: Query Performance
                        Shaps
                        No it does not!!

                        After posting this I observed the output and triggered query for number of rows and it is still running. You can imagine the size of the Fact table has grown to *68 GB* :(

                        Cheers,
                        Shaz
                        • 9. Re: Query Performance
                          sb92075
                          Sh**** wrote:
                          No it does not!!

                          After posting this I observed the output and triggered query for number of rows and it is still running. You can imagine the size of the Fact table has grown to *68 GB* :(

                          Cheers,
                          Shaz
                          consider collecting statistics for all tables & indexes accessed by the SQL
                          • 10. Re: Query Performance
                            Shaps
                            That we do after every job run (i.e. daily). We have implemented this login within the BODI Job itself.

                            Thanks,
                            Shaz

                            Edited by: Sh**** on Mar 17, 2013 10:51 PM
                            • 11. Re: Query Performance
                              Shaps
                              Hi,

                              Got the row count now more then 1.5 billion records

                              Cheers,
                              Shaz
                              • 12. Re: Query Performance
                                sb92075
                                Sh**** wrote:
                                That we do after every job run (i.e. daily). We have implemented this login within the BODI Job itself.
                                Why are statistics incorrect NOW?

                                What percentage of rows change daily?

                                When statistics do not reflect reality then CBO often makes bad choices which result in slow SQL.
                                • 13. Re: Query Performance
                                  Shaps
                                  Let me try and generate the explain plan after job run today and I will share with you the details.

                                  Thats correct if fact table and index are getting analysed everyday why are stats wrong!! Thanks for you help.. :)

                                  Cheers,
                                  Shaz