Archive for March, 2010

This year, I will talk again about basics of DB technology for 9th grade of AGW (Augustinus Gymnasium Weiden, a secondary school) again. The same event has been a success in the last years, and it’s simply nice there.

Nice? Please allow me a word about this school. They have an engaged teacher, Klaus Märker, who does a lot more of IT teaching than he would have to. His voluntary IT classes are simply looking great and promising. It’s a big opportunity for the kids, to learn some IT specials during their school time. I hope, you know and value what he does for you.

It was self-evident for me to support his approach on showing the young ones more than ministry officials creating an official curriculum can imagine. Database technology is some kind of “poor cousin” in IT schooling. I see that every day. I am not under the imagination that the girls and boys there will become database pros from one lecture, may it be good or bad. But they have a chance to see more than their history-, latin- or maths books. And be honest, at least, if you don’t use a database by yourself, your name is already inside a pretty good one.

Sometimes you simple need to know whether a statement is fast or not, or why it is slow. (Sadly, it seems like virtually nobody wants to know why a statement is fast, but that’s another story…)

Do not use EXPLAIN PLAN in such cases, it does not (can not) deal bind variables right. I’ve got a simple script in my toolbox to create a real execution plan using bind variables, but avoid displaying all the lines the SQL returns. Just have a look, and use it if it’s suitable for your needs. Please keep in mind, that you need to set the binds the way I do, because the cost based optimizer handles bind statements entirely different (bind peeking, (adaptive) cursor sharing …)

This script was designed to run in Oracle SQL Developer very well, but it seems that version 2.1 has trouble with the LAST cursor, due to some changed output.Read more…

Recently, there was trouble in the air. I migrated a database into 11g to enjoy adaptive cursor sharing, but it did not adapt cursors. In the trouble shooting process, I discovered the following:

Bug 8357294: ADAPTIVE CURSOR SHARING DOESN’T WORK FOR SQL FROM PL/SQL

Nice one. A short quotation from the bug text:

PL/SQL has famously enjoyed the so-called “soft-parse avoidance” optimization
for embedded SQL ever since its inception. Then, in 10.1, the benefit was
brough to native dynamic SQL, but only for “execute immediate”. Notice that
queries executed using a ref cursor (whether this is opened statically or
dynamically) do not enjoy the “soft-parse avoidance” optimization.

It seems that this was overlooked when adaptive cursor sharing was
introduced.

Summary: 11g PL/SQL does adaptive cursor sharing when we are using EXECUTE IMMEDIATE, but does not if you are using embedded SQL (for example in a(n implicit) cursor, like a for loop).
The bug says, that setting SESSION_CACHED_CURSORS=0 fixes this problem, but I will have to test it by myself to classify this information.