RE: execution plan changes while execute the same SQL second time
- From: "Justin Cave \(DDBC\)" <jcave@xxxxxxxxxxx>
- To: <sosoracle@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 4 Dec 2006 23:14:03 -0700
You can certainly use stored outlines to force a particular execution
plan. The Oracle documentation has a number of sections on stored
outlines, but there is a good introduction here
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/outl
ines.htm#13091
I'm a bit surprised that a job that does selects and inserts would have
had no I/O waits despite running for more than an hour. What else was
it waiting on? CPU? How did you find that disk I/O was getting worse
if there were no I/O related waits? That doesn't make sense.
If you cloned the database by doing a full backup and restore (and not
just a simple export/ import), your statistics should have been the same
on both systems. Were you using the same initialization parameters in
dev and in prod?
Justin Cave
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of oracle sos
Sent: Tuesday, December 05, 2006 1:00 AM
To: oracle-l
Subject: execution plan changes while execute the same SQL second time
Good morning
I had a batch job(select and insert) performance problem on our
9i(9.2.0.7, 32 bit) Oracle on HP 11.11 recently, due to the execution
plan changed and it took more than 12 hours to complete an 1 hour job
when execute the same batch job second time (next day different time).
No other serious processes were running at the same time the job
executed. Found disk I/O getting worse(Write) on the second execution.
I colond prod env to the dev env and run the same procedure, it took
only 1.5 hours, but using different executin plan compare to the
production. Stackpack report does not show any I/O wait while executing
the procedure on either prod or dev. When I coloned the database, I
expected the production statistic can move to dev environment, without
statistic export/import. Am I right?
Is there any way that I can force the job use the preferred execution
plan, so that we can get the consistent execution time each time the job
run? I also tried to generate a trace file while the job run on either
prod and dev, but can't find any root cause. Any other tuning
suggestions?
Truly thanks,
Cindy
Other related posts:
