UKOUG Day 1: Oracle Optimizer 12c, Cost Based Optimizer, Exadata

Today was the first day of the United Kingdom Oracle User Group (UKOUG) – usually called the Super Sunday! I already participated in today’s first session three weeks ago in Geneva, but my colleague Nicolas Jardot was talking about Solving Critical Issues with the 12c Optimizer, covering histogram collection, temporary table statistics and adaptive query optimization.

There were around thirty people at his session, and a lot of UKOUG speakers were very interested and asked a lot of questions with a typical British accent that did not destabilize Nicolas at all. All my congratulations to Nicolas!

The next session, preseented by Tony Hasler, was about the Cost Based Optimizer: Why does the CBO goes wrong?
Mr. Hasler explained that the expectations around the CBO are unreasonably high.

He explained us a lot of issues around the following points:

• Cardinality errors (correlated columns, functions and expressions, stale statistics or daft data types)
• Partition level statistics (which might mask the need for extended statistics)
• Badly written code
• Missing information
• Transitive closure (you write the statement in another way and the plan is different)

Other issues were discussed as possible reasons of changing execution plans:

• Caching effects
• Contention
• Bad physical design

His recommendations were the following:

• Proactively tune the critical statements
• Use optimizer hints cautiously
• Carefully consider the statistics gathering strategy (i. e.: gather statistics less frequently)

The last session of the afternoon was a live demo: Andy Colvin applied the latest Exadata patches to one of his company Exadata racks.

We assisted the live demo of the Exadata patching process with instructions and best practices on how to apply patches on an Exadata environment.

He used a utility named dbnodeupdate.sh which helps in the process of updating the nodes in an Exadata environment. Oracle last year introduced yum updates in Exadata compute nodes with Oracle 11.2.3.1.0.

Before that time, each Exadata server patch had a pack including a shell script that forced new RPMs on the new compute node. But if the users began to install their own packages, the installation had a lot of chances to fail.

The dbnodeupdate can run the bootstrap phase to directly update from 11.2.2.4.2 version to 11.2.3.2.1 in two reboots and running three commands. It analyses the free space on the compute node volume groups to back up the root volume, providing a way to rollback. It also has the possibility to disable the CRS on reboot and relink the Oracle Homes and enable CRS on startup.

It really was a “Super Sunday” afternoon, I will have to impatiently wait until tomorrow to discover some features like “Exadata Management deep dive with OEM 12c” or “10 optimizer hints you can’t miss”!