-
Obn our system we have a temp segment 3GB(!) in size.
How do I clear it out? How do I find which GIT is doing this to the system?
Help! Help!
-
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
-
Hi there,
no good im afraid...
My best guess is that the tansaction is still active so it wont clear. How can I trace back from the temp segment to the 'owner'/instigator? OWNER in dba_segement shows SYS as its a temp segment....
The phones are ringing.....
Bob
-
yes, it must be active (or rolling back) ... I guess you are in 8.x, so you can try :
select username, sid, serial#, contents
from v$session, v$sort_usage
where v$session.saddr = v$sort_usage.session_addr
which will return all users and their SID that are issuing a sort ...
-
Fraid not ... this one is a 7.3.4...
any further ideas? We have added a further datafile to the temp tablespace ...will keep an eye and see if that fills as well..
-B
-
If you use 8i, make tempfiles in temporary tablespaces. Here is a sample:
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/vx/rdsk/datadg/temp_1'
SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
-
ok, here is the article about it in 7, but I have tested it several times without having really interesting results :
Creator of Sort Segment in Oracle 7
-----------------------------------
In Oracle 7, it is not so simple. The information is not stored
in a queryable data dictionary view.
You can find all SIDs which are using the TEMPORARY Tablespace datafiles
use the following query:
SELECT sid,event
FROM v$session_wait
WHERE p1 in (:temp_file#)
AND event like 'db file%';
[:temp_file# = list of all files comprising the TEMP TABLESPACE]
Note: important events are 'db file scattered read'
or 'db file sequential read'
To find the exact username, you have to dump a systemstate which then
has to be searched to find the temporary/sort segment and then the
username.
To dump a systemstate:
- CONNECT SYSTEM/MANAGER
- ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10'
This will produce trace file in your user_dump_dest directory.
- Load this trace file to any editor and SEARCH for SORT SEGMENT HANDLE.
The SORT SEGMENT HANDLE will have been created by a PROCESS, and the
PROCESS will have a username.
You'll see something like (this systemstate was taken from Oracle 7.3):
PROCESS 10:
----------------------------------------
SO: 1e070c8, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=10, calls cur/top: 1e25770/1e25770, flag: (0) -
int error: 0, call error: 0, sess error: 0
(latch info) wait_event=0 bits=0
O/S info: user: Administrator, term: VKOPYRA, ospid: 00122
OSD pid info: pid: 122
----------------------------------------
SO: 1e117a8, type: 3, owner: 1e070c8, flag: ........
(session) trans: 1e69434, creator: 1e070c8, .........
oct: 9, prv: 0, user: 10/SCOTT <-- user name is here
O/S info: user: Administrator, term: .....
program:
waiting for 'db file scattered read' seq=378 wait_time=0
file#=2, block#=28e, blocks=8
----------------------------------------
SO: 1e88cc4, type: 13, owner: 1e117a8, flag: INIT/-/-/0x00
SORT SEGMENT HANDLE: tsn=3 incr=607 extents=35 <-----------
(enqueue) TS-04000002-00000000
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res:1e37888, prv: 1e2ddd4, sess: 1e117a8, proc: 1e070c8
(enqueue) ST-00000000-00000000
As a result we can see that user SCOTT was doing something that
required creating of temporary segment.
Which users are performing Sorts
--------------------------------
Often it becomes necessary to identify the users performing sorts. This
helps identify the particular sessions that may be issuing resource-intensive
sorts. You can also determine whether an in-memory or disk sort is being
performed. The following query lists the users performing sorts a well as
the type of sort:
------cut ----- cut ----- cut ----- cut ----- cut ---- cut -------
REM #*****************************************************
REM #File Name: sort.sql
REM #
REM #Purpose: Report Sort Statistics
REM#
REM #*****************************************************
SELECT substr(vs.username,1,20) "db user",
substr(vs.osuser,1,20) "os user",
substr(vsn.name,1,20) "Type of Sort",
vss.value
FROM v$session vs,
v$sesstat vss,
v$statname vsn
WHERE (vss.statistic#=vsn.statistic#) AND
(vs.sid = vss.sid) AND
(vsn.name like '%sort%')
ORDER BY 2,3;
------cut ----- cut ----- cut ----- cut ----- cut ---- cut -------
Example:
db user os user Type of Sort VALUE
-------------------- --------------- -------------------- ----------
SYS kevin sorts (disk) 4
SYS kevin sorts (memory) 12
SYS kevin sorts (rows) 9936
Search Words:
=============
TEMP PERMANENT V$SESSION V$SORT_USAGE SORT USER USERID USERNAME
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|