Re: Nested table TYPE, global temp table , and rollback segment

In article <ff68d4e6.0110180157.3b6bff45_at_posting.google.com>, jlale.cs_at_clearstream.com says...
>
>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,

did you increase maxextents or did you PHYSICALLY allocate additional extents.

Only modifications cause a rbs to grow. A query will not. In order to avoid the 1555 you need to ensure your rbs are physically, permanently allocated to be big enough NOT to wrap during the execution of your longest running query.

how big are your rbs's -- permanently big? What is their extent size and what is their MINEXTENTS? That dicates the probability if your 1555 error.

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

how are they deciding that from log files? What method are they using?

>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

if you commit and keep cursors open -- you are asking for trouble. Are you fetching across commits?

>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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp