These tools, i say are equivalent to a 'stethoscope' for a doctor or a bat for a cricket player for that matter with out which you cant do anything.
1. sql* plus
2. explain plan
3. auto trace
4. sql trace with TKPROF
5. statspack
SQL*PLUS
1. sql* plus is a good scripting tool.
4. SQL trace with TKPROF
sql_trace and tkprof are the tools used to tune queries.
sql_trace generally provides raw data about the SQL queries that your application used and its performance statistics.
tkprof is a tool which formats the raw data provided by the sql_trace to readable format.
In general, TKPROF output provides the statistics of the query, waiting events, query plan and bind variable values.
There is a difference between the query plan generated between through TKPROF and EXPLAN PLAN. Infact, TKPROF provides the actual plan that is used where as the EXPLAN PLAN query plan changes based on the session settings. It shows the query plan as if we execute the query right now. So, TKPROF query plan is the acutal one used and is the one reliable.
There are some mandatory steps that needs to be performed to enable SQL_TRACE.
1. alter session set timed_statistics=true;
2. alter session set events '1024 trace name context forever, level 12';
timed_statistics is a parameter which tells the server to collect the timed activities (cpu time, elapsed time etc) for various activities in the database.
timed_statistics can be set at the session
Alter session set timed_statistics=true;
or system level.
Alter system set timed_statistics=true;
or global level.
For global level, the init.ora file should have the following entry
timed_statistics=true
To take this effect the database needs to be restarted.
sql_trace can also be enabled at the system or session level or global level.
sql_trace produces lot of data. It accepts either true or false values. The setting for global level can be done at init.ora file.
USER_DUMP_DEST in init.ora file shows the path for the rawdata.
MAX_DUMP_FILE_SIZE is an another parameter for this regard. It controls the max size of the file generated by the sql_trace.
If a numeric value is assigned, then it takes the value as the db blocks.
It can also assigned using K or M bytes.
We can also give UNLIMITED for this parameter. But ingeneral it is not adviced to give unlimited as it loads lots of data with it. A max size of 50 or 100MB is advicable.
The ways to enable sql_trace:
1. Alter session set sql_trace=true|false this is equivalent to
alter session set events '1024 trace name context forever,level 1' so we can use eiether of it
There are certain advantages if we go for level 4,8 and 12
level 4: enables sql_trace and also captures the bind variable values in the trace file
level 8: enables sql_trace and also captures wait events in the trace file
level 12: enables sql_trace and also captures wait events and bind variable values.
The overall syntax is as follows..
alter session set events ‘10046 trace name context forever, level
alter session set events ‘10046 trace name context off’--to stop tracing
2. sys.dbms_system.set_sql_trace_in_session: This allows to set sql_trace on or off for the existing session. All needs to get is the session id and serial# for the session which can be obtained from the v$session.
The trace file generated contains the session’s data irrespective of the query.
So we must close the session or stop tracing after the needed query processed but before that we need to identify where the trace file is located.
The following block gives the process server id:
Trace file contains the server id in the file name
SELECT p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr AND s.audsid = USERENV ('sessionid')
The following blocks gives the full file path of the folder where trace fiels are sits.
declare
l_intval number;
l_strval varchar2(2000);
l_type number;
begin
l_type := dbms_utility.get_parameter_value
('user_dump_dest', l_intval, l_strval);
dbms_output.put_line(l_strval );
end;
/
By making use of the above two queries we can find out the trace file.
Now we are done with generation of trace file and finding the path for it.
But how to read the trace file? TKPROF is a tool used to rearrange the trace file contents in a readble fashion.
To read the trace file we need to have the privileges that are given by
_trace_files_public = true
In command line we need to give,
tkprof inputfilename(.trc) outputfilename(.txt)
Thats all now we have reable trace file at the outputfilename(.txt)
Here are the steps that i did to generate a trace file
SQL> alter session set timed_statistics=true
2 ;
Session altered.
SQL> alter session set sql_trace=true
2 ;
Session altered.
SQL> select owner,count(*) from dba_objects group by owner;
SQL> select p.spid from v$process p, v$session s where p.addr=s.paddr and s.audsid=userenv('sessionid')
/
SPID
------------
3916
