DBA Masterclass Application Tuning

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

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

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

| 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 * from slow_complex_sql; …but there's one thing it'll never be able to do

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

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

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

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

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

| 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

string || concat Image by geralt from Pixabay Concatenating user input into SQL statements

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

| 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

| 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

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

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

| 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

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

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

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

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

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

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

Copyright © 2020 Oracle and/or its affiliates. Use Bind Variables!
To reduce parsing time and protect your data

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

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

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

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

Copyright © 2020 Oracle and/or its affiliates. PL/SQL binds by
default

| 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

| 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

| product lookups Image by 200degrees from Pixabay on the checkout screen, customers are likely to purchase many products

| 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"

loop SQL end loop Image by geralt from Pixabay Putting SQL inside loops

| 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…

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

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

Copyright © 2020 Oracle and/or its affiliates. Use Bulk Processing
To avoid all the roundtrips

| PreparedStatement ins = conn.prepareStatement ( "insert into order_items values ( ?, ?, … )"; ); for ( i = 0; i < items.length; i++ ) { ins.setInt ( 1, orderId ); ins.setInt ( 2, orderItem[i]… ); … ins.addBatch(); } int[] rows = ins.executeBatch(); This batches the records, sending them all…

| PreparedStatement ins = conn.prepareStatement ( "insert into order_items values ( ?, ?, … )"; ); for ( i = 0; i < items.length; i++ ) { ins.setInt ( 1, orderId ); ins.setInt ( 2, orderItem[i]… ); … ins.addBatch(); } int[] rows = ins.executeBatch(); …in one call to the database

| 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

Copyright © 2020 Oracle and/or its affiliates. Use PL/SQL! To
further reduce the roundtrips

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

| 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

| 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; / The returning clause saves newly inserted values

| 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; / forall inserts all the rows in one call

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

Copyright © 2020 Oracle and/or its affiliates. Image by kliempictures
from Pixabay But what about backend processes, like shipping and invoice reconciliation?

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

Order orders[] = getUnshippedOrders (); for ( Order o :
orders ) { o.ship (); } select * from orders where shipment_date is null The data source is almost certainly a SQL query…

select * from orders where shipment_date is null Order orders[]
= getUnshippedOrders (); for ( Order o : orders ) { o.ship (); } insert into shipments … update orders … …and each shipment will modify the database

| begin for ords in ( select * from orders where shipment_date is null ) 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

| begin for ords in ( select * from orders where shipment_date is null ) loop insert into shipments values ( ords.order_id, … ); update orders set shipment_date = sysdate where …; end loop; end; …but there's still a loop We can do much better

Copyright © 2020 Oracle and/or its affiliates. 1 Statement Many
Rows The key to fast SQL is

| begin insert into shipments select … from orders where shipment_date is null; update orders set shipment_date = sysdate where shipment_date is null; end; Removing the loop and using insert … select and update is much faster

| begin insert into shipments select … from orders where shipment_date is null; update orders set shipment_date = sysdate where shipment_date is null; end; User 1 User 2 But this has concurrency problems Two people can insert the same rows!

| begin update orders set shipment_date = sysdate where shipment_date is null; insert into shipments select ... from orders where shipment_date is null; end; Switching the update and insert solves the concurrency issue

| begin update orders set shipment_date = sysdate where shipment_date is null; insert into shipments select ... from orders where shipment_date is null; end; blocking because update is

| begin update orders set shipment_date = sysdate where shipment_date is null; insert into shipments select ... from orders where shipment_date is null; end; No rows! but this means the insert sees

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

Copyright © 2020 Oracle and/or its affiliates. Batched Bulk Processing
To avoid memory limits with bulk collection

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

| 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

| 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; 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

| 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; the exit clause terminates the loop when the fetch reaches the end of the result set

| 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; You can then write the data in this batch

| 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

| product lookups Image by 200degrees from Pixabay Finally the business wants sales reports such as the running total of sales

| 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

select … some_tab … some_tab … some_tab Image by geralt from Pixabay Using one table many times in the same SQL statement

| 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

| 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

| 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

| 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,001 This can include rows after the current in the results!

| 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

| 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,001

| 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

| 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

| 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

| 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!

| 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

| 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

| 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

| 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

Image by geralt from Pixabay

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

| 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

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

DBA Masterclass Application Tuning

DBA Masterclass Application Tuning

Transcript

More Decks by Chris

Other Decks in Technology

Featured