Скачать презентацию Insert Picture Here Top 10 no make Скачать презентацию Insert Picture Here Top 10 no make

a6e9a71ab5a0f3f12278beb9b2e581c6.ppt

  • Количество слайдов: 87

<Insert Picture Here> Top 10, no – make that 11, things about Oracle Database Top 10, no – make that 11, things about Oracle Database 11 g Release 1 Thomas Kyte http: //asktom. oracle. com

The Beginning. . . del Mo l ona lati r Re f o ed The Beginning. . . del Mo l ona lati r Re f o ed “A • • har ge S nks” Lar aba at D 970 -1 odd F. C E. Data Model with Structure Data Independent of Code Set-oriented 1977 the work begins

First RDBMS: Version 2 June 1979 • FIRST Commercial SQL RDBMS • Impressive First 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 – PDP 11, Dec VAX

Portability: Version 3 March 1983 • New Implementation Designed for Portability • Written in 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

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

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

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

Cooperative Server: Oracle 7 June 1992 • Architectural and Performance Improvements • Shared SQL Cooperative Server: Oracle 7 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 Oracle 7 adds multilevel security • V 6 was all about architecture, V 7 about features

Oracle 7. 1 May 1994 • ANSI/ISO SQL 92 Entry Level • Advanced Replication Oracle 7. 1 May 1994 • ANSI/ISO SQL 92 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

Oracle 7. 2 May 1995 • • • Resizable, autoextend data files Shrink Rollback Oracle 7. 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

Oracle 7. 3 February 1996 • Partitioned Views • Bitmapped Indexes • Asynchronous read Oracle 7. 3 February 1996 • 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 Ÿ 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

Oracle 8. 0 “Warning Objects may be closer than they appear” June 1997 • Oracle 8. 0 “Warning Objects may be closer than they appear” June 1997 • • • Object Relational database SQL 3 standard Call external procedures LOB >1 per table Partitioned Tables and Indexes (range) Advanced Queuing for message handling 1000 columns/table Parallel DML statements Net 8 Connection Pooling Performance improvements in OPS - global V$ views introduced across all instances Ÿ 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

Oracle Database Innovation 30 years of sustained innovation … Audit Vault Database Vault Grid Oracle Database Innovation 30 years of sustained innovation … Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private Database Built in Java VM Partitioning Support Built in Messaging Object Relational Support Multimedia Support Data 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 … continuing with Oracle Database 11 g 2007

#1 Encrypted Tablespaces #1 Encrypted Tablespaces

Encrypted Tablespaces • Oracle Database 10 g Release 2 introduced column encryption • Could Encrypted Tablespaces • Oracle Database 10 g Release 2 introduced column encryption • Could not range scan • Primary/foreign key issues • Tablespace encryption Removes those limitations • Many encryption algorithms • • 3 DES 168 AES 128 AES 192 AES 256

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

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

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

Encrypted Tablespaces ops$tkyte%ORA 11 GR 1> alter table t move 2 tablespace encrypted; Table Encrypted Tablespaces ops$tkyte%ORA 11 GR 1> alter table t move 2 tablespace encrypted; Table altered. ops$tkyte%ORA 11 GR 1> alter index t_idx rebuild 2 tablespace encrypted; Index altered.

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

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

#2 Cache More Stuff #2 Cache More Stuff

Cache More • Everyone knows the fastest way to do something is – to 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

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

