Re: SQL Tuning - TKPROF ... NOT!

Apologies if this makes me a vulture.

There are quite a few similar products out there, basically all they do is take the info provided by trace files and accesses the v$ x$ etc tables and uses the info provided by ORACLE for 'free'.

Trouble is that the sales people go for the IT managers and often business users selling the products as a means to make a system go faster, one client recently had the choice of two developers or buying a 'performance tuning' tool, they went with the tools.

Their salesman privately admitted that all it did was reformat the trace files output and make it look 'nice'.

Sit a few developers down in a room with unix and awk, and you can get what you want, ok if you want it 'tarted' up then you can stick it in the DB and run sqlplus or srw on it.

Now when they come up with a product that will identify problems and automatically solve them, then perhaps Ill not be such a sceptic.

Some people never learn.

Heres a very cheap tuning process

a Run the DB with trace on for day or so

b Run all the trace files through tkprof, with explain plan.

c Scan all the output for 'FULL TABLE SCAN' or heavy DB access, a fancy awk script should suffice.

d Review v$sqlarea looking for 'duplicate' code, or excessive values in the ... (the fields that show rows/memory etc etc)

Hey presto problems found.

Mike Cox

Michael Cox          Input Technolgies Ltd
Email                mike_at_kane.demon.co.uk
Compuserve           100045.1715_at_compuserve.com


*********************************************************************