Database Management Systems

Exadata Hybrid Columnar Compression uses Compression Units (CU) that can span multiple blocks. Individual column values can also cross block boundaries. By default, updates to a compressed row lock the entire CU, decreasing concurrency. Therefore, options a, b, and d are correct, while c is incorrect because row-level locking is not default.

Exadata Hybrid Columnar Compression (EHCC) stores data in compression units, which are larger blocks that Oracle locks during DML operations, potentially reducing concurrency. Basic compression leaves inserted/updated rows uncompressed until they are moved during maintenance operations. Higher compression levels (Archive) require direct-path insert operations to achieve maximum compression ratios. Conventional DML is supported but results in lower compression efficiency, while warehouse compression can incur higher CPU overhead during compression and decompression operations.

COMPRESS FOR QUERY LOW is designed for load performance, using lower compression overhead for faster data loading while still providing query performance benefits. COMPRESS FOR QUERY HIGH provides better compression but slower load times. COMPRESS FOR ARCHIVE HIGH is for rarely accessed data requiring maximum compression, not for frequently accessed applications. Archive compression levels add significant overhead during data loading and modification, making them unsuitable for active workloads.

The CELL_OFFLOAD_PROCESSING parameter controls whether predicate filtering and other operations are offloaded to Exadata storage cells. This parameter can be set at system or session level and overrides the default behavior of using cell offload capabilities. The other options (CELL_OFFLOAD_PLAN_DISPLAY, OPT_PARAM, CELL_PARTITION_LARGE_EXTENTS) are not the correct parameter names for controlling cell offload processing behavior.

CELL_OFFLOAD_PROCESSING parameter controls whether predicate evaluation is offloaded to storage cells, not the overall performance comparison between Exadata and conventional storage. To compare Exadata Cell with conventional storage, you would disable cell offload features or use different testing methodologies. This parameter specifically targets offload processing behavior, not comprehensive storage subsystem performance.

CELL_OFFLOAD_PLAN_DISPLAY determines whether Oracle's EXPLAIN PLAN displays Exadata storage filter predicates. Setting it to AUTO or ALWAYS enables this feature, allowing database administrators to verify if query predicates are offloaded to storage cells. The option False is incorrect because it disables showing these storage predicates.

The initialization parameter _CELL_PARTITION_LARGE_EXTENTS (often used in Exadata) changes default initial extents for partitioned tables/indexes to 8MB. It applies to Locally Managed Tablespaces (LMT) with AUTOALLOCATE, affects only partitions, and accepts TRUE, ALWAYS, and DEFAULT.

For heavily partitioned tables, SQL*Loader may not be the most efficient loading mechanism. Using external tables with parallel INSERT direct load provides better performance by leveraging parallel processing and direct-path insert capabilities. External tables read from flat files and enable parallel direct-path inserts, which are optimized for partitioned table loading scenarios.

The disk_repair_time parameter in Oracle ASM specifies how long a disk can remain offline before ASM drops it from the disk group. This is a protection mechanism that prevents permanent disk removal during transient failures. It is measured in hours and applies to disks that go offline, not to repair time specification or disk online behavior during faults.

CELL_FLASH_CACHE is a storage clause attribute that controls how database objects are cached in Exadata Smart Flash Cache, overriding the automatic caching policy when compatible parameter is 11.2.0.0 or higher. The attribute accepts three values: NONE (no caching), DEFAULT (uses automatic policy), and KEEP (forces caching). This provides fine-grained control over flash cache usage for specific tables, partitions, or tablespaces.

Predicate evaluation is not offloaded when CELL_OFFLOAD_PROCESSING is FALSE, or when scanning certain table types. Clustered tables and index-organized tables have storage structures that don't support predicate offloading. Fast full scans on compressed indexes also don't support offload. Direct path reads are a different optimization mechanism and don't prevent predicate offloading - they're often used together in Exadata.

All listed cases prevent predicate evaluation offload to Exadata Cell. Fast full scans on reverse key indexes cannot be offloaded due to index structure. Row dependencies or ROWID fetching require database-level processing. ROWID-ordered scans bypass cell offloading capabilities. CREATE INDEX with nosort requires database-side processing. LOB/LONG column queries need database-level handling due to size and complexity.

Flashback table scans require database-level processing and cannot be offloaded. Queries over 255 columns exceed cell offload capabilities. When CELL_OFFLOAD_DECRYPTION is false on encrypted tablespaces, offload is disabled for security. Option C is redundant - HCC compression doesn't change the 255 column limit. Option E is incorrect - setting CELL_OFFLOAD_DECRYPTION to true enables offload for encrypted data.

Virtual column predicates cannot be offloaded because they require computation. If the tablespace is not fully on Exadata Cell, offload is impossible. Flashback table scans require database-level processing. CREATE INDEX with nosort needs database-side sorting operations that cannot be pushed to storage cells.