Transcript ora9i_new_features_1..

Oracle9i New Features
Donald K. Burleson
Books by Donald K. Burleson
Editor-in-Chief
of
Oracle Internals
Oracle Training by Don Burleson
www.guidehorse.org
Syllabus
 Syllabus:
–
dba-oracle.com/cou_9i_new.htm
 Class schema:
–
dba-oracle/com/ppt/pubsdb.ppt
Syllabus – Day 1
New Administration Features
2 – Oracle9i managed files (OMF)
Oracle9i db_create_file_dest parameter
Exercise – Create Oracle9i OMF tablespaces and data files.
2 – Oracle9i online reorg
Exercise – Online reorg for pubs database
3 – Oracle9i Multiple blocksizes
Exercise – Create tablespaces with multiple blocksizes
4 – Oracle9i SGA memory management
Pga_aggregate_target
5 – Oracle9i Automated undo management
5 - Oracle9i Resumable space management
5 - Managing spfiles in Oracle9i
6 - Oracle9i Virtual private databases
6 - Oracle9i Fine-grained auditing
6 - Oracle9i Database flash freeze
Syllabus – Day 2
Oracle9i Architecture Changes
1 - Oracle9i Flashback query
Exercise – Enable and use flashback query
2 - Oracle9i Logminer
3 - Oracle9i Fast-start recovery
3 - New Oracle9i RMAN features
3 - Oracle9i Data Guard
4&5 - Object/relational enhancements in Oracle9i
Inheritance, ADTs
Exercise – Create an ADT
Oracle9i VLDB features
6 - Oracle9i Multi-table inserts
Exercise – Create multi-table inserts
6 - Oracle9i Merge statements - Upserts
6 - Oracle9i Parallel direct load changes
6 - Oracle9i list partitioning
Syllabus – Day 3
Oracle9i SQL
1 - Oracle9i CASE statement
1 – Oracle9i Explicit defaults
1 – Oracle9i Scalar subqueries
1 - New joins – cross join, natural join, using and on
1 - Oracle9i Left and right outer joins
Exercise – Perform Oracle9i joins in SQL
2 - Oracle9i Explicit column value defaults
2 - Oracle9i New date time BIFs
2 – Oracle9i External tables
Exercise – create an external table
Oracle9i PL/SQL
3 - Oracle9i Compiled PL/SQL
3 - Oracle9i Common SQL parser
3 - Oracle9i cookie support
3 - Oracle9i PL/SQL Inheritance support
3 - Oracle9i New PL/SQL datatypes
Syllabus – Day 3 ctd.
New SQL optimization
4 – Oracle9i Index skip scan
Exercise – show index skip scan in action
4 - Oracle9i Index-only scans on FBIs
Exercise – show index-only FBI scan
4 - New Oracle9i first_rows_n optimization
5 - New Oracle9i execution plan columns
5 - New Oracle9i statistics gathering
5 - Oracle9i in-memory execution plans in v$sql_plan
Exercise – Join v$sql_plan and v$session
5 - Improved Oracle9i cursor sharing with peeking
RAC and TAF
6 - Evolution of OPS into RAC
6 - Oracle9i Cache fusion architecture
6 - Using Oracle9i TAF with RAC
Best Enhancements











Automatic segment free space management
Multiple block sizes by tablespace
9i Data Guard and enhanced standby features
Export and import enhancements
Memory management
Partitioning enhancements
External tables
Multi-table insert
Upsert
Oracle Managed Files
Automatic undo management
Automatic Free Space Management
 Traditional freelists and freelist groups have
always been a nightmare
 Block ID’s placed on the free list when their
pctused falls below the container’s pctused
value
 Nightmare even worse when pctfree will not
allow block to accommodate another row
Automatic Free Space Management
 Only available with locally managed
tablespaces
 Bitmaps describe the space usage of each
block within a segment
 No more contention on segment headers
create tablespace problem datafile
'/u01/oradata/corp/problem01.dbf'
size 1200m extent management local
segment space management auto;
Multiple block size
 Instance default defined by db_block_size
 Separate cache for each
–
–
Must be pre-defined for non-default block-sized
tablespaces
db_2k_cache_size . . db_32k_cache_size
 Up to 5 different cache configurations
 Change requires a bounce
9i Data Guard / Standby Database
 Enterprise Edition mandatory
 Standby need not be “sacrificed” when
activated
 Specify time delay for transporting changes to
standby site(s)
–
–
More protection
Standby in pre-error state
 Automatic datafile creation on standby
9i Data Guard / Standby Database
• Primary becomes standby
alter database commit to switchover to standby;
shutdown immediate;
startup nomount
alter database mount standby database;
alter database recover managed standby database;
• Standby becomes primary
alter database commit to switchover to primary;
shutdown immediate;
startup
Export and Import Enhancements
 resumable suspends a transaction for a
resumable_timeout period (default 2 hours)
 tables enhanced to support pattern matching
–
–
Wild card support
Table names upper case
 tablespaces
–
–
List according to standard formatting
Indexes automatically extracted
Export and Import Enhancements
 statistics {estimate|compute|none} for export
 statistics {always|none|safe|recalculate} for
import
–
–
Helpful in rule-based environments
Can reduce import run time
Dynamic SGA Memory Management
 Resizable db_cache_size and
shared_pool_size with caveats, mainly
 ORA-00384: Insufficient memory to grow
cache
 Buffer cache advisory
–
–
–
alter system set db_cache_advice =
{on|off|ready}
Results stored in v$db_cache_advice
Predicts table miss rates for sizes between 10%
and 200% of current size
Partitioning Enhancements
 List partitioning solves “almost all” the shortcomings of range-based
 Ascending partition key column values can
lead to poor distribution
create table account (id
number . . . . . .
location varchar2(2))
partition by list (location)
(partition other values ('NF','NS','NB','PE','MB','SK','AB','BC'),
partition ontario values ('ON'),
partition quebec values ('QC'),
partition unknown values (null));
Partitioning Enhancements
 Maintenance of global indexes
–
–
Add update global indexes to partition
maintenance
add, drop, move , truncate, split, merge,
exchange, coalesce (iot’s)
 Index rebuild still recommended if
–
–
Row count is >~ 200,000)
Data can be unavailable (woo)
External Tables
 Filesystem based storage
–
–
Day-to-day operations
Load into the warehouse
 SQL*Loader syntax
 Perfect for read-only data
 No longer need to stage data in the database
External Tables

NFS mount makes available to multiple servers
SQL> create or replace directory staging as
2
'/d0/wdata/stage';
Directory created.
SQL> create table relations_ext
2
(first_name
varchar2(20),
3
last_name
varchar2(20),
4
relationship
varchar2(20))
5
organization external
6 (type oracle_loader default directory staging
7
access parameters
8 (records delimited by newline badfile 'reln.bad'9
'reln.dsc' logfile 'reln.log'
10
fields delimited by '^^%^^ '
11 (first_name
char,
12
last_name
char,
13
relationship
char))
14 location ('reln.txt'))
15 reject limit unlimited;
Table created.
discardfile
Multiple table insert
 Parcel into a single unit of work
 No need to
–
–
–
–
re-summarize
re-sort
re-group
re-transform
 Distribute data based on logical attributes of
new rows
Upsert
 Using merge keyword
 Conditional insert or update depending on row
existence
 Careful selection of equating columns
merge into sales s using sales_tornt st
on (s.cust_id = st.cust_id)
when matched then update set tvol = st.tvol, tsales =
st.tsales
when not matched then
insert (s.cust_id,s.cust_loc,s.tvol,s.tord_sum)
values (st.cust_id,st.cust_loc,st.tvol,st.tord_sum);
Oracle Managed Files (OMF)
 db_create_file_dest
 db create_online_log_dest
 All you DON’T want in a database at your
fingertips
–
–
–
cryptic file names
100m default size
autoexensible
Oracle Managed Files (OMF)
system01.dbf
Becomes:
ora_xty6677.ora
Automatic undo management






undo_tablespace
undo_management = auto
undo_retention = {seconds with 900 default}
Need not be using OMF
Are you a fan of optimal?
Turnkey undo configuration via Oracle
Enterprise Manager
Current Books by Don Burleson
My web site: www.dba-oracle.com
E-mail me at : [email protected]