Exadata Smart Scan feature and fast full scan on indexes

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.

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 |
------------------------------------------------------------------------------------------------------------

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)

RMAN - transfer archivelog from primary database to standby

rman target /@rman_prm catalog [catalog]@rmancat auxiliary /@rman_stb

backup as copy archivelog sequence between  5643  and 5683   thread 2  auxiliary format '+RECOC1';

Checking most heavy queries every snapshot and system statistics

Most important system statiscts/performance overview:

SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
ORDER BY 1;

Output:

Most heavy queries:

SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)
AND t.PARSING_SCHEMA_NAME NOT                  IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;

Output: