oracle9i new features donald k. burleson
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/1.jpg)
Oracle9i New Features
Donald K. Burleson
![Page 2: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/2.jpg)
Books by Donald K. Burleson
![Page 3: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/3.jpg)
Editor-in-Chief ofOracle Internals
![Page 4: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/4.jpg)
Oracle Training by Don Burleson
![Page 5: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/5.jpg)
www.guidehorse.org
![Page 6: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/6.jpg)
Syllabus
Syllabus:– dba-oracle.com/cou_9i_new.htm
Class schema:
– dba-oracle/com/ppt/pubsdb.ppt
![Page 7: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/7.jpg)
Syllabus – Day 1New 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 database3 – Oracle9i Multiple blocksizes
Exercise – Create tablespaces with multiple blocksizes4 – Oracle9i SGA memory management
Pga_aggregate_target5 – Oracle9i Automated undo management5 - Oracle9i Resumable space management5 - Managing spfiles in Oracle9i 6 - Oracle9i Virtual private databases6 - Oracle9i Fine-grained auditing6 - Oracle9i Database flash freeze
![Page 8: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/8.jpg)
Syllabus – Day 2Oracle9i Architecture Changes
1 - Oracle9i Flashback query Exercise – Enable and use flashback query
2 - Oracle9i Logminer3 - Oracle9i Fast-start recovery3 - New Oracle9i RMAN features3 - Oracle9i Data Guard4&5 - Object/relational enhancements in Oracle9i
Inheritance, ADTsExercise – Create an ADT
Oracle9i VLDB features
6 - Oracle9i Multi-table insertsExercise – Create multi-table inserts6 - Oracle9i Merge statements - Upserts6 - Oracle9i Parallel direct load changes
6 - Oracle9i list partitioning
![Page 9: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/9.jpg)
Syllabus – Day 3Oracle9i SQL
1 - Oracle9i CASE statement1 – Oracle9i Explicit defaults1 – Oracle9i Scalar subqueries1 - New joins – cross join, natural join, using and on1 - Oracle9i Left and right outer joins
Exercise – Perform Oracle9i joins in SQL2 - Oracle9i Explicit column value defaults2 - Oracle9i New date time BIFs2 – Oracle9i External tables
Exercise – create an external table
Oracle9i PL/SQL
3 - Oracle9i Compiled PL/SQL3 - Oracle9i Common SQL parser3 - Oracle9i cookie support3 - Oracle9i PL/SQL Inheritance support3 - Oracle9i New PL/SQL datatypes
![Page 10: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/10.jpg)
Syllabus – Day 3 ctd.New SQL optimization
4 – Oracle9i Index skip scanExercise – show index skip scan in action
4 - Oracle9i Index-only scans on FBIsExercise – show index-only FBI scan
4 - New Oracle9i first_rows_n optimization
5 - New Oracle9i execution plan columns5 - New Oracle9i statistics gathering5 - Oracle9i in-memory execution plans in v$sql_plan
Exercise – Join v$sql_plan and v$session5 - Improved Oracle9i cursor sharing with peeking
RAC and TAF
6 - Evolution of OPS into RAC6 - Oracle9i Cache fusion architecture6 - Using Oracle9i TAF with RAC
![Page 11: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/11.jpg)
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
![Page 12: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/12.jpg)
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
![Page 13: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/13.jpg)
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;
Automatic Free Space Management
![Page 14: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/14.jpg)
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
![Page 15: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/15.jpg)
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
![Page 16: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/16.jpg)
9i Data Guard / Standby Database• Primary becomes standby
alter database commit to switchover to standby;shutdown immediate;startup nomountalter database mount standby database;alter database recover managed standby database;
• Standby becomes primaryalter database commit to switchover to primary;shutdown immediate;startup
![Page 17: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/17.jpg)
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
![Page 18: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/18.jpg)
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
![Page 19: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/19.jpg)
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
![Page 20: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/20.jpg)
Partitioning Enhancements
List partitioning solves “almost all” the short-comings 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));
![Page 21: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/21.jpg)
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)
![Page 22: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/22.jpg)
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
![Page 23: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/23.jpg)
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 discardfile
'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.
![Page 24: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/24.jpg)
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
![Page 25: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/25.jpg)
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.tsaleswhen 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);
![Page 26: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/26.jpg)
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
![Page 27: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/27.jpg)
Oracle Managed Files (OMF)
system01.dbf
Becomes:
ora_xty6677.ora
![Page 28: Oracle9i New Features Donald K. Burleson](https://reader036.vdocuments.us/reader036/viewer/2022062322/568148af550346895db5c53b/html5/thumbnails/28.jpg)
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