RE: Bind variable peeking and Dynamic sampling

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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  

--
http://www.freelists.org/webpage/oracle-l