Re: Need a real stats tool, tkprof is hopeless. Help !

In article <800392478snz_at_jlcomp.demon.co.uk>, Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk> wrote:
>In article <3orsrb$ne_at_voyager.cris.com> Ayana_at_voyager.cris.com "Ayana" writes:
>
>: I've been thrown to the wolves and given only tkprof to tune a
>: truly god-awful query on some large (1.5 million rec) tables.
>: The explain plan option of Tkprof quits working somewhere
>: after 90000 records -- and I have no idea of why.
>: Change the select parameters (date), rearrange the query,
>: and it sometimes works. But that's not exactly useful,
>: as I'm rearranging the query to get performance !
>
>What do you mean by 'records' in the context of tkprof ? Is this
>lines of the PARSE/EXEC/FETCH type ?
>
>If the failure is actually in tkprof, and is only when EXPLAIN PLAN
>is used, then do a tkprof without EXPLAIN PLAN to collect the I/O
>and CPU statistics.

Thanks for the suggestions, I will hang on to them for my next crisis. As it turns out, tkprof is broken a little, and editing the trace file to add a couple of blank lines after the SQL statement fixes the problem. Inconvenient, but doable.

>Disclaimer: Whilst this may help you do what are trying to do, I don't
>really think you are approaching the problem you describe in a suitable
>fashion. tkprof is useful for telling you where the problems are in a
>running system: statistical analysis of the data and an understanding
>of the purpose of the query tells you the path the statement _should_ take:
>you do not have to run a statement to find out if it is going to take
>the path you want.

I've been given the job of speeding up a query that is currently taking a *very* long time to run, i.e. close to an hour. My current problem is convincing the optimizer NOT to use the index it wants to use - I'd give my eyeteeth to be able to specify the access path, rather than just *suggesting* one. Given 1.5 million records, I have one parameter that cuts that down to 35000, and another that cuts it down to 1800. Oracle *insists* on using the first parameter as its first cut at the large table, even if I drop the index (then I get a table scan!). I'm reduced to doing sub-selects to get the rowids of the 1800 that fit the second parameter. It's the only way I've found to avoid having 35000 records read every time (and that's if I only want a few days' data, I hate to think what it is for a whole month!)

Anybody who knows of a larger hammer to *suggest* access paths, I'd really _love_ to hear it !

Ayana, still tearing out hair

-- 
ayana_at_cris.com                         Ayana Craven
Isn't there *any* good real-time database out there ??
Just *one*, that actually works, and is robust ??