| 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
I have tested this script, and it doesn't seem to work=2E First off, it is=20=
looking for a contents column in DBA_TABLESPACES, with a value of 'TEMORARY',=20=
this may be a column under version 8, but not on my 7=2E2=2E2 test instance=20=
(HP/UX)=2E I believe (correct me if I'm wrong) that there is no type=20=
'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=20= need, If anybody could show me the way down that yellow brick road I would=20= very much appreciate it=2E I am looking for all users running sorts in a=20= temporary tablespace, with the number of extents that they are explicitly=20= using=2E Any ideas? Does anybody know if this is possible/impossible under=20= version 7?
TIA Mark
-----Original Message-----
From: MIME :Colin=2EShaw_at_phs=2Ecom=20= Sent: 22 May 2000 21:57 To: ORACLE-L_at_fatcity=2Ecom Subject: RE: Information on activity in Oracle's temporary segment under
You can find the original script in MetaLink:
Note: 40758=2E1 TFTS: Script to find who owns TEMP Segments in V7=2E
-----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=2E et Exploit=2E des syst=E8mes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region,=20= DFO | R=E9gion des Maritimes, MPO E-Mail: boivinp_at_mar=2Edfo-mpo=2Egc=2Eca <mailto:boivinp_at_mar=2Edfo-mpo=2Egc=2Eca>=20= -----Original Message----- From: Daemen, Remco [SMTP:R=2EDaemen_at_arbeidsbureau=2Enl] 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=2E Major breakthru was the alteration of
the constant size_int to 1=2E After that it worked like a charm=2E I hope it
also works for you=2E Don't forget: the script should be run as user SYS ! Good hunting=2E
Remco
<<n40758_new=2Etxt>> > ----------INET: BoivinP_at_mar=2Edfo-mpo=2Egc=2Eca
> From: mleith_at_bradmark=2Eco=2Euk[SMTP:mleith_at_bradmark=2Eco=2Euk]
> 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=2ETXT>><<File: N1069041=2ETXT>>
> 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 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
> 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)=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 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
> > > << File: n40758_new=2Etxt >> -- Author: Boivin, Patrice J
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=2Ecom (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)=2E You may also send the HELP command for other information (like subscribing)=2E
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=2Ecom (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Tue May 23 2000 - 07:51:38 CDT
 |
 |