August 2016

Adaptive mayhem

So you run a query and it gives you a plan with a note that says “This is an adaptive plan”.

So you run it again and the plan changes,  with a note that says “Statistics feedback used for this statement”

So you pause to think for a bit, then run the query again and the plan changes, with a note that says “One SQL Directive used, dynamic statistics used”. (You waited too long and the internal re-optimization hints got flushed down into an SQL directive.)

So you decide to think about it the following morning when you’re feeling bright and fresh, and when you run it you get another plan because overnight the automatic stats job gathered stats on the critical table and created a column group that was indicated by the (now defunct) directive.

Happy optimisation!

 

OSWatcher integration in Trace File Analyzer (TFA)

Some time ago I wrote a post about using OSWatcher for system analysis. Neil Chandler (@ChandlerDBA) rightfully pointed out that although OSWatcher was cool, TFA was the way to go. TFA can include OSWatcher, but more importantly it adds a lot of value over and above what OSWatcher does.

I guess it depends on what you want to do-I still think that OSWatcher is a good starting point and enough for most problems on single instance systems. When it comes to clustered environments, TFA looks a lot more appealing though.

In this article I am taking a closer look at using TFA – which is part of the Oracle 11.2.0.4 and 12.1.0.2. TFA is automatically updated as part of the quarterly patches, which is nice because the default/base release does not seem to be working properly. Thankfully TFA can be patched outside the regular patch cycle.

What is TFA?

Oracle 12c: Indexing JSON In The Database Part I (Lazarus)

One of the very cool new features introduced in Oracle Database 12c Rel 1 is the ability to store JavaScript Object Notation (JSON) documents within the database. Unlike XML which has its own data type, JSON data can be stored as VARCHAR2, CLOB or BLOB data types, but with a JSON check constraint to ensure the […]

Building something new and fun for pl/sql

So having done a lot coding in different languages (recently python, ruby and javascript) I came to the conclusion that pl/sql is missing an important functionality or addition if you like.

So for a while now, whenever I've had some spare time (a bit difficult with 3 small kids) I've been coding up a fun little project.

It is now pretty close to beta testing, and so this week I will finishing of the rough edges and bring a couple of small teasers as well

An introduction to Oracle Database for beginners: What is a relational database management system?—Part IV

Database management systems such as Oracle are the interface between users and databases. Database management systems differ in the range of features they provide, but all of them offer certain core features such as transaction management, data integrity, and security. And, of course, they offer the ability to create databases and to define their structure, as well as to store, retrieve, update, and delete the data in the databases.(read more)

An introduction to Oracle Database for beginners: What is a relational database management system?—Part II

When you start thinking in terms such as security management, availability management, continuity management, change management, incident management, problem management, configuration management, release management, and capacity management, the business of database administration begins to make coherent sense and you become a more effective database administrator. (read more)

An introduction to Oracle Database for beginners: What is a relational database management system?—Part I

Oracle provides a convenient virtual machine (VM) containing a complete and ready-to-use installation of Oracle Database 12c on Linux. All you need to do is to download and install the Oracle VirtualBox virtualization software and then import a ready-to-use VM.(read more)

An introduction to Oracle Database for beginners: What is a relational database management system?—Part V

Physical Data Independence means that you and I shouldn’t have to worry about implementation details such as the storage structures used to store data.(read more)