| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Nested table TYPE, global temp table , and rollback segment
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9qmkgb0ac6_at_drn.newsguy.com>...
> 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
Hi again,
Thanks for your reply Tom. Afraid I couldn't ask the DBA whether he increased maxextents or physically allocated additional extents as he's not here right now. In the meantime, I've done my best to get some info about the RBS and extents myself.
SQL> select bytes, count(bytes)
2 from dba_free_space
3 where tablespace_name = 'RBS'
4 group by bytes;
BYTES COUNT(BYTES)
--------- ------------
2129920 92 302768128 1
SQL> select segment_name, initial_extent,
2 min_extents, max_extents, status
3 from sys.dba_rollback_segs;
SEGMENT_NAME INITIAL_EXTENT MIN_EXTENTS MAX_EXTENTSSTATUS
------------------------------ -------------- ----------- ----------- ---------------- SYSTEM 57344 2 505 ONLINE R01 2097152 2 121 ONLINE R02 2097152 2 121 ONLINE R03 2097152 2 121 ONLINE R04 2097152 2 121 ONLINE R05 2097152 2 121 OFFLINE R06 2097152 2 121OFFLINE 7 rows selected.
I also have this output from running a script you posted some time back (free.sql). Output:
Tablespace Nam KBytes Used Free %Used Largest -------------- ----------- ----------- ----------- ------ -----------
AUD_DAT_DCRM 1,048,576 307,208 741,368 29.3 741,368
AUD_IDX_DCRM 262,144 56,328 205,816 21.5 205,816
DRSYS 262,144 8,480 253,664 3.2 253,664
DYN_DAT_DCRM 2,072,576 542,736 1,529,840 26.2 1,013,752
DYN_IDX_DCRM 524,288 262,088 262,200 50.0 262,200
LOB_DAT_DCRM 4,106,240 102,408 4,003,832 2.5 4,003,832
RBS 512,000 29,128 482,872 5.7 295,672
REF_DAT_DCRM 65,536 10,928 54,608 16.7 54,608
REF_IDX_DCRM 65,536 10,928 54,608 16.7 54,608
SYSTEM 1,048,576 169,032 879,544 16.1 879,488
TEMP 262,144 93,608 168,536 35.7 2,080
TOOLS 2,097,152 288 2,096,864 .0 2,096,864
USERS 2,097,152 20,728 2,076,424 1.0 2,076,360
----------- ----------- -----------
sum 14,424,064 1,613,888 12,810,176
Apparently, when the DBA said 'there's not alot of activity in the RBS', he meant there wasn't anything in the log files to suggest there was!
You asked if we were fetching across commits. In fact, there aren't *any* commits in the procedures, primarily as they are inserting data into the temporary table, and a commit would delete the rows already inserted due to the "on commit delete rows".
I've run a little test this morning, which by-passes my new procedures. I ran the original report procs, which insert data into temp table, and then did an 'insert into my_copyoftemptable (select * from temptable)'. Of course, I got the rollback 1555.
With that in mind, it's confusing that the "execute immediate 'select cast(multiset('select on temp table') as myTYPE) from dual' into myTYPE_1", in the new procedures seems to be OK, and that the 1555 error message doesn't come until I'm halfway through reading *from* my variable myTYPE_1. I know this, because after populating myTYPE_1, I loop through values in another array (v2) and pass this v2 value, along with the myTYPE_1, into a function which returns me the appropriate value from myTYPE_1. It's this function which catches the rollback error.
Any thoughts on this?
Just to give you some kind of indication of volumes, the procedures are putting 2500 records into the temporary table (for my test report), and are taking nearly 3 minutes to do so, on account of alot of security checking.
Thanks again for your help
James
Received on Fri Oct 19 2001 - 05:16:39 CDT
 |
 |