Oracle9i New Features Donald K. Burleson

1. Oracle9i New FeaturesDonald K. Burleson

2. Books by Donald K. Burleson

3. Editor-in-Chief of Oracle Internals

4. Oracle Training by Don Burleson

5. www.guidehorse.org

6. Syllabus • Syllabus: • dba-oracle.com/cou_9i_new.htm • Class schema: • dba-oracle/com/ppt/pubsdb.ppt

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. 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;

14. 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

15. 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

16. 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

17. 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

18. 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

19. 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

20. 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));

21. 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)

22. 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

23. 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.

24. 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

25. 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);

26. 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

27. Oracle Managed Files (OMF) system01.dbf Becomes: ora_xty6677.ora

28. 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

29. Current Books by Don Burleson My web site: www.dba-oracle.com E-mail me at : Don@Burleson.cc