Question: Anything Wrong With Query Performance? (Straight To You) April 5, 2018
Posted by Richard Foote in Oracle Indexes, Performance Tuning.trackback
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)
Statistics
----------------------------------------------------------
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 🙂
Richard Foote's Oracle Blog 
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.
LikeLike
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.
LikeLike
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)
Or!
– 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.
LikeLike
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.
LikeLike
Ok, w/o testing CLUSTERING BY… only tested CTAS w/ ORDER BY code, which had good CLUSTERING_FACTOR.
LikeLike
Finally tested CLUSTERING BY (ALTER TABLE major_tom MOVE is important), it’s the same CLUSTERING_FACTOR as CTAS w/ ORDER BY.
LikeLike
At least I would not argue about my arraysize (prefetch) setting with my dba.
You inspire me, in a creepy kind of way.
LikeLike
Damn! I think this may be the first quiz I may be able to answer and but just because I attended your seminar 😋
LikeLike