Difference between 9i and 10g:
9i and 10g:-
Differences:
Ø Major
changes to SQL optimizer internals.
Ø ‘DataPump’ replaces ‘imp’ utility with ‘impdp’.
Ø Oracle
grid computing.
Ø AWR
(Automatic Workload Repository) and ASH(Automatic Storage Management) tables
incorporated into oracle performance pack and Diagnostic pack options.
Ø Automatic
Database Diagnostic Monitor (ADDM).
Ø SQL
tuning Advisor.
Ø SQL
Access Advisor.
Ø Rolling
Database upgrades (using oracle 10g RAC)
Ø Dbms_scheduler package replaces dbms_job for scheduling.
Ø Automated
Session History (ASH) materializes the oracle wait interface over time.
Features:
·
Web Server
Load Balancing: The web cache component includes
Apache extension to load-balance transactions to the least-highly-loaded Oracle
HTTP server (OHS).
·
Automated
Storage Load Balancing: Oracle’s Automatic
Storage Management (ASM) 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.
Views:-
§ 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.
Miscellaneous
Oracle10g enhancements:
·
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
·
VPD (FGAC, RLS) supports both row-level and
column-level VPD
Simply Telling:
Ø
Transparent Data Encryption
Ø
Async commits
Ø
CONNECT ROLE can not only connect
Ø
Passwords for DB Links are encrypted
Ø
New asmcmd utility for managing ASM storage
Ø
Grid computing - an extension of the clustering feature (RealApplication Clusters)
Ø
Manageability improvements (self-tuning
features)
Ø
Performance and scalability improvements
Ø
Automated Storage Management (ASM)
Ø
Automatic Workload Repository (AWR)
Ø
Automatic Database Diagnostic Monitor (ADDM)
Ø
Flashback operations available on row,
transaction, table or database level
Ø
Ability to UNDROP a table from a recycle bin
Ø
Ability to rename tablespaces
Ø
Ability to transport tablespaces across machine
types (E.g Windows to Unix)
New 'drop database' statement
New database scheduler - DBMS_SCHEDULER
Ø
DBMS_FILE_TRANSFER Package
Ø
Support for bigfile tablespaces that is up to 8
Exabytes in size
Data Pump - faster data movement with expdp and
impdp
