Database Reference
In-Depth Information
profile from a trace file, I would like to walk you through a manual calculation of some figures
based on a small trace file.
Case Study
This case study is based on a small Perl DBI program called
insert_customer.pl
. The program
inserts a single row into the table
CUSTOMER
. Each customer is identified by a unique number,
which is generated by the sequence
CUSTOMER_ID_SEQ
. An
INSERT
trigger is used to assign the
next value from the sequence to the column
ID
of the table. I have deliberately created the
sequence with the
NOCACHE
option, since this causes recursive SQL (i.e.,
dep
values greater than
zero).
3
The purpose of the case study is twofold:
To demonstrate how the response time R is calculated.
To provide evidence that runtime statistics at recursive call depths greater than zero are
rolled up into statistics at higher recursive call depths and ultimately at recursive call
depth 0.
Remember that statements executed directly by a database client have recursive call depth
zero. The program pauses twice to allow the user to query the dynamic performance view
V$SESSTAT
, once before enabling SQL trace and once just prior to disconnection from the
ORACLE instance. Since
V$SESSTAT
is not affected by double counting as discussed previously,
it holds an accurate representation of session level statistics.
Running the Perl Program
To repeat the case study, open two terminal windows. One for running the Perl program and
another one for querying
V$SESSTAT
. Run
insert_customer.pl
in the first terminal window.
4
$ insert_customer.pl
Hit return to continue
While the program waits for input, query
V$SESSTAT
in the second window.
SQL> SELECT n.name, s.value
FROM v$sesstat s, v$statname n, v$session se
WHERE s.statistic#=n.statistic#
AND n.name IN ('db block gets', 'consistent gets')
AND s.sid=se.sid
AND se.program LIKE 'perl%';
NAME VALUE
------------------ -----
db block gets 0
consistent gets 195
Do not use
NOCACHE
in any real-world applications, since it degrades performance.
3.
Use the Perl DBI environment variables
DBI_USER
,
DBI_PASS
, and
DBI_DSN
to specify user name,
password, data source, and connect string (see Chapter 22).
4.
