How testing impact of index during insert

1046786

Hi all,

I have to test if adding a particular index on a table, has some efforts or impacts on Insert queries.

How can I do it?

I'm thinking about 2 ways:

1) Create a small stored procedures that insert 2k rows (for example), and retrieve the execution time with/without the index

2) Do the point 1) using some better tools like DBMS_PROFILER to get a more precise execution time...

That's all: have you got any suggestions? Should I do some work adding/removing the index? I'm thinking about update some Oracle statistics...

Thank you so much!!

558383

In general adding an index on a table will have some impact on INSERT, UPDATE and DELETE statements run on the related table.

You can try to use T. Kyte run_stats.sql http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701  tool for this. Running an application load test should also be considered.

DBMS_PROFILER should only be used to analyze PL/SQL code not SQL code: to analyze SQL code use SQL trace with DBMS_MONITOR and tkprof ORACLE-BASE - SQL trace, 10046, trcsess and tkprof in Oracle

If you are running Oracle 11.2 Oracle will automatically gather DBMS_STATS statistics on the created index (unless it is a function-based index ?).

Simo Kemppinen

Here is sample how to create test table and add data into it:

--

CREATE TABLE TEST_STAT.STATS

(col1 varchar2(20),

col2 number )

TABLESPACE TEST_STAT;

begin

for i in 1..10000

loop

insert into TEST_STAT.STATS values(DBMS_RANDOM.STRING('U',20), DBMS_RANDOM.VALUE(1,1000));

end loop;

end;

/

--

If you run this from sqlplus set following on before it (you'll get elapsed time):

set timing on

And good way to test indexes without affecting other database sessions is to use invisible indexes.

Look more for example here:

Invisible Index Oracle 11.1 and above.

1046786

Thank you,

I'm on Oracle 10.2, and the index is function based! So do I need to update the statistics manually? How can I do that?

About DBMS_PROFILER I was thinking to create a stored procedure PL/SQL with a LOOP that creates the new rows...

I will look at your link, thank you so much!

1046786

Thank you, unfortunatly I'm on Oracle 10.2, so I cannot use invisible index

Yeah, I'm already using a LOOP to insert the new rows... But I was running it on Toad and retrieve the execution time.

Maybe it's better to put it in a stored procedure and run it using sql plus adding "set timing on" as you said..Thank you.

Simo Kemppinen

Yes it is very wise to update statistic after creating index or dropping it.

That way optimizer knows best how it should work.

You can do it for example this way:

analyze table <your_table_name> compute statistics for table for all indexes;

Example:

analyze table TEST_STAT.STATS compute statistics for table for all indexes;

558383

To use DBMS_STATS to gather statistics for a function based index see Richard Foote blog post:

Function-Based Indexes and Missing Statistics (No Surprises) | Richard Foote&amp;#039;s Oracle Blog

JohnWatson

orclz>

orclz> set timing on

orclz> create table t1(c1 date);

Table created.

Elapsed: 00:00:00.00

orclz> insert into t1 select sysdate from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:01.28

orclz> drop table t1;

Table dropped.

Elapsed: 00:00:00.20

orclz> create table t1(c1 date);

Table created.

Elapsed: 00:00:00.00

orclz> create index i1 on t1(c1);

Index created.

Elapsed: 00:00:00.01

orclz> insert into t1 select sysdate from dual connect by level < 1000000;

999999 rows created.

Elapsed: 00:00:08.83

orclz>

Seven times slower with the index.