oracle database 23ai new features for administrators

Oracle Database 23ai brings over 300 new features, focusing heavily on AI integration, simplifying developer operations, and strengthening database administration. Key administration highlights include built-in AI SQL firewalls, True Cache for application scaling, AI-driven auto-indexing, and simplified Schema-level privilege

The most impactful enhancements for Database Administrators (DBAs) are detailed below:

Security & Governance

• Oracle SQL Firewall: Built directly into the database kernel, this inspects incoming connections and SQL statements against an allowlist of approved commands, actively blocking unauthorized execution and potential SQL injection attacks.
• Schema Privileges: You can now grant access to all existing and future objects within a single schema using a single command, vastly reducing the administrative overhead of individual privilege management.
• Passwordless Authentication: Streamlines security compliance by supporting biometric logins (Face ID, fingerprints), hardware security keys (YubiKey), and OAuth-based logins.
• Immutable Backups: RMAN now supports immutable storage integration with OCI Object Storage, preventing backups from being deleted or modified during a ransomware event

 Performance & Caching

• Oracle True Cache: A diskless, read-only cache layer you can deploy at the middle tier. It automatically maintains data consistency with the primary database, improving application response times and reducing the workload on database servers.
• AI-Driven Auto-Index Optimization: The database can now learn query patterns and dynamically create or drop indexes based on workload, mitigating under-indexing (which slows queries) and over-indexing (which slows updates).

Operational Management

• Data Pump Enhancements: Troubleshooting is much faster with three new views (V$DATAPUMP_PROCESS_INFO, V$DATAPUMP_PROCESSWAIT_INFO, and V$DATAPUMP_SESSIONWAIT_INFO) which expose Data Pump processes and wait events to identify bottlenecks quickly.
• Read-Only PDBs: Administrators can make individual Pluggable Database (PDB) users or entire connected sessions strictly read-only, preventing unwanted write operations.
• AutoUpgrade Utility Enhancements: Upgrading to 23ai from versions 19c or 21c is highly streamlined using the updated AutoUpgrade tool, and patching is simplified with proactively provided monthly updates

 AI Workloads & Operations

• AI Vector Search: Allows the database to store and query semantic meanings of unstructured data (text, images) alongside relational predicates, all without needing an external vector database.
• SQL AI Syntax: You can interact with and query the database directly using natural language queries, speeding up day-to-day administrative exploration

Oracle Database 23ai brings critical enhancements to Real Application Clusters (RAC) administration, focusing on patching, containerization, and automated disaster recovery. For database administrators, these updates significantly streamline cluster operations and improve high availability without requiring major infrastructure overhauls

Key RAC features for administrators in 23ai include:

• Rolling Patching for Complex Changes: Administrators can now perform rolling patches for complex grid infrastructure modifications, minimizing downtime and human intervention.
• DBMS_ROLLING Integration with Transparent Application Continuity (TAC): This integration allows for completely seamless, uninterrupted database operations during rolling upgrades or patching, routing sessions transparently without application disruption.
• Reduced Grid Infrastructure Disk Footprint: The GI disk footprint has been reduced by more than 50% compared to previous versions. This enables up to 33% faster initial installations and significantly quicker "add node" operations.
• Lightweight Container Support: The highly optimized disk footprint makes deploying RAC environments in containers—such as Docker or Podman—faster, lighter, and much more practical for testing and dev or microservices architectures.
• RAC Fast Pluggable Database Open: Administrators can now manage pluggable database (PDB) opening and closing operations in RAC environments much faster, localizing PDB status changes without impacting the whole CDB instance.
• True Cache Integration: For read-heavy, latency-sensitive applications, administrators can leverage Oracle True Cache, a middle-tier, in-memory cache that automatically scales with RAC environments without requiring complex application change

Oracle Database 23ai introduces several architectural changes and features specifically related to memory structures that assist administrators in optimizing performance for AI and mission-critical workloads. 

New Memory Structures & Frameworks

• Managed Global Area (MGA): This is a significant addition to the Oracle Database Architecture. The MGA is a memory framework designed to share and coordinate memory across a set of trusted Oracle processes, improving efficiency in modern, high-concurrency environments.
• Oracle True Cache: A new, high-performance, middle-tier caching solution. It functions as a read-only, memory-consistent replica of the primary database, satisfying queries directly from its own buffer cache to reduce the load on the main database server.
• AI Vector Search Memory speed: Support for high-speed vector searches in memory. Specifically on Exadata platforms, this includes "Memory Speed Vector Search," which leverages memory-optimized structures to handle multi-dimensional vector mappings for AI similarity searches

Memory-Related Performance Improvements 

• In-Memory Columnar Speed for JSON: The database now provides In-Memory Columnar speed for JSON queries, allowing JSON data to be stored and processed in the In-Memory Column Store using optimized columnar formats.
• Fast Ingest (Memoptimized Rowstore) Enhancements: Improvements to the Memoptimized Rowstore now include support for direct in-memory column store population and fast flush using direct writes, which streamlines high-frequency data ingestion.
• Large Column Fetch Efficiency: Internal protocol changes have made fetching large columns more efficient for modern drivers, reducing the memory overhead during large-scale data transfer

Administrative Controls

• Automatic KEEP Object Load: On Exadata systems, administrators can benefit from the automatic loading of "KEEP" objects into Flash Cache, ensuring critical data remains in the fastest memory-adjacent storage.
• Lock-Free Column Value Reservations: This feature allows for the creation of rules for data row locking, which improves memory throughput and reduces contention in high-volume transaction environments.
• Memory Usage Monitoring: New administrative scripts and documentation are available to monitor and manage the System Global Area (SGA) and Program Global Area (PGA) more effectively

 

1. AI & Performance Management

• AI Vector Search: Allows direct storage, indexing, and querying of vector data alongside relational data, enabling semantic search capabilities without moving data to specialized vector databases.
• Automatic SQL Plan Management (SPM): The database actively monitors and auto-repairs suboptimal query execution plans to improve stability.
• True Cache: A memory-only, read-only cache that offloads read workloads and satisfies queries locally

Upgrading to Oracle Database 23ai requires a source version of Oracle 19c or 21c. The upgrade process is streamlined using the Oracle AutoUpgrade Utility, which automates pre-checks, fixups, and post-upgrade steps, replacing the older DBUA tool

Upgrade Considerations

• Direct Path: You can upgrade directly from Oracle 19c or 21c to 23ai. Direct upgrades from older releases (like 11g or 12c) require an intermediate upgrade step.
• AutoUpgrade Process: The recommended method involves executing the AutoUpgrade Utility in -mode analyze to generate reports and fix potential pre-upgrade issues before pushing to deployment.
• Client and Grid Infrastructure: If running RAC, Grid Infrastructure must be upgraded prior to the database upgrade. It is also highly recommended to upgrade client software for full interoperability

Oracle Database 23ai introduces several performance monitoring and administrative enhancements focused on AI-driven automation, real-time diagnostics, and streamlined data management. 

Key Performance Monitoring Features

• Automatic SQL Plan Management (SPM): The system can now automatically detect and correct suboptimal SQL plans by providing real-time optimization.
• Real-Time SQL Monitoring & Statistics: Enhanced tools for tracking query performance as it happens, including improved diagnostics and real-time statistics to identify bottlenecks quickly.
• SQL Performance Watch: A new feature to help DBAs monitor and maintain consistent performance during and after database upgrades.
• True Cache: A middle-tier caching solution that satisfies queries using a read-only replication of the primary database's buffer cache, significantly improving application response times and reducing server load.
• Enhanced Observability: New observability dashboards and telemetry provide proactive alerting and resource monitoring

Administrative & Performance Enhancements

• Lock-Free Column Value Reservations: Allows developers and administrators to create rules for row locking, improving throughput and end-user experience for high-concurrency workloads.
• Priority Transactions: Enables automatic prioritization of urgent transactions to prevent them from being blocked by lower-priority row access.
• Data Pump Performance Views: Three new views—(G)V$DATAPUMP_PROCESS_INFO, (G)V$DATAPUMP_PROCESSWAIT_INFO, and (G)V$DATAPUMP_SESSIONWAIT_INFO—help administrators identify bottlenecks and troubleshoot Data Pump operations more effectively.
• Automatic Transaction Quarantine: Automatically handles problematic transactions that might otherwise impact system stability.
• Unrestricted Parallel DMLs: Improvements to parallel data manipulation language (DML) and direct loads to enhance data processing speed.Flashback Log Placement: Administrators can now manage Flashback database logs outside the Fast Recovery Area (FRA) for better storage flexibility

To compare performance and resolve issues after an upgrade to Oracle Database 23ai,

To compare performance and resolve issues after an upgrade to Oracle Database 23ai, you should use Oracle's built-in performance validation tools. These tools allow you to compare a "before" (pre-upgrade) baseline with an "after" (post-upgrade) trial to pinpoint regressions

Core Tools for Comparison

• SQL Performance Analyzer (SPA): This is the primary tool for identifying SQL regressions. It captures a representative workload into a SQL Tuning Set (STS) from your pre-upgrade environment and re-executes it in 23ai to generate a comparison report.
• SQL Performance Watch: A feature within OCI Database Management and Enterprise Manager that helps automate the comparison of workload performance across two time periods (pre- and post-upgrade).
• Database Replay: For high-load environments, this tool captures actual production concurrency and re-plays it on the upgraded 23ai system to identify system-level bottlenecks that single SQL execution might miss

Comparison Workflow

1. Capture Baseline: Use your pre-upgrade system (e.g., 19c) to capture a representative workload during peak time into an STS.
2. Execute Post-Upgrade Trial: Import the STS into the 23ai database and run a "trial" execution. Tools like SQL Performance Analyzer will generate the execution plans and statistics for the new version.
3. Generate Comparison Report: Run a comparison task to view the net impact. Focus on metrics like Elapsed Time, CPU time, and changes in execution plan hash values.
4. Identify Regressions: Review the report for SQL statements with "Regressed" status. These are queries that performed better in the previous version

Post-Upgrade Performance Fixes

If you find performance issues after the move to 23ai:

• Gather Fixed Object Statistics: Run DBMS_STATS.GATHER_FIXED_OBJECTS_STATS after the system has run a representative workload in 23ai. This helps the optimizer understand the internal memory structures of the new version.
• Gather Dictionary Statistics: Use DBMS_STATS.GATHER_DICTIONARY_STATS to ensure the data dictionary metadata is accurately represented for the 23ai optimizer.
• SQL Tuning: Use the SQL Tuning Advisor or SQL Plan Management (SPM) to force the 23ai engine to use a better-performing plan if a regression is found.
• Check Initialization Parameters: Ensure parameters that affect performance haven't changed defaults significantly during the upgrade

How to transfer optimizer statistics and execution plans from your source database to your 23ai target database

 you can absolutely transfer both optimizer statistics and execution plans from your source database to your 23ai target database. This is a standard and highly recommended practice to prevent performance regressions following an upgrade

Method 1: Transferring Optimizer Statistics

You can move statistics by exporting them from the source data dictionary to a temporary staging table, moving that table via Data Pump, and importing it into the target.

1. Create a staging table on the Source:

EXEC DBMS_STATS.CREATE_STAT_TABLE('SYSTEM', 'MY_STAT_TABLE');

2. Export statistics from the Source (Schema Level):
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>'YOUR_SCHEMA', stattab=>'MY_STAT_TABLE', statid=>'SOURCE_STATS', ownname_old=>'SYSTEM');

(Note: You can also export by table or database using EXPORT_TABLE_STATS or EXPORT_DATABASE_STATS.)
3. Move and Import to Target:
Use Oracle Data Pump (expdp/impdp) to export MY_STAT_TABLE from the source, transfer the .dmp file, and import it into the target database.
Once imported, apply the statistics to your 23ai target's data dictionary

sql

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname=>'YOUR_SCHEMA', stattab=>'MY_STAT_TABLE', statid=>'SOURCE_STATS', ownname_old=>'SYSTEM');

Method 2: Transferring Execution Plans (SQL Plan Management)
To lock in exact execution plans that were performing well on your source version, you can capture them as SQL Plan Baselines and migrate them. [1]
1. Load plans into a SQL Tuning Set (STS) on the Source: 

sql
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'GOOD_PLANS_STS');
END;
/

DECLARE
  l_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''YOUR_SQL_ID''')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'GOOD_PLANS_STS', populate_cursor => l_cursor);
END;
/

2. Transport and Unpack:
Package the STS into a staging table using DBMS_SQLTUNE.PACK_STGTAB_SQLSET and export it using Data Pump.
Import the Data Pump file into your 23ai target database and unpack it using DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET. [1]
3. Evolve into Baselines on the Target:
Once the SQL Tuning Set is on your 23ai target, you can easily load those historical plans as active baselines: [1]

DECLARE l_plans PLS_INTEGER; BEGIN l_plans := DBMS_SPM.LOAD_COV_SQLSET(sqlset_name => 'GOOD_PLANS_STS'); END; /

Pro-Tip for 23ai Post-Upgrade Performance

Before importing your source statistics, ensure that your 23ai database has its fixed object statistics properly gathered. Because the 23ai data dictionary and internal memory architectures (fixed tables) differ from older releases, running this after upgrading prevents critical performance degradation

sql

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

For more details

https://learn.oracle.com/ols/course/oracle-database-23ai-new-features-for-administrators/127088/133283