Trace File Generation

The main use of enabling trace for a concurrent program comes during performance tuning.
By examining a trace file, we come to know which query/queries is/are taking the longest
time to execute, there by letting us to concentrate on tuning them in order to improve the
overall performance of the program.
The following is an illustration of how to Enable and View a trace file for a Concurrent     Program.

• Navigation: Application Developer–>Concurrent–>Program
  
  
  • Check the Enable Trace Check box. After that go to that particular Responsibility and run the Concurrent Program.

• Check that the Concurrent Program has been completed successfully.

• The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:

Select oracle_process_id from fnd_concurrent_requests where request_id=’2768335′
(This query displays Process Id)

• The path to the trace file can be found by using the below query:

SELECT * FROM V$PARAMETER WHERE NAME=’user_dump_dest’
(This Query displays the path of trace file)

• The Trace File generated will not be in the readable format. We have to use TKPROF utility to convert the file into a readable format.
  

• Run the below tkprof command at the command prompt.

TKPROF < Trace File_Name.trc> SORT=fchela

A readable file will be generated from the original trace file which can be further
analyzed to improve the performance. This file has the information about the
parsing, execution and fetch times of various queries used in the program.

SQL Tuning in Oracle 10G

/* Thanks to "Sridhar Chitupolu" for sharing this useful information */

Generally we will manually tune the SQL queries to optimize the time and cost.
Instead of manually tuning the query we can use $ORACLE_HOME/rdbms/admin/sqltrpt.sql
to tune the queries,it will provide us the suggestions and alternatives etc.. Using those suggestions we can optimize the query.

Note: It will work only in  Oracle 10G.

In TOAD, First run the query, which needs tuning.
Then follow the below steps:

1) First Get the Session Id From Session Browser in TOAD.
2) Get Hash Value :
 Query to get the Hash Value Based on SID (Session ID of particular Query)

select hash_value, sql_text
from v$sqltext
where hash_value in
        (select sql_hash_value from
                v$session where sid = &sid)
order by piece
/

3) To get the SQL ID Associated with the Hash Value

SQL> select sql_id from gv$sql where HASH_VALUE='1119157887';

To Check the cost,etc...

SQL> select plan_table_output from table(DBMS_XPLAN.display_cursor('f43ga151b9zmz'));

4)Path for Tuning SQL

-bash-3.00$ cd $ORACLE_HOME/rdbms/admin

SQL> @sqltrpt.sql <Input sql id.>

It will provide us the cost and time details and suggestions.