Audio semi-Visual Presentation on Clustering Data in Oracle

I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.

The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (more...)

Life of an Oracle I/O: tracing logical and physical I/O with systemtap

Topic: This post is about tracing logical and physical reads in Oracle using systemtap. You will find a few examples illustrating the main mechanisms used by Oracle to do physical and logical I/O and you will learn how to build systemtap scripts to further explore and troubleshoot Oracle I/O.

Introduction: Performing input/output to and from the storage media, i.e. physical I/O, is one of the critical tasks underlying all database engines. When accessing data (more...)

HPC versus HDFS: Scientific versus Social

There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy.  A little of this emotion seems to […]

Oracle Real World Performance

Great videos on Oracle Real World Performance.

RAC, 12c and Direct Path Reads

Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in they did. For introduction to DPR, you can check my previous post, because […]

SystemTap into Oracle for Fun and Profit

Topic: This post is about using SystemTap for investigating and troubleshooting Oracle RDBMS. In particular you will learn how to probe Oracle processes and their userspace functions. These techniques aim to be useful as well as fun to learn for those keen into peeking under the hood of the technology and improve their effectiveness in troubleshooting and performance investigations.


Userspace probing is a very powerful technique that can be used to complement the available (more...)

JDeveloper 12c ADF View Token Performance Improvement

There is known limitation in ADF 11g, related to accessing application in the same session from multiple browser tabs. While working with multiple browser tabs, eventually user is going to consume all view tokens, he will get timeout error once he returns back to the previous browser tab. Unused browser tab is producing timeout, because ADF 11g is sharing the same cache of view tokens for all browser tabs in the same session. This means (more...)

12c HCC Row-Level Locking

In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think that this one, is one of them. Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit […]

The cost of using an index

When trying to understand why the optimiser might choose not to use an index to read rows from a table, I find the following diagram helpful.

index cost

The axis along the bottom represents the selectivity of the query (0% being no rows selected, 100% being all rows selected), and the axis up the side represents the cost of the chosen operation (more costly = more work to do).

When reading from a table without using an (more...)

Deadlocks due to unindexed foreign keys

Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE – results may vary on other versions.

A typical deadlock graph arising from an unindexed foreign key is as follows.

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000508a-00000000        27     101    SX             28      12         SSX
TX-00090013-0000019b        28      12     X             27     101           S

This type of (more...)

Oracle IO wait events: db file sequential read

(the details are investigated and specific to Oracle’s database implementation on Linux x86_64)

Exadata IO: This event is not used with Exadata storage, ‘cell single block physical read’ is used instead.
p1: file#
p2: block#
p3: blocks

Despite p3 listing the number of blocks, I haven’t seen a db file sequential read event that read more than one block ever. Of course this could change in a newer release.

One of the important things (more...)

Capturing long-running SQL in Statspack


I have been investigating “enq: TM – contention” wait events, which are almost certainly due to un-indexed foreign keys. One such extreme example which showed up in a Statspack report is as follows :

                                                             Avg          (more...)

Complément : Analyse des performances sur du long terme

Scaling up Cardinality Estimates in

Topic: Counting the number of distinct values (NDV) for a table column has important applications in the database domain, ranging from query optimization to optimizing reports for large data warehouses. However the legacy SQL method of using SELECT COUNT (DISTINCT <COL>) can be very slow. This is a well known problem and Oracle provides a new function APPROX_COUNT_DISTINCT implemented with a new-generation algorithm to address this issue by providing (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)

Exadata storage indexes and DML

Last week I’ve gotten a question on how storage indexes (SI) behave when the table for which the SI is holding data is changed. Based on logical reasoning, it can be two things: the SI is invalidated because the data it’s holding is changed, or the SI is updated to reflect the change. Think about this for yourself, and pick a choice. I would love to hear if you did choose the correct one.

First (more...)

New Version Of XPLAN_ASH Utility

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

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)

B-tree and nulls

Today while I was scrolling my “WordPress reader” page I saw a post which reminds me to something I had fun before /and find it useful in many situations/: So if we have an index based on null column followed by not null column we can use it to filter the rows with null value. […]

FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?

June 8, 2014 It has been nearly two years since I last wrote a review of an Oracle Database related book, although I have recently written reviews of two Microsoft Exchange Server 2013 books and a handful of security cameras in the last two years.  My copy of the second edition of the “Troubleshooting Oracle […]

Simora: Alpha Testers Confirmed

It's been a while since I provided any public updates regarding Simora, our Oracle workload simulation product. It's finally time to unveil the status of Simora and our steps moving forwards. We have been working extensively on the Simora engine and infrastructure over the  last several months, with a view to transforming it into a […]