1 / 50

Oracle Database 11g Release 1 For DBAs

Oracle Database 11g Release 1 For DBAs. Thomas Kyte http://asktom.oracle.com. The Beginning. Data Model with Structure Data Independent of Code Set-oriented 1977 the work begins. “A Relational Model for Large Shared Databanks”. E.F. Codd - 1970. First RDBMS: Version 2 June 1979.

heman
Download Presentation

Oracle Database 11g Release 1 For DBAs

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. Oracle Database 11g Release 1For DBAs Thomas Kyte http://asktom.oracle.com

  2. The Beginning... • Data Model with Structure • Data Independent of Code • Set-oriented • 1977 the work begins “A Relational Model forLarge Shared Databanks” E.F. Codd - 1970

  3. First RDBMS: Version 2 June 1979 • FIRST Commercial SQL RDBMS • Impressive First SQL • Joins, Subqueries • Outer Joins, Connect By • A Simple Server • No transactions, ‘Limited’ Reliability • Portability from the Start • Written in Fortran • But multi-platform – PDP11, Dec VAX

  4. Oracle7.3 February 1996 • Spatial Data Option • Tablespaces changes - Coalesce, Temporary Permanent, • Trigger compilation, debug • Unlimited extents on STORAGE clause. • Some init.ora parameters modifiable - TIMED_STATISTICS • HASH Joins, Antijoins • Histograms • Oracle Trace • Advanced Replication Object Groups • Partitioned Views • Bitmapped Indexes • Asynchronous read ahead for table scans • Standby Database • Deferred transaction recovery on instance startup • Updatable Join View • SQLDBA no longer shipped. • Index rebuilds • DBV introduced • Context Option • PL/SQL - UTL_FILE

  5. Encrypted Tablespaces

  6. Encrypted Tablespaces • Oracle Database 10g Release 2 introduced column encryption • Could not range scan • Primary/foreign key issues • Tablespace encryption Removes those limitations • Many encryption algorithms • 3DES168 • AES128 • AES192 • AES256 Demo: encrypt.sql

  7. Standby Just got better

  8. Standby Database • Logical Standby was… • Limited in type support • But was always open for business • Physical Standby was… • Easy • But considered “not useful day to day”

  9. Standby Database • Logical Standby has… • XMLType support • DBMS_RLS & DBMS_FGA support • TDE support

  10. Active Data Guard: Develop & Test on Standby DB Use physical standby databasefor development & testing Preserves zero data loss in test/dev mode Flashback DB to back-outchanges & use as standby Production Database Developers,Testers Eliminates cost of idle DR system Standby Database

  11. Active Data Guard: Report & Backup from Standby DB Offload reporting to standby Simultaneously available for recovery Offload backups to standby Complete database and fast incremental backups Production Database Reporting Standby Database Improves performance on production database Backups

  12. Real Application Testing

  13. Real Application Testing –Database Replay Recreate actual production database workload Capture production workload incl. concurrency Replay workload in test with production timing Analyze & fix issues before production Test (RAC) Environment` MiddleTier Production Environment Capture DB Workload OracleDB servers Replay DBWorkload Storage

  14. Smaller more secure DMP files

  15. Datapump • COMPRESSION • ALL, DATA_ONLY, METADATA_ONLY, NONE • REUSE_DUMPFILES • Ability to use DML error logging like features • DATA_OPTIONS parameter • ENCRYPTION • All • Data_only • Metadata_only • None • Encrypted_columns_only • PARTITION_OPTIONS • Impdp • None • Departition • Merge Demo: dp.sql

  16. Virtual Columns

  17. Virtual Columns • Create Table • Alter Table Add Column • Are ‘column expressions’ • Expressions involving other columns in table • Constants • Deterministic functions • Ease of use and Optimizer enhancement Demo: vc.sql

  18. Partitioning just got better

  19. Partitioning • Composite Completely • Virtual Column Partitioning • Partition by Reference • Interval Partitioning

  20. Partition on virtual (computed) columns New composite partitioning Enhanced Partitioning ORDERS ORDERS >5000 Gold ORDERS >5000 1000-5000 Silver 1000-5000 EUROPE EUROPE FEB USA USA JAN LIST-RANGERegion byOrder Value RANGE-RANGEOrder Date by Order Value LIST-LISTRegion by Customer Type

  21. Partitioningby REFERENCE RANGE(order_date) Primary key order_id Table ORDERS ... ... Jan 2006 Feb 2006 • Partitioning key inherited through PK-FK relationship • Avoids redundant storage, maintenance of order_date Table LINEITEMS • RANGE(order_date) • Foreign key order_id ... ... Demo: part1.sql Jan 2006 Feb 2006

  22. Sep PartitioningAutomation New “INTERVAL” partitioning • Automatically creates a new partition when data outside the existing range is first inserted • E.g., monthly partitions, automatic new partition first day of the month • Composite partitioning: interval, interval-list, interval-hash, and interval-range • Automates partition management ORDERS Jul Aug Sep 1 2007 Demo: part2.sql

  23. Flashback Data Archive

  24. Flashback Data ArchiveTotal Data Recall Tamper-proof data archive Efficient storage and retrieval of undo Keep data for months, years, decades! Fast access to even very old data View data, versions of rows as of any time Control data retention time, purging of data Select * from orders AS OF ‘Midnight 31-Dec-2004’ Archive Tables Changes User Tablespaces Flashback Data Archive Oracle 11g Database

  25. Finer Grained Dependency Tracking

  26. Finer Grained Dependency Tracking • Fewer Invalidations • Add to a specification – so what • Add/Drop/Modify a column – so what • Holds true with view modifications too • Change a synonym pointer – so what • Replace a procedure – so what

  27. Cache More Stuff

  28. Cache More • Everyone knows the fastest way to do something is – to not do it • Client Side Cache • Server Results Cache (JIT-MV) • PL/SQL Function results cache

  29. Single Memory Setting

  30. Single Memory Parameter • Well, Two • MEMORY_TARGET • MEMORY_MAX_TARGET • Sizes PGA and SGA • Not every platform: • Linux, Solaris, Windows, HP-UX, AIX • As with automatic SGA memory management in 10g – you can set lower bounds for various segments

  31. New Diagnostics Area Demo: diag.sql

  32. Invisible Indexes

  33. Invisible Indexes • Can create them that way • Can alter them to be that way • Why? • Testing of course • How will plans change if we add this index • How will plans change if we remove this index • OPTIMIZER_USE_INVISIBLE_INDEXES

  34. Database Resident Connection Pooling

  35. DRCP • Anyone out there remember prespawned servers? • This is similar. • Pool of dedicated servers that can be reused • Instead of 50 app servers pooling 50 connections each (250 dedicated servers) – we’ll be able to have 50 app servers share 50 dedicated servers • PHP right now, others later.

  36. DDL that waits

  37. Blocking DDL • Sometimes, it mattered not how many times you hit the / key – it was never going to happen Demo: wait.sql

  38. Alter table T read only

  39. True ONLINE index build

  40. Disabled Triggers

  41. Disabled Triggers create or replace trigger Trg before insert on My_Table for each row disable begin :New.ID := My_Seq.Nextvak; end; / • Safer way to install code • Same thoughts as invisible indexes.

  42. Data Recovery Advisor

  43. Data Recovery Advisor • Analyzes failures based on symptoms • e.g. “Open failed” because datafiles missing • Intelligently determines repair strategies • Aggregates failures for efficient repair • e.g. for many bad blocks restore entire file • Presents only feasible repair options • Are there backups? • Is there a standby database? • Ranked by repair time and data loss • Can automatically perform repairs

  44. Q A & <Insert Picture Here>

More Related