Re: How to tune this stored proc?

Hi.

 Allow me to disagree.
 The result depends on a number of factors :

1. Row length - affects the number of blocks Oracle has to visit.
2. Number of records deleted in one LUW ( Logical Unit of Work ) - 4000 in that case - affects the number of passes via the table. You are correct when dealing with limited number of LUWs and short rows. I run your test with following results : ( I used text field of VARCHAR2(100) and run 3 tests
3. Data length - 10 bytes ( as in your example )
4. - 40 bytes
5. 100 bytes +----------+----------+------------+---------------+ | LUW size | Row size | With Index | Without Index | +----------+----------+------------+---------------+ 4000 Date+10 46.83 sec 25.18 sec 4000 +40 51.88 34.24 4000 100 62.00 59.41 +----------+----------+------------+---------------+ As you can see - the times without index usage are more affected by actual row size. The execution times with index are more stable. Now I tried you test with 160 rows deleted in one LUW ( to simulate 145 FULL passes through the table : 580000 / 4000 = 145 ). +----------+----------+------------+---------------+ | LUW size | Row size | With Index | Without Index | +----------+----------+------------+---------------+ 160 Date+100 62.28 sec 224.74 sec

  Againg the execution time with index remains stable,   but without index it increases ( by factor of 4 ).

  The solution is either to use index or increase   ( drastically ) LUW size.

  Michael.

> Many people, even experienced veterans believe that
> indexes are panaceas, the solution of all performance issues.
> Of cause, it's not.
>
> OK, assuming the column values are homogeneous dispersion,
> use the following script to test deleting 1/8 of rows.
> I only tested for 200,000 rows. In my Oracle8i database,
> it took 20.15 sec without index, but 35.10 sec using a index.
> (I didn't test for 4,500,000 rows.)
>
> The following is the script.
>
> ---- begin script ----
> set serveroutput on;
> set feedback off;
> create table test (p_date date, text varchar2(20));
> prompt the primitive way
> truncate table test;
> declare
> t date := trunc(sysdate);
> begin
> for i in 1..200000 loop
> insert into test values (t+dbms_utility.get_hash_value(i, 0, 8),
> 'Testing'||i);
> if mod(i, 2000)=0 then
> commit;
> end if;
> end loop;
> commit;
> end;
> /
> declare
> t1 number;
> t2 number;
> t date := trunc(sysdate);
> begin
> t1 := dbms_utility.get_time();
> loop
> delete from test where p_date=t and rownum<=4000;
> if sql%notfound then
> exit;
> else
> commit;
> end if;
> end loop;
> commit;
> t2 := dbms_utility.get_time();
> dbms_output.put_line((t2-t1)/100);
> end;
> /
>
> prompt using index
> truncate table test;
> create index ind_test on test(p_date);
> declare
> t date := trunc(sysdate);
> begin
> for i in 1..200000 loop
> insert into test values (t+dbms_utility.get_hash_value(i, 0, 8),
> 'Testing'||i);
> if mod(i, 2000)=0 then
> commit;
> end if;
> end loop;
> commit;
> end;
> /
> declare
> t1 number;
> t2 number;
> t date := trunc(sysdate);
> begin
> t1 := dbms_utility.get_time();
> loop
> delete /*+ index(test ind_test) */ from test
> where p_date=t and rownum<=4000;
> if sql%notfound then
> exit;
> else
> commit;
> end if;
> end loop;
> commit;
> t2 := dbms_utility.get_time();
> dbms_output.put_line((t2-t1)/100);
> end;
> /
> drop index ind_test;
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 16 1999 - 03:47:31 CDT