Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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

by rdfield (Priest)
on Nov 16, 2003 at 07:52 UTC ( [id://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.

rdfield

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.


    ------------
    :Wq
    Not an editor command: Wq

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (5)
As of 2024-03-28 13:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found