Release 1.4.0
A53697_01
 Library |
 Product |
 Contents |
 Index |
| Oracle
Enterprise Manager Oracle Trace Developer's Guide Release 1.4.0 A53697_01 |
|
Oracle Trace provides formatting and reporting options that let you present your collected data in the way most useful to you.
This chapter covers the following topics:
The Oracle Trace reporting and formatting activities described in this chapter are performed using a command-line interface on the server node where the data was collected. The only exception to this is the Delete Formatted Data function, which is performed using the Oracle Trace Manager.
To examine your data without performing queries against it, you can use the Oracle Trace reporting utility to create reports. Given the volume of collected data, the reports can be quite large. The Oracle Trace reporting utility includes a number of options to restrict the output.
You need to run the Oracle Trace utility on the system where the collection files reside. Enter the following command at the system prompt.
otrcrep [output_path] [-p [pid]] [-P] [-a] [-w<width>] [-l<length>] [-h] [-s] <collection>.cdf
If you do not specify any of the optional parameters, the Oracle Trace reporting utility creates a separate report for each event type found in the data file. These files are named <collection>_<eventname>.txt, where collection is the name of the collection and eventname is the name given the event in the product definition file. For example, a collection named test that contains data from the ATM demo application would produce the following files:
% otrcrep test.cdf % ls *.txt
|
test_BALANCES.txt |
test_PROCESS.txt |
test_TRANSFER.txt |
|
test_DEPOSIT.txt |
test_QUICKWTHDRW.txt |
test_VALIDOK.txt |
|
test_OVERDRAFT.txt |
test_SESSION.txt |
test_WITHDRAWAL.txt |
You can manipulate the output of the Oracle Trace reporting utility by using the optional parameters:
Specifies a full output path for the report files. If an output path is not specified, the report files are placed in the current directory.
Organizes event data by process. If you specify a process ID, you will have one file with all the events generated by that process in chronological order. If you omit the process ID, you will have one file for each process that participated in the collection. The output files are named <collection>_p<pid>.txt.
Produces a report called PROCESS.txt, which lists all the processes that participated in the collection. It does not include any event data. You could produce this report first to determine the specific processes for which you might want to produce more detailed reports.
Produces a report containing all the events for all products in the order they occur in the data collection file (.dat) file.
Sets the report width. Specifying -w132 creates a report with a width of 132 characters. The default width is 80 characters.
Sets the number of lines before a page break. The default is 63.
Suppresses all event and item report headers, resulting in a shorter report. Use of this parameter requires that you understand what is being reported and in what format, as there are no identifying headers.
This parameter is for use with SQL*Net data only. If used in combination with -p and -h, the reports produced are similar to those produced by the SQL*Net tracing mechanism.
If you want to perform queries against your Oracle Trace data or use it with a reporting tool such as Oracle Reports, you must first format the data to an Oracle Server database.
The format status of a collection is listed in the Partial Format column in the Output page of the Collection Summary window of the Oracle Trace Manager. The possible statuses are:
All the data in the file has been formatted.
A portion of the data was formatted while the collection was still in progress.
None of the data has been formatted.
Before you format data to an Oracle database, the formatter tables must be created. Preferably, this should be done at post-installation time by the person who installed Oracle Trace. If it was not done at post-installation time, it can be done any time before formatting data for the first time.
It is possible to maintain multiple sets of formatted data by creating formatter tables under more than one existing Oracle user account. You can also format multiple collections into the same set of tables.
To create formatter tables, use the vobsh command. See the Oracle Enterprise Manager Configuration Guide for more information.
You can format Oracle Trace data using either the Oracle Trace Manager or the command-line interface.
To format data using the Oracle Trace Manager, do the following:
Database
Enter a connect string or service name for the database. If you enter nothing, Oracle Trace will use the default database.
Username
Enter the Oracle username under which the Oracle Trace formatter tables were created.
Password
Enter the password for the username.
Commit Interval
Enter a number. The Commit Interval is the number of inserts made into the database before a commit operation is performed. A value is required in this field. The default is 2500.
Partial Format
Choose this option only if you want to process data that has not previously been formatted. This is useful for situations in which you want to format data for a collection while that collection is still in progress. Note, however, that you run the risk of duplicating data if you do a partial format followed by a full format.
Click OK. When the collection is formatted, the status is listed in the Format column in the Collection Summary window.
Enter the following command at the system prompt to format your data file. Replace collection.cdf with the name of your collection definition file.
otrcfmt [-f] [-c#] collection.cdf [user/password@database]
The optional formatting parameters are defined as follows:
Formats the entire data file, regardless of whether or not portions of the file have been formatted at some previous time. This is useful if you have data that was previously formatted to another database and you want to format it to a new database.
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.
Specifies the commit interval (the number of inserts into the database performed before a commit is made). If you omit the -c parameter, a system default is used.
Specifies the username, password, and database. Descriptions of these items are in the following list. If you do not enter this information on the command line, you are prompted for it.
Username
Enter the Oracle username under which the Oracle Trace formatter tables were created, or enter the owner of a schema containing formatter tables.
Password
Enter the password for the username.
Database
Enter a service name for the database. Do not enter a system identifier (SID). If you enter nothing, Oracle Trace will use the default database.
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 Delete Formatted Data function deletes rows of data for a specific collection from the formatter database tables. Although it deletes the contents of event tables, it leaves the event tables themselves intact. This is because re-creating them would be time-consuming for the formatter.
Oracle Trace provides several sample SQL scripts that demonstrate how you might make useful queries against your formatted data. They fall into the general categories of optimizing report performance, generic reports, and specialized reports, as described in the following sections. On the client, these scripts are located in $ORACLE_HOME\otracexx\admin\sample.
| Note:
For example purposes, the scripts described in the following sections use Oracle7 Server events and data. |
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.
To optimize report performance, Oracle Trace recommends that you:
Each of these topics is discussed in the following sections.
Because formatted data tables can have thousands of rows, defining indexes, as shown in this section, will improve reporting performance.
For information on how the event table names (shown in parentheses) are derived, see Appendix B, "Using Oracle Trace for Oracle Server Data Collections".
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
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.
The otrcsyn.sql script creates simpler names for the tables created by the otrcfmt command. Before executing this file, you should edit it to use synonyms appropriate to your environment.
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; 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;
For a description of the formula used to create event table names, see Appendix D, "Oracle Trace Format Database".
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.
The following otrcfunc.sql script is also a prerequisite to some of the other scripts.
CREATE OR REPLACE FUNCTION elapsed
(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;
/
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.
The following is an example of how to call the elapsed function:
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;
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.
The otrcdtl.sql script creates and populates a table containing summary data for each SQL statement. Statements are uniquely identified by the sql_text_hash field. The table contains data for the parses, executions, and fetches performed for each statement. The data spans user sessions, because multiple users can execute the same statement.
You must create the elapsed function by executing otrcfunc.sql before executing otrcdtl.sql.
You should create a separate detail table for each collection. To provide the table name and collection name, either let the script prompt you for the names or use the SQL*Plus DEFINE command as follows:
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.
The otrcdtl.sql script is lengthy; if you wish to see it in more detail, it is located in $ORACLE_HOME/otrace/demo on the UNIX server and in $ORACLE_HOME\otracexx\admin\sample on the client.
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.
To provide the collection name, either let the script prompt you for the name or use the SQL*Plus DEFINE command as follows:
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.
The following example shows how to create a report of data sorted by the overall number of times an event occurred.
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;
The examples in this section show how to create reports of data sorted by frequency of occurrence during a specified time interval.
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;
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;
The examples in this section show how to create reports of data sorted by statistical criteria.
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;
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;
The scripts described in this section perform a specific function. They cannot be tailored as the generic scripts can.
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;
To determine which statements have been swapped in and out of the library cache, query the SQL Segment event table using the statement identifier. (The event table is v_192216243_f_5_e_7_7_3.)
-- select sql_text from v_192216243_f_5_e_7_7_3 -- where sql_text_hash = <SQL ID>;
The otrcrpt7.sql script analyzes the number of fetches and rows per cursor execution.
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;
The otrcrpt8.sql script uses the tables created by otrcdtl.sql and counts the number of executions per command type. This can provide a rough estimate of database activity. Refer to the Oracle Server reference manuals for a description of command types.
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;
Output from the otrcrpt9.sql script lists all the events for a particular database session. The script prompts for session index (s_idx), session serial number (s_sn), and collection name (&&cllctn) if the variables have not been defined. The session index and the session serial number are used to identify the database session.
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);
The otrcrpta.sql script displays all user, admin, dev, error, and fatal events in the order of their occurrence. This script prompts for the collection name if cllctn is not already defined.
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;
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|