December 2014

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings for CMD.EXE under Windows for example you might have to adjust accordingly to prevent ugly line wrapping.

Here are the notes from the change log:

- New sections "Concurrent activity I/O Summary based on ASH" and "Concurrent activity I/O Summary per Instance based on ASH" to see the I/O activity summary for concurrent activity

Oracle 12c SQL – “Top-N” Queries and Pagination

“Top-N” Queries and Pagination

Oracle 12c adds “top-n” type queries and paginated queries; features that have been part of other products for years.

In fairness to Oracle; we’ve been able to answer “top-n” queries easily using nested queries (SELECT in FROM) and/or Analytic functions (RANK/DENSE_RANK) for years; the new syntax simply makes for faster coding (the optimizer uses analytics under the covers to make things work).

Two new clauses have been added to Oracle SQL’s SELECT:

  • FETCH            FIRST/LAST nn ROWS FIRST/LAST n PERCENT ROWS
  • OFFSET           nn ROWS

Here is a simple query from the “SCOTT” schema’s EMP table; please note the salary values and the “top 4” salaries:

select ename,sal from emp order by sal desc;

The BBC has “Stolen” my Interesting Shortest Day Facts

Today, the 21st December 2014, is the “shortest day” in the Western Hemisphere this year, the day in which the period of daylight is shortest (it’s the 22nd if the last Leap Year is more than 2 years ago).

I’ve blogged before about how the evenings start drawing out BEFORE the shortest day and, despite it not being an Oracle technical post and was also one of my first posts {when almost no one came by}, it gets a modest but interesting number of hits. If you look at the below graphs (sorry, it’s not as clear as it could be unless you click on the image), you will see there is a burst of hits at the end of the year and a smaller rise in interest at the middle of the year.

Exadata Patching Introduction

These I consider the most important points about Exadata Patching:

Where is the most recent information?

MOS Note 888828.1 is your first read whenever you think about Exadata Patching

What is to patch with which utility?

Exadata Patching

Expect quarterly bundle patches for the storage servers and the compute nodes. The other components (Infiniband switches, Cisco Ethernet Switch, PDUs) are less frequently patched and not on the picture therefore.

Reading Oracle memory dumps

Every DBA working with the Oracle database must have seen memory dumps in tracefiles. It is present in ORA-600 (internal error) ORA-7445 (operating system error), system state dumps, process state dumps and a lot of other dumps.

This is how it looks likes:

Friday Philosophy – my Funniest “PC Support” Call in Years

Those of us who work in IT often find ourselves being called by friends and relatives to help with issues they have with their home computer. No matter what branch of IT we work in, it’s IT {they figure} so of course we can fix their PC problems. It’s like any scientist can probably explain what the Higgs Boson really is and reverse the polarity of the neutron flow.

Mothers and other elderly relatives are probably the most baffling when it comes to such requests, but this week I had a wonderful call-out from one of my neighbours.

The neighour in question is a slightly dotty, jolly posh but well meaning Lady (we live in the cheap house around here, she lives at the other end of things).

“Oh Martin, it’s AWE-full! My computer is full of p3nises and other horrible things!!! Please help me get rid of all the p3nises!!!”

EM12c and the Optimizer Statistics Console

Today we’re going to review another great feature in the EM12c that you may not have realized was available.  Once logged into a database target, click on the Performance menu and navigate to the Optimizer Statistics Console:

RAC One Node 12c-revisited on ODA 12.1.2.1.0 featuring database on ACFS

Thanks to Andy Colvin (@acolvin) and the Enkitec lab I have been able to get my hands on an ODA X4-2. And since that’s a lot quieter than turning on my own lab server, and also a lot faster I used the environment to test RAC One Node in 12.1.0.2.1. I recently had a question from a customer about the usefulness of the solution and what it brings to the HA table. Well here you go.

Oracle database operating system memory allocation management for PGA – part 4: Oracle 11.2.0.4 and AMM

This is the 4th post in a series of posts on PGA behaviour of Oracle. Earlier posts are: here (PGA limiting for Oracle 12), here (PGA limiting for Oracle 11.2) and the quiz on using PGA with AMM, into which this blogpost dives deeper.

As laid out in the quiz blogpost, I have a database with the following specifics:
-Oracle Linux x86_64 6u6.
-Oracle database 11.2.0.4 PSU 4
-Oracle database (single instance) with the following parameter set: memory_target=1G. No other memory related parameters set.

[Oracle] Researching internal latch implementation (ksl_get_shared_latch, kslfre, kslgetsl_w) and crashing PMON

Introduction

Last week the DOAG 2014 conference took place in Nuremberg and it was a blast with a lot of useful presentations and especially great conversations and meet ups with Oracle friends. I had a nice talk about the Oracle latch implementation with a participant, who told me that his instance crashes every time, if he (manually) sets a shared latch in exclusive mode and tries to release it afterwards. It sounded really interesting as i have done this so many times without ever noticing such an issue. He also told me that this issue is reproducible at least on Oracle 11g R2 and 12c R1. I had no immediate answer or clue about the described issue and needed to research it furthermore.