-
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 08:53 AM.
-
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?
-
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.
-
Works better in PROD... like a fraction of the time.
-
Originally Posted by meerkats0505
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.
-
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
-
Originally Posted by LKBrwn_DBA
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.
Isn't that cheating????
-
Originally Posted by gandolf989
Isn't that cheating????
Not at all, it's what Oracle Support does to try duplicate an issue:
1) It will request the metadata for the schema and
2) Request the statistics and histograms.
In fact I do it for QA that has a 10% subset of PROD data in order to get expected execution plans regardless of hardware, etc...
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|