explain plans... when running in prod and dev why am I getting different results?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: explain plans... when running in prod and dev why am I getting different results?

Hybrid View

  1. #1
    Join Date
    Apr 2014
    Posts
    5

    explain plans... when running in prod and dev why am I getting different results?

    Hello...... I ran an explain plan on the same statement in both PROD and DEV here are the results… why are they different? Thank you in advance!


    Code:
    DEV:
     
    EXPLAIN PLAN SET STATEMENT_ID = 'LRH' FOR SELECT A.EMPLID FROM PS_JOB A
    WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID)
    AND A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EFFDT = A.EFFDT);
     
     
      1  select lpad(' ',2*LEVEL)||operation||' '||options||' '||object_name execution_path
      2  from plan_table
      3  where statement_id = 'LRH'
      4* connect by prior ID = parent_id and statement_id = 'LRH' start with ID = 1
    DEV>/
     
    EXECUTION_PATH
    -----------------------------------------------------------------------------------------------------------------------------
      MERGE JOIN
        MERGE JOIN
          SORT JOIN
            VIEW  VW_SQ_2
              SORT GROUP BY
                INDEX FULL SCAN PSHJOB
          SORT JOIN
            INDEX FAST FULL SCAN PSHJOB
        SORT JOIN
          VIEW  VW_SQ_1
            SORT GROUP BY
              INDEX FAST FULL SCAN PSHJOB
     
    12 rows selected.

    Code:
    PROD:
     
    EXPLAIN PLAN SET STATEMENT_ID = 'LRH' FOR SELECT A.EMPLID FROM PS_JOB A
    WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID)
    AND A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EFFDT = A.EFFDT);
     
    PROD>select lpad(' ',2*LEVEL)||operation||' '||options||' '||object_name execution_path
      2  from plan_table
      3  where statement_id = 'LRH'
      4  connect by prior ID = parent_id and statement_id = 'LRH' start with ID = 1;
     
    EXECUTION_PATH
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
      FILTER
        MERGE JOIN
          SORT JOIN
            INDEX FAST FULL SCAN PSHJOB
          SORT JOIN
            VIEW  VW_SQ_1
              SORT GROUP BY
                INDEX FAST FULL SCAN PSHJOB
        SORT AGGREGATE
          FIRST ROW
            INDEX RANGE SCAN (MIN/MAX) PSHJOB
        MERGE JOIN
          SORT JOIN
            INDEX FAST FULL SCAN PSHJOB
          SORT JOIN
            VIEW  VW_SQ_1
              SORT GROUP BY
                INDEX FAST FULL SCAN PSHJOB
        SORT AGGREGATE
          FIRST ROW
            INDEX RANGE SCAN (MIN/MAX) PSHJOB
     
    21 rows selected.
     
    PROD>
    Last edited by gandolf989; 04-11-2014 at 09:53 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    I formatted your query to make it more readable. To the underlying tables have the same indexes?
    Do you generate stats in the same way on both tables? Do they have a similar amount of data?
    Are they they same version of Oracle. There are many factors that would affect the explain plans.
    It would help to see what the differences are to know why they are different.

    Does this work better in dev or prod?
    this space intentionally left blank

  3. #3
    Join Date
    Apr 2014
    Posts
    5
    Please see below stats to compare both environments:

    DEV:

    Health Check Start Time (1)
    4/11/2014 11:40:11 AM
    Performance (11)
    Basic Info (8)
    Time to Connect : 2 seconds
    Database Version : 9.2.0.1.0
    Database Up Since : 12:02:15 PM, February 10 2014
    Statistics Level : TYPICAL
    Undo Management : AUTO
    Buffer Cache Hit Ratio : 24.8
    Library Cache Miss Ratio : 0.0072
    Dictionary Cache Miss Ratio : 1.7054
    Shared Pool Usage (4)
    Total MB Unused : 299.55
    Total MB Used : 180.45
    Total MB : 480
    Shared Pool Percent Used : 37.59
    Archive Log Mode Info (1, 1)
    Log Mode : NOARCHIVELOG
    log_archive_start (init.ora param) = TRUE
    Archive Log Info (2)
    Average Log Switches Per Day : 14
    Hard Drive Storage (in MB) for this many archive logs : 1400
    Datafile I/O Distribution (132, 12)
    Excessive reads from SYSTEM could mean that the SGA is too small.
    Try to keep I/O evenly distributed across hard drives.




    PROD:

    Health Check Start Time (1)
    4/11/2014 12:06:03 PM
    Performance (11)
    Basic Info (8)
    Time to Connect : 2 seconds
    Database Version : 9.2.0.2.1
    Database Up Since : 02:30:03 AM, April 5 2014
    Statistics Level : TYPICAL
    Undo Management : AUTO
    Buffer Cache Hit Ratio : 80.38
    Library Cache Miss Ratio : 0.036
    Dictionary Cache Miss Ratio : 2.767
    Shared Pool Usage (4)
    Total MB Unused : 37.78
    Total MB Used : 442.22
    Total MB : 480
    Shared Pool Percent Used : 92.13
    Archive Log Mode Info (2)
    Log Mode : ARCHIVELOG
    log_archive_start (init.ora param) = TRUE
    Archive Log Info (2)
    Average Log Switches Per Day : 63.62
    Hard Drive Storage (in MB) for this many archive logs : 12724.5
    Datafile I/O Distribution (124, 23)
    Excessive reads from SYSTEM could mean that the SGA is too small.
    Try to keep I/O evenly distributed across hard drives.

  4. #4
    Join Date
    Apr 2014
    Posts
    5
    Works better in PROD... like a fraction of the time.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Quote Originally Posted by meerkats0505 View Post
    Works better in PROD... like a fraction of the time.
    Do you have table partitioning in prod and not dev? How manu cores do you have in prod compared with dev?
    There may be other changes that would affect the query performance. I'm guessing that you have more memory
    in prod, which will limit the physical reads somewhat. You probably stripe you data across more disks as well.
    this space intentionally left blank

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    Once you verify what gandolf989 suggest, and everything matches, you could get same explain plan by copying all statistics and histograms from production to development.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Feb 2014
    Posts
    22
    Hi,

    Is it that your optimizer parameters in PROD are the same as in DEV?

Tags for this Thread

Posting Permissions

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


Click Here to Expand Forum to Full Width