OpenWorld 2012 Report Oracle Database 12c Features

1. OpenWorld 2012 ReportOracle Database 12c Features

2. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle`s products remains at the sole discretion of Oracle.

3. Improved Defaults – when null SQL> create table t 2 ( x number primary key, 3 y number, 4 z number default on null 42 5 ); Specify null specifically gets null. Specify nothing gets 42. Specify value gets value.

4. Improved Defaults – default to a sequence SQL> create table t 2 ( x intdefault s.nextval primary key, 3 y varchar2(30) 4 ) 5 / Table created.

5. Improved Defaults – identities SQL> create table t 2 ( x number generated as identity, 3 y number 4 ) x increments each time a table is created Similar to a sequence, but automatic Like MS SQL Server

6. Improved Defaults – identities cont… SQL> create table t 2 ( x number generated by default 3 as identity 4 (start with 42 5 increment by 1000), 6 y number 7 ) x increments each time a row is created Value can be overridden on insert Uses identity value if not overridden

7. Improved Defaults – metadata only defaults To add a column that allows null and has a default value (like a fast add column on 11g, but with null allowed) SQL> alter table t add (data char(2000) default 'x'); What would this do on 11g? On 12c Oracle just remembers the value No additional storage No long wait for value to be inserted in each row

8. 32K Strings Varchar2, NVarchar2 and Raw datatypes may be up to 32K in size Max_SQL_String_Size init.ora set to EXTENDED (default is not this) Not supported in clustered and index organized tables Will be stored out of line (LOB) but work just like long strings to your program

9. Invisible Columns Add a column that won’t break a program that already does select *

10. Metadata only add column Add a column that won’t appear in the table until a certain time/date One use to coordinate with future code changes, but be able to do the work ahead of time

11. Alter table improvement Alter table … wait ??? If an alter table needs to get a lock on a table and can’t, instead of failing it waits until it CAN get the lock

12. Code Based Access Control On 11g a procedure or function runs with either rights of creator or invoker On 12c you can grant a role to a procedure Can use a white list to limit which other procedures can call a procedure

13. Row limiting clause (top n) SQL> select /*+ first_rows(5) */ owner, object_name, object_id 2 from t 3 order by owner, object_name 4 FETCH FIRST 5 ROWS ONLY;

14. Row limiting clause cont… --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 1450 | 7 (0)| 00:00:01 | |* 1 | VIEW | | 5 | 1450 | 7 (0)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY | | 5 | 180 | 7 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T | 87310 | 3069K| 7 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | T_IDX | 5 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)

15. Row limiting clause cont… SQL> select /*+ first_rows(5) */ owner, object_name, object_id 2 from t 3 order by owner, object_name 4 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; Gets the second five rows What if order by columns are not unique?

16. Row limiting clause cont… SQL> select owner, object_name, object_id 2 from t 3 order by owner, object_name 4 FETCH NEXT 0.01 PERCENT ROWS ONLY; Useful? Slow

17. Adaptive Execution Plans Table with 10 million rows According to statistics there are 10 rows where x=123 What kind of plan would you expect if you select where x=123? What if that cardinality were wrong? What if that select is 4 levels deep in nested loops?

18. Adaptive Execution Plans cont… What if the database noticed that it was getting a lot more x=123 than the cardinality of 10 that it was given? What if it were smart enough to switch, mid execution, to a different, more appropriate join type to complete the statement much faster than it otherwise would have? That’s the idea behind adaptive execution plans.

19. Dynamic Sampling • New value (11) for dynamic sampling • When set to 11 • Dynamic sampling is automatic • Dynamic sampling is persistent • Automatically set to 11 for parallel query

20. Partitioning Improvements • Asynchronous Global Index Maintenance for DROP and TRUNCATE partition • DROP and TRUNCATE complete immediately • Cascade Functionality for TRUNCATE and EXCHANGE partition • Multiple partition operations in a single DDL • Online move of a partition (without DBMS_REDEFINITION)

21. Partitioning Improvements cont… DROP and TRUNCATE complete immediately DB maintains a list of invalid data object ids and ignores those entries in the index from then on Automatic scheduler job PMO_DEFERRED_GIDX_MAINT_JOB will run to clean up all global indexes Can be run manually Alter index [partition] CLEANUP is another approach

22. Histograms • Histograms can be inaccurate when the data has • Some really popular values • Some really unpopular values • Some almost popular values • More than 254 distinct values • 12c adds two new types of histograms to address issues

23. Statistics During Loads SQL> create table t 2 as 3 select * 4 from stage; Now generates statistics automatically Also insert append on empty table

24. Temporary UNDO • UNDO for temporary tables can now be managed in TEMP • Reduce the amount of UNDO in the UNDO tablespace • Better for retention periods for “real” data • Reduce the size of the redo generated • Allows for DML on temporary tables in Active Data Guard • ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true|false

25. Information Lifecycle MgtHot/Cold Data Classification • Enhanced Insight into Data Usage: “heat map” • Block and segment level statistics on last read and last update • Allows you to determine which data is • Active • Infrequently updated, frequently queried • Dormant

26. Information Lifecycle Mgt cont…Automatic Compression & Tiering Usage based and custom compression and tiering Define a compression policy and a move policy for partitioned tables ALTER TABLE orders ILM ADD CompressionPolicy COMPRESS Partitions for Query AFTER 90 days from creation; (infrequent) ALTER TABLE sales ILM ADD MovePolicy TIER Partitions TO ‘Archive_TBS’ ON OrdersClosedPolicy; (dormant) Additional license/licensed products required?

27. Transaction Guard Interrupted transactions can be automatically replayed. Known outcome for every transaction At-most-once transaction execution Used by Application Continuity (next slide) Available for JDBC-thin, OCI, OCCI, ODP.net Without Transaction Guard, retries can cause logical corruption Extra cost?

28. Application Continuity Mask planned/unplanned outages Handle delayed transactions/duplicate transactions Improves end user experience Improves developer productivity Application transparent when using Oracle stack Enabled with WebLogic Server, Peoplesoft, Fusion Apps, Siebel(possibly) Extra cost?

29. Pluggable Databases Multiple schemas vs multiple DBs debate Now the ability to manage multiple DBs as one DB Create a root DB and plug other DBs into it Root instance governs all (one set of processes, one SGA, one backup, one recovery, etc.)

30. Pluggable Databases cont… • Some functions can still be done by PDB • Point-in-time recovery • Flashback PDB (12.2) • Replication of table, schema, or full PDB • Extra cost?

31. Pluggable Databases cont…Upgrades from 12.1 onwards Unplug and Plug

32. Pluggable Databases cont…Create clones easilyOn clone triggers make it possible to scrub or mask data

33. Pluggable Databases cont…Performance for individual DBs vs PDBs with one root Performance comparable in both cases PDBs continued performing through 100 PDB’s while separate database instances hit the wall at 50 instances PDBs use 6x less resource compared to separate database instances 3GB vs 20GB memory, for 50 databases 27% vs 36% CPU, for 50 databases

34. Privilege Analysis • When you create a new user • You want to use the concept of least privileges • You don’t know what privileges they really need, maybe just give them SELECT ANY TABLE • That is not very secure and hard to justify to an auditor • For an existing user • Of the set of privileges they have, which ones do they actually use?

35. Privilege Analysis Can now track privilege use by Entire database, all users except SYS, any privilege used A set of roles, if someone uses a specific set of roles privileges, DB records it A condition based on SYS_CONTEXT (all of the userenv stuff like session_user, action, module, etc) A combination of a set of roles and a condition

36. Privilege Analysis cont… • For existing users • Track what they use and remove what they don’t • For new users • Give them what they say they need, or what you believe they need • Track what they use and remove what they don’t

37. Privilege Analysis cont… Start with dbms_privilege_capture.enable_capture Stop with dbms_privilege_capture.disable_capture Analyze using dba_used_privs Many other views (show what wasn’t used for example) as well

38. Data Redaction On-the-fly redaction based upon user name, IP address, application context, and other factors Transparent, consistent enforcement in the database Minimal impact on production work loads Applies to all methods of access, ie, SQL*Plus, TOAD, application, etc.

39. Data Redaction cont… Data is in the clear on the DB, but no one can see it except those who are allowed

40. Data Redaction cont… Supported Transforms

41. Auditing Completely rewritten Single Audit trail for Database Installation,   Database Vault, Label Security, Real Application Security, RMAN, Data Pump, Direct Path Loads, normal auditing No more fine grained vs normal auditing Create policies rather than rules Created in an insert only tablespace

42. Auditing cont… • Audit trail is managed only via the audit trail package DBMS_AUDIT_MGMT • Only administrators with new role can access that • Separation of Duty • Audit administrator role to create and implement audit trails • Audit user role to view audit data • Old auditing still works on 12c, but…

43. Separation of Duties • In the past we had SYSDBA and SYSOPER. • On 12c • SYSBACKUP - Use this to use RMAN instead of SYSDBA – Just enough to do backups • SYSDG - Use this to use Data Guard instead of SYSDBA - Just enough to run Data Guard • SYSKM - Use this to perform key management instead of SYSDBA - Just enough to perform key management • AUDITING – Use this to administer/view auditing instead of SYSDBA – Unclear if SYSDBA can still do AUDITING