More oracle 12C enhancements

Here are some basic notes on some enhancements that are part of the oracle 12c database release:

• Varchar can be 32K, you need to change a DB parameter, Max_string_size.  You also need to bounce the DB.
• Default / default on NULL.  So you can have a default value when NULL is specified (i.e. 50, or "something").  This is how you define a column when creating or altering a table.
• Identity column self populating uniqueness - finally.  This has taken a while.  Same concept in all other databases.
• Can create an invisible column – alter table.  Select * won’t show it, desc won’t show it, but if you know the column you’ll see it when you specifically mention in in your SQL statement.
• FETCH first 5 rows is available now, so you cal select the first 5 rows of a table - finally!!!  But remember if you sort, it still might take a long time - as the entire set needs to be sorted before giving you back your 5 rows.
• Data Redaction – mask data dynamically.  This is being back ported to 11.2.0.4.  Does not change data, just will show the field with a mask that you've defined.  This would be VERY cool for JD Edwards.  You could apply this on employee information and ALL reports and apps would show your defined redaction mask, instead of the actual values.  This is done with security in the database.  I believe that you need the advanced security option for EE.

TimesTen - Application Tier In-Memory Database for Performance and High Availability

Adapted from Tirthankar's #OOW13 presentation - thanks!

TimesTen is a separate database with separate binaries.  It shares a lot of functionality with the oracle database, but it's very different.  This is a listing of facts and figures from a presentation on the TimesTen database.  I need to be honest with you, if you're using JD Edwards this is going to be a tough option - ESPECIALLY now that oracle in-memory has been released in 12c.

A special note too, there is no columnar organisation in TimesTen, it's only available in the oracle in-memory option.  Imagine what is going to occur when this is implemented! There is also no compression in the "non exalytics" version of TimesTen.

TimesTen is the in-memory database for the application tier or used as a OEE cache.  
TimesTen is part of the exalytics machine (Adaptive in-memory cache).  Exalytics brings in the data from other systems into a TimesTen database and that is used for analytics.

TimesTen is very light weight, so it's thought that it'll not be replaced by oracle in-memory.

TimesTen is often used with very specialised applications. It's a standard relational database.  TimesTen is only in memory - no caching, the entire database is in memory all of the time.  TimesTen is also persistent.  It logs, checkpoints and can do replication.

TimesTen uses standard config. standard SQL, OCI etc.  TimesTen was launched in 1998, oracle bought it in 2005.  

This is a stand-alone purchase, separate from OEE.

Communications industry uses TimesTen a lot.  TimesTen could cache a subset of data and be called upon from the oracle database.  It's a very specialised database that is used is low latency environments.

I know that this is a JD Edwards blog, I need to determine if TimesTen is supported on the enterprise server, it seems like this would fly!  It's doubtful that you could use TimesTen is a supported environment for JD Edwards, but I think that it would work.

Some stats:
Readers and non-blocking.  read response time, 2.37 micro-seconds.  49 million reads per second.  updates in 7.67 micro-seconds.  This is database SQL operations that return in micro-seconds.  These numbers are based upon real transactions.  TimesTen can perform close to 1000000 updates per second, limited by bandwidth for logging and checkpointing.  An OLTP mix of reads and writes 2500000 operations per second on commodity hardware.

T5-8 can perform 50000000 SQL statements per second (128 cores, 1024 threads).  Mixed workload was 3.5 million operations per second.

You can use EM12C to manage your TimesTen database.

In-Memory Database cache.  It can be kept in sync with the database.  This TimesTen database can complement the oracle database.  

There was a great case study with USPS and the online stamp printing website.  TimesTen was used to check timestamps of postage to ensure that it's not a duplicate of a previous stamp print.  The savings in fraud easily paid for the investment in TimesTen.  They need the speed and the throughput because of the sorting machines that validate the postage need to run so quickly.

12c enhancements continued

12c is the most significant database release in the last 7 years from oracle.  Then enhancement list is SIGNIFICANT, analogous to when RAC was released.

You already know of in-memory (TimesTen) databases and also multitenant, here is a few more items included in the latest release.

Disk heat map

The latest oracle database release,12c has got a heat map for data, you can use the data from the heat map and decide what you are going to do with the data.  You can set policies on when data is compressed and destination of the data.  12 compression options are Advanced row, columnar query & columnar archive compression.

Remember that you can expose the heat map through 12c cloud control or through EM for the standalone database.

Hot - 3x - advanced row compression
Warm - 10x - Columnar Query compression
Archive - 15x - Columnar archive compression

You do not need the advanced compression option to run row compression.  You do however need this database option to enable the columnar compression.

Active Data Guard for Far Sync

far sync is a combo of sync and async.  Just an instance that has the redo logs, a "handoff point".  sync to the far sync instance.  Then you have async to the standby.

Need active data guard licence.

Far sync is basically included with your ADG and EE licensing.

Global Data Services

Load balancing and service failover for replicated databases.  Can coordinate read requests between data centres.  Routing and failover of reads.  Note that this might force a read from the primary.

Transaction Guard

API knows commit outcome for every transaction, could replay the transaction if there was a failure.  There is a token with every transaction.  This can run in the JDBC driver.  IT can watch for the transaction state.  This can preserve and retrieve commit status'.

Data Redaction

