Openworld day 3

Yesterday was another day of good quality sessions at OpenWorld.  Yes its true that there are many sessions that could only be described as pure marketing, but with a little effort, there are gems in the agenda !

It started with partitioning facilities that are coming in 12c.

Interval/Reference partitions will be supported, although no word on whether you can have subpartitions under interval based range partitions.  Time will tell.

One of the nice things was the CASCADE option coming for truncate/exchange partition.  In the past, when you had foreign keys between tables (as of course we always should :-)), then truncate (being a DDL) would require a lot of manual FK maintenance before and after the truncate command.

This restriction has been lifted to a reasonable degree in 12c.

Next up was a session on optimizer hints by Maria Colgan, who is product manager for the optimizer team.  As she quoted: “I’m the chief apologiser for the Oracle optimizer”.  Which is a bit harsh to be honest – I’ve generally found most optimizer issues are due to lack of understanding rather than any particular lack of function within the product itself.

The session on hints was good, in particular, a strong stress toward trying to implement “hints” via baselines rather than code changes.  I’m not entirely sure I agree with that policy, given that a hint in the code is “self-documenting” whereas a baselines is just that little bit less visible.

Another thing coming in the 12c optimizer is “adaptive execution plans”, namely, the database at run time may recognise that the plan chosen as parse time was possibly not the best one, and may change it on the fly.  Sounds impressive, but also sounds a little risky in terms of ensuring consistency of performance.  Time will tell.

Finished the day with some clam chowder !  Yum.


Openworld Day 2

Yesterday I went to a presentation on histograms in 12c.  A number of good improvements made there by Oracle coming in 12c.

Particular emphasis seems to be placed on some of the short comings of height balanced histograms.  In 12c, we will have TOP-N histograms (where the "top scorers" get their own frequency bucket, and the other values get collapsed together.  Similarly, there is a hybrid form of height balanced histogram.  Lots of playing around to be done there.

The limit on histograms has been bumped up to 2048, although you have to ask the question – if you have 2000 special values, then maybe, you don’t actually have *any* special values 🙂

Later in the day, I went to Bryn Llewellyn’s PLSQL enhancements talk.  PLSQL features in new releases is always an interesting one for me in that its such a mature product, so does an absence of "headline" new features mean that its no longer cared for, is that it is functionallty complete ?  You never can tell.

Better SQL and PLSQL interaction seemed to be the main thrust.  You can have functions within a WITH clause, as a means of being able to leverage the ‘procedural’ elements of PLSQL without having to recast all of that into SQL expressions. 

There is also a new pragma to make PLSQL that it called from SQL even faster.  I think this will get a lot of takeup from those who move to 12c, because calling PLSQL from SQL has become so commonplace nowadays.

UTL_CALL_STACK is a now package introduced, which (finally) gives some structure to the call stack that we all use the parse out of DBMS_UTILITY.

Openworld has commenced…

The ACE director briefings are over and Oracle Open World has kicked into full swing.  The scale of the event is staggering and its a challenge just finding sessions to go to.  This is not due to insufficient sessions – just the challenge of sifting through 100+ concurrent sessions, spread across multiple locations and timeslots…

I started the day with a “Beginners Performance Tuning” session – really just to see what kind of turnout would be at a beginner’s style session.  What was very interesting was the demographic of the attendees.  A large amount of them looked amazed and awestruck as some fairly basic and stock standard principles were introduced.  This was good to see, in that, when you’re a dinosaur like me, and have been around a lot of this stuff for ages, its easy to forget that for a great many people – the Oracle technology is a brand new beast…and a very very large one at that Smile 

So I’ve set myself the task of doing something similar for the Perth conference.  A tuning session that is really for the novice, trying to answer the question: “My SQL is slow….now what”

At the other end of the spectrum, sat in a talk about library cache structures, and how mutexes are (have) replaced latches as the preferred serialisation mechanism.  Perhaps not a topic for those new to the database Smile

Fusion apps

When it comes to hardware, the Fusion Apps installation guide is surprisingly non-committal:

4.2.1 Hardware Requirements

Consult an Oracle Fusion Applications sizing-certified representative to obtain specific, customized system hardware requirements

Some googling suggests that Fusion Apps is going plonk the following on your system:

– several databases
– many many WebLogic servers

and the general consensus seems to be minimum 128G RAM

Oh….my….goodness 🙂

Goldengate 11gR2

Lots of goodies in 11gR2 GoldenGate.

The big one seems to be “Integrated Capture”, which seems to be a moving of the capture processing closer to the database (did anyone say “Streams” :-))

