Home » RDBMS Server » Server Administration » Defferent execution plan
| Defferent execution plan [message #56413] |
Thu, 27 March 2003 09:15  |
Kamlesh C. Patel
Messages: 1
Registered: March 2003
|
Junior Member |
|
|
Hi,
I have two enviornments, prodction and test on UNIX AIX 4.3 / Oracle 8.1.7.3. The query when explained have two differant execution plans. Both tables, indexes are identical except on two differant machines. All the database parameters are same and have not changed on any machines.
What should I look for and also how can I change the explain plan look the same. For a change query in production is running faster then test environement.
Here is the query is it helps to answer my question.
Thanks,
Kamlesh C. Patel
SELECT jb.emplid, jb.empl_rcd#, jb.effdt, jb.effseq, jb.paygroup, jb.location,
jb.empl_status, jb.elig_config7, jb."ACTION", jb.action_reason, jb.
full_part_time, jb.std_hours, jb.elig_config1, jb.jobcode, pd."NAME", l.
descr, pr.region_03, bp.benefit_program, SYSDATE
FROM ps_job jb, ps_personal_data pd, ps_location_tbl l, ps_ghv_pr_entities
pr, ps_ben_prog_partic bp
WHERE jb.effdt = (SELECT MAX(jb1.effdt)
FROM ps_job jb1
WHERE jb.emplid = jb1.emplid
AND jb.empl_rcd# = jb1.empl_rcd#
AND jb1.effdt <= SYSDATE)
AND jb.effseq = (SELECT MAX(jb2.effseq)
FROM ps_job jb2
WHERE jb.emplid = jb2.emplid
AND jb.empl_rcd# = jb2.empl_rcd#
AND jb.effdt = jb2.effdt)
AND pd.emplid = jb.emplid
AND l.location = jb.location
AND pr.acct_cd = jb.acct_cd
AND bp.emplid = jb.emplid
AND bp.empl_rcd# = jb.empl_rcd#
AND bp.effdt = (SELECT MAX(bp1.effdt)
FROM ps_ben_prog_partic bp1
WHERE bp1.emplid = bp.emplid
AND bp1.empl_rcd# = bp.empl_rcd#
AND bp1.cobra_event_id = bp.cobra_event_id
AND bp1.effdt <= jb.effdt)
AND l.effdt = (SELECT MAX(l1.effdt)
FROM ps_location_tbl l1
WHERE l.setid = l1.setid
AND l.location = l1.location
AND l1.effdt <= jb.effdt)
AND jb.empl_status IN ('A', 'L', 'P')
AND jb.full_part_time IN ('F')
AND NOT jb.elig_config1 IN ('01', '02', '03', '04', '05', '06', '07',
'08', '09', '10', '11', '12', '13', '14', '15', '15X', '16', '16X',
'17', '17X', '18', '18X', '19')
|
|
|
|
| Re: Defferent execution plan [message #56414 is a reply to message #56413] |
Thu, 27 March 2003 10:00  |
psmyth
Messages: 81
Registered: October 2002
|
Member |
|
|
when did you last 'analyze' the two databases? I presume you have configured the sql optimizer as cost based rather than rule based, so you need to make sure they index and table data that the optimizer uses to determine the explain plan is up to date.
select to_char(last_analyzed, 'dd-mon-yy') from dba_tables;
select to_char(last_analyzed, 'dd-mon-yy') from dba_indexes;
If the dates are way different between your production and test environments, then you should re-analyze to get them up-to-date.
Check metalink for details on 'analyze' for further info.
good luck
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 00:35:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
