Oracle new features & enhancements Oracle Tips by Burleson Consulting

If your memory is as bad as mine, it?s handy to have a compressive list of Oracle enhancements, organized by each new Oracle release.

Caveat: I have tried to list new features in the release where they actually became functional, not the earlier release where they did not server a purpose because of bugs. This is not a complete list, just my personal favorites. Additions and corrections are most welcome, just e-mail me at info@remote-dba.net .

 

---

New Features of Oracle10g release 2:

?    Web server load balancing - The web cache component includes Apache extension to load-balance transactions to the least-highly-loaded Oracle HTTP server (OHS).

?    RAC instance load balancing - Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide connection pool load balancing facilities through integration with the new ?load balancing advisory? tool.  This replaces the more-cumbersome listener-based load balancing technique.

?    Automated Storage Load balancing - Oracle?s Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal load balancing.  Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.

?    Data Guard Load Balancing ? Oracle Dataguard allows for load balancing between standby databases.

?    Listener Load Balancing - If advanced features such as load balancing and automatic failover are desired, there are optional sections of the listener.ora file that must be present.  Automatic Storage Management (ASM) includes multiple disk operations and a non-ASM database migration utility

?    Enhancements to data provisioning and Oracle Streams designed to make it easier to archive, move, and copy large data sets

?    New Fast-Start Failover for automatic fail over to standby databases

?    Integrated data encryption and key management in the database

?    Automated statistics collection directly from memory designed to eliminate the need to execute SQL queries

?    New administrative reports include automatic database workload repository comparison

?    Extended use of Standard Chunk sizes - In 10gR2, the server has been enhanced to further leverage standard chunk allocation sizes. This additional improvement reduces the number of problems arising from memory fragmentation.

?    Mutexes - To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2. For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and librarycache pins. Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism. The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex to TRUE.

?    V$SGASTAT - V$SGASTAT has been enhanced to display a finer granularity of memory to component allocation within the shared pool. This allows faster diagnosis of memory usage (in prior releases many smaller allocations were grouped under the ?miscellaneous? heading).

?    V$SQLSTAT - A new view, V$SQLSTAT has been introduced which contains SQL related statistics (such as CPU time, elapsed time, sharable memory). This view is very cheap to query even on high-concurrency systems, as it does not require librarycache latch use. It contains the most frequently used SQL statistics in the V$SQL family of views.

?    V$OPEN_CURSOR - This implementation of this view has also been enhanced to be latchless, making it inexpensive to query.

?    V$SQLAREA - The V$SQLAREA view has been improved in 10gR2; the view optimizes the aggregation of the SQL statements while generating the view data.

---

New Features in Oracle10g release 10.1.0

• Oracle10g Grid ? RAC enhanced for Oracle10g dynamic scalability with server blades

• Completely reworked 10g Enterprise Manager (OEM)

• AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options

• Automated Session History (ASH) materializes the Oracle Wait Interface over time

• Data Pump replaces imp utility with impdp

• Automatic Database Diagnostic Monitor (ADDM)

• Automatic Storage Management (ASM) introduced Stripe And Mirror Everywhere (SAME) standard

• Automatic Workload Repository (AWR) replaces STATSPACK

• SQLTuning Advisor

• SQLAccess Advisor

• Rolling database upgrades (using Oracle10g RAC)

• dbms_scheduler package replaces dbms_job for scheduling

• OEM Partition Manager introduced

Miscellaneous Oracle10g enhancements:

• Set Database Default Tablespace syntax

• Rename Tablespace command

• Introduced RECYCLEBIN command for storing objects before they are dropped. Required new PURGE command for maintenance.

• sqlplus / as sysdba accessibility without quote marks

• SYSAUX tablespace

• Multiple Temporary Tablespaces supported to reduce stress on sorting in TEMP

• RMAN introduces compression for backups

• New drop database syntax

• New alter database begin backup syntax

• Oracle10g Data Guard Broker introduced

• Oracle10g RAC supports secure Redo Log transport

• Flashback enhancements for flashback database and flashback table syntax

• SQL Apply feature

• VPD (FGAC, RLS) supports both row-level and column-level VPD

• Cross Platform Transportable Tablespaces

• External Table unload utility

• SQL Regular Expression Support with the evaluate syntax

• New ROW TIMESTAMP column

• Improvement to SSL handshake speed

• Automatic Database Tuning of Checkpoints, Undo Segments and shared memory

• Automated invoking of dbms_stats for CBO statistics collection

• RAC introduces Integrated Cluster ware

• Oracle Application Builder supports HTML DB

• Browser Based Data Workshop and SQL Workshop

• PL/SQL Compiler enhanced for compile-time Warnings in utl_mail and utl_compress

• VPD (FGAC, RLS) supports both row-level and column-level VPD

• External Table unload utility

• SQL Regular Expression Support with the evaluate syntax

• New ROW TIMESTAMP column

---

Feature Enhancements in Oracle9.2:

• Oracle Streams

• RAC Cluster Guard II introduced

• XML DB enhanced for XML repository

• Oracle JVM made compliant with JDK 1.3

• SQL Apply added to Oracle DataGuard

• Locally Managed tablespaces (LMT) for the SYSTEM tablespaces

• Default Install Account locking

• VPD adds support for table and column synonyms

---

Major new utilities in Oracle- (9.0.1):

• Oracle Data Guard introduced

• OracleReal Application Clusters introduced

• OracleFlashback Utility (dbms_flashback)

• Online table reorganization with dbms_redefinition

