Exadata Cell Offload Smart Scan Features Enhancing Oracle Exadata Performance

YouTube video

In this article, we will explore the powerful features of Exadata Cell Offload and Smart Scan in Oracle Exadata Storage Server. These features play a crucial role in improving the performance of Oracle Exadata, and understanding how they work can greatly benefit database administrators and developers.

Exadata architecture has a unique design where the storage servers play a significant role in offloading resource-intensive workloads from the database server. Some of the key features that rely on the storage servers include Smart Flash Cache, hybrid columnar compression, storage indexes, and accelerator cell offloading.

Understanding Exadata Cell Offload

Exadata Cell Offload refers to the process of offloading resource-intensive workload from the database server to the storage server. In a conventional architecture, the data passes through a narrow CPU tunnel, which can lead to high CPU wait times and impact application performance. By moving the storage components exclusively into a storage server, Exadata addresses this issue.

One of the core components that enable cell offload is the Intelligent Database (IDB) protocol. Unlike conventional architectures, the IDB protocol allows the storage server to understand the type of data being stored, including minimum and maximum values. When a database transaction occurs, the IDB protocol communicates directly with the storage server, and the storage server retrieves the required data blocks for the database server. This eliminates the need for the data to pass through a narrow CPU tunnel, significantly improving database performance.

Exploring Accelerator Smart Scan

Accelerator Smart Scan is a feature that offloads SQL processing from the database server to the storage servers. When a SELECT statement is executed on a table, the Smart Scan feature forwards the operation to the storage server via the IDB protocol. The storage server then identifies the required data blocks for the SELECT statement and retrieves only those blocks, reducing resource contention on the database server CPU and improving performance.

Smart Scan works in three functionalities: predicate filtering, column projection, and join filtering.

Predicate Filtering

Predicate filtering involves pulling a subset of data based on a statement with a VAR condition. In a conventional architecture, the SELECT statement goes to the database instance, which identifies the required blocks and passes them through the storage layer. This means that all the data blocks (even those not required for the query) are pulled from storage and processed by the database instance, leading to high CPU usage.

With Accelerator Smart Scan, the SELECT statement is forwarded directly to the storage server, which filters out the required data blocks. This eliminates the need for unnecessary data retrieval and reduces CPU usage on the database server, resulting in improved performance.

Column Projection

Column projection focuses on retrieving specific columns from a table. In a conventional architecture, the entire row or column requested is pulled to the database instance, and the database instance performs all the operations. In Exadata, the SELECT statement is passed directly to the storage server, which identifies the requested columns and retrieves only those columns. This means that the database instance performs no additional work, improving performance significantly.

Join Filtering

Join filtering allows the storage server to perform join operations internally and filter data using bloom filtering. The entire SELECT statement passes through the storage server, where the storage server performs the necessary filtering and join operations. Only the filtered data is passed to the database server, reducing database IO operations and improving performance.

Prerequisites and Verification of Smart Scan

To enable Smart Scan, certain prerequisites must be met. The query must undergo a full table scan, and the underscore serial direct read parameter must be set to “always.” Additionally, the cell offload processing parameter must be set to “true.” These parameters ensure that the database directly reads from the storage server, enabling offloading of specific database operations to the storage servers.

To verify if a query is utilizing Smart Scan, you can check the execution plan of the query. If you see “table access storage full,” it indicates that Smart Scan is being used. Another way to determine Smart Scan usage is by using the V$SESSION_STAT view, where you can see the total eligible bytes for cell offload and the physical IO interconnect bytes written by the Smart Scan.

Conclusion

In conclusion, Exadata Cell Offload and Smart Scan are powerful features that significantly enhance the performance of Oracle Exadata. By offloading resource-intensive workloads and optimizing SQL processing, these features reduce CPU usage, improve database performance, and minimize IO operations. Database administrators and developers can benefit greatly from understanding and utilizing these features to maximize the efficiency of their Oracle Exadata deployments.

If you found this article useful, please consider subscribing to Tech TV for more informative content. Don’t forget to like and share this article on social media. Visit our blog site for further information and feel free to reach out to us with any queries or questions. We are always here to help you optimize your Oracle Exadata experience.

Thank you for being a part of Tech TV!