Search

Top 60 Oracle Blogs

Recent comments

February 2010

Data Access APIs–Part 1: Fun with UPI

First, I’d like to apologize to our good friend SQLLIB.  Those of you who have been working with the Oracle Database for some time will notice that, while it too is a common data access library, I’ve omitted it from this series of posts. No, it’s not because of some personal vendetta against SQLLIB.  In […]

a formula for failure (or an expensive redesign)

If 'Premature optimization is the root of all evil.'then 'Premature automation is the propagator of many evils.'else 'Failure to optimize is the abyss.'end;

Excited about NoCOUG Winter Conference

NoCOUG is hosting its winter conference next week – On February 11th.
As usual, we’ll have the best speakers and presentations ever. This time I’m extra happy because two of the speakers that are going to be there, Dr. Neil Gunther and Robyn Sands, are there because I was wowed by them in a previous conference and asked our Director of Conference Programming to invite them. And they agreed! I believe it is the first time that either of them presents at NoCOUG and I’m very excited about this.

I’m sure I don’t need to introduce Robyn Sands to any Oracle professional – She’s an OakTable member who talks a lot about the right ways to manage performance. She is very scientific and precise but she gives very practical advice that is very applicable.

Dr. Neil Gunther is a well known performance expert. So well known that he has his own Wikipedia article. I first ran into his work when I did performance testing work, something like 6 years ago. From his articles, I learned the importance of having performance models without which you cannot interpret your results and know when your tests were faulty. I ran into him again when Tanel Poder mentioned that Dr. Neil Gunther is now doing work that will be relevant to Oracle professionals. He appeared in HotSos few years back and now we get to see him at NoCOUG – with both a keynote session and a technical session. He invited the crowds to ask questions at his blog, so you can participate.

DEVCON Luzon 2010

I just recently I became a member of the PSIA Tech Council… The company I’m working for is a member of PSIA which makes up 90% of the country’s software sector promoting the growth and global competitiveness of the Philippine software industry, also an active partner of the government and academe in implementing programs that benefit the industry.

The PSIA, PSIA Tech Council, together with the Awesome and Cool sponsors will be having the Luzon leg of DEVCON here in Manila!

Below are the details of this awesome event:

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! ;-)

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! ;-)

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:

Karl’s article:

Note that in March I will be releasing PerfSheet v3.0, which will have lots of improvements! ;-)

Share/Bookmark

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.

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.

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!