RE: execution plan changes while execute the same SQL second time

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l