Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy.

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our Privacy Policy and User Agreement for details.

How to find and fix your Oracle-based application performance problem

This session was presented 2016-01-28 at Dallas Oracle User's Group January meeting.
How long does your code take to run? Is it changing? When it is slow, WHY is it slow? Is it your fault, or somebody else's? Can you prove it? How much faster could your code be? Do you know how to measure the performance of your code as user workloads and data volumes increase? These are fundamental questions about performance, but the vast majority of Oracle application developers can't answer them. The most popular performance tools available to them—and to the database administrators that run their code in production—are incapable of answering any of these questions. But the Oracle Database can give you exactly what you need to answer these questions and many more. You can know exactly where YOUR CODE is spending YOUR TIME. This session explains how.

24.
@CaryMillsap
1. Select the experience you need to improve.
2. Measure its response time (R) in detail.
3. Execute the best net-payoﬀ remedy.
4. Repeat until economically optimal.
24
Method R
How do you do this,
when the it is your code?

60.
@CaryMillsap 60
An Oracle trace ﬁle is a log that shows
what your code did inside the Oracle Database.

61.
@CaryMillsap
Some things to know...
Oracle writes a trace line when a call (db|os) ﬁnishes.
There are two primary line formats: one for db calls, one for os calls.
Each call is associated with a SQL or PL/SQL statement through a cursor id.
Each line contains a time stamp (tim) and a duration (e|ela).
R ≠ ∑(e|ela) because parent call durations include child call durations.
61

86.
@CaryMillsap
OPTIM
IZE ANYT
HING
M
eTH O D
R
You might have known that you should
“use bind variables,” but you couldn’t
have quantiﬁed the R impact on the
experience without this trace ﬁle.
86

87.
@CaryMillsap
BASELINE:
for each invoice number {
cursor = parse(“select ...where invoice_number = ” + number);
exec(cursor);
loop over the result set to fetch all the rows;
}
87
BAD
This is horriﬁc:
• Uses too much CPU for PARSE calls
• Serialization on library cache and shared pool latches
• Consumes too much memory in the library cache
• May execute too many network round-trips

88.
@CaryMillsap
BASELINE: BAD
for each invoice number {
cursor = parse(“select ...where invoice_number = ” + number);
exec(cursor);
loop over the result set to fetch all the rows;
}
FIX 1 “Hey, let’s use bind variables”:
for each invoice number {
cursor = parse(“select ...where invoice_number = :a1”);
exec(cursor, number);
loop over the result set to fetch all the rows;
}
88
STILL BAD
A little better, but still really awful:
• Uses too much CPU for PARSE calls
• Serialization on library cache latches
• Maybe, too many network round-trips

89.
@CaryMillsap
FIX 1 “Hey, let’s use bind variables”: STILL BAD
for each invoice number {
cursor = parse(“select ...where invoice_number = :a1”);
exec(cursor, number);
loop over the result set to fetch all the rows;
}
FIX 2:
cursor = parse(“select ...where invoice_number = :a1”);
for each invoice number {
exec(cursor, number);
loop over the result set to fetch all the rows;
}
89
BETTER
Better (only 1 PARSE call now!), but
still lots of network round-trips.

90.
@CaryMillsap
FIX 2: BETTER
cursor = parse(“select ...where invoice_number = :a1”);
for each invoice number {
exec(cursor, number);
loop over the result set to fetch all the rows;
}
FIX 3:
cursor = parse(“
select ...where invoice_number
in (select invoice number from wherever your for each was getting them)
”);
exec(cursor);
loop over the result set to fetch all the rows;
90
Now, only 1 PARSE call, only 1 EXEC call, and
the minimum possible number of network
round-trips.*
*Unless there’s a way to return fewer rows.
BETTER YET