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

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.

To get the path used for the interesting (i.e. high volume/time) statements, use a simple framework trace file and stick the statement into it. (e.g. in sqlplus, set sql_trace true, select user from dual, and exit. Find the trace file and replace the 'select user from dual' with the statement of your choice: in v7+ you have to alter the 'uid=nnn and len=nnn lines just before the statement to be the userid for an Oracle ID that could parse the statement, and the length in bytes of the statement respectively). (You will have to remove the 'into' section if you have one, and may have to reduce the length of bind variable names (I am assuming you have bind variables, or you could use the normal 'EXPLAIN PLAN for ....' syntax to do the job from inside SQL*Plus)).

Is it possible that the failure is not where you think it is ? Have you set the init.ora parameter 'max_dump_file_size' to something large: if not, your trace file simply stops at the default limit: if this happens to be in the middle of a line then tkprof may be failing because it sees what it considers to be a syntax error.

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.

-- 
Jonathan Lewis