jump to navigation

Answer: Anything Wrong With Query Performance? (Red Right Hand) April 11, 2018

Posted by Richard Foote in 12c, Attribute Clustering, Clustering Factor, Oracle Indexes.
add a comment

red right hand

I of course attract a highly clever readership :). As some have commented, for a single table to require 1000+ consistent gets to retrieve 1000 rows implies that each row needs to be accessed from a different block. This in turn implies the Clustering Factor for this index to be relatively bad and the associated index relatively inefficient.

If this query is very infrequently executed, then no real damage done and the index is likely a better alternative than a Full Table Scan.

However, if this query was executed very frequently (maybe 100’s of times per second), if this query featured as one of the top consuming CPU queries in an AWR report, then you could be burning more CPU than necessary. Maybe a lot lot more CPU…

Improving database performance is of course desirable but reducing a significant amount of CPU usage is always a good thing. For a start you usually pay database licenses and cloud subscriptions based on CPU consumption. The less CPU your systems use, the more head-room you have in case anything goes wrong as running out of CPU usually means performance hell for your database systems. Less CPU means more time until you need to update your infrastructure, more database systems you can run in your current environment, more time until you need to pay for more database licenses, more time until you have to increase your cloud subscriptions etc.

I have assisted many customers in significantly improving performance, in delaying IT investments costs by significantly reducing CPU wastage. Often this is based on improving queries that individually perform adequately and often when the number of rows to number of consistent gets/logical reads ratios appear OK.

So in this particular example, although things are currently deemed hunky dory,  this query can potentially be significantly improved. The root issue here is an index that has a terrible Clustering Factor being used to retrieve a significant number of rows, while being executed a significant number of times.

If we look at the current Clustering Factor:

SQL> select index_name, clustering_factor from user_indexes

where table_name='MAJOR_TOM';

-------------------- -----------------
MAJOR_TOM_CODE_I               2000000

At 2000000, it’s about as bad as it can get.

As I’ve discussed previously, Oracle now has a nice way of being able change the clustering of a table by adding a Clustering Attribute to a table (12.1) and by the reorganising the table online (12.2):

SQL> alter table major_tom add clustering by linear order(code);

Table altered.

SQL> alter table major_tom move online;

Table altered.

If we look at the Clustering Factor of the index now:

SQL> select index_name, clustering_factor from user_indexes where table_name='MAJOR_TOM';

-------------------- -----------------
MAJOR_TOM_CODE_I                  7322

It’s now about as good as it can get at just 7322.

If we now re-run the “problematic” query:

SQL> select * from major_tom where code=42;

1000 rows selected.

Execution Plan
Plan hash value: 4132562429

| Id | Operation                   | Name             | Rows | Bytes | Cost (%CPU) | Time     |
|  0 | SELECT STATEMENT            |                  | 1000 | 21000 |       9 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID | MAJOR_TOM        | 1000 | 21000 |       9 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN            | MAJOR_TOM_CODE_I | 1000 |       |       5 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2 - access("CODE"=42)

    0 recursive calls
    0 db block gets
   12 consistent gets
    0 physical reads
    0 redo size
26208 bytes sent via SQL*Net to client
  608 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
 1000 rows processed

The number of consistent gets has plummeted from 1006 to just 12, which is about as good as it gets when retrieving 1000 rows.

Of course the impact this change has on other queries on the table based on other columns needs to be carefully considered. But we have now potentially significantly reduced the overall CPU consumption of our database (especially if we tackle other problem queries in a similar manner).

If you have attended by “Oracle Indexing Internals and Best Practices” seminar, you already know all this as this is one of many key messages from the seminar 🙂