Tom Kyte’s Top 12 New Features of the Oracle Database 12c

Tom Kyte’s Top 12 Features of the 12c

  1. even better PLSQL — start a sql statement with a bit of PLSQL (compile PLSQL into SQL)
  2. Improved defaults – populate a column by sequence (new data type -> identity which automatically populates sequence); Default value on NULL
  3. Increased size limits (varchar2 can store up to 32k! just like PLSQL block)
  4. easy top-n and pagination queries; new row limiting clause – run this query and give rows 5–10 etc..
  5. row pattern matching; V-patters and W-patters; inside of SQL pattern matching clause (similar to reg-ex)
  6. partitioning: maintenance on multiple partitions at the same time; referenced and interval partitioning together; move partition data online (no need to use redefinition); async global index maintenance!
  7. adaptive execution plans; change SQL plan half way through execution; evaluates how many rows processed and adapts during runtime by changing the plan
  8. enhanced statistics: new histogram – deals much better with sets of data that has more than 235 distinct values in a column; automatically compute statistics during data loads
  9. temporary UNDO: global TEMPs generate REDO because of UNDO they generated; now 12c can store UNDO for TEMP tables in the TEMP tablespaces so that during DG sync database doesn’t have to sent UNDO for the TEMP
  10. data optimization: ILM cap: heat map is generated for each block of data – associates a temperature with block usage: you can use DDL language to describe to the DATABASE what and how to compress HOT, COLD and WARM blocks
  11. transaction guard: ensures TRX only happens once (hitting ENTER key twice example) – implemented via API – custom coding required
    application continuity (extension of TAF) before only READ-ONLY could be failed over; 12c allows READ-WRITE TAF and can replay it and rejoin on another node/DB
  12. Pluggable databases: solve multiple problems of database consolidation such as schema name collisions, PUBLIC SYNONYM collisions; run one ORACLE container instance and plug up to 252 databases into it. ONE SGA – significantly smaller footprint; ONE container database with multiple pluggable DBs running underneath it simplifies maintenance.

Multitenant Container Database
Pluggable databases
High level of consolidation – managing many databases as one

Unplugged database is built by the vendor ahead of time and is later delivered to a customer where it gets plugged in onsite

Automatic Database Optimization (ADO)
Automation of movement and compression of data
heat map – when data is accessed (insert update delete is tracked separate from selects)
tracked at table level partition level and block level
define policies based on heat map and the database will move data accordingly (to a different tier storage)
SAVE SPACE and IMPROVE performance and do it automatically

Maximum Availability Architecture
Foundation: RAC, DATAGUARD, GOLDEN GATE (logical replication), RMAN, FLASHBACK DB (human error correction)
Global data services – run DB service anywhere in datacenter, any location (implemented using ACTIVE DG and GOLDEN GATE)
Application continuity (API can be used to query the state of the transaction)

Security and Compliance
Data reduction: On the fly reduction – data is kept the way it is and is reducted at runtime
Privilege analysis – tells you what privileges were used during an operation
Conditional Auditing: I only want to audit specific user, specific time in a specific role
Real Application Security implemented using Oracle Audit vault and database firewall

Closing Notes:What does the C stand for: Consolidata, Compress, Control, Cloud, Customers

If you found this article helpful and would like to receive more like it as soon as I release them make sure to sign up to my newsletter below:


July 10, 2013

Posted In: Operations

Tags: ,

Notes from the 11g Upgrade & Best Practices hosted by Tom Kyte

I finally got out of the dungeon and made it to an Oracle hosted event titled “Oracle Technology Day11g Upgrade & Best Practices” which was hosted by Tom Kyte and Oracle Center of Excellence (COE) folks in Los Angeles on June 16 ’09. And it was definitely worth it. I got a chance to finally meet Tom Kyte face to face and I even had my original, WROX “Expert One-One-One” signed by him. The place was packed and I even heard that the event organizers had to turn some people down because there was no room left.

The presentation was focused on 11g upgrade — I guess Oracle has had it with us “conservative” DBAs that stick to our guns and don’t upgrade until the R2 is out. Well, they sure got my attention with the “Revolutionary New Capabilities brought to you by the upcoming 11g R2” … But before I get to that I figured I’ll run through the notes of the stuff that was most interesting to me as a DBA, so instead of just stuffing the notepad someplace where I will never find it I figured it would be beneficial to both you and I if I wrote it up here:

Interesting stuff to research:
Incident Packaging
Total Recall
Data Masking

Capture & Replay Workload AKA Real Application Testing:
You can now achieve realistic testing with Database Capture and Replay. DB Capture is capable of recording bind variables, SCN and order of operations on your production instance and then using DB Replay you can reproduce the same exact load on a test system which will allow you to compare metrics (v$..), test upgrades and even test capacity plan.

Database Capture is available as of and it can be used across platforms, so you can easily use Captured workload from and replay it on 11g test instance. Replay only works on 11g ( see Note:738538.1).

The core of Real Application Testing is SPA (SQL Performance Analyzer) [pronounced like “heath SPA”] and SQL Tuning Advisor. SPA can be used in conjunction with Replay to look for regressed SQL, allowing you to build acceptable optimizer plans for the new release of the database before you upgrade. Replay commits changes — use Flashback DB to revert back and replay. Grid Control can automate the Replay and restore of the database using GUI.

11g R2 will be able to UP the workload so it can be effectively used for future capacity planning.

Advise: when making comparisons using these tools rely on CPU TIME and BUFFER GETS rather than ELAPSED TIME.

