Re: Tuning query

"damorgan" <damorgan_at_exesolutions.com> wrote in message news:3DEB8682.2F2299C1_at_exesolutions.com...
> Niall Litchfield wrote:

<snip>
> Love your column names. ;-)
>
> Daniel Morgan

Actually the column names do make sense. The really fun thing about the naming conventions for this product is that they obviously have a principle of avoiding abbreviations in identifiers wherever possible, except for the abbreviation shd, which is used to mean two different things depending on where in the object name it appears.

The other really fun thing is that of my top ten sql statements no fewer than 7 are data dictionary operations, for example in a single working day

Insert into
col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fi xedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetfo rm,spare1,spare2)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,d ecode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183 ,:8,.....

was executed no fewer than 108379 times, resulting in more than 280000 logical io's. If ever there was an illustration of the perils of sqlserver style temporary tables in an Oracle database I submit these stats. (700-1000 temporary tables being created and destroyed each working day, not to mention some of them being modified).

--
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

******************************************