What To Expect From Oracle Database 12c

I know I promised more blog posts on the new features in Oracle Database 12c but I thought I would cheat on today’s post and point you to an article I wrote in the current issue of UKOUG’s Scene Magazine.

There you will find a 3 page article that outline what you can expect from Oracle Database 12c both in terms of marquee features as well as small but useful enhancements.

I’ll have more technical posts on these and other new features in the coming weeks.

Click on the picture to read the article.

Three page overview of what to expect from Oracle Database 12c

Longer Identifiers in Oracle Database 12c Release 2

Prior to Oracle Database 12c Release 2, all object names had been limited to just 30 bytes. This limitation lead to some interesting problems, especially if you wanted to use descriptive names for the database objects you were creating.

Take for example the dictionary tables we wanted to create in 12.1 to help manage SQL Plan Directives. The first table was DBA_SQL_PLAN_DIRECTIVES, with 23 characters, which wasn’t problem. However, the second table we wanted was DBA_SQL_PLAN_DIRECTIVES_OBJECTS.

Now that Oracle Database 12c Release 2 is available on-prem, I thought it would be a good time to remind folks about what they can expect from the new release. I’m going to divide topics into two categories, Marquee Features and top-tips. Let’s start with a top-tip!

Prior to Oracle Database 12c Release 2, all object names had been limited to just 30 bytes. This limitation lead to some interesting problems, especially if you wanted to use descriptive names for the database objects you were creating.

Take for example the dictionary tables we wanted to create in 12.1 to help manage SQL Plan Directives. The first table was DBA_SQL_PLAN_DIRECTIVES, with 23 characters, which wasn’t problem. However, the second table we wanted was DBA_SQL_PLAN_DIRECTIVES_OBJECTS.

Continue reading “Longer Identifiers in Oracle Database 12c Release 2”

12.2 and Tim Tams

Yesterday saw Oracle complete the release of Oracle database 12c Release 2 when the software became available for download on Oracle.com for both Linux & SPARC.

Earlier this year I was lucky enough to have an opportunity to grab a coffee and enjoy some Tim Tams (cookies) with Connor McDonald, my fellow askTOM team member, and chat about our favorite new features in 12.2. I thought you might enjoy see what we had to say about the new features and the enhancements that could make your life a lot easier.

Happy viewing!

Oracle Database 12c Release 2 Available Everywhere!

Today Oracle Database 12c Release 2 became available for download on Oracle.com for both Linux & SPARC. So anyone who wasn’t ready to try 12.2 in the Cloud can now play around with it, in the comfort of their own environment.

With each new release of the Oracle Database come fundamental architectural changes, driven by new technologies and user requirements. This has never been more evident than with Oracle Database 12c, which has 3 marquee features:

Today Oracle Database 12c Release 2 became available for download on Oracle.com for both Linux & SPARC. So anyone who wasn’t ready to try 12.2 in the Cloud can now play around with it, in the comfort of their own environment.

With each new release of the Oracle Database come fundamental architectural changes, driven by new technologies and user requirements. This has never been more evident than with Oracle Database 12c, which has 3 marquee features:
Continue reading “Oracle Database 12c Release 2 Available Everywhere!”

Oracle Storage Index

If you are on Exadata or taking advantage of Database In-Memory it’s possible your queries will benefit for the automatically created and maintained Storage Indexes.

But what exactly are Storage Indexes and why don’t I always see a benefit from them?

Let me start by describing what Storage Indexes are in relation to Database In-Memory but remember they behavior in exactly the same way on the Exadata storage cell.

A Storage Index keeps track of minimum and maximum values for each column in an In-Memory Compression Unit (IMCU) or 1MB chunk on the Exadata storage cells. When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column(s) is examined to determine if any entries with the specified value exist.

If you are on Exadata or taking advantage of Database In-Memory it’s possible your queries will benefit for the automatically created and maintained Storage Indexes.

But what exactly are Storage Indexes and why don’t I always see a benefit from them?

Let me start by describing what Storage Indexes are in relation to Database In-Memory but remember they behavior in exactly the same way on the Exadata storage cell.

A Storage Index keeps track of minimum and maximum values for each column in an In-Memory Compression Unit (IMCU) or 1MB chunk on the Exadata storage cells. When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column(s) is examined to determine if any entries with the specified value exist.

Continue reading “Oracle Storage Index”

Histogram sample size and Online Statistics Gathering

I got a great follow up question to my earlier blog on Online Statistics Gathering; that I thought might be of interest to other and worthy of a short post of its own.

The question related to sample size used to gather histograms on a table that originally had its statistics gathered via a direct path load operation.

Let’s look at any example:

I got a great follow up question to my earlier blog on Online Statistics Gathering; that I thought might be of interest to other and worthy of a short post of its own.

The question related to sample size used to gather histograms on a table that originally had its statistics gathered via a direct path load operation.

Let’s look at any example:

Continue reading “Histogram sample size and Online Statistics Gathering”

Upcoming Events : RMOUG 2017

I’m really looking forward to the Rocky Mountain Oracle User Groups Training days next week, at the Colorado Convention Center, as it will bring several new challenges for me.  I am going to be involved in three very different sessions at this year’s conference:

Continue reading “Upcoming Events : RMOUG 2017”

Locking Table Statistics Provides Plan Stability True or False?

I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.

There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.

From Oracle Database 10g onwards you have had the ability to lock the optimizer statistics for a table or schema, via the DBMS_STATS package.

BEGIN
DBMS_STATS.LOCK_TABLE_STATS(‘SH’,’SALES’);
END;
/
The LOCK_STATS procedure locks all of the statistics related to a table, including the table statistics (number of rows, number of blocks etc.), column statistics (min and max values, number of distinct values etc.), histograms and the statistics on all dependent indexes. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked or if the FORCE option is used in the GATHER_*_STATS procedures.

I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.

There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.

Continue reading “Locking Table Statistics Provides Plan Stability True or False?”

Building a simple Node.js application with Oracle

Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.

My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.

The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.

Anyone who knows me will tell you, my expertise is the Oracle Database and not web applications or JSON. But my new role is all about pushing myself out of my comfort zone, so I wanted to get some hands on experience with both application development and JSON in the Oracle Database.

My colleague, Mark Drake, the PM for JSON in the Oracle Database, suggested I get my feet wet by trying the new Movie Ticketing Application tutorial available on the JSON page on Oracle.com.

The Movie Ticketing Application is written in Node.js and connects to the Oracle Database via a REST Data Service. Since I recently signed up for the Oracle Database Exadata Express cloud service, I had the perfect Database setup for a web application.

Continue reading “Building a simple Node.js application with Oracle”

Excited to be the newest member of the AskTom Team

A little over a year ago Connor McDonald and Chris Saxon took over answering questions posted on AskTom, which covers many topics related to building applications on top of the Oracle Database. Connor and Chris have done an amazing job of keeping up with the constant influx of questions, with nearly 16,000 answered to date.

Last week, I was lucky enough to be invited to join Connor and Chris in answering questions submitted AskTom. So, if you have any burning questions regarding the Oracle Database or building applications on top of the Oracle Database, you can submit them at AskTom and Connor, Chris or I will get back to you!