Oracle Database 11g Release 1: What's New, Release History & Support Lifecycle

What Is New in Oracle Database 11g Release 1

What Are the Biggest Performance Improvements in Oracle Database 11g Release 1?

Oracle 11g Release 1 delivers its most impactful performance gains through three complementary innovations: result caching, adaptive cursor sharing, and SQL Plan Management. Together they address three of the most common production performance problems -- redundant logical I/O, bind variable peeking limitations, and plan regression after statistics refreshes.

Result Caches introduce three distinct in-memory caching layers. The SQL Query Result Cache stores complete result sets in the SGA, making them reusable across all sessions without re-executing the query. The PL/SQL Function Result Cache lets you annotate a function with RESULT_CACHE, so repeated calls with the same arguments skip re-execution entirely. Finally, the OCI Client Result Cache pushes caching all the way to the application-server tier, eliminating round trips for stable reference data.

In practice, workloads that repeatedly join large dimension tables to return a small, slowly-changing result set -- country code lookups, product catalogs, ZIP code ranges -- see dramatic reductions in logical reads. Cache invalidation is automatic when any underlying DML commits against the source tables.

-- Enable the query result cache in SQL
SELECT /*+ RESULT_CACHE */ country_code, country_name
FROM   country_lookup
ORDER BY country_name;

-- Check result cache usage
SELECT name, value
FROM   v$result_cache_statistics
WHERE  name IN ('Create Count Success','Find Count','Invalidation Count');

Adaptive Cursor Sharing solves the long-standing bind variable peeking problem on skewed data distributions. The optimizer can now detect when a single shared cursor produces poor plans for different bind values and create additional child cursors with bind-aware execution plans, avoiding the need to disable cursor sharing or resort to literals.

SQL Plan Management (SPM) stores accepted execution plans in a plan baseline. When statistics change and the optimizer generates a new plan, that plan is evaluated against the accepted baseline before it can take over. Watch out for the automatic capture setting (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES) -- enabling it in a system with large, volatile SQL workloads will grow the plan baseline rapidly. Most teams prefer to load baselines selectively using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

-- Load a plan baseline from the cursor cache
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id'
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

-- Accept an evolved plan into the baseline
DECLARE
  l_plans_altered PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => '&sql_handle',
    plan_name       => '&plan_name',
    attribute_name  => 'accepted',
    attribute_value => 'YES'
  );
END;
/

How Does SecureFiles Change LOB Storage and Management in Oracle 11g?

SecureFiles is a complete redesign of Oracle's LOB storage engine, and it is the most significant change to unstructured data handling since LOBs were introduced. It replaces the legacy BASICFILE storage model with a faster, feature-rich alternative that supports inline compression, deduplication, and encryption -- all manageable at the column or tablespace level.

Key requirements to be aware of before migration:

• The tablespace must use locally managed extents with ASSM (Automatic Segment Space Management).
• COMPATIBLE must be set to 11.1 or higher.
• The initialization parameter DB_SECUREFILE controls system-wide policy: PERMITTED (default), FORCE, NEVER, or ALWAYS.
• Online Redefinition (DBMS_REDEFINITION) is the recommended migration path -- it avoids taking the table offline and requires temporary space equal to the full table plus all LOB segments.

-- Create a table with SecureFiles LOB storage
CREATE TABLE documents (
  doc_id   NUMBER PRIMARY KEY,
  doc_name VARCHAR2(200),
  content  CLOB
)
LOB (content) STORE AS SECUREFILE sf_content (
  COMPRESS HIGH
  DEDUPLICATE
  ENCRYPT USING AES256
  CACHE
);

-- Check LOB storage type in DBA_LOBS
SELECT table_name, column_name, securefile
FROM   dba_lobs
WHERE  table_name = 'DOCUMENTS';

This matters if your application manages large volumes of documents, XML payloads, or binary content. Write throughput to SecureFiles is measurably faster than both traditional BASICFILE LOBs and -- notably -- faster than a standard Linux file system for comparable workloads. Read throughput is roughly equivalent. Deduplication is particularly valuable for document management systems where users frequently upload identical or near-identical files.

Logical standby Data Guard environments can now replicate SecureFiles LOB columns, including compression and encryption operations (though de-duplication and fragment-level operations are not propagated via SQL Apply).

How Do Database Replay and SQL Performance Analyzer Help with Testing in Oracle 11g?

Oracle 11g Release 1 ships two tools that effectively replace expensive third-party load-testing products for database-tier workload validation: Database Replay and the SQL Performance Analyzer (SPA).

