Database Reference
In-Depth Information
Both TKPROF and ESQLTRCPROF report CPU usage. Often high CPU usage is associated
with SQL statements that access thousands or millions of blocks in the buffer cache. If this is
the case, a poor execution plan, which includes unnecessary full scans, has an inappropriate
join order, or uses the wrong index, might be the culprit. SQL statements with high CPU usage
must be identified and their execution plans checked. TKPROF provides the sort options
fchela
and
exeela
, which may be combined. If you suspect that a
SELECT
statement is the most expen-
sive statement in a trace file, use
sort=fchela,exeela
. Otherwise use
sort=exeela,fchela
.
TKRPOF cannot sort by total elapsed time, which comprises elapsed time from the three stages
parse, execute, and fetch. Furthermore it does not consider inter database call wait events
attributed to a cursor when sorting. ESQLTRCPROF always sorts by total elapsed time and
considers in between database call wait events such as
SQL*Net message from/to client
while
ignoring think time. This makes sure that a SQL statement that causes many round-trips and
thus incurs a lot of network latency is ranked as more expensive than a statement that is respon-
sible for the same amount of elapsed time within database calls, but does not accumulate as
much network latency.
Reproduction
Before the actual performance optimization may begin, a way to reproduce the problem at
hand must be found. It is crucial to reproduce the problematic code path as closely as possible.
Depending on the kind of performance problem, to reproduce an issue, the following factors
may need to be identical between the original environment and the test environment:
Hardware capability
Operating system and release
ORACLE DBMS release
Initialization parameters (documented and hidden), especially optimizer parameters
Database object statistics (a.k.a. optimizer statistics)
Database block size
Bind variables and bind data types in SQL statements which are optimized
Stored outlines
SQL profiles (Oracle10
g
and later releases only)
When creating database objects, make sure you create them with the same DDL in
tablespaces with the same block size as the original application. The package
DBMS_METADATA
may be used to extract DDL for database objects. When reproducing SQL statements with bind
variables, it's important to use bind variables with the same data type as the original statement.
Level 12 SQL trace files contain bind variable values as well as bind variable data types in the
BINDS
section (see Chapter 24). SQL*Plus or PL/SQL variables may be used to reproduce the
bind data types. Last but not least, make sure no other users are running stress tests or other
resource-intensive programs on your test system, since this may lead to contradictory results
of your tests.
