Nested table TYPE, global temp table , and rollback segment

Hi,

Can anyone help please?.....I've got a problem with ever popular 1555 error "snapshot too old". I figure it's a problem with read-consistency for a query which is taking a long time to execute, but I'm not sure how to fix it.

The scenario.....we're running on version 8.1.6.3.0 on Solaris, using iAS to serve up webpages. Some procs already existed which were putting report data into a global temporary table (on commit delete rows) as User selects given report. The problems came when we had to build on top of these procs, still using the original procs to populate the temp table, but now parsing a dynamic select on the temp table for some new reports.

Seeing as I had to parse the SQL string anyway, I decided to use SQL TYPES (nested table of object types which are replicas of fields in temp table) so that I insert the data directly into an type variable in one parse: "execute immediate 'select cast(multiset('select on temp table') as myTYPE) from dual' into myTYPE_1".

All was working fine, and it was almost as quick as the original reports. But, we're now dealing with much more data, and many more Users in the system. When a large report is selected, the query takes a long time to execute, and we're getting the 1555 error.

We've already increased the number of extents in the rollback segment, and checked there aren't lots of commits being executed in the relevant procs, but to no avail. The DBA has looked at the log files and says there isn't actually alot of activity in the RBS.

I suspect it's something to do with the commits on the temporary table freeing up the extents being used for read consistency, but then, if that's the case, why don't the original reports have the same problem? I'm not sure whether the insert into the type variable would be using extents for read consistency. If it is, then it may be that these are being overwritten by commit on the temp table?

Can anyone see a blindingly simple explanation?

TIA
James Received on Thu Oct 18 2001 - 04:57:07 CDT