Database Replay captures a full production workload at the kernel level -- all user calls, timings, concurrency, and transaction boundaries -- and stores it as a portable workload file. That file can be replayed against a test database running a different patch level, schema version, or hardware configuration. Because the capture happens inside the database, application-server variability, network latency, and client-side logic are excluded from the replay, producing a much more controlled comparison than external load generators can achieve.

The typical workflow is:

• Enable workload capture on production with DBMS_WORKLOAD_CAPTURE.START_CAPTURE.
• Transfer the capture directory to the test environment.
• Preprocess the workload with DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE.
• Replay using DBMS_WORKLOAD_REPLAY.START_REPLAY and analyze divergence reports.

SQL Performance Analyzer is the targeted counterpart to Database Replay. Rather than replaying an entire production workload, SPA runs a specific SQL Tuning Set before and after a change -- a statistics refresh, parameter change, or patch -- and produces a side-by-side report of plan and performance differences. This is the right tool when you need to validate a targeted schema or statistics change without the overhead of a full workload capture.

This matters if your team has been relying on production as the de facto test environment for major changes, or has experienced plan regression after patches. Both tools integrate directly with Enterprise Manager, though the underlying PL/SQL APIs are fully scriptable for pipeline automation.

What Changed in Oracle ASM and Storage Administration in 11g Release 1?

Oracle 11g Release 1 hardens ASM into a more operationally independent storage platform with the addition of the SYSASM privilege, variable-size extents, rolling upgrade support, and expanded ASMCMD capabilities.

The SYSASM privilege is the most consequential administrative change. In 10g, ASM instances required SYSDBA to connect and administer, meaning storage administrators needed the same elevated privilege as the database administrator. SYSASM creates a clean separation of duties, granting full ASM administrative rights without granting rights over Oracle database instances. SYSDBA will continue to work in 11.1 but is deprecated for ASM use and restricted further in later releases.

-- Create a dedicated ASM administrator
CREATE USER asm_admin IDENTIFIED BY &password;
GRANT SYSASM TO asm_admin;

-- Connect exclusively as SYSASM
sqlplus asm_admin/&password AS SYSASM
-- or from the OS:
-- sqlplus / AS SYSASM

Variable-size extents improve ASM memory efficiency for large files. In 10g, every extent was one allocation unit (AU). In 11g, extent size grows automatically as a file grows (when disk group compatibility is set to 11.1 or higher), shrinking the extent map that ASM must keep in memory. This is particularly relevant for large data warehouse environments with multi-terabyte datafiles.

Additional ASM improvements worth noting:

• ASM Rolling Upgrades -- upgrade ASM nodes in a RAC cluster individually without taking the whole cluster offline.
• Preferred Read Failure Groups -- set ASM_PREFERRED_READ_FAILURE_GROUPS to bias reads toward the local failure group in extended-distance RAC configurations, reducing inter-site I/O.
• Faster Mirror Resync -- after a transient disk path failure, ASM tracks the changed extents and resynchronizes only the affected blocks rather than re-mirroring the entire disk.
• New ASMCMD commands -- cp (copy between ASM and OS), lsdsk, remap, and others close the gap between ASM and conventional file system tooling.

What New Diagnostics and Fault Management Tools Does Oracle 11g Release 1 Provide?

Oracle 11g Release 1 replaces the scattered alert log, trace file, and core dump landscape with a unified, structured fault management framework built around the Automatic Diagnostic Repository (ADR).

The ADR is a file-based repository stored outside the database ($ORACLE_BASE/diag by default), initialized at database startup. It consolidates the alert log (now in XML format alongside the human-readable version), incident packages, core dumps, and trace files under a single directory structure accessible via the ADRCI command-line utility or Enterprise Manager. This means diagnostic data is available even when the database is not open -- a significant improvement when diagnosing startup failures or instance crashes.

The Health Monitor runs automatically when a critical error is detected, performing integrity checks across block layer, redo log, undo, and dictionary structures. It can also be invoked manually on a schedule. Results feed into the Data Recovery Advisor, which identifies root causes and proposes repair actions -- including automated RMAN-based block media recovery for corrupted blocks.

-- Query ADR incidents from SQL
SELECT incident_id, create_time, problem_key
FROM   v$diag_incident
ORDER BY create_time DESC;

-- From ADRCI command line: package an incident for Oracle Support
adrci> ips create package incident 12345
adrci> ips generate package 1 in /tmp

The Incident Packaging Service (IPS) automates the gathering of all diagnostic artifacts for an incident -- trace files, dumps, ADR metadata -- into a zip package formatted for upload to Oracle Support. This alone eliminates a significant amount of manual effort during P1 incidents. Watch out for ADR directory size in busy production environments; implement a retention policy via ADRCI SET CONTROL (SHORTP_POLICY=720) to prevent the diagnostic repository from consuming excessive disk space.