Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 2 Mar 2003 17:20:33 GMT
Message-ID: <b3teh1$1qf0nb$2@ID-82536.news.dfncis.de>

> Thanks for all the feedback from everyone, it's looks like GT's should be
> avoided at certain times. So the real question is this. How do I avoid
> using them in PL/SQL? It seems like I could use a memory-only structure
> like Nested Tables or Varrays but what happens when 15 thousand rows (or as
> high as 500k) are inserted into the memory-only structure?

Im not sure, what exactly happens (that is how efficient it is), but the database can definitely handle it. I have written a test that you might find useful:

First, redo_diff is a package to measure the redo generated:

   create or replace package redo_diff as      procedure diff_it;
   end;
   /    

   create or replace package body redo_diff as      s number;    

     function get_size return number is
       s_ number;
     begin
       select value into s_ from sys.v_$sysstat where name = 'redo size';
       return s_;
     end get_size;
    
     procedure diff_it is
       s_new number;
     begin
       s_new := get_size; 
       dbms_output.put_line('redo diff: ' || to_char(s_new - s));
       s    := s_new;
     end diff_it;
   
     begin
       s := get_size;

   end;
   /    

Here's the more interesting thing:   

  create or replace type subst_ as object (     rn number,
    ob varchar2(128)
  );
  /   

  create or replace type subst_t_ as table of subst_;   /   

  declare
    t subst_t_;

    time1_ number;
    time2_ number;
    sz1_   number;
    sz2_   number;

  begin   

    redo_diff.diff_it;   

    time1_ := dbms_utility.get_time;

• Filling 500000 records select cast( multiset( select rownum,a.object_name from all_objects a,all_objects b,all_objects c,all_objects d where rownum <= 500000) as subst_t_) into t from dual;

    sz1_ :=t.count;
    time2_ := dbms_utility.get_time;   

    dbms_output.put_line(

              'filled ' || sz1_ || 
              ' records, time used: ' ||
              to_char((time2_ - time1_)/100, '99999.00') || ' secs');
  
  

    redo_diff.diff_it;      

• deleting approx 50%

    select cast (

      multiset(
        select 
          rn,ob 
        from 
          table(cast (t as subst_t_))
        where
          substr(ob,1,2) > 'DB'
        ) as subst_t_
        ) into t from dual;
  

    sz2_ :=t.count;
    time1_ := dbms_utility.get_time;
    dbms_output.put_line(

              'deleted ' || to_char(sz1_ - sz2_) || 
              ' records, time used: ' ||
              to_char((time1_-time2_)/100, '99999.00') || ' secs');
  
  

    redo_diff.diff_it;   

  end;
  /   

  drop type subst_t_;
  drop type subst_;      

Try it out, and let me know about your experiences.   

hth
Rene Nyffenegger

-- 
  no sig today

Received on Sun Mar 02 2003 - 11:20:33 CST