Replay clients are called WRC. You can start multiple WRC processes to simulate concurrency. Filters can be used during Capture to limit what is being recorded. 11g R2 will also have selective replay capability.

Data Recovery Advisor:
If you want a second pair of “eyes” or have no idea what to do when your database requires recovery — Data Recovery Advisor might be able to help. Of course this requires you to use RMAN to backup your database in the first place, but you should be already doing that anyway right? But it also requires RMAN Catalog …

Flashback Data Archive:
Flashback is designed to “cure” the “right command / wrong database syndrome“, where you urgently want to UNDO a mistake.

9i – brought to us Flashback Query
10g – Flashback Tables and DB
11g – brings Flashback Data Archive & Transactions

Flash Data Archive is enabled on a table by table basis, where once a table is placed in the Data Archive a shadow table is created where all changes are recorded and kept. It works through a new background process and has very little (if any) overhead because it mines the UNDO. This is the best option for auditing and should replace all of your DIY audit triggers. While Audit triggers can be disabled or dropped, once a table is placed in the flash data archive it cannot be dropped even by the SYS DBA – perfect AUDIT.

Revolutionary New Capabilities brought to you by the upcoming 11g R2:
Expect to hear a lot of buzz about 11g R2 by October ’09. 80% of the Open World “talk” will be about 11g R2. What can we expect? How about “Upgrade Database Application” Online using a new, “revolutionary” feature called “ADDITION“?

create ADDITION v2;
alter session set ADDITION = v2;
— from now on everything takes place in this new virtual schema called v2 —
[compile new PKGs]
[add indexes and tables ???] Tom mentioned later that ADDITIONs only work for things that “live” in SYSTEM, i.e. compiled and virtual stuff, so this might not work for tables and indexes …
[v1/original is still fully operational and running]
[test v2]
[pause DB]
alter database set schema XYZ to V2 addition; [not real syntax …]

Obviously, this will only work if you use PL/SQL. V2 becomes the current ADDITION once it’s enabled. V1 can be kept around if you desire so. This is NOT a source code control — it’s only designed to easily deploy database applications without disrupting your online users.

11g Upgrade Best Practices:
Upgrading to 10g? Review Note:466181.1
Upgrading to 11g? Review Note:601807.1

Oracle support really, really wants you to use OCM (Oracle Configuration Manager). They also want you to use the new Metalink (I don’t use it just in case you wanted to know …).

USE DBUA (I agree). DBUA == easy. Manual Upgrade == hard.

Backup your optimizer stats — see Note:465787.1

Don’t set any optimizer_* or db_multiblock_* params that you found on google. This is BAD practice. Most of the default values are good for 99% of installations — don’t touch them.

Gather dictionary statistics before you upgrade, it can dramatically improve the time it takes to upgrade your database.

Post upgrade run:
execute DBMS_STATS.GATHER_SYSTEM_STATS(start); [not exact command]
execute DBMS_STATS.GATHER_SYSTEM_STATS(stop); [not exact command]

On 11g use AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT — this is 5-6 times faster and close to 100% accurate.

Export AWR repository BEFORE the upgrade.

Plan Stability:
On 11g use Plan Stability feature — see DBMS_SPM. SPM allows you to create SQL Baseline where only known and accepted plans are used for specific SQL. This can be either automated or setup manually by DBA. SPM works in three phases:

  1. Capture (stored in SYSAUX)
  2. Selection (DBA marks what’s accepted)
  3. Evolution (EVAL all unverified plans to see if they are worth considering)

Selection: OPTIMIZER_USE_SQL_PLAN_BASELINES = true + The Plan must be ENABLED=YES AND ACCEPTED=YES to be used by SPM.
Evolution: Scheduled by DBA via EVAL procedure.

See Note:456518.1.

You can also pack the stored SQL Plan Baselines on DEV and then unpack them into PROD. Great thing to ask of your vendors — provide the SQL Plan Baselines with all your code.

If you are going to 11g from 10g you can utilize STS — create STS (SQL Tuning Set) on 10g, unload them to staging table and load them into 11g’s plan baseline.

Some Random Bits and funny Pieces:
Evolution of APEX:
Browse (good) -> WebDB (good) -> Portal (messy) -> dead end
few years later …
HTML DB (good) -> APEX (better)

The guy who wrote APEX (Mike Hichwa) is also largely responsible for existence of SQLPLUS and SQL Developer.

Bryn Llewellyn owns PLSQL.

DBMS_OUTPUT.put_line (danger)
sys.DBMS_OUTPUT.put_line (safe)

Tom Kyte:

This is a really big table. Really? How big? It’s really big — imagine the largest table and insert one more record into it — that’s how big.

Rexx is my favorite language …


June 17, 2009

Posted In: Events

Tags: , , , , , , , , , , , , , , , ,

09′ IOUG Collaborate – notes for Oracle DBAs

I just reviewed Tom Kyte’s presentation from the 09′ IOUG Collaborate event and made a list of things that are specifically interesting to an Oracle DBA with the links for further research into each feature.

I. Those managing PHP/Oracle shops will be happy to learn that there’s a new Database Resident Connection Pool [DRCP] that performs as well as dedicated server (it really is) with benefits of shared server model.

II. Are you sill using TOAD? Perhaps it’s time to give Oracle Sql Developer a shoot because it’s offering a host of Sql/PLSQL Tuning Features built right in:

June 10, 2009

Posted In: Tuning

Tags: , , , , , ,