| 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
| 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
| 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
| 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
| 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
| 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"
| 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…
| 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
| 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; / forall inserts all the rows in one call
| 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
| 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
| 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
| 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
| 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 …, ( 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
| 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
| 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
chrissaxon
asei
sonatard
sadnessojisan
yoshidashingo
tohutohu
takumiogawa
msato
ryomrt
oracle4engineer
onk
pauljervisheath
vanstee
jonrohan
lara
akosma
palkan
bkeepers
lauravandoore
thoeni
cromwellryan
brad_frost
![Order orders[] = getUnshippedOrders (); for ( Order o :](https://files.speakerdeck.com/presentations/7e5a915cc07845dbbf56b2cbc1c40cde/slide_48.jpg){kind=link}
![Order orders[] = getUnshippedOrders (); for ( Order o :](https://files.speakerdeck.com/presentations/7e5a915cc07845dbbf56b2cbc1c40cde/slide_49.jpg){kind=link}
![select * from orders where shipment_date is null Order orders[]](https://files.speakerdeck.com/presentations/7e5a915cc07845dbbf56b2cbc1c40cde/slide_50.jpg){kind=link}
