1 / 69

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.

elpida
Download Presentation

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

  6. #1 Encrypted Tablespaces

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

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

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

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

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

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

  13. 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$%')

  14. #2 Cache More Stuff

  15. 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 • Lots more detail in afternoon session

  16. #3 Standby Just got better

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

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

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

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

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

  22. #4 Real Application Testing

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

  24. #5 Smaller more secure DMP files

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

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

  27. #6 Virtual Columns

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

  29. Virtual Columns ops$tkyte%ORA11GR1> /* ops$tkyte%ORA11GR1> create table emp ops$tkyte%ORA11GR1> as ops$tkyte%ORA11GR1> select all_objects.*, object_id sal, round(dbms_random.value( 1000, 100000 )) comm ops$tkyte%ORA11GR1> from all_objects ops$tkyte%ORA11GR1> / ops$tkyte%ORA11GR1> ops$tkyte%ORA11GR1> */ ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'EMP' ) PL/SQL procedure successfully completed.

  30. Virtual Columns ops$tkyte%ORA11GR1> select avg( sal+comm ) avg_comp, avg(sal) avg_sal, avg(comm) avg_comm from emp; AVG_COMP AVG_SAL AVG_COMM ---------- ---------- ---------- 85376.9437 34821.6827 50555.261 ops$tkyte%ORA11GR1> select count(case when sal+comm > 85376.9437 then 1 end) above_comp, 2 count(case when sal > 34821.6827 then 1 end) above_sal , 3 count(case when comm > 50555.261 then 1 end) above_comm 4 from emp; ABOVE_COMP ABOVE_SAL ABOVE_COMM ---------- ---------- ---------- 33957 33830 34036

  31. Virtual Columns ops$tkyte%ORA11GR1> select * from emp where sal > 34821.6827; -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 34673 | 3893K| 309 (1)| 00:0 |* 1 | TABLE ACCESS FULL| EMP | 34673 | 3893K| 309 (1)| 00:0 -------------------------------------------------------------------- ABOVE_COMP ABOVE_SAL ABOVE_COMM ---------- ---------- ---------- 33957 33830 34036

  32. Virtual Columns ops$tkyte%ORA11GR1> select * from emp where comm > 50555.261; -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33943 | 3811K| 309 (1)| 00:0 |* 1 | TABLE ACCESS FULL| EMP | 33943 | 3811K| 309 (1)| 00:0 -------------------------------------------------------------------- ABOVE_COMP ABOVE_SAL ABOVE_COMM ---------- ---------- ---------- 33957 33830 34036

  33. Virtual Columns ops$tkyte%ORA11GR1> select * from emp where sal+comm > 85376.9437; -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3398 | 381K| 309 (1)| 00:0 |* 1 | TABLE ACCESS FULL| EMP | 3398 | 381K| 309 (1)| 00:0 -------------------------------------------------------------------- ABOVE_COMP ABOVE_SAL ABOVE_COMM ---------- ---------- ---------- 33957 33830 34036

  34. Virtual Columns ops$tkyte%ORA11GR1> ALTER TABLE emp ADD (comp AS (sal+comm)); Table altered. ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'EMP', method_opt=> 'for columns comp' ); PL/SQL procedure successfully completed.

  35. Virtual Columns ops$tkyte%ORA11GR1> select * from emp where sal+comm > 85376.9437; -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33927 | 3975K| 309 (1)| 00:0 |* 1 | TABLE ACCESS FULL| EMP | 33927 | 3975K| 309 (1)| 00:0 -------------------------------------------------------------------- ABOVE_COMP ABOVE_SAL ABOVE_COMM ---------- ---------- ---------- 33957 33830 34036

  36. #7 Partitioning just got better

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

  38. Partition (or index) 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

  39. 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 ... ... Jan 2006 Feb 2006

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

  41. Partitioning ops$tkyte%ORA11GR1> create table audit_trail 2 ( ts timestamp, 3 data varchar2(30) 4 ) 5 partition by range(ts) 6 interval (numtodsinterval(1,'day')) 7 store in (users, example ) 8 (partition p0 values less than 9 (to_date('22-sep-2007','dd-mon-yyyy')) 10 ) 11 / Table created.

  42. Partitioning ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value 2 from user_tab_partitions 3 where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ---------------------------------- P0 USERS TIMESTAMP' 2007-09-22 00:00:00'

  43. Partitioning ops$tkyte%ORA11GR1> insert into audit_trail 2 select sysdate+rownum, 'x' 3 from all_users 4 where rownum <= 5 5 / 5 rows created.

  44. Partitioning ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value 2 from user_tab_partitions 3 where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ---------------------------------- P0 USERS TIMESTAMP' 2007-09-22 00:00:00' SYS_P66 EXAMPLE TIMESTAMP' 2007-09-23 00:00:00' SYS_P67 USERS TIMESTAMP' 2007-09-24 00:00:00' SYS_P68 EXAMPLE TIMESTAMP' 2007-09-25 00:00:00' SYS_P69 USERS TIMESTAMP' 2007-09-26 00:00:00' SYS_P70 EXAMPLE TIMESTAMP' 2007-09-27 00:00:00' 6 rows selected.

  45. Partitioning ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value 2 from user_tab_partitions 3 where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ------------------------------- P0 USERS TIMESTAMP' 2007-09-22 00:00:00'

  46. Partitioning ops$tkyte%ORA11GR1> insert into audit_trail values ( add_months(sysdate,12), 'x' ); 1 row created. ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value 2 from user_tab_partitions 3 where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ------------------------------- P0 USERS TIMESTAMP' 2007-09-22 00:00:00' SYS_P180 EXAMPLE TIMESTAMP' 2008-11-10 00:00:00'

More Related