• Database Configuration Assistant (DBCA) ? OEM Wizard

• Multiple blocksize support

• New blocksize parameter for create tablespace syntax

• Easy extraction of DDL with dbms_metadata package

• System-level triggers ? startup trigger DDL trigger

• Oracle Internet File System (IFS) becomes functional

• Oracle upsert statement introduced

• SQL case statement replaces decode syntax

• Oracle external tables

---

Feature Enhancements on Oracle9.0.1:

• New v$sql_plan allows execution plan analysis

• New set autotrace traceonly explain syntax for SQL*Plus replace explain plan syntax

• System-managed rollback segments

• Updateable Scrollable Cursors

• analyze command and dbms_utility.analyze_schema replaced by dbms_stats package

• connect as sysdba replaces as sysdba syntax in SQL*Plus

• Crummy svrmgrl utility disappears

• List Partitioning the multi-level list-hash partitioning

• RMAN allows re-startable backups

• RMAN supports block-level recovery

• Oracle ultra-search

---

Oracle Enhancements 8i (8.1.7)

• Oracle HTTP server gets Apache extensions

• Oracle Integration Server (OIS) introduced

• PL/SQL Gateway introduced for deploying PL/SQL based solutions on the Web

• Oracle JVM Accelerator

• Java Server Pages (JSP) engine

• New memstat utility for analyzing Java Memory footprints

• OEM auto-discover for new services

• New UNDO tablespace

• Oracle Character Set Migration utility

---

New features in Oracle 8i (8.1.6)

• PL/SQL Server Pages (PSP's)

• Oracle DBA Studio Introduced

• New SQL analytic Functions (rank, moving average)

• Alter table xxx storage (freelists) command supported

• Java XML parser

• PL/SQL dbms_crypto_toolkit encryption package

---

New Features in Oracle8i 8.1.5:

• Oracle Log Miner

• Oracle iFS

• JAVA stored procedures introduced (Oracle Java VM)

• Virtual private database (VPD, FGAC) using dbms_rls package

• Locally managed tablespace (LMT) reduces dictionary contention

---

New Feature Enhancements in Oracle8i 8.1.5:

• Functional based indexes (FBI)

• ESTAT/BSTAT is made obsolete by STATSPACK

• MTTR Fast Start recovery - Checkpoint rate auto-adjusted to match roll forward criteria

• Online index rebuilding

• Support for alter table xxx drop column syntax

• Oracle Parallel Server (OPS) adds Cache Fusion

• Advanced Queuing improved

• New execute immediate syntax

• New dbms_debug package

• Oracle users and roles can be accessed across dblinks for multiple databases

• Resource Management introduces priorities and resource classes

• Partitioned tables enhanced for Hash and Composite partitioning

• SQL*Loader introduces a direct load API

• dbms_stats enhanced to allow saving and migration of CBO statistics
  analyze table in parallel

• Net8 support for SSL, HTTP, HOP protocols

---

Major Enhancements in Oracle8 - Release 8.0

• Nested tables

• OID addressing

• Pointers allowed in table columns

• VARRAY support within tables (non first normal form tables)

• New create type syntax

• Table Partitioning and Index partitioning enhancements

• Oracle Universal Server introduced

• Oracle Data Cartridges introduced for Oracle Spatial

• Oracle ConText cartridge introduced

• RMAN introduced to replace Enterprise Backup Utility (EBU)

• RMAN has incremental backups and parallel syntax support

• Oracle security server introduced (precursor to Oracle SSO)

• Oracle WebServer enhanced

---

Other Oracle8 8.0 enhancements:

• OPS introduces $gv views

• OPS allows transparent failover to a new node

• OPS has Oracle-created Integrated Distributed Lock Manager (IDLM)

• Ability to call external procedures

• exp enhanced to export specific table partitions

• online/offline, backup/recover individual partitions

• reverse key index supported

• updatable views

• syntax to merge and balance partitions

• Advanced Queuing allows message handling

• parallel syntax supported for insert, update and delete DML

• MTS connection pooling

• Improved "STAR" query joins in CBO

• Tablespace point in time recovery (TSPITR)

• Oracle password expiration feature

• Database links are privileged (no embedded passwords)

• Oracle Replication manager introduced into OEM

• Deferred Oracle constraints aids data loading

• SQL*Net v 2 is renamed to Net8

---

Major enhancements in Oracle 7.3:

• Bitmap Indexes

• Partitioned Views

• Oracle Standby Database packages

• Full table scans support prefetch (asynchronous read ahead)

• alter index xx rebuild syntax

• Oracle db_verify package for corruption detection

• Oracle block editor (BBED) created

• Oracle Spatial

• Oracle ConText

• Oracle Trace

Other New Features in Oracle 7.3:

• Updatable Join Views

• SQL*DBA obseleted

• Alter tablespace coalesce syntax

• temporary parameter added to create tablespace syntax for TEMP tablespace

• extents unlimited syntax added to table syntax

• CBO introduced histograms for skewed columns and n-way joins

• CBO introduces hash joins and hash_area_size parameter

• CBO supports antijoins for NOT subqueries

• New dba_histograms view

• New utl_file package allows interface to flat files

---

New features in Oracle 7.2

• Manual Shrink Rollback Segment syntax

• Resizing of datafiles introduced with resize syntax

• New dbms_job package

• Autoextend option added for data files

---

Enhancements in Oracle 7.1

• dbms_sql package

• Read-only tablespaces

• Snapshot refresh groups introduced

• svrlgrl server manager utility

• Advanced replication