Exadata Smart Scan

Feed:
To query specific requirements:
Smart Scan is only available for full table or index scans.
Smart Scan can only be used for direct path reading:
Direct path reads are automatically used for parallel queries.
Direct path reading can be used for serial queries.
They are not used by default for serial scanning of small tables.
Use _serial_direct_read=true to enforce direct path reads.

In order to query the advantages of Exadata offload capability, the optimizer must decide to use a full table scan or a fast full index scan to execute the statement, where the wording is more general, in general, these two words correspond to the execution Plan of table ACCESS full and index fast SCAN. In Exadata, the naming of these similar operations has changed slightly to indicate that the Exadata storage is being accessed. The new operation name is table ACCESS STORAGE full and index STORAGE FAST full SCAN. Note that there are also subtle changes, such as the Mat_view ACCESS STORAGE full event, which also indicates that smart scanning can be used. But you should know that, in fact, even if the execution plan shows the TABLE ACCESS STORAGE full operation, it does not mean that the query will necessarily perform a smart scan, it simply means that the premise is satisfied. We will discuss later in this chapter how to verify that a statement actually implements an unload operation through a smart scan.

What does direct path reading mean? Here are two paragraphs to explain
In addition to a full scan, the smart scan requires read operations to be performed through the Oracle direct path read mechanism. Direct path reading has been around for a long time, and traditionally, this reading mechanism is used by the dependent process (Slave) that serves parallel queries. Because parallel queries are initially expected to be used to access very large amounts of data (often too large to be put into Oracle buffers), the parallel dependent process is decided to read the data directly and then into its own memory (also known as the program Global Zone or PGA). The direct path-reading mechanism completely skips the standard Oracle caching mechanism that puts a database into a buffer, which is very effective for large amounts of data, because it eliminates the extra work that is not helpful (caching the data that the full-table scan obtains but which may not be reused). Let these blocks not flush other chunks of data out of the buffer. As we mentioned earlier, the Kcfis (Kernel File Intelligent Storage) function is called by the Kcbldrget (Kernel Block Direct Read GET) function, so The smart scan executes only when the direct path reading mechanism is used.

In addition to parallel dependent processes, direct path reads may also be used in non-parallel SQL statements as long as conditions permit. There is an implied parameter _serial_direct_read can control this function. When this parameter is set to the default auto, Oracle automatically determines whether to use direct path reads for non-parallel scans. Calculations are based on several factors, including the object size, Buffer cache size, and how much of the object's data block already exists in the buffer cache. In addition, there is an implicit parameter (_small_table_threshold) that defines how large the table should be at least if it is to be read using a serial direct path. The algorithm for non-serial scanning to decide whether to use the direct path reading mechanism is not public, although the function of serial direct path reading already exists, but only recently become more common phenomenon. Oracle database 11GR2 has made some modifications to calculate whether to use direct path reading for non-parallel scans, and the newly modified algorithm enables Oracle 11GR2 to take direct path reads more frequently than previous database versions. This may be because of the Exadata Smart scan, so you want to trigger direct path reads as much as possible, but such algorithms may be slightly aggressive on non-exadata platforms.

Note My Oracle support documentation: 793845.1 contains the following representations.
In 11g, we made exploratory changes about whether to use direct path reads or cache reads in a serial table scan. In 10g, serial scanning for large tables is cached by default, and in 11g, the decision is to read directly or through the cache, based on the table size, buffer size, and a variety of other statistical information. Because the latch is avoided (latche), direct path reads are faster than discrete reads (scattered read) and have less impact on other processes.

The difference between a full index scan and a fast full index scan:

Full Index Scan: This performance feeling is the worst, only a single block of reading, but also to get the data through the table, but because he is indexed in order to read, so no longer need to sort. For example, select * FROM table order by name, if there is an index on name, obviously this time there are usually 2 plans, first, full table scan, then sort, and in another case, a full index scan will be OK. Because it doesn't need to be sorted at this time.
The index full scan is based on the leaf node chain. Index full scan first to start from the root, find the first data block on the leaf node chain, and then scan along the leaf node chain, because the leaf node chain is sorted according to the index key value, so the scanned data itself is sorted, the data read out no need to re-order. This scanning method is the first to find the root of the index, and then to find the first leaf node through the minor points, and then scan the entire index along the leaf node chain, compared to the index fast full scan. The IO cost of the index full scan is much larger than the index fast full scan, and the cost of reading the root and leaf nodes is relatively small, but because it is not possible to use multiple blocks of reading while scanning the entire index along the leaf node chain, only a single block of read can be used, so the IO overhead of this scan is much larger than that of the index This index scan, if we trace the session, we will find a large number of DB file sequential read wait.

Fast Full Index Scan:
First of all, this scan does not need to get the data through the table. This is the point. Then, without data from the table, it is clear that the data can only be obtained by indexing. That's even more obvious. The field you want to take must be in the index, and how does the person not pass the table to the data? Usually for example: select name from table; If there is an index on name, it is possible to go to the FFS. However, if the ID is not indexed, it is impossible to walk the FFS in any way, if it is a select name,id from table. A scan of an index can be performed based on the extent of the index, using multiple blocks of read. So in this kind of operation, we can see that the session will appear in a large number of db file scattered read waits.
The latter is the way we want the index scan to appear on the Exadata

Exadata Smart Scan