Oracle

I tweeted the following yesterday,

“It’s 7 years ago today that I was made an Oracle ACE. Seriously. It was April Fools Day 2006… ”

The followup from that tweet included a number of questions about what you get out of becoming an Oracle ACE and what is the quickest way to become one. In my mind, these types of questions highlight the misunderstanding of what the Oracle ACE program is. You can hear Vikki, Debra, Alex and myself talking about the Oracle ACE program here, but I feel like I want to clarify a few things. This is just my opinion. Others may say different.

Should you aim to become an Oracle ACE?

You may have used the Oracle 11g V$SQL_HINT view already – it displays all the valid hints (both documented and undocumented ones) available in your Oracle version, for example:

Here’s a summary of a recent posting on OTN:

I have two indexes (REFNO, REFTYPESEQNO) and (REFNO,TMSTAMP,REFTYPESEQNO). When I run the following query the optimizer uses the second index rather than the first index – which is an exact match for the predicates, unless I hint it otherwise:

In order to look how Exadata smartscans are different, first let’s have a peek the Oracle full segment/multiblock read evolution as short as possible:

a) Traditional multiblock reads, visible via the event ‘db file scattered read’
The essence is: Multiple adjacent blocks are read from disk, and put in the buffercache. Because every read is sequentially processed, IO latency is a performance penalty for every physical read. This works roughly this way: get a set of adjacent blocks from the segment header, fetch these blocks from disk, process these blocks, then get the next set of adjacent blocks, fetch these blocks from disk, process these blocks, etc.

I’ve just wasted quite a bit of time trying to figure out why one of my VirtualBox VMs wasn’t starting properly, so I thought I would share the experience here in case anyone else has a similar issue…

It all started because I decided to fire up an OL6 VM, that I hadn’t used for a little while. It started up fine and I figured I should probably patch the OS and the VirtualBox Guest Additions. The first thing I did was a “yum update” to bring it up to OL6.4 and the the latest UEK2. Once that was complete I did a reboot and that is where the issue started. The VM booted in the normal fashion, but then hung at this point.

Here’s a little detail that appeared in 11gR2 that may help you answer questions about open cursors. Oracle has added a “cursor type” column to the view v$open_cursor, so you can now see which cursors have been held open because of the pl/sql cursor cache, which have been held by the session cursor cache, and various other reasons why Oracle may take a short-cut when you fire a piece of SQL at it.

The following is the output showing the state of a particular session just after it has started up in SQL*Plus and called a PL/SQL procedure to run a simple count:

Last year I wrote an article about the installation of Oracle Forms and Reports Services 11gR2 on Oracle Linux 5. I’ve now written the article for Oracle Forms and Reports Services 11gR2 on Oracle Linux 6. The latest patch of F&RS is certified for OL6, along with JDK6 and JDK7.

In addition to the installation articles, I’ve compiled a collection of random notes about post-installation configuration into a separate article. I keep adding to it every time I come across a new (for me) issue.

This note is about a feature of LOBs that I first desribed in “Practial Oracle 8i” but have yet to see used in real life. It’s a description of how efficient Oracle can be, which I’ll start with a description of, and selection from, a table:

If you attended my Exadata hacking session today, you saw me using the cellver.sql script which lists some basic configuration info about the currently connected storage cells:

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.

Here’s a simple table T1 holding 1M rows with the indexed column X having 10 distinct values: