Exadata Database Service on OCI is not simply a larger database instance. It is a purpose-built converged infrastructure that offloads query processing from database server CPUs to storage servers using Smart Scan, delivers persistent memory for redo log writes, and scales compute and storage independently. The workloads that benefit most are large analytical queries against OLTP data, high-throughput OLTP with heavy redo generation, and mixed workloads that need both simultaneously.
Step 1: Cloud Exadata Infrastructure
resource "oci_database_cloud_exadata_infrastructure" "production" {
availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
compartment_id = var.compartment_id
display_name = "production-exadata"
shape = "Exadata.X9M"
compute_count = 2
storage_count = 3
maintenance_window {
preference = "CUSTOM_PREFERENCE"
days_of_week { name = "SUNDAY" }
hours_of_day = [2]
lead_time_in_weeks = 2
}
defined_tags = {
"Operations.Environment" = "production"
"Operations.ManagedBy" = "terraform"
}
}Step 2: VM Cluster
resource "oci_database_cloud_vm_cluster" "production" {
backup_subnet_id = var.backup_subnet_id
cloud_exadata_infrastructure_id = oci_database_cloud_exadata_infrastructure.production.id
compartment_id = var.compartment_id
cpu_core_count = 16
display_name = "production-vm-cluster"
gi_version = "21.0.0.0"
hostname = "exaprod"
ssh_public_keys = [var.ssh_public_key]
subnet_id = var.client_subnet_id
license_model = "LICENSE_INCLUDED"
is_sparse_diskgroup_enabled = false
is_local_backup_enabled = true
nsg_ids = [var.exadata_nsg_id]
defined_tags = {
"Operations.Environment" = "production"
"Operations.ManagedBy" = "terraform"
}
}
output "vm_cluster_id" {
value = oci_database_cloud_vm_cluster.production.id
}Step 3: Database Home and Database
resource "oci_database_db_home" "production" {
vm_cluster_id = oci_database_cloud_vm_cluster.production.id
display_name = "DBHOME19C"
db_version = "19.22.0.0"
source = "NONE"
database {
admin_password = var.db_admin_password
db_name = "PROD"
db_unique_name = "PROD_PRIMARY"
pdb_name = "PRODPDB"
db_workload = "OLTP"
character_set = "AL32UTF8"
ncharacter_set = "AL16UTF16"
db_backup_config {
auto_backup_enabled = true
auto_backup_window = "SLOT_FOUR"
recovery_window_in_days = 14
}
}
}Step 4: Smart Scan Validation
Smart Scan offloads full table scan processing to Exadata storage cells. Storage servers apply WHERE clause predicates and column projection before sending data over the InfiniBand fabric. For large table scans, this reduces I/O by 90 percent or more and removes processing load from database server CPUs.
-- Verify Smart Scan is enabled
SELECT name, value FROM v$parameter
WHERE name IN ('cell_offload_processing', 'cell_offload_plan_display');
-- Both should show ENABLE / AUTO
-- Enable plan display and run a scan-eligible query
ALTER SESSION SET cell_offload_plan_display = ALWAYS;
SELECT customer_region, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM orders.order_header
WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-06-30'
AND status = 'COMPLETED'
GROUP BY customer_region ORDER BY revenue DESC;
-- Confirm Smart Scan in the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL'));
-- Look for: TABLE ACCESS STORAGE FULL and Predicate Offload
-- Check I/O offload statistics
SELECT metric_name, value
FROM v$cell_global_history
WHERE metric_name IN (
'cell physical IO bytes eligible for predicate offload',
'cell physical IO bytes saved by storage index',
'cell physical IO interconnect bytes returned by smart scan'
)
ORDER BY end_time DESC FETCH FIRST 5 ROWS ONLY;Step 5: IORM Workload Prioritization
-- Allocate 70% I/O to OLTP, 30% to reporting
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => 'EXADATA_PRODUCTION_PLAN',
comment => 'Exadata IORM plan'
);
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'EXADATA_PRODUCTION_PLAN',
group_or_subplan => 'OLTP_GROUP',
shares => 70
);
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'EXADATA_PRODUCTION_PLAN',
group_or_subplan => 'REPORTING_GROUP',
shares => 30
);
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
ALTER SYSTEM SET resource_manager_plan = 'EXADATA_PRODUCTION_PLAN' SCOPE=BOTH;Step 6: Data Guard for HA
resource "oci_database_data_guard_association" "production_dg" {
creation_type = "ExistingDbSystem"
database_admin_password = var.db_admin_password
database_id = oci_database_db_home.production.database[0].id
delete_standby_db_home_on_delete = true
protection_mode = "MAXIMUM_AVAILABILITY"
transport_type = "SYNC"
peer_db_system_id = var.standby_vm_cluster_id
peer_db_home_id = var.standby_db_home_id
peer_vm_cluster_id = var.standby_vm_cluster_id
}-- Monitor Data Guard status and lag
SELECT name, db_unique_name, database_role,
protection_mode, switchover_status
FROM v$database;
SELECT name, value FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag', 'apply finish time');Operational Notes
Smart Scan only activates for full table scans, direct path reads, and parallel queries. Index range scans and single-row OLTP lookups do not use Smart Scan. The workloads that see the biggest benefit are ad-hoc analytics, end-of-day batch reports, and large aggregation queries across fact tables.
Storage Index is a complementary Exadata feature that maintains min/max metadata for data regions on each storage cell. For range queries with selective predicates, the storage cells skip entire storage regions without reading them. Storage Index is automatic and requires no configuration. Monitor cell physical IO bytes saved by storage index to see how much I/O it is eliminating.
Regards,
Osama
#OCI #OracleCloud #Exadata #ExadataCS #OracleDatabase #Terraform #DatabaseEngineering #TechBlog #Oracle #CloudDatabase #SmartScan #DataGuard #IORM #OracleDBA #HighAvailability #OracleCloudInfrastructure #DatabasePerformance #ExadataCloud #CloudArchitecture #PlatformEngineering
Technology Geek 
Leave a comment