RE: Bind variable peeking and Dynamic sampling
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx, "'Shivaswamy Raghunath'" <shivaswamykr@xxxxxxxxx>, "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 May 2007 22:49:47 +0800
Hi,
I finally have managed to put the fine-grained hard parsing presentation up
to my website too (you know, uploading a zip file to a ftp site isn't a
trivial task ;)
http://www.tanelpoder.com/files/Tanel_Poder_Fine_Grained_Hard_Parsing_and_De
mo.zip
Demo script included.
Anyone implementing this in live should understand what this approach causes
-> long library cache hash chains, lots of child cursors under a library
cache hash bucket. This means hard parses for statements hashing to same
value will potentially take longer, library cache lookups during soft parses
may get longer (and hold the corresponding library cache latch longer) if
session cursor cache isn't used.
Also for testing make your shared pool big and parse a lot of (tens to
hundreds of thousands) of new child cursors (using concurrent sessions),
measure the slowdown and see if anything breaks in your db. But in live i
recommend to not oversize the shared pool as smaller SP will age out old
unused cursors faster.
The baseline is - do not make Oracle to generate thousands or more child
cursors for a statement, try to stay within reasonable limits - and as the
FGHP rules can be anything you can call in PL/SQL, you've got the
flexibility...
Tanel.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Allen, Brandon
Sent: Wednesday, May 09, 2007 05:05
To: Shivaswamy Raghunath; Oracle-L
Subject: RE: Bind variable peeking and Dynamic sampling
Yes, Tanel has been kind enough to share some of his secrets with me. He
actually posted a preview of his presentation on this list a few months ago
as well. I am currently looking into implementing his methods, but still
have some testing to do.
I have actually tried disabling bind variable peeking also, but got negative
results immediately as the first query I tried (the same one I was having
trouble with due to inappropriate plan sharing) suffered a major drop in
performance - running in 20 seconds with the bind variable peeking compared
to 20 minutes with the plan it chose w/o peeking at the binds.
Thanks,
Brandon
Other related posts:
