http://qs1969.pair.com?node_id=307253


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

In Oracle
analyze table <table name> compute statistics for all indexed columns +size 254;
with generate a histogram of weightings for each range of values that are in the indexed columns. Parse trees can be generated on a case by case basis. In the latest versions you can "fake" the stats to get the optimizer to behave properly.

rdfield

Replies are listed 'Best First'.
Re: Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE
by mpeppler (Vicar) on Nov 15, 2003 at 01:16 UTC
    With Sybase you'd use the optdiag tool to get information about index statistics and index distribution, and to load simulated index statistics.

    To see query plans you use set showplan on, and dbcc traceflags 302 and 310 are used to get a full explanation of the choices made by the optimizer (very useful when the optimizer decides to do things that seem very counter-intuitive to you).

    Michael

Re: Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE
by etcshadow (Priest) on Nov 15, 2003 at 17:20 UTC
    Yes, I understand how histogram statistics work... my point was that if you bind a value which the CBO would be interested in... it is only going to come up with one execution plan for the query, regardless of which value it is executed with.

    Or am I just misunderstanding what you are saying? Is there some hint I don't know of yet... something along the lines of: /*+ force_reparse_after_parameter_substitution */? If so, I'd love to hear it... because it would be really useful for me in a few situations. Situations where an application only allows for a query to be parameterized by bind values... and I want the query to be parametrizable, but not to suffer the hamstringing of the CBO that bind parameters are causing.


    ------------
    :Wq
    Not an editor command: Wq
      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

        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