SQL Tuning

SQL_TRACE may be enabled at the session or the system level.
As a lot of output is generated rarely use it at system level (set SQL_TRACE=TRUE in init.ora, output will go to user_dump_dest, be careful and make sure max_dump_file_size is set).

ALTER SESSION SET SQL_TRACE=TRUE|FALSE - This enables it in the current session, useful in sqlplus or embedded in the application.

SYS.DBMS_SESSION.SET_SQL_TRACE_IN_SESSION - This enables us to turn it on for another session, all you need is SID & SERIAL#.

ALTER SESSION SET EVENTS - We can set an event to enable tracing with more information than is normally available via ALTER SESSION SET SQL_TRACE=TRUE;. This is not documented by Oracle but ...

(There are other methods but these are the most widely used.)

These methods are great for client-server applications as a session stays connected for a while so it is possible to query v$session and find out a users SID & SERIAL# and turn SQL_TRACE on. These days more applications are web based and the transactions come and go very quickly. A way to trace these users is to turn SQL_TRACE on via the LOGON DDL trigger as below:

create or replace trigger logon_trigger
after logon on database
begin
if ( user = 'TKYTE' ) then
execute immediate
'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
end if;
end;/


Setting _trace_files_public=true allows os users who have not got dba os privilege to read the generated trace files in udump. If this does not work umask may be set incorrectly.

To run TKPROF:

tkprof ora????.trc report.txt

PARSE phase - where Oracle finds a query in the shared pool (soft parse) or creates a new plan (hard parse).
EXECUTE phase - This will be where the work is done for an UPDATE, but not for a SELECT.
FETCH phase - This is where most of the work is done for a SELECT.

COUNT - how many times the event occured.
CPU - How much CPU time was spent on this phase, o if timed_statistics=false.
ELAPSED - As measured by a wall clock.
DISK - How many physical I/Os to disk.
ROWS - How many affected by that phase of processing.

Important bits to look for:

A high parse count.
You should be aiming to only parse once and execute many times.

Execute count of one for all or nearly all SQL.
This means bind variables are not being used.

A large disparity between CPU time and elapsed time.
These are the queries that are gumming up the works, fix these and your whole application will work faster.

A high (FETCH COUNT)/(rows fetched) ratio.
The application is not performing bulk fetches. Every application has the ability to do this (to fetch many rows at a time in a single call). If you do not utilise this you are performing more round trips to the client than necessary. (PRO*C prefetch=NN) (JAVA call SETROWPREFETCH method).

An excessively high disk count.
If DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT, then all blocks were read from disk, the SGA may need resizing.

An excessively high query or current count.
This indicated that your query does a lot of work, but lets hope its not one that is run too often.