Release 1.4.0
A53696_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Trace User's Guide Release 1.4.0 A53696_01 |
|
Oracle Trace provides several sample SQL scripts that demonstrate how you might make useful queries against your formatted data. These queries, which are described in this chapter, fall into the following general categories:
These sample scripts are located in $ORACLE_HOME\otracexx\admin\sample on the client.
| 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 Corporation recommends that you:
Each of these topics is discussed in the following sections.
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 for server and SQL*Net data. 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 the Oracle Enterprise Manager Oracle Trace Developer's Guide.
Because formatted data tables can have thousands of rows, using indexes, as shown in this section, will improve reporting performance.
For information on how the event table names (shown in parentheses) are derived, see Oracle Enterprise Manager Oracle Trace Developer's Guide.
In the following example, 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
The otrcfunc.sql script defines a function, named elapsed, that makes it easier to calculate elapsed time for duration events. Without this 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 epc_collection table and varies by platform. 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 epc_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 you use 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;
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. For example purposes, we are using Oracle Server data, but you can use your own data. 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 SQL statements executed by each database session. 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 using statistical functions.
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 are specific to Oracle Server and SQL*Net data. They cannot be tailored as the generic scripts can.
Assembling summary data about SQL statement executions can be resource-intensive. To optimize report performance, Oracle Corporation 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 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;
If you just want to examine your data without performing queries against it, you can use the Oracle Trace reporting utility to create reports. Due to 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 to 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 (pid), 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_ppid.txt.
Produces a report called collection_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.
