| Oracle Enterprise Manager Oracle Trace User's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Trace User's Guide | Library |
Product |
Contents |
Index |
Oracle Trace provides formatting and reporting options that let you present your collected data in the way most useful to you.
Enter the following command at the system prompt to produce a Detail report. Replace collection.cdf with the name of your collection definition file on the node where the collection ran.
otrcrep [output_path] [-pwl] [-P] [-h] [-s] [-a] collection.cdfIf you do not specify any of the optional parameters, the otrcrep command creates a separate report for every event type found in the data file. Each resulting .txt filename takes the form of the collection name followed by the event name.
The reporter formats the data into a text file. The default report type is the Detail report. The optional Detail report parameters are as follows:
To create a separate report for every process found in the data file, use the -p option without a number. Each resulting .txt filename takes the form of a collection name and P followed by a process ID (<collection name>P<pid>.txt). Each file shows every event logged by the corresponding process, in chronological order.
The format status of a collection is listed in the Partial Format column in the Output page of the Collection Summary window. The possible statuses are:
Take the following steps to create formatter tables:
You can format Oracle Trace data using either the Oracle Trace Manager or the command line interface.
otrcfmt [-f] [-c#] collection.cdf [user/password@database]The optional formatting parameters are defined as follows:
If you omit the -f parameter from the command line, only data that has not previously been formatted is processed. This is useful for situations in which you want to format data for a collection while that collection is still in progress. You can later format only new data for the collection by omitting the -f parameter from the command line.
Deleting Formatted Data
Because formatted data takes up a lot of space in the database, you may want to regularly delete formatted data. Deletion of formatted data is performed using the Oracle Trace Manager as follows:
The Oracle Enterprise Manager program group contains an icon for the Oracle SQL Worksheet. Invoking the worksheet is one method you can use to open, edit, and execute the Oracle Trace sample scripts.
For information on how the event table names (shown in parentheses) are derived, see Tables for Collected Event Data on page C-7.
In the following recommendation, if you have only one formatted collection per schema, you can omit the collection_number.
Connects (V_192216243_F_5_E_1_7_3):
collection_number, session_index, session_serial
Disconnects (V_192216243_F_5_E_2_7_3):
collection_number, session_index, session_serial
RowSource (V_192216243_F_5_E_6_7_3):
collection_number, session_index, session_serial, cursor_number
SQLSegment (V_192216243_F_5_E_7_7_3):
collection_number, sql_text_hash (view by SQL statement)
collection_number, session_index, session_serial, cursor_number
(view by user session)
Parse (V_192216243_F_5_E_8_7_3):
collection_number, sql_text_hash_end (view by SQL statement)
collection_number, session_index_end, session_serial_end, cursor_number_end
(view by user session)
Execution (V_192216243_F_5_E_9_7_3):
collection_number, sql_text_hash_end (view by SQL statement)
collection_number, session_index_end, session_serial_end, cursor_number_end
(view by user session)
Fetch (V_192216243_F_5_E_10_7_3):
collection_number, sql_text_hash_end (view by SQL statement)
collection_number, session_index_end, session_serial_end, cursor_number_end
(view by user session)
Logical Txn (V_192216243_F_5_E_11_7_3):
collection_number, session_index_end, session_serial_end
Physical Txn (V_192216243_F_5_E_12_7_3):
collection_number, session_index_end, session_serial_end, tx_id_end
Wait (V_192216243_F_5_E_13_7_3):
collection_number, session_index, session_serial
Defining Synonyms
To ensure uniqueness among table names, the Oracle Trace formatter uses a specific formula that incorporates as much information as necessary to derive unique names. As a result, table names can be quite long. rem synonyms for the tables created by otrcfmt for Server data
create synonym connects for v_192216243_f_5_e_1_7_3;
create synonym disconnects for v_192216243_f_5_e_2_7_3;
create synonym errorstacks for v_192216243_f_5_e_3_7_3;
create synonym applregs for v_192216243_f_5_e_5_7_3;
create synonym rowsources for v_192216243_f_5_e_6_7_3;
create synonym segments for v_192216243_f_5_e_7_7_3;
create synonym parses for v_192216243_f_5_e_8_7_3;
create synonym executions for v_192216243_f_5_e_9_7_3;
create synonym fetches for v_192216243_f_5_e_10_7_3;
create synonym logtxns for v_192216243_f_5_e_11_7_3;
create synonym phystxns for v_192216243_f_5_e_12_7_3;
create synonym waits for v_192216243_f_5_e_13_7_3;rem synonyms for the tables created by otrcfmt for SQL*Net data
create synonym user_t for v_192216243_f_115_e_1_v2_3;
For a description of the formula used to create event table names, see Tables for Collected Event Data on page C-7.
create synonym admin_t for v_192216243_f_115_e_1_v2_3;
create synonym dev_t for v_192216243_f_115_e_1_v2_3;
create synonym hex_t for v_192216243_f_115_e_1_v2_3;
create synonym time_t for v_192216243_f_115_e_1_v2_3;
create synonym pkt_t for v_192216243_f_115_e_1_v2_3;
create synonym cf3_t for v_192216243_f_115_e_1_v2_3;
create synonym datapkt_t for v_192216243_f_115_e_1_v2_3;
create synonym conpkt_t for v_192216243_f_115_e_1_v2_3;
create synonym rdpkt_t for v_192216243_f_115_e_1_v2_3;
create synonym rfpkt_t for v_192216243_f_115_e_1_v2_3;
create synonym fatal_t for v_192216243_f_115_e_1_v2_3;
create synonym error_t for v_192216243_f_115_e_1_v2_3;
Defining the Elapsed Function
The otrcfunc.sql script defines an elapsed function that makes it easier to calculate elapsed time for duration events. Without this elapsed function, it would be more difficult to calculate elapsed time because timestamps are split across two fields. The timestamp field contains everything down to seconds. The timestamp_nano field contains fractions of a second. The number of nanos per second is stored in the MS_GRANULARITY field of the COLLECTION table and varies by platform. To solve this, the elapsed function created by otrcfunc.sql calculates elapsed time in units of seconds. CREATE OR REPLACE FUNCTION elapsed
The collection name is necessary to obtain the number of units in the nanos fields. When using this script, substitute the name of your collection as appropriate.
(coll_name VARCHAR2,
start_time DATE,
start_nanos NUMBER,
end_time DATE,
end_nanos NUMBER)
RETURN NUMBER
AS
ms_units NUMBER; -- # nanos per second
nanos NUMBER;
new_end_time DATE;
time NUMBER;
seconds NUMBER;
BEGIN
SELECT ms_granularity INTO ms_units FROM collection
WHERE collection_name = coll_name;
new_end_time := end_time;
nanos := end_nanos - start_nanos;
IF nanos < 0 THEN
new_end_time := end_time - (1/(60*60*24)); -- subtract 1 second
nanos := (ms_units + end_nanos) - start_nanos;
END IF;
time := new_end_time - start_time; -- in units of days
seconds := time * (60*60*24); -- days * seconds/day
seconds := seconds + (nanos/ms_units);
RETURN(seconds);
END;
/select avg(elapsed('oracle7',
x.timestamp_start, x.timestamp_nano_start,
x.timestamp_end, x.timestamp_nano_end))
from v_192216243_f_5_e_9_7_3 x, collection c
where c.collection_name = 'oracle7' and
c.collection_id = x.collection_number; Generating Summary Data for Each Statement
Assembling summary data about SQL statement executions can be resource-intensive. To optimize report performance, Oracle Trace recommends that you create a table of summary data once rather than reconstructing it with every query.SQL>DEFINE DTL_TABLE_NAME <table name>
SQL>DEFINE CLLCTN <collection name>
DTL_TABLE_NAME is the name of the table to create. CLLCTN is the name of the collection from which to summarize the data. Once you provide values for DTL_TABLE_NAME and CLLCTN, they will be used throughout your session unless you override them with other DEFINE commands. Generic Report Templates
The scripts discussed in this section perform generic queries; that is, you can substitute your own values in them to produce reports meaningful to you. The sample scripts shown use the table names created by the otrcfmt command, rather than synonyms.SQL>DEFINE CLLCTN <collection name>
Once you provide a value for CLLCTN, it will be used throughout your session unless you override it with another DEFINE command. Generating an Occurrence Report
The following example shows how to create a report of data sorted by the overall number of times an event occurred. Reporting the Number of Connection Events
The otrcrpt1.sql script reports the number of connection events per system username. Substitute your own collection name.select session_index, session_serial, count(*) "# SQL statements"
from v_192216243_f_5_e_7_7_3 e, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = e.collection_number
group by session_index, session_serial; Generating Frequency Reports
The examples in this section show how to create reports of data sorted by frequency of occurrence during a specified time interval. Reporting the Frequency of Wait Events
The otrcrpt2.sql script reports the frequency of wait events per minute. You can change the interval to hours (HH) or seconds (SS) rather than minutes (MI) by changing the date format in the trunc functions.select to_char(trunc(w.timestamp, 'MI'), 'DD-MON-YY HH24:MI') "Interval",
w.description "Cause of Wait",
count(*) "Frequency"
from v_192216243_f_5_e_13_7_3 w, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = w.collection_number
group by trunc(w.timestamp, 'MI'), w.description; Reporting the Frequency of Logical Transactions
The otrcrpt3.sql script reports the frequency of logical transactions ending per second. Substitute your own collection name.select to_char(t.timestamp_end, 'DD-MON-YY HH24:MI:SS') "Interval",
count(*) "Frequency"
from v_192216243_f_5_e_11_7_3 t, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = t.collection_number
group by timestamp_end; Generating Statistical Reports
The examples in this section show how to create reports of data sorted by statistical criteria. Analyzing Resource Wait Times
The otrcrpt4.sql script analyzes resource wait times. Substitute your own collection name.select w.description "Cause of Wait",
min(w.wait_time) "Min Wait Time",
max(w.wait_time) "Max Wait Time",
avg(w.wait_time) "Avg Wait Time",
count(*) "Number of Waits"
from v_192216243_f_5_e_13_7_3 w, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = w.collection_number
group by description; Reporting Summary Performance Data
The otrcrpt5.sql script provides a statistical summary of performance data for the execute event. Other data of possible interest for the execute event could include consistent_gets, physical_reads, redo_entries, redo_size, sort_memory, sort_disk, and sort_rows.select min (db_block_change_end - db_block_change_start) "Min Block Changes",
max (db_block_change_end - db_block_change_start) "Max Block Changes",
avg (db_block_change_end - db_block_change_start) "Avg Block Changes",
min(t_scan_rows_got_end - t_scan_rows_got_start) "Min Table Scan Rows",
max(t_scan_rows_got_end - t_scan_rows_got_start) "Max Table Scan Rows",
avg(t_scan_rows_got_end - t_scan_rows_got_start) "Avg Table Scan Rows",
count(*) "Number of Executes" from v_192216243_f_5_e_9_7_3, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = collection_number; Specialized Reports
The scripts described in this section perform a specific function. They cannot be tailored as the generic scripts can. Analyzing SQL Library Cache Performance
The otrcrpt6.sql script analyzes SQL library cache performance. This can be done in two ways:
select sql_text_hash_end "SQL ID", count(*) "# Misses"
from v_192216243_f_5_e_9_7_3
where missed_end = 1
group by sql_text_hash_end
having count(*) > 1;
select distinct (et.sql_text_hash_end) "SQL ID",
count(distinct et.lib_cache_addr_end) "Cache Addrs"
from v_192216243_f_5_e_9_7_3 et, v_192216243_f_5_e_9_7_3 ev,
epc_collection c
where et.sql_text_hash_end = ev.sql_text_hash_end and
c.collection_name = '&&cllctn' and
c.collection_id = et.collection_number and
c.collection_id = ev.collection_number
group by et.sql_text_hash_end
having count(distinct ev.lib_cache_addr_end) > 1;
select count(distinct sql_text_hash_end) "# of SQL Stmts"
from v_192216243_f_5_e_9_7_3;
-- select sql_text from v_192216243_f_5_e_7_7_3
-- where sql_text_hash = <SQL ID>;
CREATE TABLE fetches_temp
(session_index NUMBER,
session_serial NUMBER,
cursor_number NUMBER,
sql_text_hash NUMBER,
frequency NUMBER,
rows_fetched NUMBER);
INSERT INTO fetches_temp (session_index, session_serial,
cursor_number, sql_text_hash)
SELECT DISTINCT f.session_index_end, f.session_serial_end,
f.cursor_number_end, f.sql_text_hash_end
FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
WHERE c.collection_name = '&&cllctn' and
c.collection_id = f.collection_number;
UPDATE fetches_temp t
SET frequency = (SELECT count(*)
FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
WHERE t.session_index = f.session_index_end
and t.session_serial = f.session_serial_end
and t.cursor_number = f.cursor_number_end
and c.collection_name = '&&cllctn'
and c.collection_id = f.collection_number),
rows_fetched = (SELECT sum(f.row_count_end)
FROM v_192216243_f_5_e_10_7_3 f, epc_collection c
WHERE t.session_index = f.session_index_end
and t.session_serial = f.session_serial_end
and t.cursor_number = f.cursor_number_end
and c.collection_name = '&&cllctn'
and c.collection_id = f.collection_number);
SELECT min(frequency) "Min Fetches",
max(frequency) "Max Fetches",
avg(frequency) "Avg Fetches",
min(rows_fetched) "Min Rows",
max(rows_fetched) "Max Rows",
avg(rows_fetched) "Avg Rows",
count(*) "Cursors"
FROM fetches_temp;
SELECT s.sql_text "Stmts fetched single row"
FROM v_192216243_f_5_e_7_7_3 s, fetches_temp t, epc_collection c
WHERE t.rows_fetched = 1
and t.sql_text_hash = s.sql_text_hash
and c.collection_name = '&&cllctn'
and c.collection_id = s.collection_number;
drop table fetches_temp;
select d.cmd_type, count(x.session_serial_end) "# Execs"
from &&dtl_table_name d, v_192216243_f_5_e_9_7_3 x
where d.sql_text_hash = x.sql_text_hash_end and
d.sql_text_hash <> 0
group by d.cmd_type;
select function_id, probe_id
from v_192216243_f_115_e_1_v2_3 u, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = u.collection_number and
u.cross_fac_3 = (select distinct t.cross_fac_3_start
from v_192216243_f_5_e_11_7_3 t
where t.session_index_start = &&s_idx and
t.session_serial_start = &&s_sn and
t.collection_number = u.collection_number);
select 'User' EVENT, epid, to_char(timestamp, 'DD-MON-YY HH24:MI') timestamp,
timestamp_nano, function_id, probe_id, message
from v_192216243_f_115_e_1_v2_3 u, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = u.collection_number
UNION
select 'Admin' EVENT, epid, to_char(timestamp, 'DD-MON-YY HH24:MI') timestamp,
timestamp_nano, function_id, probe_id, message
from v_192216243_f_115_e_2_v2_3 a, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = a.collection_number
UNION
select 'Dev' EVENT, epid, to_char(timestamp, 'DD-MON-YY HH24:MI') timestamp,
timestamp_nano, function_id, probe_id, message
from v_192216243_f_115_e_3_v2_3 d, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = d.collection_number
UNION
select 'Error' EVENT, epid, to_char(timestamp, 'DD-MON-YY HH24:MI') timestamp,
timestamp_nano, function_id, probe_id, message
from v_192216243_f_115_e_12_v2_3 r, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = r.collection_number
UNION
select 'Fatal' EVENT, epid, to_char(timestamp, 'DD-MON-YY HH24:MI') timestamp,
timestamp_nano, function_id, probe_id, message
from v_192216243_f_115_e_13_v2_3 f, epc_collection c
where c.collection_name = '&&cllctn' and
c.collection_id = f.collection_number
order by timestamp, timestamp_nano;
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |