Oracle Exadata Administration

The CELL_OFFLOAD_PLAN_DISPLAY parameter is a dynamic database parameter that can be modified using the ALTER SESSION or ALTER SYSTEM command to control offload plan visibility at either level.

The parameter CELL_OFFLOAD_PLAN_DISPLAY controls the display of Exadata offload information in explain plans. Its valid values are AUTO, ALWAYS, and NEVER, with the default value being AUTO.

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.

The disk_repair_time attribute setting change only affects future disk offline events, not disks that are currently offline. Disks that are already offline will continue to use the disk_repair_time value that was in effect when they went offline. To change the repair time for a currently offline disk, you must use the ALTER DISKGROUP...OFFLINE DISK...DROP AFTER command specifically for that disk.

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.

To change the disk repair time for a specific offline disk, use ALTER DISKGROUP with OFFLINE DISK and DROP AFTER clauses specifying the new time duration. This command sets the repair time for that specific disk instance, overriding the default disk_repair_time attribute. The syntax shown correctly specifies the disk group name, disk name, and the new repair time duration.

Exadata Smart Scan offloading is bypassed if disabled via CELL_OFFLOAD_PROCESSING = FALSE, if a direct path read is not used by the optimizer (smart scans require direct path reads), or if the scanned segment is too small.

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.

Longer disk repair time increases the window during which a second disk failure could cause data loss. In RAID/redundant storage, the system operates in degraded state after first failure until repair completes. Extended repair time means longer exposure to this vulnerable state, thus higher probability of catastrophic double failure.

Querying the V$SQLFN_METADATA view's OFFLOADABLE column indicates whether an operator can be offloaded to Exadata cells. The value 'YES' indicates offloading is allowed, while 'NO' indicates it is not, making the statement false.

Catch_#22 is valid because SQL identifiers can contain letters, numbers, underscores, and certain special characters like hash when properly quoted. Column is a reserved word in some databases, 1966_Invoices starts with a number (problematic in some SQL dialects), and #Invoices starts with a special character.

TRUNCATE is a DDL command that quickly removes all rows by resetting the table without logging individual row deletions to the rollback segment. DELETE is DML that logs each deletion, DROP removes the entire table structure, and CASCADE is a constraint option for related deletions.

SQL Server 2005 supports clustered and non-clustered indexes, unique constraints (which are enforced via unique indexes), and introduced XML indexes for querying XML data type columns. Hence, all of these index types are supported by SQL Server 2005.