jump to navigation

Question: Anything Wrong With Query Performance? (Straight To You) April 5, 2018

Posted by Richard Foote in Oracle Indexes, Performance Tuning.

nick cave

I have a query that runs pretty darn efficiently, here’s the setup:

SQL> create table major_tom (id number, code number, name varchar2(42));

Table created.

SQL> insert into major_tom select rownum, mod(rownum,2000)+1, 'DAVID BOWIE'
from dual connect by level  commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'MAJOR_TOM');

PL/SQL procedure successfully completed.

SQL> create index major_tom_code_i on major_tom(code);

Index created.

SQL> select * from major_tom where code=42;

1000 rows selected.

Elapsed: 00:00:00.00

Execution Plan
Plan hash value: 4132562429

| Id | Operation                   | Name             | Rows | Bytes | Cost (%CPU) | Time     |
|  0 | SELECT STATEMENT            |                  | 1000 | 21000 |    1005 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID | MAJOR_TOM        | 1000 | 21000 |    1005 (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
 1006 consistent gets
    0 physical reads
    0 redo size
26208 bytes sent via SQL*Net to client
  607 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
1000 rows processed

So the query basically returns 1000 rows based on the CODE column and it does so using an index on CODE. The CBO has got the costings for this just about spot on. For 1000 rows returned, it does so with just 1006 consistent gets, which if you consider the index blocks that need to be accessed and the 1000 rows accessed, all seems quite reasonable.

If you look at the elapsed time of just 00:00:00.00, well you can’t really beat that.

None of the user base is complaining, users are more than happy with this performance.

So the question I have is why on earth would a DBA complain about the performance of this query?

Note: if you’ve attended my “Oracle Indexing Internals and Best Practices” seminar, you’re not allowed to answer 🙂


1. REIS - April 5, 2018

1000 blocks to retrieve 1000 rows – too much blocks processed. A single execution won’t cause any trouble. Too many concurrent executions could lead to a CPU consumption issue.


2. ricsi - April 5, 2018

As REIS said too much blocks; DBA_INDEXES.CLUSTERING_FACTOR is the same as the number of rows, so a reorganize (CLUSTERING BY LINEAR) on MAJOR_TOM.CODE column should solve the problem (so clustering should be better by several scales.


kcrum2011gmailcom - April 5, 2018

Alternatively, you could:

– Use an IOT so that the clustering factor takes care of itself (code would need to be incorporated into the primary key)


– Widen your index to include all selected columns, so that no table access is necessary. Which… In this case, that would be all columns. So if you’re going to do that, might as well use an IOT so that you have fewer segments to worry about.


REIS - April 6, 2018

Would indeed avoid table blocks being accessed by IFFS, but perhaps still too much blocks to process as number of blocks in the index increases as columns are added. Most probable cause, the index segment is fragmented.


3. ricsi - April 5, 2018

Ok, w/o testing CLUSTERING BY… only tested CTAS w/ ORDER BY code, which had good CLUSTERING_FACTOR.


ricsi - April 5, 2018

Finally tested CLUSTERING BY (ALTER TABLE major_tom MOVE is important), it’s the same CLUSTERING_FACTOR as CTAS w/ ORDER BY.


4. rastl - April 6, 2018

At least I would not argue about my arraysize (prefetch) setting with my dba.
You inspire me, in a creepy kind of way.


5. Jose Rodriguez - April 7, 2018

Damn! I think this may be the first quiz I may be able to answer and but just because I attended your seminar 😋


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: