Acknowledgements: I wish I knew.
One question that appears fairly frequently on the comp.database.oracle.server news group is 'Who is using the TEMP segment right now'.
In Oracle 8 a new view (v$sort_usage) has been introduced to tell you all about it; and in Oracle 7 you can get part of the way there by looking at v$sort_segments if you have the temporary segment defined as a proper temporary and then trying to see which process is using most CPU etc. etc.
The attached script came my way a couple of years ago (I have forgotten the source unfortunately) which gave a complete answer. As you will see from the header, there are no guarantees attached to the script, and it may not work for your version of Oracle on your version of your operating system. When I got it, I did nothing beyond executing the script as SYS on a version 7.3.3.5 system running under HP-UX 10.20 - it works for me, it may work for you.
A sample of using the procedure is at the bottom of the page.
The script
rem set serveroutput on rem execute dbms_output.enable(1000000) rem execute find_sort_segment_owners
create or replace procedure find_sort_segment_owners as -- -- ************ BEGIN PORT SPECIFIC VALUES ****************** sga_word_size number := 4 ; /* 8 for Alpha o/w 4 */ byte_swapped boolean := false ; /* True for Sequent and DEC UNIX ow false */ debug boolean := false; -- -- ************ END PORT SPECIFIC VALUES ******************* size_int number := 4 ; size_kgglk number := 2*sga_word_size ; size_kssob number := 4*sga_word_size; offset_to_temp_table_dba number := 0 ; max_scan_count constant number := 200; /* Max. no of so. to scan */
type temp_seg_number_array is table of number index by binary_integer; temp_seg_file_no temp_seg_number_array; temp_seg_block_no temp_seg_number_array; temp_seg_owner temp_seg_number_array; no_of_temp_segments number := 0; sga_base_address number; sga_base_address_hex varchar2(50); loop_cnt number; temp_tmp_flag number; perm_tmp_flag number; temp_table_dba number; temp_table_block_no number; temp_table_file_no number; temp_table_nblocks number; temp_table_nextents number; temp_table_tbs_num number; sess_id number; sess_addr varchar2(50); sga_addr varchar2(50); kssob_type_flag varchar2(50); kssob_own_ptr varchar2(50); kssob_nxt_link varchar2(50); kssob_prv_link varchar2(50); parent_head number; cursor c1 is select sid,rawtohex(saddr) from v$session ;
function to_decimal (hex_str_in in varchar2) return number as
hex_str varchar2(50) :=hex_str_in;
result number;
hex_char number;
begin
if ( sga_word_size = 8 ) then
hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0');
result := 0;
for i in 1..16 loop
hex_char := ascii(substr(hex_str,i,1));
if (hex_char - 64 > 0 ) then
hex_char := hex_char - 64 + 9;
else
hex_char := hex_char - 48 ;
end if;
result := result + ( hex_char * power(2, ( 64 - (i*4) ) ) );
end loop;
return (trunc(result));
else
hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),8,'0');
result := 0;
for i in 1..8 loop
hex_char := ascii(substr(hex_str,i,1));
if (hex_char - 64 > 0 ) then
hex_char := hex_char - 64 + 9;
else
hex_char := hex_char - 48 ;
end if;
result := result + ( hex_char * power(2, ( 32 - (i*4) ) ) );
end loop;
return (trunc(result));
end if;
end;
procedure get_base_address as
hex_address varchar2(40);
begin
select rawtohex(addr)
into hex_address
from x$ksmmem
where indx = 0;
sga_base_address := to_decimal(hex_address);
sga_base_address_hex := hex_address;
end;
function get_sga_index(sga_address in varchar2) return number as begin return trunc((to_decimal(sga_address)-sga_base_address)/sga_word_size ); end;
function get_sga_index_rem (sga_address in varchar2) return number as begin return (mod((to_decimal(sga_address)-sga_base_address),sga_word_size)); end;
function get_sga_value (hex_address in varchar2,adjustment in number default 0)
return varchar2 as
sga_index number;
local_value varchar2(50);
begin
sga_index := get_sga_index(hex_address) + adjustment;
if (debug) then
dbms_output.put_line('. get_sga_value '||hex_address||' '||
to_char(adjustment));
dbms_output.put_line('. get_sga_value get_sga_index '||sga_index);
end if;
select rawtohex(ksmmmval) into local_value from x$ksmmem
where indx = sga_index;
return local_value;
end;
function get_temp_table_state_object(state_name in varchar2)
return number as
local_state_object number;
hex_str varchar2(50);
state_object_address varchar2(50);
state_object_offset number;
sga_structure boolean;
sga_col_count number;
c1 integer;
rc integer;
sql_stmt varchar2(255);
begin
select count(*) into sga_col_count from x$kqfta a, x$kqfco b
where a.kqftanam = 'X$KSMFSV' and a.indx = b.KQFCOTAB
and b.KQFCONAM = 'KSMFSADR';
if ( sga_col_count = 1 ) then
sga_structure := false;
else
sga_structure := true;
end if;
if ( sga_structure ) then
c1 := dbms_sql.open_cursor;
sql_stmt := 'select KSMFSOFF from x$ksmfsv
where KSMFSNAM like '''||state_name||'''';
dbms_sql.parse(c1,sql_stmt,dbms_sql.native);
dbms_sql.define_column(c1,1,state_object_offset);
rc := dbms_sql.execute(c1);
rc := dbms_sql.fetch_rows(c1);
dbms_sql.column_value(c1,1,state_object_offset);
dbms_sql.close_cursor(c1);
local_state_object:=to_decimal(
get_sga_value(sga_base_address_hex,
state_object_offset/sga_word_size));
else
c1 := dbms_sql.open_cursor;
sql_stmt := 'select rawtohex(KSMFSADR) from x$ksmfsv
where KSMFSNAM like '''||state_name||'''' ;
dbms_sql.parse(c1,sql_stmt,dbms_sql.native);
dbms_sql.define_column(c1,1,state_object_address,50);
rc := dbms_sql.execute(c1);
rc := dbms_sql.fetch_rows(c1);
dbms_sql.column_value(c1,1,state_object_address);
dbms_sql.close_cursor(c1);
dbms_output.put_line('state_object_address '||
state_object_address);
if ( sga_word_size = 8 ) then
hex_str := get_sga_value(state_object_address);
dbms_output.put_line('. temp flag value at address '||hex_str);
hex_str := lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0');
if ( get_sga_index_rem(state_object_address) != 0 ) then
hex_str := substr(hex_str,1,8);
else
hex_str := substr(hex_str,9,16);
end if;
dbms_output.put_line('. substr temp flag value at address '||hex_str);
local_state_object := to_decimal(hex_str);
else
local_state_object:=to_decimal(get_sga_value(state_object_address));
end if;
end if;
dbms_output.put_line('. temp_table_state_object '||local_state_object);
return(local_state_object);
end;
function state_object_initialised(flags in varchar2) return boolean
is
begin
if ( sga_word_size = 8 and byte_swapped ) then
if ( to_decimal(substr(flags,14,1)) = 1 ) then
return true;
else
return false;
end if;
else
if ( byte_swapped ) then
if ( to_decimal(substr(flags,6,1)) = 1 ) then
return true;
else
return false;
end if;
else
if ( to_decimal(substr(flags,4,1)) = 1 ) then
return true;
else
return false;
end if;
end if;
end if;
end;
function match_flag(state_flag in number,flags in out varchar2) return boolean
is
begin
if ( byte_swapped ) then
if ( sga_word_size = 8 ) then
flags:=lpad(upper(nvl(ltrim(flags,'0'),'0')),16,'0');
if (debug) then
dbms_output.put_line('. '||
to_decimal(substr(flags,15,2))||
'?='||state_flag);
end if;
if ( to_decimal(substr(flags,15,2)) = state_flag ) then
return true;
else
return false;
end if;
else
if ( to_decimal(substr(flags,7,2)) = state_flag ) then
return true;
else
return false;
end if;
end if;
else
if ( to_decimal(substr(flags,1,2)) = state_flag ) then
return true;
else
return false;
end if;
end if;
end;
procedure build_active_temp_segments as
cursor c1 is select file#,block# from seg$ where type=3;
local_file_no number;
local_block_no number;
begin
open c1;
no_of_temp_segments := 0 ;
loop
fetch c1 into local_file_no,local_block_no;
exit when c1%notfound;
no_of_temp_segments := no_of_temp_segments + 1;
temp_seg_file_no(no_of_temp_segments) := local_file_no ;
temp_seg_block_no(no_of_temp_segments) := local_block_no;
temp_seg_owner(no_of_temp_segments) := (-1);
end loop;
close c1;
end;
function valid_temp_seg_dba (t_file_no number, t_block_no number,
sess_id number ) return boolean
is
begin
for i in 1..no_of_temp_segments loop
if ( temp_seg_file_no(i) = t_file_no and
temp_seg_block_no(i)= t_block_no ) then
temp_seg_owner(i) := sess_id ;
return true;
end if;
end loop;
return false;
end;
begin
get_base_address;
dbms_output.put_line('SGA base '||sga_base_address);
temp_tmp_flag:=get_temp_table_state_object('%ktstud%');
perm_tmp_flag:=get_temp_table_state_object('%ktatlt%');
dbms_output.put_line('temp tmp state '||temp_tmp_flag);
dbms_output.put_line('perm tmp state '||perm_tmp_flag);
build_active_temp_segments;
open c1;
loop
fetch c1 into sess_id,sess_addr ;
exit when c1%notfound;
dbms_output.put_line('********** sess_id,sess_addr '||
to_char(sess_id)||' '||sess_addr||' ***********');
kssob_type_flag := get_sga_value(sess_addr);
if (debug) then
dbms_output.put_line('. kssob_type_flag '||kssob_type_flag);
end if;
parent_head := to_decimal(sess_addr) + size_kssob;
kssob_nxt_link := get_sga_value(sess_addr,size_kssob/sga_word_size);
if (debug) then
dbms_output.put_line('. kssob_nxt_link '||kssob_nxt_link);
end if;
sga_addr := parent_head ;
loop_cnt := 0;
if ( state_object_initialised(kssob_type_flag) ) then
loop
exit when to_decimal(kssob_nxt_link) = parent_head;
exit when loop_cnt > max_scan_count ;
loop_cnt := loop_cnt + 1;
sga_addr := kssob_nxt_link;
kssob_type_flag := get_sga_value(sga_addr,
- (size_kgglk/sga_word_size) );
kssob_nxt_link := get_sga_value(sga_addr,0);
if (debug) then
dbms_output.put_line('. type flag '||kssob_type_flag);
end if;
if ( state_object_initialised(kssob_type_flag)) then
if (match_flag(perm_tmp_flag,kssob_type_flag)) then
temp_table_dba := to_decimal( get_sga_value(sga_addr,
( size_kgglk ) / sga_word_size
+ offset_to_temp_table_dba ));
temp_table_file_no
:=dbms_utility.data_block_address_file(temp_table_dba);
temp_table_block_no := dbms_utility.data_block_address_block(temp_table_dba);
if (valid_temp_seg_dba(temp_table_file_no,temp_table_block_no,
sess_id) ) then
sys.dbms_output.put_line(
'Session '||rpad(to_char(sess_id),6,' ')||
' DBA '||rpad(temp_table_dba,10,' ')||
' File No '||rpad(temp_table_file_no,6,' ')||' Block No '||
rpad(temp_table_block_no,10,' '));
end if;
end if;
if (match_flag(temp_tmp_flag,kssob_type_flag) ) then
temp_table_tbs_num:=to_decimal( get_sga_value(sga_addr,
( size_kgglk ) / sga_word_size
+ (size_int) ));
temp_table_nextents:=to_decimal( get_sga_value(sga_addr,
( size_kgglk ) / sga_word_size
+ (3*size_int) ));
temp_table_nblocks:=to_decimal( get_sga_value(sga_addr,
( size_kgglk ) / sga_word_size
+ (4*size_int) ));
sys.dbms_output.put_line(
'Session '||rpad(to_char(sess_id),6,' ')||
' TS id # :'||temp_table_tbs_num||
' Blocks used :'||temp_table_nblocks||
' Exetents used :'||temp_table_nextents);
end if;
end if;
end loop;
end if;
end loop;
close c1;
for i in 1..no_of_temp_segments loop
if (temp_seg_owner(i) = (-1) ) then
dbms_output.put_line('Unresolved Segment file:'||temp_seg_file_no(i)||
' block: '||temp_seg_block_no(i)||' owner: '||temp_seg_owner(i));
end if;
end loop;
end;
/
set serveroutput on execute find_sort_segment_owners
PL/SQL procedure successfully completed.
SGA base 3251826688 state_object_address C1D33468 . temp_table_state_object 13 state_object_address C1D332C4 . temp_table_state_object 10 temp tmp state 13 perm tmp state 10 ********** sess_id,sess_addr 1 C1D4F520 *********** ********** sess_id,sess_addr 2 C1D4FB5C *********** ********** sess_id,sess_addr 3 C1D50198 *********** ********** sess_id,sess_addr 4 C1D507D4 *********** ********** sess_id,sess_addr 5 C1D50E10 *********** ********** sess_id,sess_addr 6 C1D5144C *********** ********** sess_id,sess_addr 7 C1D51A88 *********** ********** sess_id,sess_addr 10 C1D52D3C *********** Session 10 DBA 268442678 File No 4 Block No 7222
At this point you can find the size of the segment with:
'select * from dba_segments where name = '4.7222';
and see who is doing the work with
'select username from v$session where sid = 10;'