RE: Information on activity in Oracle's temporary segment under

From: <Colin.Shaw_at_phs.com>
Date: Tue, 23 May 2000 18:15:33 -0700
Message-Id: <10506.106454@fatcity.com>

Mark:

Dunno what to tell you. I just compiled and ran the verbatim MetaLink script in Oracle 7.3.3.6 on an Alpha OpenVMS 7.1-2 box and it worked as expected. All I can see is that you'd need to change the byte_swapped = and
sga_word_size variables for HP/UX. However, the one I have has no = mention
of DBA_TABLESPACES or 'TEMORARY' [sic] in the script. Is it the same = one as
from MetaLink? HTH.

Colin.

-----Original Message-----
From: mleith_at_bradmark.co.uk [mailto:mleith_at_bradmark.co.uk] Sent: Tuesday, May 23, 2000 5:52 AM
To: Shaw Colin H; ORACLE-L_at_fatcity.com
Subject: RE: Information on activity in Oracle's temporary segment = under

I have tested this script, and it doesn't seem to work. First off, it = is
looking for a contents column in DBA_TABLESPACES, with a value of 'TEMORARY', this may be a column under version 8, but not on my 7.2.2 = test
instance (HP/UX). I believe (correct me if I'm wrong) that there is no = type
'TEMPORARY' of segments under version 7?

I am beggining to be resigned to the fact that I just can't get to the = info
I need, If anybody could show me the way down that yellow brick road I = would
very much appreciate it. I am looking for all users running sorts in a temporary tablespace, with the number of extents that they are = explicitly
using. Any ideas? Does anybody know if this is possible/impossible = under
version 7?

TIA Mark

-----Original Message-----

From:	MIME :Colin.Shaw_at_phs.com Sent:	22 May 2000 21:57
To:	ORACLE-L_at_fatcity.com
Subject:	RE: Information on activity in Oracle's temporary segment

under

You can find the original script in MetaLink:

Note: 40758.1 TFTS: Script to find who owns TEMP Segments in V7.

-----Original Message-----
Sent: Thursday, May 18, 2000 10:33 AM
To: Multiple recipients of list ORACLE-L 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 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> -----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 temporary

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


	> 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

	> 	> <<File: N40758.TXT>><<File: N1069041.TXT>>

	> Hi there,

	> 	> 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.
> > 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;
> > 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
> > 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.
> > Occasionally, someone will report experiencing the
problem, we
will ask

> them
> to repeat the action, and invariably, it will work the second time
around.

> > 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.
> > 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.
> > 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'.
> > <<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).
> > Your mission, should you choose to accept it.......!
> > TIA
> > Mark
> > > << File: n40758_new.txt >> -- Author: Boivin,
Patrice J
INET: BoivinP_at_mar.dfo-mpo.gc.ca

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 (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Author: INET: Colin.Shaw_at_phs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------