| 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 O
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01BFC0A6.A83D9318
Content-Type: text/plain
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
>
>
>
------_=_NextPart_000_01BFC0A6.A83D9318
Content-Type: text/plain;
name="n40758_new.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="n40758_new.txt"
set serveroutput on size 1000000
create or replace procedure find_sort_segment_owners as=20
--
/*
** This procedure should ONLY be run as user SYS, because some=20
** internal tables are being used. If the procedure still doesn't work,
** you can try to change the parameters in the section=20
** PORT SPECIFIC VALUES below.
*/
-- ************ BEGIN PORT SPECIFIC VALUES ******************=20
sga_word_size number :=3D 4 ; /* 8 for Alpha o/w 4 =
*/ =20
byte_swapped boolean :=3D true ; /* True for Sequent =
and DEC UNIX ow false */=20
size_int number :=3D 1 ;=20
debug boolean :=3D false;=20
-- ************ END PORT SPECIFIC VALUES *******************=20
size_kgglk number :=3D 2*sga_word_size ; =20
size_kssob number :=3D 4*sga_word_size; =20
offset_to_temp_table_dba number :=3D 0 ; =20
max_scan_count constant number :=3D 200; /* Max. no of so. to =
scan */=20
--
type temp_seg_number_array is table of number index by =
binary_integer;=20
--
temp_seg_file_no temp_seg_number_array;=20
temp_seg_block_no temp_seg_number_array;=20
temp_seg_owner temp_seg_number_array;=20
no_of_temp_segments number :=3D 0;=20
sga_base_address number;=20
sga_base_address_hex varchar2(50);=20
loop_cnt number;=20
temp_tmp_flag number;=20
perm_tmp_flag number;=20
temp_table_dba number;=20
temp_table_block_no number;=20
temp_table_file_no number;=20
temp_table_nblocks number;=20
temp_table_nextents number;=20
temp_table_tbs_num number;=20
sess_id number;=20
sess_addr varchar2(50);=20
sga_addr varchar2(50);=20
kssob_type_flag varchar2(50);=20
kssob_own_ptr varchar2(50);=20
kssob_nxt_link varchar2(50);=20
kssob_prv_link varchar2(50);=20
parent_head number;=20
gevonden boolean :=3D false; =20
total_used_extents number :=3D 0;
total_used_mb number :=3D 0;
l_s_username varchar2(30);
l_s_osuser varchar2(30);
l_n_serial# number;
l_n_db_block_size number;
l_n_max_extents number :=3D 0;
l_n_max_blocks number :=3D 0;
l_n_max_mb number :=3D 0;
l_n_pct number;
--
cursor c1 is select sid,rawtohex(saddr) from v$session;=20
--
function to_decimal (hex_str_in in varchar2) return number as=20
hex_str varchar2(50) :=3Dhex_str_in;=20
result number;=20
hex_char number;=20
begin=20
if ( sga_word_size =3D 8 )=20
then=20
hex_str :=3D lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0');=20
result :=3D 0;=20
for i in 1..16=20
loop=20
hex_char :=3D ascii(substr(hex_str,i,1));=20
if (hex_char - 64 > 0 )=20
then=20
hex_char :=3D hex_char - 64 + 9;=20
else=20
hex_char :=3D hex_char - 48 ;=20
end if;=20
result :=3D result + ( hex_char * power(2, ( 64 - (i*4) ) ) );=20
end loop;=20
return (trunc(result));=20
else=20
hex_str :=3D lpad(upper(nvl(ltrim(hex_str,'0'),'0')),8,'0');=20
result :=3D 0;=20
for i in 1..8 loop=20
hex_char :=3D ascii(substr(hex_str,i,1));=20
if (hex_char - 64 > 0 )=20
then=20
hex_char :=3D hex_char - 64 + 9;=20
else=20
hex_char :=3D hex_char - 48 ;=20
end if;=20
result :=3D result + ( hex_char * power(2, ( 32 - (i*4) ) ) );=20
end loop;=20
return (trunc(result));=20
end if;=20
end;=20
--
--
procedure get_base_address as=20
hex_address varchar2(40);=20
begin=20
select rawtohex(addr)=20
into hex_address=20
from x$ksmmem=20
where indx =3D 0;=20
sga_base_address :=3D to_decimal(hex_address);=20
sga_base_address_hex :=3D hex_address;=20
end;=20
--
function get_sga_index(sga_address in varchar2) return number as=20
begin=20
return =
trunc((to_decimal(sga_address)-sga_base_address)/sga_word_size );=20
end;=20
--
function get_sga_index_rem (sga_address in varchar2) return number as=20
begin=20
return =
(mod((to_decimal(sga_address)-sga_base_address),sga_word_size));=20
end;=20
--
function get_sga_value (hex_address in varchar2,adjustment in number =
default 0)=20
return varchar2 as=20
sga_index number;=20
local_value varchar2(50);=20
begin=20
sga_index :=3D get_sga_index(hex_address) + adjustment;=20
-- if (debug)=20
-- then=20
-- dbms_output.put_line('. get_sga_value '||hex_address||' '||=20
-- to_char(adjustment));=20
-- dbms_output.put_line('. get_sga_value get_sga_index =
'||sga_index);=20
-- end if;=20
select rawtohex(ksmmmval) into local_value from x$ksmmem=20
where indx =3D sga_index;=20
-- dbms_output.put_line('. local_value '||local_value);=20
return local_value;=20
end;=20
--
function get_temp_table_state_object(state_name in varchar2) =20
return number as=20
--
local_state_object number;=20
hex_str varchar2(50);=20
state_object_address varchar2(50);=20
state_object_offset number;=20
sga_structure boolean;=20
sga_col_count number;=20
c1 integer;=20
rc integer;=20
sql_stmt varchar2(255);=20
begin=20
select count(*)=20
into sga_col_count from x$kqfta a, x$kqfco b=20
where a.kqftanam =3D 'X$KSMFSV' and a.indx =3D b.KQFCOTAB=20
and b.KQFCONAM =3D 'KSMFSADR';=20
if ( sga_col_count =3D 1 )=20
then=20
sga_structure :=3D false;=20
else=20
sga_structure :=3D true;=20
end if;=20
if ( sga_structure )
then=20
c1 :=3D dbms_sql.open_cursor;=20
sql_stmt :=3D 'select KSMFSOFF from x$ksmfsv=20
where KSMFSNAM like '''||state_name||'''';=20
dbms_sql.parse(c1,sql_stmt,dbms_sql.native);=20
dbms_sql.define_column(c1,1,state_object_offset);=20
rc :=3D dbms_sql.execute(c1);=20
rc :=3D dbms_sql.fetch_rows(c1);=20
dbms_sql.column_value(c1,1,state_object_offset);=20
dbms_sql.close_cursor(c1);=20
local_state_object:=3Dto_decimal(=20
get_sga_value(sga_base_address_hex,=20
=
state_object_offset/sga_word_size));=20
else=20
c1 :=3D dbms_sql.open_cursor;=20
sql_stmt :=3D 'select rawtohex(KSMFSADR) from x$ksmfsv=20
where KSMFSNAM like '''||state_name||'''' ;=20
dbms_sql.parse(c1,sql_stmt,dbms_sql.native);=20
dbms_sql.define_column(c1,1,state_object_address,50);=20
rc :=3D dbms_sql.execute(c1);=20
rc :=3D dbms_sql.fetch_rows(c1);=20
dbms_sql.column_value(c1,1,state_object_address);=20
dbms_sql.close_cursor(c1);=20
if (debug)=20
then=20
dbms_output.put_line('state_object_address '||=20
state_object_address
);=20
end if;
if ( sga_word_size =3D 8 )=20
then =20
hex_str :=3D get_sga_value(state_object_address);=20
if (debug)=20
then=20
dbms_output.put_line('. temp flag value at address =
'||hex_str);=20
end if;
hex_str :=3D lpad(upper(nvl(ltrim(hex_str,'0'),'0')),16,'0');=20
if ( get_sga_index_rem(state_object_address) !=3D 0 )=20
then =20
hex_str :=3D substr(hex_str,1,8);=20
else =20
hex_str :=3D substr(hex_str,9,16);=20
end if;=20
dbms_output.put_line('. substr temp flag value at address =
'||hex_str);=20
local_state_object :=3D to_decimal(hex_str);=20
else=20
=
local_state_object:=3Dto_decimal(get_sga_value(state_object_address));=20
end if;=20
end if;=20
if (debug)=20
then=20
dbms_output.put_line('. temp_table_state_object =
'||local_state_object);=20
end if;
return(local_state_object);=20
end;=20
--
function state_object_initialised(flags in varchar2) return boolean=20
as=20
begin=20
if ( sga_word_size =3D 8 and byte_swapped )=20
then=20
if ( to_decimal(substr(flags,14,1)) =3D 1 )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
else=20
if ( byte_swapped )=20
then=20
if ( to_decimal(substr(flags,6,1)) =3D 1 )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
else=20
if ( to_decimal(substr(flags,8,1)) =3D 1 )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
end if;=20
end if;=20
end;=20
--
function match_flag(state_flag in number,flags in out varchar2) return =
boolean=20
is=20
begin=20
if ( byte_swapped )=20
then=20
if ( sga_word_size =3D 8 )=20
then=20
flags:=3Dlpad(upper(nvl(ltrim(flags,'0'),'0')),16,'0');=20
if (debug)=20
then=20
dbms_output.put_line('. '||=20
to_decimal(substr(flags,15,2))||=20
'?=3D'||state_flag);=20
end if;=20
if ( to_decimal(substr(flags,15,2)) =3D state_flag )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
else=20
if ( to_decimal(substr(flags,7,2)) =3D state_flag )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
end if;=20
else=20
if ( to_decimal(substr(flags,1,2)) =3D state_flag )=20
then=20
return true;=20
else=20
return false;=20
end if;=20
end if;=20
end;=20
--
procedure build_active_temp_segments as=20
cursor c1 is select file#,block# from seg$ where type=3D3;=20
local_file_no number;=20
local_block_no number;=20
begin=20
open c1;=20
no_of_temp_segments :=3D 0 ;=20
loop=20
fetch c1 into local_file_no,local_block_no;=20
exit when c1%notfound;=20
no_of_temp_segments :=3D no_of_temp_segments + 1;=20
temp_seg_file_no(no_of_temp_segments) :=3D local_file_no ;=20
temp_seg_block_no(no_of_temp_segments) :=3D local_block_no;=20
temp_seg_owner(no_of_temp_segments) :=3D (-1);=20
end loop;=20
close c1;=20
end;=20
--
function valid_temp_seg_dba (t_file_no number, t_block_no number,=20
sess_id number ) return boolean=20
is=20
begin=20
for i in 1..no_of_temp_segments=20
loop=20
if ( temp_seg_file_no(i) =3D t_file_no =20
and temp_seg_block_no(i)=3D t_block_no=20
)=20
then=20
temp_seg_owner(i) :=3D sess_id ;=20
return true;=20
end if;=20
end loop;=20
return false;=20
end;=20
--
--
begin=20
select value=20
into l_n_db_block_size=20
from v$parameter=20
where name=3D'db_block_size';
select sum(bytes)/(1024*1024)=20
into l_n_max_mb
from dba_data_files df,dba_tablespaces t
where df.tablespace_name=3Dt.tablespace_name
and t.contents=3D'TEMPORARY';
select floor((l_n_max_mb*(1024*1024) - initial_extent)/next_extent)=20
into l_n_max_extents
from dba_tablespaces=20
where contents=3D'TEMPORARY';
get_base_address;=20
if (debug)=20
then=20
dbms_output.put_line('SGA base '||sga_base_address);=20
end if;
temp_tmp_flag:=3Dget_temp_table_state_object('%ktstud%');=20
perm_tmp_flag:=3Dget_temp_table_state_object('%ktatlt%');=20
if (debug)=20
then=20
dbms_output.put_line('temp tmp state '||temp_tmp_flag);=20
dbms_output.put_line('perm tmp state '||perm_tmp_flag);=20
end if;
build_active_temp_segments;=20
open c1;=20
loop=20
fetch c1 into sess_id,sess_addr ;=20
exit when c1%notfound;=20
kssob_type_flag :=3D get_sga_value(sess_addr);=20
if (debug)=20
then=20
dbms_output.put_line('. kssob_type_flag '||kssob_type_flag);=20
end if;=20
parent_head :=3D to_decimal(sess_addr) + size_kssob; =20
kssob_nxt_link :=3D =
get_sga_value(sess_addr,size_kssob/sga_word_size);=20
if (debug)=20
then
dbms_output.put_line('. kssob_nxt_link '||kssob_nxt_link);=20
end if;=20
sga_addr :=3D parent_head ;=20
loop_cnt :=3D 0;=20
if ( state_object_initialised(kssob_type_flag) )=20
then=20
loop=20
exit when to_decimal(kssob_nxt_link) =3D parent_head;=20
exit when loop_cnt > max_scan_count ;=20
loop_cnt :=3D loop_cnt + 1;=20
sga_addr :=3D kssob_nxt_link;=20
kssob_type_flag :=3D get_sga_value(sga_addr,=20
- (size_kgglk/sga_word_size) );=20
kssob_nxt_link :=3D get_sga_value(sga_addr,0);=20
if (debug)=20
then=20
dbms_output.put_line('. type flag '||kssob_type_flag);=20
end if;=20
if ( state_object_initialised(kssob_type_flag))
then=20
if (match_flag(perm_tmp_flag,kssob_type_flag))=20
then=20
temp_table_dba :=3D to_decimal( get_sga_value(sga_addr,=20
( size_kgglk ) / sga_word_size =20
+ offset_to_temp_table_dba ));=20
temp_table_file_no:=3D =
dbms_utility.data_block_address_file(temp_table_dba);=20
=
temp_table_block_no:=3Ddbms_utility.data_block_address_block(temp_table_=
dba);=20
if =
(valid_temp_seg_dba(temp_table_file_no,temp_table_block_no,=20
sess_id) )=20
then=20
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,' =
'));=20
end if;=20
end if;=20
if (match_flag(temp_tmp_flag,kssob_type_flag) )=20
then=20
temp_table_tbs_num:=3Dto_decimal( get_sga_value(sga_addr,=20
( size_kgglk ) / sga_word_size=20
+ (size_int) ));=20
temp_table_nextents:=3Dto_decimal( get_sga_value(sga_addr, =
( size_kgglk ) / sga_word_size=20
+ (3*size_int) ));=20
temp_table_nblocks:=3Dto_decimal( get_sga_value(sga_addr,=20
( size_kgglk ) / sga_word_size=20
+ (4*size_int) ));=20
if not gevonden
then
dbms_output.put_line(rpad('=3D',100,'=3D'));
dbms_output.put_line( rpad('SID',7,' ')
||rpad('Serial#',8,' ')
||rpad('Username ',31,' ')
||rpad('Osuser ',31,' ')
||rpad('Extents',9,' ')
||rpad('Mb',9,' ')
||rpad('%',5,' ')
);
dbms_output.put_line( rpad('=3D=3D=3D=3D=3D=3D',7,' ')
=
||rpad('=3D=3D=3D=3D=3D=3D=3D',8,' ')
=
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D',31,' ')
=
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D',31,' ')
=
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
=
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
||rpad('=3D=3D=3D=3D=3D',6,' ')
);
gevonden :=3D true;
end if;
select serial#,username,osuser=20
into l_n_serial#,l_s_username,l_s_osuser
from v$session=20
where sid=3Dsess_id;
l_n_pct :=3D =
round((((temp_table_nblocks*l_n_db_block_size)/(1024*1024))/l_n_max_mb)*=
100,1);
dbms_output.put_line( rpad(to_char(sess_id),7,' ')
||rpad(to_char(l_n_serial#),8,' ')
||rpad(l_s_username,31,' ')
||rpad(NVL(l_s_osuser,'<Niet =
gevuld>'),31,' ')
||rpad(temp_table_nextents,9,' ')
=
||rpad(round(temp_table_nblocks*l_n_db_block_size/(1024*1024)),9,' ')
||rpad(l_n_pct,6,' ')
);=20
total_used_extents :=3D total_used_extents + =
temp_table_nextents;
total_used_mb :=3D total_used_mb + =
(temp_table_nblocks*l_n_db_block_size/(1024*1024));
end if;=20
end if;=20
end loop;=20
end if;=20
end loop;=20
close c1;=20
if not gevonden
then
dbms_output.put_line(rpad('=3D',100,'=3D'));
dbms_output.put_line( rpad('SID',7,' ')
||rpad('Serial#',8,' ')
||rpad('Username ',31,' ')
||rpad('Osuser ',31,' ')
||rpad('Extents',9,' ')
||rpad('Mb',9,' ')
||rpad('%',6,' ')
);
end if;
dbms_output.put_line( rpad('.',77,' ')
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
||rpad('=3D=3D=3D=3D=3D',6,' ')
);
l_n_pct :=3D round((total_used_mb/l_n_max_mb)*100,1);
dbms_output.put_line( rpad('Total in use : ',77,' ')
||rpad(total_used_extents,9,' ')
||rpad(round(total_used_mb),9,' ')
||rpad(l_n_pct,6,' ')
);
dbms_output.put_line( rpad('.',77,' ')
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
||rpad('=3D=3D=3D=3D=3D=3D=3D=3D',9,' ')
||rpad('=3D=3D=3D=3D=3D',6,' ')
);
dbms_output.put_line( rpad('Maximum : ',77,' ')
||rpad(l_n_max_extents,9,' ')
Received on Thu May 18 2000 - 04:04:51 CDT
 |
 |