September 2016

Slides for OOW16 Session Preventing Bad SQLs

Thank you for coming to my session Preventing Bad SQLs at Oracle Open World despite being at lunchtime on the last day.

Congratulations to the winners of my book.

Download slides here http://bit.ly/2cGJqR7

Oak Table World 2016 Followup

The Oak Table network is a closed membership of about 100 technical scientists who have a passion for technology and the world around them.  These men and women, representing some of the brightest minds from each of their companies, are individuals who have built companies and major technical innovations.

The simple fix to date queries

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date).


SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL>
SQL> create index t_ix on t ( created );

Index created.

The problems start when we do a query on the CREATED column and get "unexpected" results

Read Only Users and Database Performance

One of the questions we see being asked reasonably frequently is how to allow read only access to someone who wants to view database performance issues, using tools such as Real Time ADDM, ASH Analytics and so on. This is generally asked for someone like an application developer, who can use this information to identify performance issues in Production and then fix the root cause in their Development environments.

Well, that’s a reasonable question to ask, and indeed if you search the internet you can find different ways of doing something like this. For EM12c, one of the best write-ups I’ve seen done on this was of course by one of my colleagues, Courtney Llamas. That write-up is available here.

How to reinstate the old Primary as a Standby after Failover in #Oracle

You have done a failover to your Standby database so it becomes the new Primary. It may be possible to convert the old Primary into a Standby database now instead of having to do a time consuming duplicate again. The old Primary must have been running in flashback mode before the failover. The playground:

Why Bother

This note comes to you prompted by “Noons” in a recent twitter exchange

In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno  asked me to blog about my claim that this non-difference is a good educational example on at least three different counts.

Some Exciting Customer Presentations!

While I always enjoy getting in front of customers to present on some new and really cool functionality in Enterprise Manager Cloud Control, it’s even better to be in the audience listening to actual customers doing just that!  There are a couple of those presentations coming up on Thursday this week at OOW.  Unfortunately I can’t be there to listen in, but if you’re interested in learning how to provide scalable and flexible patching solutions, make sure you get along to these two presentations and learn directly from these customers!

Video Tutorial: XPLAN_ASH Active Session History - Part 11

#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px;">The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.
#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px;" />
#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px;" />#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px;">More parts to follow.
#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px;">

One of my Favourite Database Things: DBMS_APPLICATION_INFO

Notes for my slot in session UGF2630: EOUC Database ACES Share Their Favorite Database Things: Part I – OpenWorld , Sunday 18th September 2016
DBMS_APPLICATION_INFO is probably one of the most undervalued and without any doubt the most underused of all the packages supplied with the database by Oracle. On the face it, it is a very simple package that does a few very simple things, but it is not possible to overstate its significance.

Statement-level PARALLEL Hint

From version 11.2 onward, the PARALLEL hint supports two syntaxes: object-level and statement-level. The object-level syntax, which is the only one available up to version 11.1, overrides the DOP associated to a tables. The statement-level syntax can not only override the PARALLEL_DEGREE_POLICY initialization parameter at the SQL statement level, but also force the utilization of parallel processing.

The statement-level PARALLEL hint supports the following values: