11G Oracle TKPROF & SQL_TRACE Performance investigation.

System Privilege To Enable and Disable Tracing

GRANT/REVOKE alter session TO uwclass;

Add time_statistics in trace file.

ALTER SYSTEM SET timed_statistics=TRUE;

SQL COMMANDS

ALTER SESSION SET sql_trace = TRUE;
ALTER SESSION SET sql_trace = FALSE;

Using PL/SQL CODE

trace enable

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);

trace disable

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);

Location of TKRPROF Utl.

$ORACLE_HOME/rdbms/admin/utltkprf.sql

Trace file location

$ORACLE_BASE/diag/orabase/orabase/trace

Sqlplus commandline

TKPROF c: emp\orabase_ora_1492.trc c: emp race_out.txt

To enable the SQL Trace Session from SQL prompt

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);

To disable tracing specified in the previous step:

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634); Taken from, for more details trace_tkprof

Optimizer Environment | Oracle Scratchpad

Optimizer Environment | Oracle Scratchpad: "Optimizer Environment
Filed under: Troubleshooting — Jonathan Lewis @ 8:28 pm BST May 1,2007
From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.

Oracle 10g allows us to see the optimizer environments at the system, session, and individual SQL level, through v$sys_optimizer_env, v$ses_optimizer_env, and v$sql_optimizer_env. This can be very helpful when tracking down anomalies, for example:

select
        first_load_time, child_number, last_load_time,
        plan_hash_value, loads, executions
from
        v$sql
where
    sql_id = 'g4pkmrqrgxg3b'
;    "



'via Blog this'

Oracle UTLXPLAN.SQL Explain Plan Script for Download

rem
rem $Header: utlxplan.sql 08-may-2004.12:53:19 bdagevil Exp $ xplainpl.sql
rem
Rem Copyright (c) 1988, 2004, Oracle. All rights reserved.
Rem NAME
REM    UTLXPLAN.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem     bdagevil   05/08/04  - add other_xml column
Rem     bdagevil   06/18/03  - rename hint alias to object_alias
Rem     ddas       06/03/03  - increase size of hint alias column
Rem     bdagevil   02/13/03  - add plan_id and depth column
Rem     ddas       01/17/03  - add query_block and hint_alias columns
Rem     ddas       11/04/02  - revert timestamp column to DATE (PL/SQL problem)
Rem     ddas       10/28/02  - change type of timestamp column to TIMESTAMP
Rem     ddas       10/03/02  - add estimated_time column
Rem     mzait      04/16/02  - add row vector to the plan table
Rem     mzait      10/26/01  - add keys and filter predicates to the plan table
Rem     ddas       05/05/00  - increase length of options column
Rem     ddas       04/17/00  - add CPU, I/O cost, temp_space columns
Rem     mzait      02/19/98 -  add distribution method column
Rem     ddas       05/17/96 -  change search_columns to number
Rem     achaudhr   07/23/95 -  PTI: Add columns partition_{start, stop, id}
Rem     glumpkin   08/25/94 -  new optimizer fields
Rem     jcohen     11/05/93 -  merge changes from branch 1.1.710.1 - 9/24
Rem     jcohen     09/24/93 -  #163783 add optimizer column
Rem     glumpkin   10/25/92 -  Renamed from XPLAINPL.SQL
Rem     jcohen     05/22/92 -  #79645 - set node width to 128 (M_XDBI in gendef)
Rem     rlim       04/29/91 -  change char to varchar2
Rem     Peeler     10/19/88 - Creation
Rem
Rem This is the format for the table that is used by the EXPLAIN PLAN
Rem statement.  The explain statement requires the presence of this
Rem table in order to store the descriptions of the row sources.

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

Oracle SQL Tune Useful Slide Share

http://www.oracle.com/technetwork/database/manageability/sqltune-presentation-ow07-130395.pdf

Oracle Application Performance bottleneck idenfiication Queries

The Performance of application can caused due to many reasons either in Application itself or Database side as well.

Especially the performance surely decrease when there is bottleneck left behind while writing the program without considering and keeping in mind that this program may face the Bulk data plugged in future to the application/ report.

That's why the following queries may help to see each area to make sure every things is in order before considering change to the program behavior to handle the bulk data.

--To see is there any invalid objects
select * from user_objects where status ='INVALID';

/
--To Check the DBA Tablespace
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
/

--To get the detail for long operations which is not completed and running.
select * from v$session_longops where totalwork <>sofar
/

--- To see is there any LOCK in place due to report.

SELECT s.sid,s.serial#,username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id
/

--To see is there any DBA_WAITERS, DBA_BLOCKERS

select * from dba_waiters
/
select * from dba_blockers
/
--========================================================--
--To see the Undo Retention.
select * from v$parameter where name like '%undo%'
/

--to get the process detail for each session running the queries
select a.process, a.program, a.module, a.machine, b.USED_UREC, c.sql_text
from v$sql c, v$session a, v$transaction b
where b.addr = a.taddr
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
order by b.USED_UREC
/

--to check what is the undo table space

SELECT distinct rpad(s.sid,3) "SID",S.USERNAME,
E.SEGMENT_NAME,
T.START_TIME "Start",
rpad(T.STATUS,9) "Status",
round((t.used_ublk*8)/1024) "Size(MB)"
--T.USED_UBLK||' Blocks and '||T.USED_UREC||' Records' "Rollback Usage"
FROM DBA_DATA_FILES DF,
DBA_EXTENTS E,
V$SESSION S,
V$TRANSACTION T
WHERE DF.TABLESPACE_NAME = E.TABLESPACE_NAME AND
DF.FILE_ID = UBAFIL AND
S.SADDR = T.SES_ADDR AND
T.UBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID+E.BLOCKS AND
E.SEGMENT_TYPE in( 'ROLLBACK','TYPE2 UNDO')
/