Cache More ops$tkyte%ORA 11 GR 1> set autotraceonly ops$tkyte%ORA 11 GR 1> select /*+ Cache More ops$tkyte%ORA 11 GR 1> set autotraceonly ops$tkyte%ORA 11 GR 1> 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.

Cache More Execution Plan -------------------------------------------------| Id | Operation | Name | Rows | Bytes Cache More Execution Plan -------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 5 cwffcum 1 ajfud 1088 m 1 m 73 f 81 | | 2 | | 3 | RESULT CACHE SORT GROUP BY | TABLE ACCESS FULL| T | 58742 | 1606 K| 284 (2)| 00: 04 | | 58742 | 1606 K| 280 (1)| 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"

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

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

Cache More ops$tkyte%ORA 11 GR 1> select * from ( 2 select /*+ result_cache Cache More ops$tkyte%ORA 11 GR 1> 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.

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

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

Cache More SQL> exec dbms_output. put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully Cache More SQL> exec dbms_output. put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00: 01. 93 SQL> exec dbms_output. put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00: 01. 29 SQL> exec dbms_output. put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00: 01. 07

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

Cache More SQL> update t set owner = initcap(owner) where rownum = 1; 1 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: 01. 25 SQL> exec dbms_output. put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00: 00. 01

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

#3 Standby Just got better #3 Standby Just got better

Standby Database • Logical Standby was… • Limited in type support • But was 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”

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

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

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

Active Data Guard – More than a Standby Disaster protection only Disaster and performance Active Data Guard – More than a Standby Disaster protection only Disaster and performance protection Recovery mode only Simultaneous read and recovery Used in disaster only Use daily in testing and production Manual intensive Automated Low ROI High ROI

#4 Real Application Testing #4 Real Application Testing

Real Application Testing – Database Replay • Recreate actual production database workload • Capture 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 Middle Tier Oracle DB servers Production Environment Capture DB Workload Storage Test (RAC) Environment` Replay DB Workload

#5 Smaller more secure DMP files #5 Smaller more secure DMP files

Datapump • COMPRESSION • ALL, DATA_ONLY, METADATA_ONLY, NONE $ expdp / directory=tmp dumpfile=uncompressed. dmp 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 ora 11 gr 1 4124672 Sep 21 12: 24 /tmp/compressed. dmp -rw-r----- 1 ora 11 gr 1 33136640 Sep 21 12: 23 /tmp/uncompressed. dmp

Datapump • ENCRYPTION • • • All Data_only Metadata_only None Encrypted_columns_only • PARTITION_OPTIONS • 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

#6 Virtual Columns #6 Virtual Columns

Virtual Columns • Create Table • Alter Table Add Column • Are ‘column expressions’ 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

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

Virtual Columns ops$tkyte%ORA 11 GR 1> select avg( sal+comm ) avg_comp, avg(sal) avg_sal, avg(comm) Virtual Columns ops$tkyte%ORA 11 GR 1> 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%ORA 11 GR 1> select count(case when sal+comm > 85376. 9437 then 1 end) above_comp, 2 count(case when sal 3 count(case when 4 from emp; ABOVE_COMP ABOVE_SAL ABOVE_COMM ----------33957 33830 34036 > 34821. 6827 then 1 end) above_sal , comm > 50555. 261 then 1 end) above_comm

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

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

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

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

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

#7 Partitioning just got better #7 Partitioning just got better

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

Enhanced Partitioning • Partition (or index) on virtual (computed) columns • New composite partitioning Enhanced Partitioning • Partition (or index) on virtual (computed) columns • New composite partitioning Range List Hash Range 11 g 9 i 8 i List 11 g 11 g S R DE OR N JA 0 500 > 000 0 1 0 50 B FE RANGE-RANGE Order Date by Order Value RS 00 >50 E RD O 0100 00 50 PE O UR AE US LIST-RANGE Region by Order Value S ER D ld Go OR Sil PE O UR AE US LIST-LIST Region by Customer Type ver

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

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

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

Partitioning ops$tkyte%ORA 11 GR 1> select partition_name, tablespace_name, high_value 2 3 from user_tab_partitions where Partitioning ops$tkyte%ORA 11 GR 1> select partition_name, tablespace_name, high_value 2 3 from user_tab_partitions where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE -----------------P 0 USERS TIMESTAMP' 2007 -09 -22 00: 00'

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

Partitioning ops$tkyte%ORA 11 GR 1> select partition_name, tablespace_name, high_value 2 3 from user_tab_partitions where Partitioning ops$tkyte%ORA 11 GR 1> select partition_name, tablespace_name, high_value 2 3 from user_tab_partitions where table_name = 'AUDIT_TRAIL'; PARTITION_ TABLESPACE HIGH_VALUE -----------------P 0 USERS TIMESTAMP' 2007 -09 -22 00: 00' SYS_P 66 EXAMPLE TIMESTAMP' 2007 -09 -23 00: 00' SYS_P 67 USERS TIMESTAMP' 2007 -09 -24 00: 00' SYS_P 68 EXAMPLE TIMESTAMP' 2007 -09 -25 00: 00' SYS_P 69 USERS TIMESTAMP' 2007 -09 -26 00: 00' SYS_P 70 EXAMPLE TIMESTAMP' 2007 -09 -27 00: 00' 6 rows selected.

#8 The long awaited pivot #8 The long awaited pivot

Pivot ops$tkyte%ORA 11 GR 1> select deptno, 2 sum( decode( job, 'CLERK', sal ) Pivot ops$tkyte%ORA 11 GR 1> select deptno, 2 sum( decode( job, 'CLERK', sal ) ) clerk, 3 sum( decode( job, 'SALESMAN', sal ) ) salesman, 4 sum( decode( job, 'MANAGER', sal ) ) manager, 5 sum( decode( job, 'ANALYST', sal ) ) analyst, 6 sum( decode( job, 'PRESIDENT', sal ) ) president 7 from emp 8 group by deptno 9 order by deptno 10 / DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT ---------- -----10 1300 2450 20 1900 2975 30 950 5600 2850 5000 6000

Pivot ops$tkyte%ORA 11 GR 1> select * 2 from (select deptno, job, sal 3 Pivot ops$tkyte%ORA 11 GR 1> select * 2 from (select deptno, job, sal 3 from emp ) e 4 pivot( sum(sal) for job in 5 ( 'CLERK', 'SALESMAN', 'MANAGER', 6 'ANALYST', 'PRESIDENT' ) ) 7 8 order by deptno / DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT' ---------- ----------10 1300 2450 20 1900 2975 30 950 5600 2850 5000 6000

#9 Flashback Data Archive #9 Flashback Data Archive

Flashback Data Archive Total Data Recall Select * from orders AS OF ‘Midnight 31 Flashback Data Archive Total Data Recall Select * from orders AS OF ‘Midnight 31 -Dec-2004’ Changes User Tablespaces Archive Tables Flashback Data Archive Oracle 11 g Database • 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

#10 Finer Grained Dependency Tracking #10 Finer Grained Dependency Tracking

Finer Grained Dependency Tracking • Fewer Invalidations • Add to a specification – so 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

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create or replace package p 1 Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create or replace package p 1 2 as 3 function f return number; 4 end; 5 / Package created. ops$tkyte%ORA 10 GR 2> create or replace package p 2 2 as 3 g_global number : = p 1. f; 4 end; 5 / Package created.

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> select object_name, status 2 3 from Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> select object_name, status 2 3 from user_objects where object_name like 'P_'; OBJECT_NAME STATUS ---------------P 1 VALID P 2 VALID

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create or replace package p 1 Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create or replace package p 1 2 as 3 function f return number; 4 procedure p; 5 end; 6 / Package created. ops$tkyte%ORA 10 GR 2> select object_name, status 2 3 from user_objects where object_name like 'P_'; OBJECT_NAME STATUS ---------------P 1 VALID P 2 INVALID

Finer Grained Dependency Tracking ops$tkyte%ORA 11 GR 1> create or replace package p 1 Finer Grained Dependency Tracking ops$tkyte%ORA 11 GR 1> create or replace package p 1 2 as 3 function f return number; 4 procedure p; 5 end; 6 / Package created. ops$tkyte%ORA 11 GR 1> select object_name, status 2 3 from user_objects where object_name like 'P_'; OBJECT_NAME STATUS ---------------P 2 VALID P 1 VALID

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create table t ( x int, Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> create table t ( x int, y int ); Table created. ops$tkyte%ORA 10 GR 2> create or replace procedure p 2 as 3 begin 4 for x in ( select x, y from t ) 5 loop 6 null; 7 end loop; 8 end; 9 / Procedure created. ops$tkyte%ORA 10 GR 2> select status from user_objects where object_name = 'P'; STATUS ------VALID

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> alter table t add z int; Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> alter table t add z int; Table altered. ops$tkyte%ORA 10 GR 2> select status from user_objects where object_name = 'P'; STATUS ------INVALID ops$tkyte%ORA 11 GR 1> select status from user_objects where object_name = 'P'; STATUS ------VALID

Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> alter procedure p compile; Procedure altered. Finer Grained Dependency Tracking ops$tkyte%ORA 10 GR 2> alter procedure p compile; Procedure altered. ops$tkyte%ORA 10 GR 2> alter table t drop column z; Table altered. ops$tkyte%ORA 10 GR 2> select status from user_objects where object_name = 'P'; STATUS ------INVALID ops$tkyte%ORA 11 GR 1> select status from user_objects where object_name = 'P'; STATUS ------VALID

#11 OLTP Table Compression #11 OLTP Table Compression

Oracle Advanced Compression • Oracle 9 i compresses data only during bulk load; useful Oracle Advanced Compression • Oracle 9 i compresses data only during bulk load; useful for DW and ILM • Oracle 11 g compresses w/ inserts, updates • Trade some cpu for disk & i/o efficiency • Compress large application tables • Transaction processing, data warehousing • Compress all data types: structured, unstructured • Savings cascade to all db copies: test, dev, standby, mirrors, archiving, backup, etc.

<Insert Picture Here> Q&A Q&A