The benefit of that is that lots of the boutique data types and compression etc, are all now supported by Goldengate.

More details here:

ACE director briefings

A thoroughly enjoyable day at the first day of ACE director briefings.

Of course, all of this stuff is Non-Disclosure so I cant really reveal any of the content, but as soon as OpenWorld is complete, then many of those restrictions will be lifted and we can all bask in the features that are coming.

Obviously, without giving too much away, you can expect lots of content with “next generation”, “latest release”, “Exa-this” and “Exa-that” plastered all over it.  But look through the marketing hype and trust me, you’ll see some very very exciting things on the horizon.

For those of you not at OpenWorld, its still worthwhile going to the open world site – many of the talks are streamed online, so you can (partially) participate, although for my Australian colleagues, the time of day might not be that pleasant 🙂

The next release of Oracle….

….is going to be the next release.

[which is about all I can say at the moment]

How cool is that …



YY vs YYYY…It can make a BIG difference

Check out the execution plan differences here. The fully qualified date (2nd one) gets it right, and note that the optimizer gains the partition start / stop information.

SQL> select fds.fin_account_num,
2  sum(fds.sales) as sales from whs.fin_day_summary fds
3  where activity_date >= to_date('30-08-12','dd-mm-yy') - 390
4  and activity_date <= to_date('30-08-12','dd-mm-yy')
5  and fds.fin_account_num is not null
6  group by fds.fin_account_num ;
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT                      |                    | 29264 |   457K|       | 48512   (1)| 00:09:43 |       |       |
|   1 |  HASH GROUP BY                        |                    | 29264 |   457K|  1312K| 48512   (1)| 00:09:43 |       |       |
|*  2 |   FILTER                              |                    |       |       |       |         |     |       |       |
|   3 |    PARTITION RANGE ITERATOR           |                    | 47222 |   737K|       | 48318   (1)| 00:09:40 |   KEY |   KEY |
|   4 |     PARTITION LIST ALL                |                    | 47222 |   737K|       | 48318   (1)| 00:09:40 |     1 |     4 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| FIN_DAY_SUMMARY    | 47222 |   737K|       | 48318   (1)| 00:09:40 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN                | FIN_DAY_SUMMARY_UQ | 47222 |       |       |   785   (1)| 00:00:10 |   KEY |   KEY |
SQL> select fds.fin_account_num,
2  sum(fds.sales) as sales from whs.fin_day_summary fds
3  where activity_date >= to_date('30-08-2012','dd-mm-yyyy') - 390
4  and activity_date <= to_date('30-08-2012','dd-mm-yyyy')
5  and fds.fin_account_num is not null
6  group by fds.fin_account_num ;
| Id  | Operation                 | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT          |                 | 29264 |   457K|       | 24893   (5)| 00:04:59 |       |       |
|   1 |  HASH GROUP BY            |                 | 29264 |   457K|  1312K| 24893   (5)| 00:04:59 |       |       |
|   2 |   PARTITION RANGE ITERATOR|                 | 47222 |   737K|       | 24699   (5)| 00:04:57 |    98 |   153 |
|   3 |    PARTITION LIST ALL     |                 | 47222 |   737K|       | 24699   (5)| 00:04:57 |     1 |     4 |
|*  4 |     TABLE ACCESS FULL     | FIN_DAY_SUMMARY | 47222 |   737K|       | 24699   (5)| 00:04:57 |   389 |   612 |

OakTable World is coming

If you’re at OpenWorld…then get some awesome additional content AND beer AND real life stories



ORA-32033: unsupported column aliasing

When you see this

SQL> with xyz
 2 (
 3 select 1 my_alias from dual
 4 )
 5 select * from xyz;
ERROR at line 2:
ORA-32033: unsupported column aliasing

your first thought might be that there is something wrong with the column alias “my_alias”.

In fact, it got nothing to do with that….its missing the “AS”.

SQL> with xyz as
 2 (
 3 select 1 my_alias from dual
 4 )
 5 select * from xyz;


If you are in Melbourne this week, hopefully you’ve registered for the InSync conference (, which is the main user group conference in Australia. I’d love you to come share in my year long journey with Oracle clusterware and RAC upgrades on Tuesday afternoon. Pop in and say hi…

ORA-28511: lost RPC connection to heterogeneous remote agent

A client has been seeing this error when holding sessions open over HS for lengthy periods of time (greater than an hour or so).

Some workarounds that have worked are:

  • when possible, issue, alter session close database link, and re-open it for subsequent queries


  • Set INBOUND_CONNECT_TIMEOUT_<listener_name>=0 in listener.ora