DBA Masterclass Application Tuning

DBA Masterclass
Application Tuning
Chris Saxon
@ChrisRSaxon & @SQLDaily
https://www.youtube.com/c/TheMagicofSQL
https://blogs.oracle.com/sql

View Slide

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. |
select *
from slow_complex_sql;
Oracle Autonomous Database has many
tools and tricks to make SQL faster
It can improve row estimates with cardinality
feedback and Real-Time Statistics, and create
missing indexes with Automatic Indexing

View Slide

select *
…one day it may also be able to do other
physical optimizations
like Partition tables, load them In-Memory or
create Materialized Views

View Slide

know if this is the correct SQL statement!
In this session we'll look at performance problems
you need to solve at the application level
select *
…but there's one thing it'll never be able to do

View Slide

Copyright © 2020 Oracle and/or its affiliates.
Crimes
against SQL
Image by geralt from Pixabay

View Slide

Image by mobinovyc from Pixabay
These "crimes" are mistakes almost all developers make
avoid punishing them for these

View Slide

Image by Yogattc Scholarships from Pixabay
Instead be a mentor
showing developers the path to enlightenment

View Slide

DBA Masterclass
Application Tuning
Chris Saxon
@ChrisRSaxon & @SQLDaily
https://www.youtube.com/c/TheMagicofSQL
https://blogs.oracle.com/sql

View Slide

product lookups
Image by 200degrees from Pixabay
Consider a simple web app,
with queries to fetch product details

View Slide

stmt.execute (
'select …
from …
where id = ' + var
);
The simple, obvious way to pass variable values in most
programming languages is string || concatenation
This is the first SQL crime

View Slide

Crimes
against SQL
string || concat
Concatenating user input
into SQL statements

View Slide

SQL
When you send a SQL
statement to the database,
it has to parse it This is a
multi-step process

View Slide

Syntax
SQL
seelctformwehre
First is the syntax check:
does this follow the rules of
the SQL language
is nonsense that could
never be a valid statement

View Slide

Syntax
Semantic
SQL
select *
from invalid_tab
Next is the semantic
check: is the statement
valid on this database
This passes if invalid_tab
exists & you have access

View Slide

Syntax
Semantic
Optimize
SQL
FULL TABLE SCAN
Then it optimizes the
statement, choosing the
fastest execution plan

View Slide

Syntax
Semantic
Optimize
Row Source
SQL
FULL TABLE SCAN
Row sourcing turns this
plan into computer
instructions to get the data

View Slide

Finally, parsing is complete,
the database executes the
query and you can
get the data!
These steps form a
hard parse
Syntax
Semantic
Optimize
Row Source
Execute
SQL

View Slide

select * from … where id = 1;
…
When using string concatenation, you send many
queries to the database that are syntactically
identical, semantically identical
and (often) have an identical plan

View Slide

Syntax
Semantic
Optimize
Row Source
Execute
SQL
intensive
Optimization and row sourcing is

View Slide

Image by Intellectual from Pixabay
More CPU usage =
higher costs
Parse time CPU =
wasted money
but there's a
bigger problem

View Slide

stmt.execute (
'select …
from …
where id = ' + var
);
SQL
SQL concatenating user input is vulnerable to

View Slide

select * from … where id = 1
or 1 = 1;
every row in
the table
This allows hackers to extend the where clause to see

View Slide

select * from … where id = 1
union select …;
every table
you can see!
This allows hackers to extend the where clause to see

View Slide

Use Bind
Variables!
To reduce parsing time and protect your data

View Slide

select * from … where id = ?;
Value
placeholder
Inputs never part of SQL =>

View Slide

Syntax
Semantic
Optimize
Row Source
Execute
SQL
After the semantic check,
Oracle Database asks
"Is this SQL in the
shared pool?"

View Slide

Syntax
Semantic
Optimize
Row Source
Execute
SQL
If yes, it can bypass
the optimization and
row sourcing stages
This is a
soft parse

View Slide

PreparedStatement stmt = conn.prepareStatement (
"select … where id = ?";
);
stmt.setInt ( 1, productId );
ResultSet rows = stmt.executeQuery();
To bind input values, use
PreparedStatements

View Slide

PL/SQL binds
by default

View Slide

begin
select …
into …
from products
where product_id = id_var;
end;
/
This is a
bind variable
Static SQL in PL/SQL uses bind variables
Unlike other languages it's easier to write safe statements

View Slide

Improve SQL Query Performance by Using
Bind Variables
https://blogs.oracle.com/sql/improve-sql-
query-performance-by-using-bind-variables
How to Hack Your App Using SQL Injection
https://speakerdeck.com/chrissaxon/how-to-
hack-your-app-using-sql-injection
Further Reading

View Slide

product lookups
on the checkout screen,
customers are likely to purchase many products

View Slide

