Re: Optimization question: Unrolling subquery into IN clause
Date: Sat, 24 Oct 2009 11:42:19 -0700 (PDT)
Message-ID: <2d38105f-5a10-4545-aca4-66efd6fd97b9_at_31g2000vbf.googlegroups.com>
On Oct 23, 9:42 am, Wolfram Roesler <w..._at_spam.la> wrote:
> That was the reason of the problem. I fixed the histograms on the
> column in question, and now my query finishes in less than a second.
> For the version with bind variables, this means a speed-up by a
> factor of 1000.
>
> The problem is thus completely understood and solved.
> Thanks to all who helped!
>
> Best regards
> W. Rösler
Just to point that out: Having added histograms and using bind variables might now lead to different issues, depending on how your application works. If you use a bind variable on a skewed column you might end up with execution plans being shared across executions that might lead again to performance issues, e.g. if you have a execution plan based on a highly selective bind value and now execute the same statement with a bind value that is not selective at all. All pre-11g versions will re-use the execution plan if it is still available in the shared pool and it might not perform well if you need to access now most of the rows from the tables using an index access and nested loop joins (11g tries to address that with Adaptive Cursor Sharing).
So that is just something to watch out for, you might run into issues with some "unpredictable" performance where the same statement sometimes performs well and sometimes doesn't, depending on the combination "bind value used at parse time" and "bind value used at execution time".
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Sat Oct 24 2009 - 13:42:19 CDT