I spent some time analyzing the problem, why some queries, despite a
good execution plan and use of the Exadata (smart scan) functionality, are
slow.
Lack of WAIT cell smart table scan
In execution plan ( what is most important for database) we see that
full scan table WITH SMART SCAN functionality is used on table TABLE and for rest is used join with indexes (with index fast full scan).
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 933K(100)| |
| 1 | SORT ORDER BY | | 22M| 917M| 1113M| 933K (38)| 00:05:02 |
| 2 | HASH JOIN RIGHT SEMI | | 22M| 917M| 23M| 720K (45)| 00:03:53 |
| 3 | VIEW | ViEW_1 | 1316K| 9000K| | 201K (49)| 00:01:05 |
| 4 | HASH JOIN | | 1316K| 77M| 84M| 201K (49)| 00:01:05 |
| 5 | TABLE ACCESS STORAGE FULL | TABLE_1 | 1316K| 69M| | 182K (48)| 00:00:59 |
| 6 | INDEX STORAGE FAST FULL SCAN| PK_TABLE_2 | 28M| 191M| | 5398 (83)| 00:00:02 |
| 7 | INDEX FAST FULL SCAN | NPI_INC_COMPRESS_1 | 488M| 16G| | 162K (63)| 00:00:53 |
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 933K(100)| |
| 1 | SORT ORDER BY | | 22M| 917M| 1113M| 933K (38)| 00:05:02 |
| 2 | HASH JOIN RIGHT SEMI | | 22M| 917M| 23M| 720K (45)| 00:03:53 |
| 3 | VIEW | ViEW_1 | 1316K| 9000K| | 201K (49)| 00:01:05 |
| 4 | HASH JOIN | | 1316K| 77M| 84M| 201K (49)| 00:01:05 |
| 5 | TABLE ACCESS STORAGE FULL | TABLE_1 | 1316K| 69M| | 182K (48)| 00:00:59 |
| 6 | INDEX STORAGE FAST FULL SCAN| PK_TABLE_2 | 28M| 191M| | 5398 (83)| 00:00:02 |
| 7 | INDEX FAST FULL SCAN | NPI_INC_COMPRESS_1 | 488M| 16G| | 162K (63)| 00:00:53 |
------------------------------------------------------------------------------------------------------------
Reality looks a bit different. During execution of this query with
default execution plan (with smart scan) I saw that smart scan is not used!!!
---------------------------------------------------------------------------------------------
Active% | SQL_ID |
SQL_CHILD |
EVENT
| WAIT_CLASS
---------------------------------------------------------------------------------------------
58% | 2rvn92mjxr7pk |
0 | ON
CPU
| ON CPU
42% | 2rvn92mjxr7pk |
0 | cell multiblock physical
read | User I/O
Lack of WAIT cell smart table scan
Why? If in the execution plan is smart scan but smart scan is not used!
This part of my analysis took most of the time: reason of this behavior
When I forced this query to use only full scans on all tables (without
indexes) then on session I saw :
1558, SYS , TIME, DB
CPU
,
3798473, 759.69ms, 76.0%, [@@@@@@@@
],
, ,
1558, SYS , TIME, sql
execute elapsed
time
, 4004145,
800.83ms, 80.1%, [######## ],
, ,
1558, SYS , TIME, DB
time
, 4004145,
800.83ms, 80.1%, [######## ],
, ,
1558, SYS , WAIT, cell
smart table
scan
, 244890,
48.98ms, 4.9%,
[W
], 443,
88.6, 552.8us
And query finished 4 times faster than using default execution plan.
The reason of this behavior is little tricky. I found that if in the
execution plan is used (INDEX FAST FULL SCAN) on compressed index then the
smart scan cannot be used in all of this query!!! Also if in execution
plan database show that smart scan is used:
Smart Scans On Compressed Indexes (Doc ID 1561260.1)
And we have this situation for problematic query. Index NPI_INC_COMPRESS_1 is a compressed index and database in execution plan decide
to use INDEX FAST FULL SCAN on this index and after that FULL_SCAN (SMART SCAN)
on TABLE_1 is not used but execution plan shows that is .
Solution for that is add hints FULL on all tables
used in this query or change this index to uncompressed . After that smart scan feature is used.
What is really interesting that if index fast full scan is used then is
more than 10 conditions when smart scan is not really used if the even
execution plan says otherwise (details in 1561260.1)