TkProf

TKPROF (Transient Kernel Profiler) is an utility to translate the trace files in to a readable format.

Syntax

$ TKPROF variable_list

Where variable_list include

Tracefile - The name of the trace file in the udump.
Output_file - The name of the output file in the udump.
Explain=user/pass - Run Explain plan on user’s SQL in trace file.
Sys= Yes or No - Display Recursive SQL statements in output file.
Waits= Yes or No - Record summary of for any waits.
Aggregate = Yes or No - If no, then SQL text is shown once if executed more than once.

Sort=parameters - A number of sorting options are available. Most common are:
FCHCPU (Cpu time fetch), FCHDSK (Disk reads for fetch),
FCHCU and FCHQRY (Memory Reads for fetch), FCHROW(no. of rows fetched),
EXEDSK( Disk reads during execute), EXECU, EXEQRY, EXEROW, EXECPU,
PRSCPU(Parse CPU) and PRSCNT (times parsed).

Table= schema.table - Table in which tkprof temporarily put execution plans before writing them to output file.
Insert=filename - Creates a script to create a table and store the trace file statistics for each SQL stmt traced.
Record=filename - Produces a file of all the user’s SQL stmt.
print= number - The no. of statements in the output file. Default is all statement.

Example:

$ cd $ORACLE_BASE/admin/gcuat/udump
$ tkprof gcuat_ora_mytrace_704678.trc output.txt explain=system/manager aggregate=no sys=no waits=yes sort=(FCHCPU,EXECPU,PRSCPU)

Thanks

Event 10046 Tracing (Other's session)

Method 1: Using oradebug utility

Get the SPID of the session you want to trace.
SQL> Select p.spid, p.pid From v$session s, v$processs p
Where s.paddr = p.addr
And s.sid = 149 And s.serial#= 3643

$ sqlplus / as sysdba
SQL> oradebug setospid 12345;
-- 12345 is the p.spid value from the above query.

SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 8;

-- To turn off tracing
SQL> oradebug event 10046 trace name context off;

Method 2: Using DBMS_MONITOR package in 10g or DBMS_SUPPORT package

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE)
SQL> Exec DBMS_MONITOR session_trace_disable(149,3643)

or

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);
SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Event 10046 Tracing

To enable and disable event 10046 tracing for the current session,

SQL> Alter session set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter session set events ‘10046 TRACE NAME CONTEXT OFF’;

To enable and disable event 10046 tracing for the entire instance

SQL> Alter system set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter system set events ‘10046 TRACE NAME CONTEXT OFF’;

Thanks

Event 10046 Levels

Event 10046 enables us to collect extended SQL trace data. There are four levels in event 10046 tracing.

Level 1: Standard tracing with parse, fetch, execute, Explain plan.
Level 2: Level 1 + Bind Variables
Level 3: Level 1 + Waits
Level 4: Level 1 + Bind Variables + Waits

Thanks

Tracing other's session

To trace other user's session prior to Oracle 10g use DBMS_SUPPORT package.

If the package is not available, run the following script.

SQL>@$ORACLE_HOME/rdbms/admin/dbmssupp.sql

Syntax

DBMS_SUPPORT.START_TRACE_IN_SESSION(sid, serial#, wait, bind)
DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid, serial#)

To start tracing

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);

To stop tracing

SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Tracing other's session in 10g

To trace the other user's session in Oracle 10g,

Step 1: Set the init parameter.

SQL> Alter system set TIMED_STATISTICS = TRUE;
SQL> Alter system set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter system set TRACEFILE_IDENTIFIER = ‘mytrace’;

Step 2: Enable Tracing

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE);
Where
1st Parameter: Session ID. (null means current session’s ID)
2nd Parameter: Serial # (null means current session’s serial#)
3rd Parameter: Trace Waits (Default True)
4th Parameter: Bind Variables (Default False)

Step 3: Disable Tracing

SQL> Exec DBMS_MONITOR.SESSION_TRACE_DISABLE(149,3643);

Thanks

Tracing own session

To trace your own session follow the steps below,

Step 1: Set the init parameters

SQL> Alter session set TIMED_STATISTICS = TRUE;
SQL> Alter session set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter session set TRACEFILE_IDENTIFIER = ‘mytrace’;

Timed_statistics – Allows tracing to occur on the system.
Max_dump_size – Maximum size of the trace file in OS level blocks.
Tracefile_identifier - To identify the trace file generated for a particular session in the udump.

Step 2: Enable the tracing

SQL> Alter session set SQL_TRACE='TRUE';

or in 10g

SQL> Execute DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL,NULL, TRUE, TRUE);

Note: In Oracle 10g SQL_TRACE parameter has been deprecated.

Step 3: Disable the Tracing

SQL> Alter session set SQL_TRACE= False;
Or
SQL> Execute DBMS_MONITOR.SESSION_TRACE_DISABLE;
Or
SQL> Exit;

Thanks

Autotrace

Autotrace facility enables you to produce the EXPLAIN PLAN and Execution statistics for SQL Statement in SQL*Plus.

Requirements to use Autotrace

1. You must have a plan table to generate Explain plan report. If you don't have it then execute the following script to create it.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

2. You must have PLUSTRACE role. If you don't have it, then run the following script as a SYS user and grant the role.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> Grant plustrace to scott;

Turning on Autotrace

SQL> Set Autotrace ON explain -- Generates only Explain plan report
SQL> Set Autotrace ON Statistics -- Generates only Statistics report
SQL> Set Autotrace ON -- Generates both Explain plan and Statistics.

SQL> Set Autotrace OFF -- Turns off tracing

Sample Output

SQL> Select * from test;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=22)

Statistics

314 recursive calls
0 db block gets
55 consistent gets
5 physical reads
0 redo size
143 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed

Thanks

Hit Ratio in Oracle 10g

From Oracle 10g, there is no need to run huge scripts to get the hit ratios. You can query the v$sysmetric view to get the hit ratios.

SQL> select * from gv$sysmetric
where metric_name like '%Ratio%'
and intsize_csec in (Select max(intsize_csec) from gv$sysmetric where inst_id =&id)
and inst_id = &&id;

Thanks

Rollback Segment Wait to Get Ratio

The ratio of sum of WAITS to the sum of GETS in v$rollstat should be less than 5%.

The V$WAITSTAT view is used to find the contention in rollback segment header block.
A value over zero in the column UNDO_HEADER means there is contention.

Run hitratio script to get the ratio.

Thanks

Database Wait Time to CPU Time Ratio

This ratio is available from Oracle 10g.

CPU Time Ratio - Percentage of total time used in performing user requests.
Wait Time Ratio- Percentage of total time used in waiting for resources.

When Wait time ratio is higher than the CPU time ratio, perform the Wait event analysis.

Run hitratio script to get the ratio.

Thanks

Chained Row Ratio

Chained rows occurs as a result of row migration or row chaining.

Row chaining When the size of a row is more than the size of the data block, then the row is spread across multiple blocks. This is called Row Chaining.

Row migration When an update increases the row size and there is no free space available in the data block to accommodate the row, the entire row will be moved to a new block leaving only a pointer in the original block. A row migration can also lead to a row chain if the row becomes large enough.

Row chaining affects index reads and full table scans. You cannot do much to avoid row chaining, one way is to calculate the length of the row and put it in a tablespace that has large block size.

Row migration does not affect full table scans but affects index reads. Increase the PCTFREE to allow more space for future updates. Re-organize or recreate the tables and indexes to avoid row migration.

Run hitratio script to get the ratio.

Thanks

Latch Hit Ratio

It gives the ratio of the total number of latch misses to the number of latch gets for all latches. If the latch hit ratio is below 99 percent, it indicates a latching problem. A latch free wait event in the Top 5 wait events section of the statspack or AWR report confirms that. You must refer to the Latch section of the report for further analysis.

Run the hitratio script to get the ratio.

Thanks

Execute to Parse Ratio

This is an important ratio. Formula for finding this ratio:

Execute to Parse ratio = 100 - (Parse count/ Executions * 100)

If you parse once and execute more, then the ratio will be near to 100%.
If you parse every time you execute, then the ratio will be near to 1%.

When the value is too low, it means the SQL is not sharable.

You may need to set CURSOR_SHARING to FORCE. Set SESSION_CACHED_CURSORS init parameter. Increase OPEN_CURSORS init parameter.

Find out which SQL statement has parse count equal to execute count. These statements are contributing to inefficient cursor sharing.

Run hitratio script to get the ratio

Thanks

Parse CPU to Parse Elapsed

Parse CPU means amount of CPU time used for parsing.
Parse Elapsed means amount of clock time used for parsing.

Parse Elapsed = Parse CPU + Wait time.

Parse CPU to Parse Elapsed ratio = 100 * parse CPU/ parse Elapsed.

Ideally Parse Elapsed must be equal to Parse CPU, i.e., only CPU time is used for parsing. In that case the ratio is 100%. If wait time is more then the ratio will be less.

Low Value for this ratio is an indicator of latching problem. Investigate the latch sections in AWR and Statspack report for contention on library cache and shared pool latches.

Run hitratio script to get the ratio.

Thanks

Soft Parse Ratio

This ratio indicates the percentage of parses that were soft.

Soft-Parse Ratio = 100 * Soft Parse/(Hard + Soft Parse)

If the ratio is low, that is less than 80%, then set CURSOR_SHARING init parameter to FORCE.

Run hitratio script to get the ratio.

Thanks

Pin Hit Ratio

The Pin Hit Ratio indicates how frequently the executed statements found the associated parsed SQL already in the Library Cache.

The value should be greater than 95%. Else increase shared pool size.

Run hitratio script to get the ratio.

Get Hit Ratio

Get Hit Ratio = GETS/GETHITS

GETS = Statements that require parsing
GETHITS = Parsed statements already in the memory.

The value should be greater than 95. In 10g the get hit ratio will be too low, this is a bug.

Run hitratio script to get the ratio

Thanks

Dictionary Cache Hit Ratio

It indicates the percentage of time the data dictionary objects are read from memory rather than on disk.

The value should be greater than 95%. If less then increase the shared pool (SHARED_POOL_SIZE init parameter).

Run hitratio script to get dictionary cache hit ratio.

A cache miss on Dictionary and Library cache is more expensive than a miss on buffer cache.

Thanks

Library Cache Reload Ratio

When the Shared pool is not large enough, it cannot hold the parsed statement for a long time. When new statements are parsed the header of the old parsed statement is kept in the shared pool and the body is pushed out of the memory. Reloads occur when the body of previously parsed statements had to be loaded again.

If the value of this ratio is greater than 1% then increase the shared pool by increasing the SHARED_POOL_SIZE init parameter.

Run hitratio script for library cache reload ratio.

Thanks

Library Cache Hit Ratio

This gives the percentage of Pin requests that result in pin hits.

PINS: No. of times an item in the library cache was executed.
PINHITS: No. of times an item was executed without reloads.

The value should be greater than 95%. If less then,

• Increase the SHARED_POOL_SIZE init parameter.
• CURSOR_SHARING may need to be set to FORCE.
• SHARED_POOL_RESERVED_SIZE may be too small.
• Inefficient sharing of SQL, PLSQL or JAVA code.
• Insufficient use of bind variables.

Run hitratio scrip to get the Library hit ratio.

Thanks

In (PGA) Memory Sort Ratio

Sorts occur either in memory or in disk.

Memory - PGA (Faster no I/O involved)
Disk - Temporary Tablespace (Slower I/O involved)

This ratio indicates the amount of sorts performed in the Memory rather than on Disk.

OLTP database should have value greater than 95%.

If the value is less than 95%, then

• When not using Automatic PGA memory, then increase SORT_AREA_SIZE init parameter.
• When using Automatic PGA memory, then increase PGA_AGGREGATE_TARGET init parameter.

Run hitratio script to get the memory-sort ratio.

Thanks

Buffer and Redo Nowait Ratio

Buffer Nowait Ratio

If this ratio is greater than 99%, then it indicates that the server process is able to obtain the buffer immediately.

If this ratio is less than 99%, then it indicates that the server process has to wait for something before obtaining the buffer. You must investigate the Buffer Waits Section of Statspack/ AWR report.


Redo Nowait Ratio

It indicates the percentage of time the redo space is immediately available without having to wait. A low value indicates small redo log file and frequent redo switch. The value should be greater than 99%. If not then increase the redo log file size or use faster disks for redo log files.

Run the hitratio script to get the ratios.

Thanks

Buffer Cache Hit Ratio

When the hit ratio is

Too Low (<50): Check for indexing. Consider increasing the buffer cache.

Too High (>99): Check for bad SQL or index that is causing a surge in the logical reads (High Buffer_gets SQL).

A high hit ratio is not an indication of good performance, but a low ratio is a sign of performance that can be improved.

Run the script hitratio script to get the buffer cache hit ratio.

Thanks

Hit Ratio Script

Execute the following script for the various hit ratio

column Ratio format a30
column Value format a5
TTITLE '******Hit Ratio Report*****'
BTITLE '*****End of Report*****'

SELECT cur.inst_id, 'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Value"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=1
and cur.inst_id=1
and con.inst_id=1
union all
SELECT cur.inst_id,'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Buffer Cache Hit Ratio"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=2
and cur.inst_id=2
and con.inst_id=2
union
SELECT inst_id, 'Library Cache Hit Ratio ' "Ratio", to_char(Round(sum(pins) / (sum(pins)+sum(reloads)) * 100,2)) "Library Cache Hit Ratio"
FROM gv$librarycache group by inst_id
union
SELECT inst_id,'Dictionary Cache Hit Ratio ' "Ratio", to_char(ROUND ((1 - (SUM (getmisses) / SUM (gets))) * 100, 2)) "Percentage"
FROM gv$rowcache group by inst_id
union
Select inst_id, 'Get Hit Ratio ' "Ratio",to_char(round((sum(GETHITRATIO))*100,2)) "Get Hit"--, round((sum(PINHITRATIO))*100,2)"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
Select inst_id, 'Pin Hit Ratio ' "Ratio", to_char(round((sum(PINHITRATIO))*100,2))"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
select a.inst_id,'Soft-Parse Ratio ' "Ratio", to_char(round(100 * ((a.value - b.value) / a.value ),2)) "Soft-Parse Ratio"
from (select inst_id,value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'parse count (hard)') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Execute Parse Ratio ' "Ratio", to_char(round(100 - ((a.value / b.value)* 100),2)) "Execute Parse Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'execute count') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Parse CPU to Elapsed Ratio ' "Ratio", to_char(round((a.value / b.value)* 100,2)) "Parse CPU to Elapsed Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse time cpu') a,
(select inst_id, value from gv$sysstat where name like 'parse time elapsed') b
where a.inst_id = b.inst_id
union
Select a.inst_id,'Chained Row Ratio ' "Ratio", to_char(round((a.val/b.val)*100,2)) "Chained Row Ratio"
from (SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name = 'table fetch continued row' group by inst_id) a,
(SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid') group by inst_id) b
where a.inst_id = b.inst_id
union
Select inst_id,'Latch Hit Ratio ' "Ratio", to_char(round(((sum(gets) - sum(misses))/sum(gets))*100,2)) "Latch Hit Ratio"
from gv$latch
group by inst_id
/* Available from 10g
union
select inst_id, metric_name, to_char(value)
from gv$sysmetric
where metric_name in ( 'Database Wait Time Ratio', 'Database CPU Time Ratio')
and intsize_csec = (select max(intsize_csec) from gv$sysmetric)
order by inst_id
*/
/

Thanks

AWR Basic Info

By default the Automatic Workload Repository (AWR) is populated every hour.

Tablespace: SYSAUX (Data is stored in this tablespace)
Background Process : MMON (Flushes data from Memory to Disk)

To find the space used by AWR query the V$SYSAUX_OCCUPANTS view.

SQL> Select * From V$SYSAUX_OCCUPANTS where occupant_name like '%AWR%';

The space_usage_kbytes column gives the amount of space used by AWR in kilobytes.

To find the oldest AWR information

SQL> Select dbms_stats.get_stats_history_availability From dual;

To find the retention period for AWR. Default is 7 days.

SQL> Select dbms_stats.get_stats_history_retention From dual;

To change the retention period for AWR

SQL> Exec dbms_stats.alter_stats_history_retention(10);

Thanks

Basic database Info

To get the basic database information such as the database name, dbid, creation time, archiving information etc query V$DATABASE view.

SQL> Select * From V$DATABASE;

To view the oracle version query V$VERSION view.

SQL> Select * From V$VERSION;

DBA Views

To get the underlying objects that make up the DBA_ views query the DBA_VIEWS view.

SQL> Select view_name, text From DBA_VIEWS where view_name like 'DBA%';

Thanks

V$ Views

To get the list of all the V$ or GV$ Views, query the V$FIXED_TABLE view.

SQL> Select name From V$FIXED_TABLE where name like 'V%';

The difference between V$ and GV$ is GV$ includes inst_id column which is used in RAC instances.

V$ views are created from the X$ tables. To know the details of the underlying X$ tables from which the v$ views are created, query the V$_FIXED_VIEW_DEFINITION view.

SQL> Select * From V$FIXED_VIEW_DEFINITION where view_name like 'GV$SESSION';

Thanks