Skip to Main Content
  • Questions
  • Monitoring and measuring index overhead on table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: April 09, 2020 - 3:34 pm UTC

Last updated: April 21, 2020 - 8:07 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello, Ask Tom Team.

I have a table with four indexes. I have been asked to monitor and measure index overhead on the table by DML operations.

1. How can I accomplish this?

Thanks in advanced.

Regards.

and Chris said...

From 12.2 index monitoring is enabled out-of-the-box. You're on 18c. So to see which queries use your indexes and how, all you need to do is query DBA_INDEX_USAGE.

Read more about this here https://oracle-base.com/articles/12c/index-usage-tracking-12cr2

For the effect on writes, use a procedure like this:

create or replace procedure test_indexes ( num_runs int ) as
  start_time pls_integer ;
  time_taken number ;
begin
  start_time := dbms_utility.get_time ();
  for i in 1 .. num_runs loop
    
    /* YOUR DML STATEMENTS HERE */

  end loop;
  time_taken := ( dbms_utility.get_time () - start_time ) / 100;
  dbms_output.put_line ( 
    'Total duration: ' || time_taken || ' seconds; ' ||
    ' seconds/execution: ' || ( time_taken / num_runs )
  ) ;
end test_indexes;
/


Run this a few times to get average execution times. Then add/remove indexes to see what impact they have:

exec test_indexes ( 10000 );
truncate table t;
exec test_indexes ( 10000 );
truncate table t;
exec test_indexes ( 10000 );

-- DROP/CREATE INDEXES and repeat

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo, April 21, 2020 - 2:08 pm UTC

Thanks for the help.

Regards,

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.