Smart Scan cannot offload processing of user-defined functions (UDFs) to storage cells. When a WHERE clause contains a UDF, the entire data set must be returned to the database server for evaluation, dramatically reducing offload efficiency. This is a common real-world performance pitfall. While other options can impact performance, a UDF in the predicate is the most direct and common cause of a sudden drop in Smart Scan efficiency for a specific query.

The correct method is to use the `STORAGE (FLASH_CACHE KEEP)` clause at the table, materialized view, or tablespace level. This instructs the Exadata Smart Flash Cache to retain the object’s blocks, effectively “pinning” them. This is a database-managed, declarative approach. Manually pinning via CELLCLI is not the standard or managed method. While a keep pool exists for the smart flash log, there is no ‘keep size’ configuration for general caching. Relying only on the LRU algorithm does not guarantee persistence for critical objects.

The documented and recommended order is to patch Oracle Grid Infrastructure (including ASM) first, followed by the database software. This is because the database software often has dependencies on the Grid Infrastructure libraries and services. Patching in the reverse order can lead to incompatibility issues. Shutting down all databases for a combined patch is overly disruptive and not the standard rolling or minimal-downtime approach. Patching in a mixed, node-by-node fashion is not standard and can lead to a split-version cluster, causing instability.

Exadata’s I/O Resource Management (IORM), a function of the Database Resource Manager, is specifically designed for this scenario. It allows you to define interdatabase and intradatabase resource plans that control the allocation of I/O bandwidth (and, on later models, flash cache) between competing workloads. This can guarantee minimum I/O for the OLTP database while limiting the impact of the batch aggregation jobs. Increasing the buffer cache helps with logical reads but not with physical I/O contention. Moving to a different machine is costly and not a software feature. Smart Flash Logging helps redo writes but doesn’t manage resource contention between two active workloads.

The key observation is that almost all eligible bytes ARE being offloaded (high savings), yet there is still a high wait time for the smart scan event. This points to the offload processing itself being the bottleneck. The work is successfully shifted to the storage cells (good offload efficiency), but the cells are spending considerable time executing that work, causing the database server to wait. This is a classic sign that the storage cell CPUs might be busy or the specific offload processing (e.g., decompression, predicate evaluation) is intensive for the given data set. It is not a network issue (data is not being shipped back) nor a sign of optimal performance (high wait time is problematic).

The recommended practice is to use a separate set of tablespaces (e.g., DATA, INDEX, TEMP) for each PDB. This is typically achieved by using the `FILE_NAME_CONVERT` clause or OMF with PDB-specific directories. This organizes files logically by PDB, simplifying operations like cloning, migrating, or recovering a single PDB. Using one giant disk group for everything loses logical separation. Dedicated disk groups per PDB is wasteful and complex, as Exadata IORM can manage isolation at the software level. Mixing SYSTEM/SYSAUX files in one location is not standard and complicates PDB mobility.

The first diagnostic step is to access the cell via its ILOM (Integrated Lights Out Manager) and check the status of its critical services: CELLSRV (cell services), MS (management server), and RS (restart server). This can be done using commands like `cellcli -e list cell` or by checking the service status directly. This will determine if it’s a software service failure or a deeper hardware/OS issue. Rebooting should not be the first step. Failing over databases is a major action that may be unnecessary if it’s a simple service restart. Checking the database server’s network config is less likely, as the problem is isolated to one cell’s reachability.

For large databases with minimal downtime requirements migrating to Exadata, Transportable Tablespaces (TTS) with Cross-Platform Incremental Backup (introduced in 12.1) is often the best choice. It allows you to initially convert and move the datafiles (which can be done physically), then repeatedly apply incremental RMAN backups to roll the copy forward, drastically reducing the final downtime to just the last incremental apply and metadata import. Data Pump over a network link for a large database is slow. RMAN restore over NFS for the full dataset can be time-consuming. GoldenGate is excellent for near-zero downtime but can be more complex to set up for the initial bulk load compared to TTS with incremental apply.

On Exadata, the multiblock read count for smart scans is controlled internally by the storage cells, not by the `db_file_multiblock_read_count` parameter. Setting it to an arbitrarily high value on the database server is unnecessary and has no positive effect on offloaded queries; it only affects non-offloaded serial full scans, which are rare in an optimized Exadata environment. The other options are valid and beneficial: Auto DOP works well with Exadata’s resource management, changing block size can be a valid tuning exercise (though not always recommended), and the Database In-Memory option is fully compatible and complementary to Exadata’s storage tiering.

Smart Scan cannot offload processing of user-defined functions (UDFs) to storage cells. When a WHERE clause contains a UDF, the entire data set must be returned to the database server for evaluation, dramatically reducing offload efficiency. This is a common real-world performance pitfall. While other options can impact performance, a UDF in the predicate is the most direct and common cause of a sudden drop in Smart Scan efficiency for a specific query.0