Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Query Taking too much time to complete DATA_PHYSICAL_READS is higher

    Hi
    I need some help my query is much long time approx 8 mins to complete,
    i checked and found that max time is being taken due to data_physical_read is too much
    can someone suggest to lower this

    select BP_NAME,DATA_PHYSICAL_READS, DATA_HIT_RATIO_PERCENT, INDEX_PHYSICAL_READS,INDEX_HIT_RATIO_PERCENT from SYSIBMADM.MON_BP_UTILIZATION

    BP_NAME DATA_PHYSICAL_READS DATA_HIT_RATIO_PERCENT INDEX_PHYSICAL_READS INDEX_HIT_RATIO_PERCENT
    IBMDEFAULTBP 6186475 99.96 3700 99.2

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Post some facts. Your DB2-version/fixpack and platform. Have you examined the access-plan for the query? Have you tried the query tuning features of db2advis or Data-Studio?

  3. #3
    Join Date
    Jun 2012
    Posts
    5

    environmnet details

    db2 is 10.5 with fixpack 2 on windows 2012 with 64 gb ram and 8 processor 2ghz
    same query is running with 30 seconds on UAT

  4. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    It is normal for queries to take different elapsed times on different environment. Compare the volumetrics. Act on the suggestions previously made regarding query tuning. And verify that the runstats are current and suitable for the workload.

  5. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    do you have an index on your test system that doesn't exist elsewhere? As Mor says you are not giving us any info to help you. You haven't said anything about his suggestions of running a simple explain, which should always be your first step. At least share the query and we can tell you if there is any glaring issues.

  6. #6
    Join Date
    Feb 2017
    Posts
    2
    posted under incorrect thread.
    Last edited by gkmagu; 02-22-17 at 18:56.

Posting Permissions

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