Trace Analysis Reports

Caution

This script requires the TRCANLZR (Trace Analyzer) utility to be installed and trace data to be loaded into TRCA$ tables.

Script: trace_reports.sql

column dum1 noprint;
column dum2 noprint;
set term     on;
set linesize 4000;
set verify   on;
set echo     off;
set heading  on;
set pagesize 60;


prompt
accept traceid prompt   '   Enter Trace id number : '
prompt
prompt
define spoolp1 = d:\temp\trace_obs_&traceid..txt
define spoolp2 = d:\temp\trace_rec_&traceid..txt
define spoolp3 = d:\temp\trace_Hdr_&traceid..txt

set term     off;
set feedback off;
set verify   off;
set echo     off;
set heading  off;
set trimspool on;
set truncate on;
set pagesize 0;

set term on
prompt spooling observations to &spoolp1
set term off
spool &spoolp1

SELECT 5 dum1, ranking dum2, ' '
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
UNION
SELECT 10 dum1, ranking dum2, '-- HashValue: '||HV||' Rank: '||ranking
FROM TRCANLZR.TRCA$_CURSOR
Where TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
UNION
SELECT 20 dum1, ranking dum2, '-- Idle Waits Secs: '||idle_wait_secs||' Non-Idle Waits Secs: '||non_idle_wait_secs
FROM TRCANLZR.TRCA$_CURSOR
Where TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
and (idle_wait_secs > 0 or non_idle_wait_secs >0 )
UNION
select 30 dum1,ranking dum2 , '-- Statement Cost: '||cost||' CPU Cost: '||cpu_cost||' I/O Cost: '||io_cost
  from trca$_cursor a, trca$_plan_table b
 WHERE TRACE_ID = &traceid
   and uid$ <> 0
   and ranking is not null
   and b.statement_id = trace_id||'-'||cursor_id
   and b.id = 0
UNION
SELECT 40 dum1, ranking dum2,
         '-- Calls: '||call_count||' CPU: '||ltrim(to_char(cpu_secs,'990.999'))||' Elapsed: '||ltrim(to_char(elapsed_secs,'990.999'))||' DISK: '||nvl(disk_blocks,0)||' Logical: '||(nvl(query_blocks,0)+nvl(current_blocks,0)) ||' Rows: '||nvl(rows_returned,0)
    FROM trca$_call_summary cs, trca$_cursor cu
   WHERE cs.CALL = 'Total'
     AND cs.trace_id = cu.trace_id
     AND cs.cursor_id = cu.cursor_id
     AND cu.cursor_id = cu.same_as_cursor_id
     AND cu.uid$ <> 0
     AND cu.dep = 0
     AND cu.trace_id = &traceid
     AND ranking IS NOT NULL
union
SELECT 50 dum1, ranking dum2, ' '
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
UNION
SELECT 60 dum1, ranking dum2, trim( trailing ' ' from (replace(substr(SQL_STATEMENT_SMALL,1,LENGTH(SQL_STATEMENT_SMALL)),chr(10),' '))) || ';'
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
union
SELECT 70 dum1, ranking dum2, ' '
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
and rownum < 21
order by dum2, dum1;

spool off;

set term on
prompt spooling Recommendatios to &spoolp2
set term off

spool &spoolp2

select * from (
SELECT 10 dum1, ranking dum2, '-- HashValue: '||HV||' Rank: '||ranking
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
and ranking is not null
UNION
select 20 dum1,ranking dum2 , '* '||operation||' '||options||' ON '||object_owner||'.'||object_name
  from trca$_cursor a, trca$_plan_table b
 WHERE TRACE_ID = &traceid
   and uid$ <> 0
   and ranking is not null
   and b.statement_id = trace_id||'-'||cursor_id
   and (options like '%FULL%' or options like '%CARTESIAN%')
UNION
SELECT 35 dum1, ranking dum2, ' '
FROM TRCANLZR.TRCA$_CURSOR
Where
TRACE_ID = &traceid
and uid$ <> 0
order by dum2, dum1
)
where rownum < 80;

