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?

Threaded 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.

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