for ( i = 0; i < items.length; i++ ) {
PreparedStatement ins = conn.prepareStatement (
"insert into order_items values ( ?, ?, … )";
);
ins.setInt ( 1, orderId );
ins.setInt ( 2, orderItem[i]… );
…
int row = ins.executeUpdate();
}
The obvious way to save
these is to loop through the
items, inserting to the
database on each iteration
This leads to the next "crime"

View Slide

Crimes
against SQL
loop SQL end loop
Putting SQL inside loops

View Slide

SQL
SQL
SQL
result
result
result
Looping through arrays and sending SQL on each iteration leads
to lots of back and forth between the app and database
Each individual call is fast, but overall the process is
sloooooooow, which can lead to…

View Slide

Your DB is
sloooow!
My SQL
is fast!
Ryan McGuire / Gratisography

View Slide

SQL
SQL
SQL
result
result
result
Network Time
In all this back-and-forth,
most time is spent on the network

View Slide

Use Bulk
Processing
To avoid all the roundtrips

View Slide

);
…
ins.addBatch();
}
int[] rows = ins.executeBatch();
This batches the records,
sending them all…

View Slide

);
…
ins.addBatch();
}
int[] rows = ins.executeBatch();
…in
one call to the database

View Slide

SQL
SQL
result
result
Batch processing processes many rows in one call,
but transactions often involve many tables
So you still have to call the database once per table

View Slide

Use PL/SQL!
To further reduce the roundtrips

View Slide

CallableStatement order =
conn.prepareStatement (
"{ call create_order ( … ) }";
);
order.executeUpdate ();

View Slide

begin
insert into orders
values ( … )
returning order_id into id;
forall i in 1 .. items.count
insert into order_items
values ( id, items(i).item, … );
end;
/
All these inserts execute
as part of the PL/SQL call

View Slide

begin
insert into orders
values ( … )
end;
/
The returning clause saves
newly inserted values

View Slide

begin
insert into orders
values ( … )
end;
/
forall inserts all the
rows in one call

View Slide

PL/SQL
result
Using stored procedures enables you to change all
the data in one call to the database

View Slide

Image by kliempictures from Pixabay
But what about
backend processes,
like shipping and
invoice
reconciliation?

View Slide

Order orders[] = getUnshippedOrders ();
for ( Order o : orders ) {
o.ship ();
}
This reuses existing methods to send outstanding shipments
…but there's a problem lurking

View Slide

o.ship ();
}
select * from orders
where shipment_date is null
The data source is almost
certainly a SQL query…

View Slide

o.ship ();
} insert into shipments …
update orders …
…and each shipment will
modify the database

View Slide

begin
for ords in (
) loop
insert into shipments
values ( ords.order_id, … );
update orders
set shipment_date = sysdate
where …;
end loop;
end;
Placing the SQL in
a PL/SQL block can
give big speed
gains

View Slide

begin
for ords in (
) loop
values ( ords.order_id, … );
update orders
where …;
end loop;
end;
…but there's still a loop
We can do much better

View Slide

1 Statement
Many Rows
The key to fast SQL is

View Slide

begin
select … from orders
where shipment_date is null;
update orders
end;
Removing the loop and using
insert … select and update
is much faster

View Slide

begin
select … from orders
update orders
end;
User 1
User 2
But this has concurrency problems
Two people can insert the same rows!

View Slide

begin
update orders
select ... from orders
end;
Switching the update and insert
solves the concurrency issue

View Slide

begin
update orders
end;
blocking
because update is

View Slide

begin
update orders
end; No rows!
but this means the insert sees

View Slide

begin
update orders
returning …
bulk collect into ord_arr;
forall ord_arr in 1 .. ord_arr.count
values ( … );
end;
memory limit!
You can solve the insert issue by
bulk collecting the updated rows
but this could blow out you PGA

View Slide

Batched
Bulk Processing
To avoid memory limits with bulk collection

View Slide

declare
cursor orders_cur is
select …
from orders
for update;
type orders_arr_t is
table of orders_cur%rowtype
index by pls_integer;
orders_arr orders_arr_t;
begin

View Slide

open orders_cur;
loop
fetch orders_cur
bulk collect
into orders_arr
limit 100;
exit when orders_arr.count = 0;
forall ords in 1 .. ords.count
…
end loop;
close orders_cur;
Only 1 user
the for update clause means
can open this cursor at a time, solving the
concurrency problem

View Slide

open orders_cur;
loop
fetch orders_cur
bulk collect
into orders_arr
limit 100;
…
end loop;
close orders_cur;
the limit clause controls how many
rows you fetch on each pass
100 is a good default; test different
values if you need to make this faster

View Slide

open orders_cur;
loop
fetch orders_cur
bulk collect
into orders_arr
limit 100;
…
end loop;
close orders_cur;
the exit clause terminates the loop when the
fetch reaches the end of the result set

View Slide

open orders_cur;
loop
fetch orders_cur
bulk collect
into orders_arr
limit 100;
…
end loop;
close orders_cur;
You can then write the data in this batch

