Re: Help! Server Tuning/speed/optimization

You can produce a trace file that contains informations about parse and execution time, access path and more for every executed sql statement. You only have to set sql_trace=true in your init.ora file and alter the session to switch the sql trace on. Oracle's ships a command line utility TKPROF to read the trace file.

Mostly the SQL optimization has the greatest effects on good or bad performance. The SQL syntax, the availability of indexes and its selictivity are the most interesting points. To display the access path you can use the EXPLAIN PLAN command within SQL*Plus, etc. Try to eliminate full table scans for large tables and superflous nested loops. Modify the SQL statement and see the effects on explain plan. (The dictionary view PLAN_TABLE has to be created by executing utlxplan.sql)

There are also some methods to tune the server. The Oracle server administration guide describes the methods in the chapters 21-24.

You may make use of the tool Hora from KeepTool. It allows you to display the execution plan in a Windows tree view. Modify the statement and press the explain button again. Another button lets you execute the sql statement. The tool can also switch on the sql trace for any selected session by the right mouse button contect menu. It can monitor most tuning script results described in the Oracle administration guide as bar diagrams. You can download a 30 day free demo version from www.keeptool.swn.de .

Regards
Holger Heidenbluth

Saunnie McGee wrote:
>
> Does anyone know of some simple strategies for optimizing a database?
>
> Queries that return under 1000 records take about 1 minute. In user time
> that's way too long. We have an average of 20,000 records in the tables we
> are testing. We are using a Developer 2000 (forms 4.5) front end and the
> database is version 7.3.2.2.1.
>
> saunnie_at_gcr1.com
Received on Wed Sep 03 1997 - 00:00:00 CEST