Information on activity in Oracle's temporary segment under Oracle 7

Message is sent with MIME. Attachments are base64 encoded
--TFS-with-MIME-and-DIME

Content-Type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: quoted-printable

Hi there,

I am running 7=2E3=2E4=2E4, with a temporary tablespace (TEMP) of 500M, marked as type 'temporary'=2E I believe that this causes there to be a single temporary=20= segment in the
tablespace, shared by all users, which grows and shrinks according to demand=2E

I occasionally see ORA-1652 errors in the alert log, such as 'ORA-1652: unable to extend temp segment by 63 in tablespace TEMP'=2E If I catch these quickly enough, I can see the state of the temporary segment using the V$SORT_SEGMENT view, as shown in the below example;

TABLESPACE_NAME                 SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE=20=

• ------------- ------------ ------------ USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS=20= FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE
  ----------- ------------ ----------- ------------- ----------- -------------=20=
  
• ---------- ---------- ------------- MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS
  --------------- ------------- ---------------
  TEMP 3 62271 63 =20= 2 978 63569 16 1040 962 62529 978 3188 0 =20= 0 978 63569 978 63569 412 26780

This example was taken while the system was quiet, but shows that there were two users using the segment, and were using 14 extents (978 - 962)=2E What I cannot tell from this view is, a) who the two users are, and b) how many extents each is using=2E

Occasionally, someone will report experiencing the problem, we will ask them to repeat the action, and invariably, it will work the second time around=2E

What I suspect is happening when I see the ORA-1652 is that one user is running a large (and probably invalid) query which is taking up the lion's share of the temporary segment, and then some other 'innocent' user is running a genuine query which is requiring a sort, and failing because it is exhausting the remaining space in the segment=2E

In the absence of any visibility of what is going on in there, I don't know who I should go and take a big stick to!

Interestingly, there is a view provided in Oracle8 called V$SORT_USAGE which would do the job, but this doesn't help me on Oracle7=2E

I also attach a couple of documents I received from Oracle when I raised this as a TAR=2E The first (n40758) is, I think, not exactly pertinent to my situation, and the second talks about 'dumping a systemstate'=2E

 <<n40758=2Etxt>> <<n1069041=2Etxt>> What I would like is a) visibility of who=20= is doing what with regard to sorts
in the temporary segment, and b) some means of monitoring this usage and raising an alert when some condition is met (e=2Eg=2E user using more than nn extents, or something similar)=2E

Your mission, should you choose to accept it=2E=2E=2E=2E=2E=2E=2E!

TIA Mark

 =20=

--TFS-with-MIME-and-DIME

Content-Type: text/plain; name="N40758.TXT" Content-transfer-encoding: 7bit

<binary content removed -- do not send binaries to the list> Received on Wed May 17 2000 - 03:28:42 CDT