spool off;

set term on
prompt spooling Trace Header to &spoolp3
set term off

spool &spoolp3

set linesize 80
set truncate off
set heading off

SELECT 'End_Date:               '||to_char(END_DATE,'dd-MON-yyyy hh24:mi:ss'),
       'Instance Name:          '||INSTANCE_NAME,
       'Schema Name:            '||USERNAME,
       'Test Number:            '||TRACE_ID,
       'SQL Count:              '||SQL_STATEMENTS_USER||'/'||UNIQUE_SQL_USER,
       'Total CPU (secs):       '||C_TOTAL,
       'Total Elapsed (Secs):   '||E_TOTAL,
       'Total Physical IO:      '||DISK_TOTAL,
       'Total Logical IO:       '||(QUERY_TOTAL+CURRENT_TOTAL),
       'Row Count:              '||ROWS_TOTAL,
       'Trace File:             '||TRACE_FILENAME
  FROM TRCANLZR.DUT_TRCA$_TRACE
 WHERE TRACE_ID = &traceid;

spool off

set term     on;
set feedback 6;
set verify   on;
set echo     off;
set heading  on;
set pagesize 60;

Purpose

This script generates three comprehensive reports from trace data that has been processed by Oracle’s Trace Analyzer (TRCANLZR) utility. It provides detailed analysis of SQL statements, performance metrics, and optimization recommendations.

Key Features

• Three Report Types: Observations, recommendations, and header summary
• Performance Ranking: SQL statements ranked by resource consumption
• Cost Analysis: Shows CPU, I/O, and total costs
• Optimization Hints: Identifies FULL scans and CARTESIAN joins

Prerequisites

1. Trace Analyzer Installation: TRCANLZR must be installed
2. Processed Trace Data: Trace files must be loaded into TRCA$ tables
3. Access Privileges: SELECT access on TRCANLZR schema objects
4. Windows Path: Default output path is d:\temp\ (modify as needed)

Generated Reports

1. Observations Report (trace_obs_*.txt)

• SQL statement rankings
• Wait time analysis (idle vs non-idle)
• Statement costs (CPU, I/O)
• Performance metrics per SQL
• Actual SQL text

2. Recommendations Report (trace_rec_*.txt)

• Problematic operations (FULL scans, CARTESIAN joins)
• Specific objects involved
• Optimization opportunities

3. Header Report (trace_Hdr_*.txt)

• Trace summary information
• Total resource consumption
• SQL statement counts
• Original trace file reference

Usage

SQL> @trace_reports.sql
   Enter Trace id number : 12345

spooling observations to d:\temp\trace_obs_12345.txt
spooling Recommendations to d:\temp\trace_rec_12345.txt
spooling Trace Header to d:\temp\trace_Hdr_12345.txt

Report Contents

Observations Include

• Hash values and rankings
• Wait times (idle and non-idle)
• Statement costs
• Execution statistics (CPU, elapsed, I/O, rows)
• SQL text

Recommendations Focus On

• FULL table scans
• CARTESIAN joins
• Objects requiring optimization

Header Summary Shows

• End date and time
• Instance and schema information
• SQL counts (total and unique)
• Resource totals (CPU, elapsed, I/O)
• Original trace filename

Customization Options

1. Output Directory: Change from d:\temp\ to your preferred location
2. Result Limits: Modify rownum restrictions for more/fewer results
3. Filter Criteria: Add additional WHERE clauses for specific analysis

Best Practices

1. Trace Collection: Use proper trace levels (binds and waits)
2. Regular Analysis: Process traces promptly after collection
3. Baseline Comparison: Compare with known good performance
4. Action Tracking: Document optimizations implemented

Related Tools

• TKPROF: Oracle’s basic trace file formatter
• OraSRP: Session Resource Profiler
• Method-R Profiler: Commercial trace analysis tool