Oracle Database 23ai - New Features for DBAs

By Gowthami | apps-dba.com | Oracle Database Series

Oracle Database 23ai (formerly known as Oracle Database 23c) is Oracle's latest long-term support release, bringing a significant leap forward with native AI integration directly into the database engine. This post covers the key new features that every Oracle DBA should know about.

Key Insight: Oracle 23ai introduces AI-native capabilities including Vector Search, JSON Relational Duality Views, and True Cache—fundamentally changing how DBAs design and query databases.

AI Vector Search

One of the most significant additions in Oracle 23ai is AI Vector Search. This feature allows the database to store, index, and query vector embeddings natively, enabling semantic similarity searches alongside traditional SQL queries. The new VECTOR data type stores high-dimensional embeddings.

-- Create a table with a vector column
CREATE TABLE documents (
  id      NUMBER PRIMARY KEY,
  content CLOB,
  embedding VECTOR(1536, FLOAT32)
);

-- Similarity search using cosine distance
SELECT content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH FIRST 5 ROWS ONLY;

Schema Privileges with SQL Domains

Oracle 23ai introduces SQL Domains, a new schema-level object for centralizing column constraints and properties. DBAs can define a domain once and reuse it across many tables, improving consistency and maintainability.

-- Define a reusable domain
CREATE DOMAIN email_address AS VARCHAR2(255)
  CONSTRAINT email_check CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'));

-- Apply domain to a column
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  email  email_address
);

JSON Relational Duality Views

A game-changing feature: JSON Relational Duality Views let you expose relational tables as JSON documents and vice versa with full read/write support. This bridges the gap between relational and document-oriented data models.

-- Create a duality view over relational tables
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  SELECT JSON {
    'emp_id'   : e.emp_id,
    'name'     : e.last_name,
    'dept'     : d.dept_name
  }
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WITH INSERT UPDATE DELETE;

True Cache for Read Offloading

True Cache is a read-only cache of the primary database that offloads read workloads without requiring application changes. Unlike Oracle GoldenGate replicas, True Cache stays synchronized using redo logs, providing transparent performance improvements.

Developer-Friendly Features

• IF [NOT] EXISTS for DDL: CREATE TABLE IF NOT EXISTS simplifies deployment scripts
• Table Value Constructors: Insert multiple rows cleanly and efficiently
• Annotations: Attach metadata to schema objects for better documentation
• Property Graph: Graph queries using SQL/PGQ standard

Summary

Oracle 23ai brings AI-native capabilities, developer ergonomics, and architectural innovations that every DBA should understand. From Vector Search to Duality Views, these features change how you design and query databases, making it essential to stay current with this latest release.

Oracle Exadata - The Complete Guide

Want to master every aspect of Exadata? Smart Scan, Storage Indexes, Hybrid Columnar Compression, and Exadata-specific SQL tuning techniques. Get Gowthami's complete guide.

Get the Book

Oracle Exadata Smart Scan Explained – How It Accelerates Query Performance

By Gowthami | apps-dba.com | Oracle Exadata Series

If you work with Oracle Exadata, one of the most transformative features you will encounter is Smart Scan. It is the engine behind Exadata's legendary query acceleration, and understanding it deeply is essential for any Oracle DBA working in Exadata environments. In this post, we will break down exactly how Smart Scan works, when it activates, how to verify it, and how to tune your environment to get the most out of it.

What is Smart Scan?

Smart Scan is Exadata's ability to offload SQL processing — specifically full table scans and index range scans — directly to the Exadata Storage Cells. Instead of the database server reading all data blocks and filtering them in the database layer, Exadata pushes the filter predicates, column projections, and even joins down to the storage cells. Only the relevant rows and columns are returned to the database tier.

This is a radical departure from traditional Oracle storage architecture, where storage is "dumb" — it only reads and returns blocks. Exadata storage cells are intelligent processing units running Cellsrv software that understands SQL predicates.

How Smart Scan Works – Step by Step

1. Predicate Offloading: Oracle sends the WHERE clause predicates down to each storage cell.
2. Column Projection: Only the columns referenced in the SELECT list are returned — not all columns in the table.
3. Row Filtering at Storage: Each cell evaluates predicates row by row and discards non-matching rows before sending data back.
4. Parallel Processing: Multiple storage cells process different data segments simultaneously in parallel.
5. IB Network Transfer: Only the filtered, projected result set travels over the InfiniBand network to the database server.

💡 Key Insight: In a traditional setup, a 1 TB table scan might transfer 1 TB over the storage network. With Smart Scan, only the matching rows and required columns are transferred — potentially reducing data movement by 99%.

Prerequisites for Smart Scan to Activate

Condition	Detail
Full Table Scan or Fast Full Index Scan	Smart Scan only works with these access paths — not regular index range scans
Direct Path Read	The segment must be read via direct path (bypassing buffer cache). Happens automatically for large objects on Exadata.
Object on Exadata Storage	The table must reside on ASM diskgroups backed by Exadata storage cells
Cell Offload Compatible SQL	Not all SQL constructs can be offloaded. Complex PL/SQL functions may prevent offloading.

Verifying Smart Scan is Active

1. Check Cell Physical IO Statistics

SELECT name, value
FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name IN (
  'cell physical IO interconnect bytes',
  'cell physical IO interconnect bytes returned by smart scan',
  'physical read total bytes'
)
ORDER BY name;

2. Execution Plan – Storage Predicate

EXPLAIN PLAN FOR
SELECT /*+ FULL(e) */ department_id, SUM(salary)
FROM employees e
WHERE hire_date > DATE '2020-01-01'
GROUP BY department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for "storage" in the Predicate Information section — this indicates predicate pushdown to storage cells.

What Prevents Smart Scan?

• Small tables – Oracle may cache them in buffer cache rather than use direct path reads
• Index access paths – Regular B-tree index range scans do not trigger Smart Scan
• Non-offloadable SQL functions – User-defined PL/SQL functions in WHERE clauses cannot be pushed to cells
• Cell offload disabled – The parameter cell_offload_processing is set to FALSE (default is TRUE)

Forcing or Disabling Smart Scan

-- Disable Smart Scan for current session
ALTER SESSION SET cell_offload_processing = FALSE;

-- Re-enable
ALTER SESSION SET cell_offload_processing = TRUE;

-- Check current setting
SELECT name, value FROM v$parameter WHERE name = 'cell_offload_processing';

Smart Scan and Storage Indexes

Smart Scan works hand-in-hand with Storage Indexes — an Exadata-only feature that maintains in-memory min/max value ranges for each 1MB storage region. Before performing a Smart Scan, Exadata consults storage indexes to skip entire 1MB storage regions that cannot possibly contain matching rows. This further reduces I/O dramatically.

Summary

Smart Scan is one of the most powerful features that distinguishes Exadata from conventional Oracle deployments. By pushing filter and projection processing down to the storage cells, it enables massive reductions in data movement and dramatically accelerates analytical queries and large table scans.

Tags: Oracle Exadata, Smart Scan, Exadata Performance, Oracle DBA, Cell Offload