Test case when index size can grow than table size and effect of rebuild index

Posted on by debasis maity

Test case preparation

 

I am going to create test case to simulate different index test cases

 

Test case 1:-First we create procedure to parallel insert in 10 sessions.The insert query will insert data based on random values generated from select query.Please note I will run gather stats after every test case.

 

SQL>create table t(id number,sometext varchar2(50),mydate date);

SQL>CREATE OR REPLACE procedure TEST.manyinserts as
v_m number;
begin
for i in 1..1000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/

 

SQL>CREATE OR REPLACE procedure TEST.manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

SQL> create index I1_t1 on t(ID) pctfree 0;

SQL> create index I2_ti on t(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,'T');
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='T';

SUM(BYTES)/1024/1024/1024

————————-

.7734375

Index statistics

 

Col INDEX_NAME format a10;

Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

Size of index and table

 

select bytes/1024/1024/1024,segment_name from user_segments where segment_name in ('I1_T','I2_T','T');
BYTES/1024/1024/1024 SEGMENT_NA
——————– ———-
            .265625 I1_T
           .8671875 I2_T
           .7890625 T

Test case 2:-Second we create procedure to  insert serially.

 

SQL>create table t1(id number,sometext varchar2(50),mydate date);
SQL>CREATE OR REPLACE procedure TEST.manyinserts1 as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t1 values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/

SQL> create index I1_t1 on t1(ID) pctfree 0;

SQL> create index I2_ti on t1(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,’T’);

 

SQL> Set linesize 300;

INDEX STATISTICS

 

SQL> Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

INDEX_NAME PCT_INCREASE   PCT_FREE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
———-         ————           ———-   ———- ———–         —————–               ———-
I1_T                                                      0 2            20296              9402633                       9589796
I2_T                                                    10 2            85153              10138784                     10138858

INDEX AND TABLE SIZE

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NAME
——————– ——————————————————————————————————————————–
           .6484375 I2_T
           .7890625 T
           .1640625 I1_T

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=’T’;

SUM(BYTES)/1024/1024/1024
————————-
              .7734375

Test case3:-Now I will run some select statement in parallel 10 sessions.I forcefully making index on column ID.THE TABLE I SELECTED WHERE I INSERTED DATA PARALLELY.

CREATE OR REPLACE procedure TEST.manyselect as
v_m number;
begin
for i in 1..100 loop
select count(id) into v_m from t;
end loop;
end;
/
CREATE OR REPLACE procedure TEST.manysessions_select as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyselect;’, sysdate);
END LOOP;
commit;
end;
/

 

Exec TEST.manysessions_select;

Now let me check SQL statistics and buffer read from AWR

 

Test case 4:-Now I will run update in parallel 10 sessions

 

CREATE OR REPLACE procedure TEST.manyupdates as
v_m number;
begin
for i in 1..100000 loop
 select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
 update t set id=v_m/15*i where rownum<10;
commit;
end loop;
end;
/

 

CREATE OR REPLACE procedure TEST.manysessions_update as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyupdates;’, sysdate);
END LOOP;
commit;
end;
/

 

Exec TEST.manysessions_update;

Table statistics

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.265625 I1_T

.8671875 I2_T

.7890625 T

Index statistics:-

 

Test case 5:-Now I will run SELECT AGAIN

 

Exec TEST.manysessions_select;

TEST case 6:-Let me rebuild index on column ID

 

SQL> alter index I1_t rebuild;

Index altered.

TABLE and INDEX size

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.1875 I1_T

.8671875 I2_T

.7890625 T

Index statistics

 

Test case 7:-Let me run the select again in parallel

 

Exec TEST.manysessions_select;

Observations:-

 

Please note that select query was taking more time (3.07 s) after bulk update which I think was due to temp area usage (see segments by logical read section in awr) .AFter index reuild the query response time (0.30 s)improved.

 

select query which traverses index I1_T Elapsed time/exec Buffer gets
Before bulk update 0.35 35809
After bulk update 3.07 41832
after rebuild index 0.30 23704

 

Also please note index size I2_T is more in size than table T.Index rebuild reduced the index size though cluster factor remains same.

 

TABLE_SIZE IN GB INDEX SIZE I1_T IN GB INDEX SIZE I2_T in GB CLUSTER FACTOR I1_T CLUSTER FACTOR I2_T
PARALLEL INSERT .7890625 .265625 .8671875 9676232 10117165
SERIAL INSERT .7890625 .1640625 .6484375 9402633 10138858
PARALLEL UPDATE ON COLUMN ID .7890625 .265625 .8671875 10262755 9399026
AFTER REBUILD I1_T .7890625 .1875 .8671875 9999904 9399026

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>