Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E60075D.592FCF7A_at_exesolutions.com...
> What I'd like to know is what is anyone doing that requires GTTs anyway.
I've
> been coding in Oracle for more than a decade and have yet to "need" one.

I see you've been jumped on already:( I have another addition.

We have a 3rd party app that is cross platform. Which in practice means it comes from a sql server background :(. Its standard method of doing complex and flexible business logic is to create and populate a series of tables throughout a batch process and at the end update the main tables with result values. These processes repeat many hundreds or thousands of time per year. So its your classic break the problem down into small chunks and work on it a chunk at a time (and the work done might be dependent upon system arameters for the app). The tables are dropped at the end of each run. In addition all SQL goes through an SQL parser specific for each rdbms

This system has DDL statements 7 times in its top 10 resource hungry sql statements :(

I suspect that this is not an uncommon position, and whilst ideally one would require the app to run on a particular platform (or support n versions of it), this isn't going to happen. It does seem to me that the vendor could usefully rewrite the system to use session based GTT's on Oracle where the GTT's have the same definition as the permanent transient objects it currently uses.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************