Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re: Re: Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

by rdfield (Priest)
on Nov 16, 2003 at 07:52 UTC ( #307440=note: print w/replies, xml ) Need Help??

in reply to Re: Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE
in thread Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

You can always issue a alter system flush shared_pool which will force re-compliles (at the cost of performance), but the official Oracle line is to either use literals (which is what we don't want) or to force the use of an index with the /*+ INDEX(<analyzed index>) */ hint. Mind you, the official Oracle line is to use DBMS_STATS rather than analyze, but I've never managed to get it to compile stats on a large table in a reasonable amount of time (and I've never been able let it run long enough to complile stats with the "cascade" option - batch windows are such a constraint), where I count "reasonable time" as the time it takes "analyze" to complete the same operation, so when it comes to taking the Oracle line...the track record isn't terribly good.


Replies are listed 'Best First'.
"Re: "x6 Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE
by etcshadow (Priest) on Nov 16, 2003 at 23:23 UTC
    Well, flushing the shared pool every time you execute the query still wouldn't work... because even the first time the query is compiled... it is compiled without looking at the bind values. Also, flushing the shared pool every time you execute a query is so sad I could cry.

    As far as forcing the query plan you'd like with /*+ index */ hints (as well as nested-loops, hash, ordered, etc, etc) defeats the purpose of the CBO in the first place. One thing that the CBO has taught me is that regardless how smart I think I am at query optimization... once you're combining dozens of tables in all sorts of inner-views, unions, sorts, and merges... the CBO really can be smarter than me.

    Also for my example, I could be running the same query on hundreds of different schemata, each of which have the same table structure, but contain a different demographic of data. The CBO is going to pick the proper execution path for the schema it is being executed in.

    Anyway... I'm familiar with all of those issues, and I'm really going to end up just altering the application code in which this query is executing, so that I can parameterize the sql text itself (so I don't have to bind these literals). I was just curious if you knew some cool trick that I didn't. :-D

    Thanks, though.

    Not an editor command: Wq

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://307440]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2023-02-01 15:36 GMT
Find Nodes?
    Voting Booth?
    I prefer not to run the latest version of Perl because:

    Results (10 votes). Check out past polls.