Forum Stats

  • 3,781,161 Users
  • 2,254,485 Discussions
  • 7,879,597 Comments

Discussions

How testing impact of index during insert

1046786
1046786 Member Posts: 23
edited Mar 27, 2014 9:00AM in General Database Discussions

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!!

1046786

Answers

  • 558383
    558383 Member Posts: 6,961
    edited Mar 27, 2014 5:49AM

    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 ?).

    1046786558383
  • Simo Kemppinen
    Simo Kemppinen Member Posts: 113
    edited Mar 27, 2014 6:08AM

    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.

    Simo Kemppinen
  • 1046786
    1046786 Member Posts: 23

    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
    1046786 Member Posts: 23

    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.

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

    Simo Kemppinen
  • 558383
    558383 Member Posts: 6,961
    edited Mar 27, 2014 7:53AM

    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
    JohnWatson Member Posts: 2,461 Gold Trophy

    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.

    1046786JohnWatson
This discussion has been closed.