Results 1 to 5 of 5

Thread: Query Tuning

  1. #1
    Join Date
    Oct 2008
    Posts
    82

    Unanswered: Query Tuning

    Hello,

    DB2 9.7, Red hat Linux,

    Can anyone please suggest me better idea for below query.

    I wrote below query and it taking 4 hours time because all tables are huge.It's a single partition.

    select drc.MARKET_ID,dc.CHANNEL_CODE,ps.PMS_PERSON_ID ,fsh.RESERVATION_ID,fsh.CHANNEL_ID , re.FIRST_name , cg.FIRST_name
    from FCT_STAY_HDR fsh
    join PMS_STAY ps ON fsh.PMS_STAY_ID = ps.PMS_STAY_ID and fsh.STAY_DATE = ps.arrival_date
    left join DIM_CHANNEL dc on fsh.CHANNEL_ID = dc.CHANNEL_ID
    left join DIM_RATE_PLAN drp on fsh.rate_plan_id = drp.rate_plan_id
    left join DIM_RATE_CATEGORY drc on drp.RATE_CATEGORY_ID = drc.RATE_CATEGORY_ID
    left join RESERVATION re on fsh.RESERVATION_ID = re.RESERVATION_ID
    left jOIN conf_guest_profile cg ON cg.src_sys_id = ps.pms_person_id
    where ps.arrival_date = '7/1/2014' and fsh.RESERVATION_STATUS_ID = 1
    with ur

    db2advise is suggesting below indexes but is there any way to imporve performance without creating index

    db2advise :


    Trying variations of the solution set.
    7 indexes in current solution
    [2226061.0000] timerons (without recommendations)
    [2171.0000] timerons (with current solution)
    [99.90%] improvement


    --
    --
    -- LIST OF RECOMMENDED INDEXES
    -- ===========================
    -- index[1], 2431.833MB
    CREATE INDEX "DB2IPDW1"."IDX1407210753420" ON "EDW "."PMS_STAY"
    ("ARRIVAL_DATE" ASC, "PMS_PERSON_ID" ASC, "PMS_STAY_ID"
    ASC) NOT PARTITIONED ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[2], 30.478MB
    CREATE UNIQUE INDEX "DB2IPDW1"."IDX1407210754190"
    ON "EDW "."DIM_RATE_PLAN" ("RATE_PLAN_ID" ASC)
    INCLUDE ("RATE_CATEGORY_ID") ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[3], 5882.294MB
    CREATE INDEX "DB2IPDW1"."IDX1407210752320" ON "EDW "."FCT_STAY_HDR"
    ("STAY_DATE" ASC, "RESERVATION_STATUS_ID" ASC, "CHANNEL_ID"
    ASC, "RESERVATION_ID" ASC, "RATE_PLAN_ID" ASC, "PMS_STAY_ID"
    ASC) NOT PARTITIONED ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[4], 16.185MB
    CREATE UNIQUE INDEX "DB2IPDW1"."IDX1407210754240"
    ON "EDW "."DIM_RATE_CATEGORY" ("RATE_CATEGORY_ID"
    ASC) INCLUDE ("MARKET_ID") ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[5], 0.118MB
    CREATE UNIQUE INDEX "DB2IPDW1"."IDX1407210754140"
    ON "EDW "."DIM_CHANNEL" ("CHANNEL_ID" ASC) INCLUDE
    ("CHANNEL_CODE") ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[6], 1718.032MB
    CREATE UNIQUE INDEX "DB2IPDW1"."IDX1407210754290"
    ON "EDW "."RESERVATION" ("RESERVATION_ID" ASC)
    INCLUDE ("FIRST_NAME") NOT PARTITIONED ALLOW REVERSE
    SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;
    -- index[7], 1959.521MB
    CREATE INDEX "DB2IPDW1"."IDX1407210754340" ON "EDW "."CONF_GUEST_PROFILE"
    ("SRC_SYS_ID" ASC, "FIRST_NAME" ASC) ALLOW REVERSE
    SCANS COLLECT SAMPLED DETAILED STATISTICS;
    COMMIT WORK ;


    --

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as no details about tables/existing indexes/data/distribution
    no hints can be given at this point
    have indexes on join condition and on a predicate
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2008
    Posts
    82
    Yes there are indexes on join condition

    Please find the attached explain plan and let me know if you need any more information.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    and you think, that with just submitting the explain plan we will resolve your problem (for free) ...
    the sun goes up for free..(sometimes)
    we can give any hints and tips if you do the job - supply ddl / info / details....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    That [99.90%] improvement suggests me that you cannot obtain anything big without index creation.
    However in the explain plan there are only 2 or 3 operations that are raising the cost. Focus on those one.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •