How to Trace SQL or PL/SQL Performance Occurring on the Database ?

To check on the SQL or Object Performance: 

SQLTXPLAIN Utility: 

Download and install the latest version of SQLTXPLAIN utility (Explain Plan Statistics - SQLTXPLAIN.SQL) available from Oracle MySupport or Google 'SQLTXPLAIN utility'

Method Xecute executes the statement and collects all information during run time.

Steps: 

Use method Xecute.

Create a script containing your problem select statement in sqlt/run/stmt.sql

For Example: 

cd sqlt/run

sqlplus [apps user]

start sqltxecute.sql [name of script with one SQL]

start sqltxecute.sql stmt.sql <== script with problem sql

Each SQLTXPLAIN run will collect the statistics. After each run you will find a SQLT_s<xxxx>.zip in your current directory

If there are multiple Database instances or machine where the same SQL or object performs better, please rename sqlt where the Performance is good as 'sqlt_good.zip' and rename sqlt of bad Performance as sqlt_bad.zip so it is easier to look back.

SQLT Utility:

1. Download sqlt.zip (From Oracle MySupport KM 215187.1 or Google to get this script) and unzip file sqlt.zip 

2. Install SQLT following directions from sqlt_instructions.html file

3. Run sqltxtract.sql (use XTRACT method) for the SQL statements following directions from sqlt_instructions.html

3.1. Pick up the SQL you want to focus on or where there seems to be Performance issue. 

3.2. Identify the SQL ID for such SQL as the application starts it (basically the one running slow)

3.3. Collect a SQLT XTRACT for the SQL. 

4. SQLT normally generates a *.zip file.  Rename this file for your own naming convention. 

Reference: Oracle MySupport KM 215187.1, SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement.  

Thanks.