JL Computer Consultancy

Who is sorting how much in Oracle 7

December 2001


Acknowledgements: After showing this script for a couple of years on the website, I can now reveal the name of the Author, thanks to an email I received a short while ago. The code was originally written by Roger Saunders of Oracle UK, with some subsequent enhancements and documentation from Kyle Hailey of Oracle France. See http://oraperf.sourceforge.net/find_tmp/index.html and an interesting little note at http://oraperf.sourceforge.net/find_tmp/other.html

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;
/

Sample of Output

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;'

 

Back to Main Index of Topics