Skip to Main Content
  • Questions
  • Method to measure performance gain of clustered table vs non-clustered tables

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 07, 2021 - 1:18 pm UTC

Last updated: May 11, 2021 - 7:57 am UTC

Version: ORACLE DATABASE 19c Standard Edition 2 Release 19.0.0.0.0 Production

Viewed 1000+ times

You Asked

I have 2 pairs of parent and child tables ,1 pair is stored in a clustered object and the other is non-clustered. The primary key of the master table (which is foreign key) in the child table is clustered. An index on cluster is also created.

The structure of 2 the parent tables is identical and structure of 2 child tables is also identical. Records in the 2 pairs are also identical.

I want measure the performance gain of clustered tables vs non clustered table for SELECT statement.
I am using SET TIMING ON and printing the elapsed time after the SELECT is executed on the tables. The SELECT statement is also identical. I was expecting the elapsed time of clustered tables to be less than the non-clustered table, consistently. But it is the not. Can you please explain this?

Also , is there other way to measure the performance of non-clustered vs clustered, using auto trace or explain plan?

and Chris said...

It's hard to explain the reason for an effect without seeing exactly what you've done!

There's also no guarantee that changing the clustering will make the query faster. It depends on the data, your query, etc.

So with that in mind, here's some things to consider:

If statements you're running are fast - say under a tenth of a second - any gains you get from tweaking the physical layer will be minimal. Most of the processing time will go on sending the results over the network, displaying them in the client, etc.

Wall-clock run time can also be affected by what else is happening on the system at the time. For example, if the database server is starved of resources, your queries will take longer for reasons unrelated to your tweaks.

A more universal measure is the number of logical I/Os (buffer gets/consistent reads) a statement does. You can get this by running:

alter session set statistics_level = all;
set serveroutput off

select * from ...;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));


The Buffers column for the top line (ID 0) is the total for the query*.

Compare these for the different configurations. In general the query that does less logical IO will scale better.

If you want to dig further, you could use Tom's run_stats package to get more details on the resources each option uses.

You can find an enhanced version of this at https://github.com/oracle-developer/runstats

* assuming no scalar subqueries.

Rating

  (1 rating)

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

Comments

Scalar subqueries

Rajeshwaran Jeyabal, May 11, 2021 - 6:38 am UTC

Team,

what is the reason behind this disclaimer " * assuming no scalar subqueries ", could you please explain ?
Chris Saxon
May 11, 2021 - 7:57 am UTC

Scalar subqueries that aren't unnested (converted to a join) aren't included in the overall buffers total for line 0. You have to add these separately

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.