| 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: Temp tablespace
--=_ORCL_5965294_0r0
Content-Type:text/plain; charset="iso-8859-1"
Content-Transfer-Encoding:quoted-printable
That may have been true in Oracle7 but in Oracle8 you can indeed find out who is using a sort segment. This is coming from the Oracle DBA course notes:
Join v$session and v$sort_usage views to obtain information on the currently active sorts in the instance:
EXAMPLE
select s.username, u."USER", u.tablespace,
u.contents, u.extents, u.blocks
from v$session s, v$sort_usage u
where s.addr =3D u.session_addr;
The USER column in v$sort_usage always shows the user querying this view and not the user performing the sort. Always obtain the user name from the v$session view. The contents column shows whether the temporary segment is created in a permanent or a temporary tablespace.
I am to lazy to try this on a Oracle7 database but I would be suprized if that did not work also. Maybe v$sort_usage is a new view???
kimberly.smith_at_eds.com
--=_ORCL_5965294_0r0
content-type:message/rfc822
Date: 03 May 2000 02:04:44
From:"Klavers, Richard" <R.Klavers_at_arbeidsbureau.nl>
To:Multiple recipients of list ORACLE-L <ORACLE-L>
Subject:RE: Temp tablespace
Reply-to:UNIX.IOGMD:ORACLE-L_at_fatcity.com
Return-Path:root_at_fatcity.cts.com
Received:from gmd.fujitsu.com (ducks.gmd.fujitsu.com [198.6.119.2]) by hdoc02.gmd.fujitsu.com with SMTP (8.7.1/8.7.1) id BAA29452 for <ksmith1.ofcmail_at_hdbi01>; Wed, 3 May 2000 01:16:33 -0700 (PDT) Received:from dfwl01.gmd.fujitsu.com by gmd.fujitsu.com (SMI-8.6/) id BAA29545; Wed, 3 May 2000 01:16:28 -0700 Received:from dfwl01.gmd.fujitsu.com (root_at_localhost) by dfwl01.gmd.fujitsu.com with ESMTP id BAA01509 for <ksmith1_at_gmd.fujitsu.com>; Wed, 3 May 2000 01:16:27 -0700 (PDT) Received:from newsfeed.cts.com (newsfeed.cts.com [209.68.192.199]) by dfwl01.gmd.fujitsu.com with ESMTP id BAA01505 for <ksmith1_at_gmd.fujitsu.com>; Wed, 3 May 2000 01:16:26 -0700 (PDT) Received:from fatcity.UUCP (uucp_at_localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id BAA48234; Wed, 3 May 2000 01:13:26 -0700 (PDT) Received:by fatcity.com (02-Mar-2000/v1.0f-b68/bab) via UUCP id 00156C31; Wed, 03 May 2000 01:04:44 -0800Message-ID:<F001.00156C31.20000503010444_at_fatcity.com> X-Comment:Oracle RDBMS Community Forum
Hi,
I found a book which went a little deeper on the TEMP tablespace in Oracle
7.3.
The book says that the first sort to use the temp tablespace allocates a
temporary segment within the temp tablespace, and when the query completes,
the space used by the temporary segment is not dropped. Instead, the space
used by the temporary segment is available for use by other queries. This
avoids the cost of allocating and releasing space for temporary segments. So
a temp tablespace can fill up to 100% and stay that way, even if no one is
using sorts.
You can not see who or what is using the TEMP tablespace, only what amount
of space is being used.
The book i found this in is the Oracle DBA Handbook 7.3 edition from Oracle
Press by Kevin Loney.
As you can see, it all comes down to RTFM. Next time i will read first and then ask...
Richard Klavers
> ----------
> Van: Lyall Barbour[SMTP:lyallbarbour_at_sanfranmail.com]
> Antwoord naar: ORACLE-L_at_fatcity.com
> Verzonden: woensdag 3 mei 2000 3:44
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: RE: Temp tablespace
>
> I've looked for that also. The only user that puts things in that
> tablespace is SYS. I'm not sure, if you do find out, let me know! :)
>
> Sorry
> Lyall Barbour
>
> ------Original Message------
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: May 2, 2000 7:57:32 PM GMT
>
>
> Hi,
>
> is there a way to see which user or process is consuming the
> TEMP tablespace?
> The tablespace is created as a 'TEMPORARY' tablespace.
> I've looked in all kinds of documentation, and you can find all sort of
> things
> about rollback segments and memory and so on, but nothing is said
> about the TEMP tablespace.
>
> Regards, Richard Klavers
>
> --
> Author: Klavers, Richard
> INET: R.Klavers_at_arbeidsbureau.nl
>
> 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).
>
> ______________________________________________
> FREE Personalized Email at Mail.com
> Sign up at http://www.mail.com/?sr=signup
>
> --
> Author: Lyall Barbour
> INET: lyallbarbour_at_sanfranmail.com
>
> 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: Klavers, Richard INET: R.Klavers_at_arbeidsbureau.nl 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). --=_ORCL_5965294_0r0-- ------------------------------ From: CHUCK_HAMILTON_at_qvc.com Date: Wed, 3 May 2000 11:29:33 -0400 Subject: Re: Oracle db writer taking too much CPU ? A couple of my suspicions are confirmed. The db buffer cache hit ratio is only 77%. Minimum target is 90% unless it's a DSS system where sometimes that number can't be obtained (rare). Your buffer cache is currently 50m (16k/block x 3200 blocks). Try increasing db_block_buffers to 4000 and see what happens to the cache hit ratio. Incomplete checkpoints no longer seems to be a problem , but a 20 minute sample is pretty small to check this. Also, 12 checkpoints in 20 minutes seems like a lot. Is there any reason for doing them so frequently? Is this intentional? It will reduce recovery time in the event of a media failure,Received on Wed May 03 2000 - 10:15:37 CDT
 |
 |