Dealing with Bind Issues December 1, 2009Posted by mwidlake in Meeting notes, performance.
Tags: histograms, Meeting, performance
One of the presentations I have seen today was on handling bind values. I can’t say it was, for me, the best I have seen this week, I’ve done a lot of work on binds in the past so I had come across most of the material before. Should I have bothered?
Well, there was one little gem in there which struck me as very simple and potentially very effective.
Bind variables and histograms are not a good mix, as has been commented on many, many times. In essence, if your data in a column is skewed so that some values match very few records and others match a large number, when oracle sees a SQL statement with a bind value being compared to that column, it peeks at the first value being passed in with the bind and uses it to decide on the plan (this is pre 11G, by the way).
That plan is then used for every execution of that sql statement until it is thrown out the SGA. Which is jolly unfortunate if all the values subsequently passed in via the bind do not suit the plan.
The solutions to this usually boil down to one of three approaches; remove the histograms on the column in question so that all values are treated equally;stop it being a bind/prevent bind peeking; force the “bad” plan out of the SGA and hope the next parse gets a better plan.
All have their merits and drawbacks.
Well, in this presentation there was a fourth solution. Something like this:
if :status in (1,2,3) then select /*+ cardinality (t1 100000) */ from table_1 t1 , table_t t2 where t1.status=:status and....; else select /*+ cardinality (t1 100) */ from table_1 t1 , table_t t2 where t1.status=:status and....; end;
I might be missing something blindingly obvious here (and this might be a common solution that has just passed me by), but it seems to me to be a simple and effective solution that could be used in many situations.
I also learnt that it is rare not to find at least one good thing out of any presentation, so long as you keep paying attention.
UKOUG So Far December 1, 2009Posted by mwidlake in Meeting notes.
It is the start of the second day (actually, an hour in, have a small “one beer too many” issue to cope with so I missed the first session) and so far I’ve very much enjoyed the event.
I saw several good talks yesterday. highlights for me were; Graham Wood’s talk on ASH, Alex Gorbachev on ASM and Doug Burn’s talk on parallel processing. Doug’s talk fell foul of the curse of all talks, the carefully prepared and tested demo deciding not to play, but a few of us clustered around his laptop to see the final results after the event.
I really wanted to get to Randolf Geist’s talk but one of the “problems” with having been coming to the conference for so many years is meeting up with people you have not seen for ages, getting into a conversation and realising that the next set of sessions started 10 minutes ago.
Today my chairing duties start, with Luca Canli from CERN talking about compressing very large data sets, which is something that is highly pertinent to my current work.
Tomorrow I chair 4 sessions:
Larry Carpenter on DataGuard 11GR2. Larry is an old friend, he gave us some excellent support on Dataguard 5 or 6 years ago.
Piet de Visser on Good Indexing. Piet is up against Jonathan Lewis and James Moorle and he has joked to me that it could be just him and me in the room, but I doubt that. I don’t want to discourage anyone from going to see JAmes and Jonathan (both give cracking presentations) but heck, come and see Piet
I’m then chairing Christian Antogini, talking on parallel processing. I’ve never “Met” Christian, I’ve been in a couple of large discussions where he was also there, so I am looking forward to meeting him properly.
My final Chairing duty is right at the end of the last day, when only the die-hards and those who’s train ticket is pre-booked late in the day remain
I’ll be chairing Husnu Sensoy who is talking about backing up enormous database, which many of you will know is a topic close to my heart. That should be a fine rounding off of the conference.
My aim today is to get to a few more sessions and get through the evening without drinking that one beer too many.