Question: What is the difference between Oracle 9i and Oracle 10g.
What differences are there in options between 9i and 10g? I'm considering
a migration to 10g and I need to get a list of all differences.
Answer: Each release of
Oracle has many differences, and Oracle 10g is a major re-write of the Oracle
kernel from Oracle 9i. I keep a list of the differences and make sure to
see these
important differences when migrating from 9i to Oracle 10g. While there are several hundred new features and other differences
between 9i and 10g, here are the major differences between Oracle9i and Oracle10g:
-
Major changes to SQL optimizer
internals
-
Oracle Grid
computing
-
AWR and ASH tables incorporated
into Oracle 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)
-
SQLTuning Advisor
-
SQLAccess Advisor
-
Rolling database upgrades (using
Oracle10g RAC)
-
dbms_scheduler package
replaces dbms_job for scheduling
Other notes on differences between 9i and 10g:
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
|
|
and include the URL for the page.
