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>