execution plan changes while execute the same SQL second time
- From: "oracle sos" <sosoracle@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Dec 2006 01:00:20 -0500
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:
