Search

Top 60 Oracle Blogs

Recent comments

February 2010

Oracle Peformance Visualization…

Coskan Gundogar and Karl Arao have written two interesting articles about Oracle performance analysis and visualization, check these out!
Coskan’s article:
http://coskan.wordpress.com/2010/01/27/working-with-statspack-part-1a-di... Karl’s article:
http://karlarao.wordpress.com/2010/01/31/workload-characterization-using... Note that in March I will be releasing PerfSheet v3.0, which will have lots of improvements! ;-)

Bind Variable Peeking – execution plan inefficiency

In my Beyond Oracle Wait interface article I troubleshooted a test case where an execution plan somehow went “crazy” and started burning CPU, lots of logical IOs and the query never completed.

I have uploaded the test case I used to my new website, to a section where I will upload some of my demo scripts which I show at my seminars (and people can download & test these themselves too):

http://tech.e2sn.com/oracle-seminar-demo-scripts

Basically what I do is this:

  1. I run the query with bind variable values where only a handful of rows match the filter condition. Thus Oracle picks nested loop join (and indexed access path)
  2. Then I run the same query with different bind values, where a lot of rows match the filter condition. Oracle reuses existing execution plan (with nested loops!!!). Oracle ends up looping through a lot of blocks again and again (because nested loop visits the “right” side of the join once for every row coming from the “left” side of the join).

Using nested loops over lots of rows is a sure way to kill your performance.

And an interesting thing with my script is that the problem still happens in Oracle 11.1 and 11.2 too!

Oracle 11g has Adaptive Cursor Sharing, right? This should take care of such a problem, right? Well no, adaptive bind variable peeking is a reactive technique – it only kicks in after the problem has happened!

Bind Variable Peeking – execution plan inefficiency

In my Beyond Oracle Wait interface article I troubleshooted a test case where an execution plan somehow went “crazy” and started burning CPU, lots of logical IOs and the query never completed.
I have uploaded the test case that you can run here (note that it drops and creates tables T1..T5 in your schema):
/ast/02_bind_peeking_nested_loops.sql
Basically what I do is this:
I run the query with bind variable values where only a handful of rows match the filter condition.

Small Intermezzo Towards Miracle OpenWorld 2010

Created a small YouTube introduction movie as requested by Moans for the Miracle OpenWorld 2010 event this year. I am happy they more or less resolved their issues with Oracle Legal regarding the naming for the event (see the movie and news section on the Miracle OpenWorld 2010 main page).

Anyway, made a small attempt (in HD) to tell a bit about XML on YouTube.

As said:

    “An exploration in mending and gluing things together, while it absolutely doesn’t make any sense what I am saying, but then again, who cares…its bogus anyway…”

Hope to CU around somewhere this year.

:-)

Tuning the Application, Database and Hardware


Tuning Example in 3 parts



part 1 - "The Database is hanging!" AKA "the application has problems, good grief"


Upcoming Presentation Slots, Spring 2010

I will be presenting the Oracle Wait Interface is Useless Dog and Pony show a couple of times this spring. It was first presented at the UKOUG 2009 with Tanel Poder, and will be seeing the light of day at the following events:

I hope to see some of you there, and will get the final part posted before RMOUG – so the picture will be complete. If you’ve got any questions, it’s a good opportunity to come and heckle at one of those events!

Craig Shallahamer is now blogging!

Craig Shallahamer is now blogging… check out his initial post here!

Earlier I had a few mail exchanges with him and got to check his new work.. called the Stress Identifier. Hmm.. so how it will be helpful? Well if you have already characterized your workload, you may want to definitively determine which internal database activity is stressing your CPU, IO, and network subsystems. So this tool will avoid the guess work of selecting that “unit of work”… check out the link for more details…