Union All in 10g

[AnaFlorTech]

Hello,

In 9i I had a procedure that opens a cursor with a select with union all inside. Then, the values of the cursor are inserted into a table and it gives a commit each 10000 rows.

After upgrading to 10g, the procedure that used to take about 1min to run, takes over an hour.

Could you please help me understanding what coult it be?

Thanks.

 

[carp]

Let's get the easy stuff out of the way first. Are all indexes enabled and statistics up to date? Also, you might want to check the optimizer mode being used. When our company upgraded, somebody set the optimizer wrong and performance tanked.

 

[AnaFlorTech]

Hi,

The indexes were rebuilded after the upgrade and all the statistics that were lost, are now up to date.

 

[Dagon]

I would suggest breaking it down by first running the select on its own to see whether it is that or the insert that is causing the slow speed.

Retired (not by choice) Oracle contractor.