SQL Plan Management Choices

My thoughts on SQL plan management decision points:


SQL Patches are also available and not covered in the above flowchart.

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.

Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches.

But can there be uses where we want (more...)

Upgrade to Oracle 12c, Get the Huge Trace Files for Free!

Last week we began testing a copy of our production database on Oracle 12c ( This past weekend we were alerted that the disk holding our ADR diagnostic directory was near full. We noticed some pretty big (and recent) trace files there. Then it happened twice more. This last time filled the disk before we could get to it (thankfully only dev, and during the evening), meaning it filled up fast. The (more...)

“Bob the Builder: Build/Deploy of #ADF enterprise applications” – I’m a speaker at #DOAG2015:


Today my presentation is confirmed:  “Bob the Builder: Build/Deploy of ADF enterprise applications”.

I will speak over our experiences with following

  • build tools
  • deployment tools
  • architecture
  • versioning & branching
  • continuous integration
  • hot deployment in development
  • compile & runtime dependencies

If you speak german and you are interested in some of this, you should come and attend DOAG 2015.

Filed under: Deploying, Development Tagged: 11g, 12c, ADF, ADF Architecture, Branching, (more...)

Clear ADF 12c deployment on Integrated WLS

Sometimes you want to cancel your Integrated WebLogic Server, however, if you do this while a deployment is currently going on, you might run into trouble. The log window will not tell you anymore than a remote deployment failed and that the Application can not be run:

You need to clear some directories in your JDevHome before the application deployment works again. Before clearing the directories, make sure you close JDeveloper.

Go to the folder: (more...)

V506 of Oracle OBIEE SampleApp Virtual Machine

A few days ago Oracle released the latest version of the Virtual Machine for OBIEE SampleApp. The current version has a number of new features and new product versions (see below).

To get this latest version go to the following link to download the VM files and to install. As always this is a beast of a VM and you should only consider the install and setup if you have the space and in particular (more...)


This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

Possible Truncation Attack? logged in #em12c nodemanager.log file

Recently I’ve come across issues with restarting Oracle Enterprise Manager and seeing messages in the nodemanager.log. The message that I’m referring to is (followed by a java trace stack):

<Jul 2, 2015 11:46:11 PM> <WARNING> Uncaught exception in server handlerjavax.net.ssl.SSLException: 
Inbound closed before receiving peer's close_notify: possible truncation attack? 
Inbound closed before receiving peer's close_notify: possible truncation attack?

What is interesting about this message is the panic (more...) PDB fails to come out of restricted mode

This one is a nasty bug I was trying to setup Oracle PDB in a test environment for the first time and got stuck with ORA-01035 error [oracle@oracle11g ~]$ sqlplus hr/hr@//oracle11g:1522/engg SQL*Plus: Release Production on Fri Jul 3 07:34:58 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01035: ORACLE only available to users with…

Fix completion insight in JDeveloper 12.1.3: show return type again


If you work with JDeveloper 12.1.3 you may missing the return type of methods in completion insight.

For an example you hit CTRL-SPACE after following code


you see that at the start and at the end of the popup the available method names and parameter. If you have worked before with older versions of JDeveloper, you know that there is shown the return type of the method at the end. What a loose of (more...)


Note to self because it’s just one of those date/timezone-related topics which just doesn’t seem to stick…

Epoch/Unix time – See https://en.wikipedia.org/wiki/Unix_time

Unix time (also known as POSIX time or erroneously as Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,

Firstly when converting from Oracle date or timestamp – we need to (more...)

Index Tree Dumps in Oracle 12c Database (New Age)

I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c. Let’s begin by creating a little table and index: To generate an Index Tree Dump, we first need to OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]

Event Histogram Metric and Oracle 12c

Topic: event histogram metric, a script and some related comments on collecting and displaying wait event latency histograms for Oracle performance troubleshooting.

Why: Latency histograms (and by extension wait event histograms) provide very useful information when troubleshooting performance for systems exhibiting response time with multi-mode distribution. In such cases average wait values are often not sufficient to understand the behavior of the system under study and histograms provide a finer level of details. A (more...)

Oracle Database 12c: Interactive Quick Reference

12c DB Architecture

© Eddie Awad's Blog, 2015. | Permalink | Add a comment | Topic: Oracle | Tags: ,

Warning: standby redo logs not configured for thread – BUG

Surprise to find the following error for single instance Data Guard environment:

    Warning: standby redo logs not configured for thread 3 on boston


This will be fixed with an upcoming patch for the existing issue in Oracle Database – good reason to stay up to date on patching?

Also, the number of Standby Redo Log Groups do not match since thread = 1 is being used.

Not *ALL* SRL have thread (more...)

SQL Plan Baseline Manual Evolution

I’ve never really spent time on evolution because most of the time I use baselines for emergency sql plan management and not with the added value of controlled evolution with which the feature was conceived.

But some observations on sql plan baseline evolution originating from the questions:

  • What does evolution actually do?
  • What information does it use?
  • What happens with DML?

Starting point – one baselined plan

1. FTS plan in memory from SQL which (more...)


One use for the addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
   v_char varchar2(2000);
   select high_value into v_char
   from user_tab_partitions a
   where a.table_name = i_tab
   and a.partition_name = i_part;
   if v_char like 
     'TO_DATE(''%'', (more...)

Loading JSON data into Oracle using ROracle and jsonlite

In this post I want to show you one way of taking a JSON file of data and loading it into your Oracle schema using ROracle. The JSON data will then be used to create a table in your schema. Yes you could use other methods to connect to the database and to create the table. But ROracle is by far the fastest method of connecting, selecting and processing data.

1. Necessary R Packages

You (more...)

Finding the Oracle Database Appliance Plug-in within #em12c

The Oracle Database Appliance (ODA) has been around for a few years now. It is a great, compact, and powerful machine for running at two-node Oracle Real Application Cluster (RAC). The adoption of the ODA has been mostly seen in medium sized organizations that need a work horse but cannot afford the sticker price of an Oracle Exadata.

Just like all the appliances that Oracle puts out, there is a need to monitor these appliances (more...)

Oracle Data Miner (ODM 4.1) New Features

With the release of SQL Developer 4.1 we also get a number of new features with Oracle Data Miner (ODMr). These include:

  • Data Source node can now include data sources that contain JSON data, generating JSON schema and has a JSON viewer
  • Create Table can now create data in JSON
  • JSON Query Node allows you to view, query and process JSON data, combine it with relational data, generate sub-group by, and nested columns to (more...)