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?
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 ...;
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
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.