This discussion is archived
13 Replies Latest reply: Mar 17, 2013 10:34 AM by Shaps RSS

Query Performance

Shaps Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    do the displayed ROW count accurately reflect reality?
  • 8. Re: Query Performance
    Shaps Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    Got the row count now more then 1.5 billion records

    Cheers,
    Shaz
  • 12. Re: Query Performance
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points