Data masking.  Replace sensitive application data when replicating databases.  12c you can subset and mask "in-flight".  Data Redaction works on the display of sensitive data.  Transparent to applications users and operational activities.  This is enforced at the database.  This would be great for JD Edwards. This is a feature of the "Advanced Security" database option.

Database Activity Monitoring and Firewall

enhancement to audit vault to add the firewall capability.  Can be used on different databases.

database enhancements continued - PDBs

The list of database options continues to grow, but so does the price of oracle Enterprise Edition.  Multitenant databases is another database option that can only be licensed under enterprise edition.

Enterprise Edition Options:
Multitenant	350	77.00	17,500	3,850.00
Real Application Clusters	460	101.20	23,000	5,060.00
Real Application Clusters One Node	200	44.00	10,000	2,200.00
Active Data Guard	230	50.60	11,500	2,530.00
Partitioning	230	50.60	11,500	2,530.00
Real Application Testing	230	50.60	11,500	2,530.00
Advanced Compression	230	50.60	11,500	2,530.00
Advanced Security	300	66.00	15,000	3,300.00
Label Security	230	50.60	11,500	2,530.00
Database Vault	230	50.60	11,500	2,530.00
OLAP	460	101.20	23,000	5,060.00
Advanced Analytics	460	101.20	23,000	5,060.00
Spatial and Graph	350	77.00	17,500	3,850.00
In-Memory Database Cache	460	101.20	23,000	5,060.00

See the image above for the new options and the new prices.

Multitenant is an exciting option that has created the concept of container databases and pluggable database - PDB.  The container database is able to have multiple PDB's within in.  This lets you have separate security and management of the PDB's within a single container database.

This would be great for hosting and for cloud provisioning.  This would suit JD Edwards use, but not necessary.  If you were provisioning JD Edwards in the cloud, then this would be a great option.

This is licensed at 17.5K per core, as the above table states.

In-Memory Database options

This is a great announcement that has come out of #oow13.  I was lucky enough to attend the keynote that made this announcement, unlucky enough to spend 30 mins leaving the venue...

So the data is being stored in columnar format "in-memory", so this allows for you to drop indexes and reap the benefits of some incredible performance gains.  When your architecture is up to it, you can get benefits way in excess of the 100's times claim.  This is technology that is really going to test your architecture.  It's relying on the solid CPU / memory backbone, therefore the more tightly coupled this is with the database - the better the performance gains might be.  Note also that the closer oracle are getting to putting DB instructions on silicon will mean the benefits from this type of technology are only going to get more "real".  

Traditionally a heterogeneous database ERP like JD Edwards does NOT stress the database out that much.  The database is too busy doing reads (waiting for the IO) to be stressed.  Choosing the right tables to cache and having a lot of memory (remember, this is in-memory - you are going to need a lot of memory) might start putting some more stress on the JD Edwards database - nice!

"Oracle In-Memory Database Cache (IMDB Cache) enables database applications to selectively cache performance-critical subsets of the Oracle Database tables into the TimesTen In-Memory Database to improve application response time. "

The above statement indicates that there are two products in play here.  The "TimesTen In-Memory" database and the Oracle In-Memory database cache.  The TimesTen is the engine, this is generally deployed at the application teir.  Note also that because this is on the application tier, it would need to be kept "up-to-date" in an OLTP environment. 

The in-memory database cache is the "poor mans" implementation of the TimesTen database.  This is putting your data into a virtual TimesTen database without changing the application logic - everything through tnsnames.

In a JD Edwards environment, I'd guess that the in-memory database cache is your best option.

http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21631/img/findingrecords.gif

The above diagram shows the totally different path to the data.

This is a true database option that is easy to enable on a table by table basis.  You initialise your 12c database to enable this option (note that you'll need to get your cheque book out too), initial RRP indications have it at about 23K per "oracle processor" [or core].

The Oracle In-Memory Database Cache option supports Oracle Database 12c, Oracle Database 11g Release 2, and Oracle Database 11g Release 1.

A good answer to try and explain what you need to run this option is found at:

2. Can Oracle TimesTen In-Memory database be used as an in-memory cache to the Oracle database?
Yes, this is the Oracle database option ' Oracle In-Memory Database Cache". This database option includes the TimesTen In-Memory Database, and caching technologies to enable TimesTen to be deployed as  an in-memory cache database  with automatic data synchronization between TimesTen and the Oracle database.

So this says that when you enable the "in-memory" option for your database, it deploys TimesTen to be deployed as a in-memory database cache.

3. Can I run Oracle In-Memory Database Cache on a different platform from the Oracle database server?
Yes, since In-Memory Database Cache runs as an Oracle client, it can be running on a different platform from that of the Oracle database server. Typically, In-Memory Database Cache resides on the application tier, whereas the Oracle Database sits on the database tier.

If you refer to the oracle price list, you'll also see that this is an "enterprise edition" enhancement only too.  You'll not be able to enable this without EE licences.  

Media objects gotcha–upgrade / deployment server rename

Too many times we forget to update all of the records in F00165 when you change out your deployment server and you’ve been using it for media object storage.  Remember that when you rename the MO queue in the P98MOQUE, this does not change the location of all the records that you may have saved against the MO.

So you will need to run update statements in SQL to ensure that once you’ve copied the directory structure to your new deployment server you also change all of the internal pointers.

Different media object types have different requirements for pointing to the final file name, you’ll need to look into the F00165 to work it all out.