1 / 87

Top 10, no – make that 11, things about Oracle Database 11g Release 1

Top 10, no – make that 11, things about Oracle Database 11g Release 1. 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.

tuyet
Télécharger la présentation

Top 10, no – make that 11, things about Oracle Database 11g Release 1

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. Top 10, no – make that 11, things about Oracle Database 11g Release 1 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. Portability: Version 3 March 1983 • New Implementation Designed for Portability • Written in ‘C’ • Single Source • Architectural Changes • Transactions, but no read consistency • AI/BI files • Oracle Corporation – name established • Selling the idea of Relational Database

  5. Reliability: Version 4 October 1984 • Larger Installed Base (well, it had one) • Architectural Improvements • Read Consistency & Multiversioning

  6. Cooperative Server: Version 5 April 1985 • My First Oracle Experience • 1st Client/Server • Cooperative Server • Distributed Processing • Parallel Server • Portability • V5 was first to go beyond 640K memory on PCs • Single-user for Macintosh o/s • SQL_TRACE! • select trace('sql',1),1 from dual; • Portability , Compatibility, Capability, Connectability

  7. Transaction Processing: Version 6 July 1988 • New Architecture • Performance (first SMP) • Availability • Parallel Server • TPO • PL/SQL • V6 Lays Architectural Groundwork for the Future • This was a rewrite of the entire database

  8. Cooperative Server: Oracle7 June 1992 • Architectural and Performance Improvements • Shared SQL • Cost-based query optimization • DBA features improve ease of administration • Data Integrity and Security Enhancements • ANSI/ISO standard SQL with declarative integrity • Roles-based security model simplifies security • Trusted Oracle7 adds multilevel security • V6 was all about architecture, V7 about features

  9. Oracle7.1 May 1994 • ANSI/ISO SQL92 Entry Level • Advanced Replication - Symmetric Data replication • Snapshot Refresh Groups • Parallel Recovery • Dynamic SQL - DBMS_SQL • Parallel Query Options - query, index creation, data loading • Server Manager introduced • Read Only tablespaces

  10. Oracle7.2 May 1995 • Resizable, autoextend data files • Shrink Rollback Segments manually • Create table, index UNRECOVERABLE • Subquery in FROM clause – inline views (documented…) • PL/SQL wrapper • PL/SQL Cursor variables • Checksums - DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM • Parallel create table • Job Queues - DBMS_JOB • DBMS Application Info • Sorting Improvements - SORT_DIRECT_WRITES

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

  12. Oracle8.0 “Warning Objects may be closer than they appear” June 1997 • The year of the ‘Cartridge’ (image, video, context, time, spatial) • TSPITR • RMAN introduced -- Incremental backups, parallel backup/recovery. • Security Server introduced for central user administration. User • Password profiles • Index Organized tables • Deferred integrity constraints • Reverse Key indexes • Any VIEW updateable • New ROWID format • Object Relational database • SQL3 standard • Call external procedures • LOB >1 per table • Partitioned Tables and Indexes (range) • Advanced Queuing for message handling • 1000 columns/table • Parallel DML statements • Net8 Connection Pooling • Performance improvements in OPS - global V$ views introduced across all instances

  13. Oracle Database Innovation Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private DatabaseBuilt in Java VM Partitioning Support Built in Messaging Object Relational Support Multimedia SupportData Warehousing Optimizations Parallel Operations Distributed SQL & Transaction Support Cluster and MPP Support Multi-version Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation 1977 2007 30 years of sustained innovation … … continuing with Oracle Database 11g

  14. #1 Encrypted Tablespaces

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

  16. Encrypted Tablespaces ops$tkyte%ORA11GR1> create tablespace encrypted 2 datafile '/…/encrypted.dbf' size 10m 3 ENCRYPTION default storage( encrypt ); Tablespace created. ops$tkyte%ORA11GR1> create tablespace clear 2 datafile '/…/clear.dbf' size 10m; Tablespace created.

  17. Encrypted Tablespaces ops$tkyte%ORA11GR1> create table t 2 tablespace clear 3 as 4 select * from all_users; Table created. ops$tkyte%ORA11GR1> create index t_idx 2 on t(lower(username)) 3 tablespace clear; Index created.

  18. Encrypted Tablespaces ops$tkyte%ORA11GR1> alter system checkpoint; System altered. $ strings /…/clear.dbf | grep -i ops.tkyte OPS$TKYTE from the table ops$tkyte from the index

  19. Encrypted Tablespaces ops$tkyte%ORA11GR1> alter table t move 2 tablespace encrypted; Table altered. ops$tkyte%ORA11GR1> alter index t_idx rebuild 2 tablespace encrypted; Index altered.

  20. Encrypted Tablespaces ops$tkyte%ORA11GR1> alter system checkpoint; System altered. $ strings /…/encrypted.dbf | grep -i ops.tkyte [This space intentionally left blank]

  21. Encrypted Tablespaces ps$tkyte%ORA11GR1> set autotrace traceonly explain ops$tkyte%ORA11GR1> select * from t where lower(username) like 'ops$%'; Execution Plan ------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 112 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 112 | |* 2 | INDEX RANGE SCAN | T_IDX | 1 | | ------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(LOWER("USERNAME") LIKE 'ops$%') filter(LOWER("USERNAME") LIKE 'ops$%')

  22. #2 Cache More Stuff

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

  24. Cache More ops$tkyte%ORA11GR1> /* ops$tkyte%ORA11GR1> drop table t; ops$tkyte%ORA11GR1> create table t as select * from all_objects; ops$tkyte%ORA11GR1> */ ops$tkyte%ORA11GR1> update t set object_type=object_type where rownum=1; 1 row updated.

  25. Cache More ops$tkyte%ORA11GR1> set autotrace traceonly ops$tkyte%ORA11GR1> select /*+ result_cache */ 2 owner, 3 object_type, 4 count(*) cnt 5 from t 6 group by owner, object_type 7 order by owner, object_type 8 / 249 rows selected.

  26. Cache More Execution Plan -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 58742 | 1606K| 284 (2)| 00:00:04 | | 1 | RESULT CACHE | 5cwffcum1ajfud1088m1m73f81 | | | | | | 2 | SORT GROUP BY | | 58742 | 1606K| 284 (2)| 00:00:04 | | 3 | TABLE ACCESS FULL| T | 58742 | 1606K| 280 (1)| 00:00:04 | -------------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=3; dependencies=(OPS$TKYTE.T); parameters=(nls); name="select /*+ result_cache */ owner, object_type, count(*) cnt from t group by owner, object_type order by"

  27. Cache More Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1005 consistent gets 0 physical reads 0 redo size 6922 bytes sent via SQL*Net to client 596 bytes received via SQL*Net from client 18 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 249 rows processed

  28. Cache More ops$tkyte%ORA11GR1> / 249 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 6922 bytes sent via SQL*Net to client 596 bytes received via SQL*Net from client 18 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 249 rows processed

  29. Cache More ops$tkyte%ORA11GR1> select * from ( 2 select /*+ result_cache */ 3 owner, object_type, count(*) cnt 4 from t 5 group by owner, object_type 6 order by owner, object_type 7 ) 8 where cnt > 100 9 / 38 rows selected.

  30. Cache More Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 1516 bytes sent via SQL*Net to client 442 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 38 rows processed

  31. Cache More SQL> create or replace 2 function not_cached 3 ( p_owner in varchar2 ) 4 return number 5 as 6 l_cnt number; 7 begin 8 select count(*) 9 into l_cnt 10 from t 11 where owner = p_owner; 12 dbms_lock.sleep(1); 13 return l_cnt; 14 end; 15 / Function created. SQL> create or replace 2 function cached 3 ( p_owner in varchar2 ) 4 return number 5 result_cache 6 relies_on(T) 7 as 8 l_cnt number; 9 begin 10 select count(*) 11 into l_cnt 12 from t 13 where owner = p_owner; 14 dbms_lock.sleep(1); 15 return l_cnt; 16 end; 17 / Function created.

  32. Cache More SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.93 SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.29 SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.07

  33. Cache More SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.09 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01

  34. Cache More SQL> update t set owner = initcap(owner) where rownum = 1; 1 row updated. SQL> commit; Commit complete. SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.25 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01

  35. Cache More SQL> exec dbms_output.put_line( cached( 'SYS' ) ); 29339 PL/SQL procedure successfully completed. Elapsed: 00:00:01.21 SQL> exec dbms_output.put_line( cached( 'SYS' ) ); 29339 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00

  36. #3 Standby Just got better

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

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

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

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

  41. Active Data Guard – More than a Standby Disasterprotection only Disaster and performance protection Recoverymode only Simultaneous readand recovery Used in disaster only Use daily in testingand production Manual intensive Automated Low ROI High ROI

  42. #4 Real Application Testing

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

  44. #5 Smaller more secure DMP files

  45. Datapump • COMPRESSION • ALL, DATA_ONLY, METADATA_ONLY, NONE $ expdp / directory=tmp dumpfile=uncompressed.dmp compression=NONE schemas=ops\$tkyte Export: Release 11.1.0.6.0 - Production on Friday, 21 September, 2007 12:23:26 . . exported "OPS$TKYTE"."BIG_TABLE" 24.57 MB 250000 rows . . exported "OPS$TKYTE"."T" 6.791 MB 67945 rows $ expdp / directory=tmp dumpfile=compressed.dmp compression=ALL schemas=ops\$tkyte Export: Release 11.1.0.6.0 - Production on Friday, 21 September, 2007 12:23:58 . . exported "OPS$TKYTE"."BIG_TABLE" 3.110 MB 250000 rows . . exported "OPS$TKYTE"."T" 762.1 KB 67945 rows $ ls -l /tmp/*compressed.dmp -rw-r----- 1 ora11gr1 ora11gr1 4124672 Sep 21 12:24 /tmp/compressed.dmp -rw-r----- 1 ora11gr1 ora11gr1 33136640 Sep 21 12:23 /tmp/uncompressed.dmp

  46. Datapump • ENCRYPTION • All • Data_only • Metadata_only • None • Encrypted_columns_only • PARTITION_OPTIONS • None • Departition • Merge • REUSE_DUMPFILES • Ability to use DML error logging features • DATA_OPTIONS parameter

More Related