A Metaphor

Around Christmas time I usually drive up to Oxfordshire to collect my mother and bring her to stay with the family for a few days. The trip typically takes about 1 hour 45 minutes each way. Over the years I’ve tried to figure out some way of getting there and back more quickly, and I’ve finally thought of the silver bullet that will knock about 30 minutes off the journey time.

Meaningless Keys

I have been known to criticize “meaningless key” syndrome from time to time. I’m not going to go through the arguments here, but I’d like to tell you about a lovely example of how badly things can go wrong when you follow that route.

Table4 is at the end of a sequence of referential integrity constraints; in fact, rather than the usual 1-to-many relationships, it’s almost a 1-to-1 all the way from table1 to table2 to table3 to table4 with just a few cases where their data volume multiplies up.

Area_code is part of the (undeclared) primary key of table1, which should have cascaded all the way to table4. But everything is done with meaningless keys generated by Oracle sequences.

Missing the Point

One of the little features of WordPress is that you can see where incoming readers came from (their search criteria, or a site that redirected them). Yesterday I was quite interested to see a few viewers coming in from a Burleson article which referenced a page I had written about getting some help from statspack for a recent trouble-shooting exercise.

Naturally I had to see what Burleson had to say – unsurprisingly he was fairly selective in what he said, picking the bits that best suited the purpose of advertising one of his books, and closing with an advert for a website that he has helped develop and which he claims to be an “expert system” to help you learn how to spot system-wide performance problems.  (Funnily enough I was planning to use the example from that site in a future post to explain how not to read a statspack report.)

Parallel Execution

Looking back at some of my previous posts I was reminded how easy it is to overlook one important feature when trying to comment on another.  In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment from David Aldridge jogged my memory. So here’s an important thought about parallel execution.

Analysing Statspack (2)

[Back to Part 1][Forward to Part 3] 

One of the important things to know about the standard statspack report is where not to look. Here’s an example:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:    100.00
            Library Hit   %:   99.96        Soft Parse %:     99.00
         Execute to Parse %:   98.02         Latch Hit %:    100.00
Parse CPU to Parse Elapsd %:   92.74     % Non-Parse CPU:     98.56       

The Instance Efficiency summary (note especially the indication that 100% is the ideal in all cases) is essentially useless. At least, it is useless in isolation if you run off the occasional report trying to spot problems.

dbms_xplan – again

I’ve discussed the capabilities of the dbms_xplan package in a couple of posts already; and shown how useful it can be in two examples: understanding a problem with filter subquery selectivity and understanding why some Cartesian merge joins were appearing unexpectedly.

Let me make a crucial point about execution plans (again):  if you have a problem with an execution plan, and need help in understanding what’s going you, you should provide at least the same information that is available from a simple:

explain plan for {statement}
select * from table(dbms_xplan.display);

In particular, you must generate the filter_predicates and access_predicates.

Constraints

A little while ago I discussed one side-effect of transitive closure and predicate generation. Coincidentally, David Aldridge and Jeff Hunter have both come up with further examples of predicate generation - in their examples generated from check constraints – that can cause problems.

Mything in action

I came across a blog entry recently with the title Myths about bitmap indexes which set about debunking the following three claims:

Myth 1 – Bitmap indexes take up a lot more space than B-Tree indexes.
Myth 2 – Bitmap indexes are only suitable for data warehouses because of the nature of the data
Myth 3 – Bitmap indexes have slow performance on DML actions

I have a few problems with anyone who tries to debunk these “myths” because they are essentially correct – although I have to say I’ve not heard them stated in exactly the way they appear above.

Proxy Users

Here’s a little experiment that may surprise you – to be run only on 10gR2, using an account with DBA privileges.

A couple of warnings before you start. The script assumes you don’t already have a couple of users called end_user and application_user; the script assumes you have the (commonly installed) tablespace called users. If these assumptions cause a problem, change the code before you run it.

The question is – what do you expect to see as the final two sets of output:

Buffer Sorts

In an earlier article I mentioned the buffer sort in a footnote; I thought I would expand a little more on what I think it does and why it appears as a buffer sort in an execution plan rather than the more traditional sort (join).

Consider the trivial script:

ORA-01722: upgrade error

I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 9.2.0.6 to 10.2.0.2.  After the upgrade, the following SQL statement (shown here with its original format – not according to my standards) started failing with Oracle error: ORA-01722: invalid number.

Index Operations

Just a little note on two index operations that you might see in an execution plan that are worth highlighting because it’s so easy to miss just one critical word in the name of the operation.

Constraints, Inserts and bind

Everyone knows that bind variables are an important aid to achieving scalability  in OLTP systems. Here’s an example to show how important they are with insert statements – particularly if you believe in using constraints to enforce data correctness.

Cartesian Merge Join

Have you ever had an execution plan which gave you a Cartesian join that you knew just couldn’t be happening ?

Surely you put a join condition in for every table ! Well maybe you did, and maybe the optimizer took some of your join conditions away. When the (not always) terrible Cartesian join appears, make sure you check the predicates section of your execution plan.  Here’s a demonstration of why:

Plans in Memory

In an earlier article I described how dbms_xplan.display_cursor() could be used to query memory to find the execution plans (and row source statistics) of recently executed SQL.

In 9i, you have to use your own SQL to get the equivalent results. If you do, you should avoid using the “traditional” hierarchical type of query that you would use against the plan table, as it can be a little brutal on the library cache latches. Instead, you query the v$sql_plan view by hash_value and child_number with a simple “order by id” using the new, internally calculated, depth column instead of the psuedo-column level as the means of indenting the operations.