1 / 29

Oracle9i New Features Donald K. Burleson

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.

kato
Download Presentation

Oracle9i New Features Donald K. Burleson

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related