A not so sophisticated but very effective way of seeing what is going
on in your server is to run a sql trace. Start up the database with
sql_trace=true and timed_statistics=true in the parameter file. Also
check user_dump_dest points to a directory whith sufficient free space
and set max_dump_file_size=unlimited. Run the system like this during
an average working day.
At the end of the day you typically have some 100 - 500 Mb of trace
files. Concatenate thesa all into one big file and analyze this with
tkprof utility. Supply the explain=<connectinfo> option to include the
execution plans in the output. Surprisingly the final output is
condensed to the number of different sql statements in your database.
You can search for the word FULL to locate full scans and see how
often these where executed and see what was the impact on CPU and
disk.
Received on Sat Feb 23 2002 - 15:46:06 CST