View Slide

Bulk Processing with BULK COLLECT and
FORALL
https://blogs.oracle.com/oraclemagazine/bulk-
processing-with-bulk-collect-and-forall
Tuning Inserts, Updates, and Deletes
Live SQL Tutorial
https://livesql.oracle.com/apex/livesql/file/tuto
rial_JN0UV1NQ9RAPON857O1666SUJ.html
Further Reading

View Slide

product lookups
Finally the business wants sales reports
such as the running total of sales

View Slide

select …, (
select count (*)
from orders o2
where o2.order_date
<= o1.order_date
)
from orders o1
Same Table
You can get the running count of orders placed
before the current one with a subquery
Giving the final SQL crime: using the
many, many times in one query

View Slide

Crimes
against SQL
select … some_tab …
some_tab … some_tab
Using one table many times
in the same SQL statement

View Slide

select …, (
select count (*)
from orders o2
where o2.order_date
<= o1.order_date
)
from orders o1
The problem with this is in the worst case the
database executes the subquery once/row in orders

View Slide

Analytic
Functions
To avoid multiple table accesses use

View Slide

select …,
count (*) over (
order by order_date
) running_total
from sales
Adding the over () clause after count (*) turns
this into an analytic function, getting the running
count while only accessing the table once

View Slide

orders
count (*) over (
order by order_date
range between unbounded preceding
and current row
)
1 Jan
1 Feb
1 Mar
1 Feb
1 Apr
100,000
This returns the number of rows with a
value <= than the current row

View Slide

orders
count (*) over (
order by order_date
and current row
)
1 Jan
1 Feb
1 Mar
1 Feb
1 Apr
100,001
This can include rows after the current
in the results!

View Slide

order_date running_total
31 Jan 1,403
1 Feb 1,422
1 Feb 1,422
1 Feb 1,422
2 Feb 1,555
With the default windowing clause,
many rows can have the same total

View Slide

orders
count (*) over (
order by order_date
and current row
)
1 Jan
1 Feb
1 Mar
1 Feb
1 Apr
100,001

View Slide

orders
count (*) over (
order by order_date
rows between unbounded preceding
and current row
)
1 Jan
1 Feb
1 Mar
1 Feb
1 Apr
100,000

View Slide

order_date running_total
31 Jan 1,403
1 Feb 1,410
1 Feb 1,417
1 Feb 1,422
2 Feb 1,555
Changing the window to rows means
the total will increase for every row

View Slide

Find most recent order
Get the last order

View Slide

select …
from some_table
where …
and insert_date = (
select max ( insert_date )
from some_table
where …
); This finds the most recent row in
the table, but queries it twice

View Slide

with rws as (
select t.…,
max ( insert_date )
over ()
as mx_dt
from some_table t
where …
)
select …
from rws
where insert_date = mx_dt
This returns the maximum date in
the table, but it's possible for two
rows to have the same date!

View Slide

with rws as (
select t.…,
row_number () over(
order by insert_date desc
) as rn
from some_table t
where …
)
select …
from rws
where rn = 1
To ensure you only get one row,
use row_number () This assigns
sequential numbers starting at 1

View Slide

Find most recent order
Get the last order
For each customer

View Slide

with rws as (
select t.…,
max ( insert_date ) over (
partition by customer_id
) mx_dt
from some_table t
where …
)
select …
from rws
where insert_date = mx_dt
Partitioning the data splits the
rows into groups, so you'll get the
last date for each customer

View Slide

with rws as (
select t.…,
row_number () over (
partition by customer_id
order by insert_date desc
) rn
from some_table t
where …
)
select …
from rws
where rn = 1
You can get the same result
by assigning a row number
for each customer

View Slide

Analytic SQL for Developers
https://devgym.oracle.com/pls/apex/dg/class/
analytic-sql-for-developers.html
Analytic Functions Live SQL Tutorial
https://livesql.oracle.com/apex/livesql/file/tut
orial_GNRYA4548AQNXC0S04DXVEV08.html
Further Reading

View Slide

Crimes
against SQL

View Slide

Avoiding crimes against SQL
String ||
concatenation
Use bind
variables
SQL in loops
1 statement,
many rows
Batch
processing
Same table
many times
SQL analytics
1 2 3

View Slide

Databases for Developers: Performance
https://devgym.oracle.com/pls/apex/dg/class/
databases-for-developers-performance.html
A Higher-Level Perspective on SQL Tuning
https://blogs.oracle.com/oraclemagazine/a-
higher-level-perspective-on-sql-tuning
Further Reading

View Slide

Ryan McGuire / Gratisography
See you
soon!
asktom.oracle.com
#AskTOMOfficeHours

View Slide

DBA Masterclass Application Tuning

DBA Masterclass Application Tuning

Chris

More Decks by Chris

Other Decks in Technology

Featured

Transcript