SQL Trace File--Performance Tunning -1

Hi All,Quite often we come across tunning the queires...This is the area where most people find it difficult to understand ..The blind rule every one says check the explain plan and try to remove the full table scans...most of times it works to a extent..

For really tunning to the best u need to understand what oracle is doing to the data you require or perform the transactions you have issued..

For that purpose oracle has give sqltrace utility which will help you to figure out what all operations are getting performed and the way and time taken by each of them
First and foremost we will see what all setups required(mostly done by DBA..but nice to know things)
lets try to answer some simple questions
1.At what level we can enable the trace??

Both at the system level and session level
2.How to know the sessions and enable the trace?

select username, sid, serial#, paddr from v$session where username='APPS'
USERNAME SID SERIAL# PADDR------------------------------ --------- --------- --------APPS 372 313 4308F6F8APPS 373 27 43092A70APPS 375 36 430924B8APPS 376 184 4308DA60APPS 377 334 4308FCB0APPS 378 102 4308A6E8APPS 380 24 43091948APPS 381 12823 4308F140APPS 382 5297 4308EB88
To enable the trace for any one of the sessions
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(, , TRUE) EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(382, 5297, TRUE)

3.Where are the trace files getting stored??

you find the trace files into /admin//udump.

Normally trace file are difficult to handle..so we use the tkprof utility
tkprof tracefile.trc t1 RECORD=sqlfile.sql

in other case they are stored in the directory specified in user_dump_dest parameter in the init.ora file

4.What does SQLTrace utility provides??

The diagnostic tool 'sql trace' provides performance information aboutindividual SQL statements and generates the following statistics foreach statement:
* parse, execute, and fetch counts

* CPU and elapsed times

* physical reads and logical reads

* number of rows processed

* misses on the library cache
This information is input to a trace (.trc) file and sql trace can beenabled/disabled for a session or an instance
5.What all setups need to be done for enabling trace in a instance??
The following parameters need to be set up in the "init.ora" filefor the particular instance (SID) that you wish to use SQL Trace:
* SQL_TRACE + Enable/Disable SQL Trace for the instance.
Values -- TRUE Enable statistics to be collected for all sessions.

FALSE Disable statistics to be collected for all sessions.
* TIMED_STATISTICS + Enable/Disable the collection of timed statistics, such as CPU and elapsed times.
Values ------ TRUE Enable timing (we usually recommend this)

FALSE Default value.

* MAX_DUMP_FILE_SIZE : + Specifies the maximum size of trace files operating system blocks.

Values ------ The default value for this is 500 but if your trace file is truncated then increase this value.
* USER_DUMP_DEST: Specifies the destination for the trace file.
Values : The default value for this parameter is the default destination for system dumps on your operating system.
6.How to enable the trace for a session?

ALTER SESSION SET SQL_TRACE = TRUE;

ALTER SESSION SET SQL_TRACE = FALSE;

For timed statistics

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

ALTER SYSTEM SET TIMED_STATISTICS = FALSE;

7.how to set SQL Trace Facility for an Instance?
If the initialization parameter SQL_TRACE=TRUE, then statisticswill be gathered for ALL sessions. If the facility has been enabled forthe instance, it may be disabled for an individual session by issuing bythe above SQL statement