| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Information on activity in Oracle's temporary segment under O
Ditto :)
-----Original Message-----
From: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca]
Sent: Thursday, May 18, 2000 1:33 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Information on activity in Oracle's temporary segment =
under
O
Remco,
I don't know if you sent the e-mail to the list by mistake or not, but =
would
you mind forwarding me a copy of that script?
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des syst=E8mes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique=20 Maritimes Region, DFO | R=E9gion des Maritimes, MPO
E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>=20
-----Original Message----- From: Daemen, Remco [SMTP:R.Daemen_at_arbeidsbureau.nl] Sent: Thursday, May 18, 2000 1:12 PM To: Multiple recipients of list ORACLE-L Subject: RE: Information on activity in Oracle's temporarysegment under O
Mark,
I rewrote the script n40758 a little. Major breakthru was the alteration of
the constant size_int to 1. After that it worked like a charm. I hope it
also works for you. Don't forget: the script should be run as user SYS ! Good hunting.
Remco
<<n40758_new.txt>>=20
> ----------
> From: mleith_at_bradmark.co.uk[SMTP:mleith_at_bradmark.co.uk]
> Sent: woensdag 17 mei 2000 11:44
> To: Multiple recipients of list ORACLE-L
> Subject: Information on activity in Oracle's temporary
segment under
> Oracle 7
>=20
> <<File: N40758.TXT>><<File: N1069041.TXT>>
> Hi there,
>=20
> I am running 7.3.4.4, with a temporary tablespace (TEMP) of 500M,
marked
> as
> type 'temporary'. I believe that this causes there to be a single
> temporary segment in the
> tablespace, shared by all users, which grows and shrinks according
to
> demand.
>=20
> I occasionally see ORA-1652 errors in the alert log, such as
'ORA-1652:
> unable to extend temp segment by 63 in tablespace TEMP'. 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;
>=20
> TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK
EXTENT_SIZE
> CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS
> ------------------------------- ------------ -------------
-----------
> ------------- ------------- ------------ ------------
> USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS
> FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE
> ----------- ------------ ----------- ------------- -----------
> ------------- ------------- ---------- ---------- -------------
> MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS
> --------------- ------------- ---------------
> TEMP 3 62271
63
> 2 978 63569 16
> 1040 962 62529 978 3188
> 0 0 978 63569 978
> 63569 412 26780
>=20
> 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). What I
> cannot tell from this view is, a) who the two users are, and b)
how many
> extents each is using.
>=20
> Occasionally, someone will report experiencing the problem, we
will ask
> them
> to repeat the action, and invariably, it will work the second time
around. >=20
> 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.
>=20
> 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!
>=20
> 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.
>=20
> I also attach a couple of documents I received from Oracle when I
raised
> this as a TAR. The first (n40758) is, I think, not exactly
pertinent to my
> situation, and the second talks about 'dumping a systemstate'.
>=20
> <<n40758.txt>> <<n1069041.txt>> What I would like is a)
visibility of
> who 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.g. user using more
than nn
> extents, or something similar).
>=20
> Your mission, should you choose to accept it.......!
>=20
> TIA
>=20
> Mark
>=20
> =20
> << File: n40758_new.txt >>=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Thu May 18 2000 - 12:17:24 CDT
 |
 |