Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DBA Masterclass Application Tuning

Chris
September 29, 2020

DBA Masterclass Application Tuning

An overview of SQL optimizations you can make at the application level to improve them run faster

Chris

September 29, 2020
Tweet

More Decks by Chris

Other Decks in Technology

Transcript

  1. 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
  2. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  4. Image by mobinovyc from Pixabay These "crimes" are mistakes almost

    all developers make avoid punishing them for these
  5. Image by Yogattc Scholarships from Pixabay Instead be a mentor

    showing developers the path to enlightenment
  6. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | product lookups Image by 200degrees from Pixabay Consider a simple web app, with queries to fetch product details
  7. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  8. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    string || concat Image by geralt from Pixabay Concatenating user input into SQL statements
  9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | SQL When you send a SQL statement to the database, it has to parse it This is a multi-step process
  10. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  11. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  12. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize SQL FULL TABLE SCAN Then it optimizes the statement, choosing the fastest execution plan
  13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize Row Source SQL FULL TABLE SCAN Row sourcing turns this plan into computer instructions to get the data
  14. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  15. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  16. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize Row Source Execute SQL intensive Optimization and row sourcing is
  17. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Image by Intellectual from Pixabay More CPU usage = higher costs Parse time CPU = wasted money but there's a bigger problem
  18. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | stmt.execute ( 'select … from … where id = ' + var ); SQL SQL concatenating user input is vulnerable to
  19. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | select * from … where id = 1 or 1 = 1; every row in the table This allows hackers to extend the where clause to see
  20. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | select * from … where id = 1 union select …; every table you can see! This allows hackers to extend the where clause to see
  21. Copyright © 2020 Oracle and/or its affiliates. Use Bind Variables!

    To reduce parsing time and protect your data
  22. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | select * from … where id = ?; Value placeholder Inputs never part of SQL =>
  23. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize Row Source Execute SQL After the semantic check, Oracle Database asks "Is this SQL in the shared pool?"
  24. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize Row Source Execute SQL If yes, it can bypass the optimization and row sourcing stages This is a soft parse
  25. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PreparedStatement stmt = conn.prepareStatement ( "select … where id = ?"; ); stmt.setInt ( 1, productId ); ResultSet rows = stmt.executeQuery(); To bind input values, use PreparedStatements
  26. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  27. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  28. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | product lookups Image by 200degrees from Pixabay on the checkout screen, customers are likely to purchase many products
  29. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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"
  30. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    loop SQL end loop Image by geralt from Pixabay Putting SQL inside loops
  31. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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…
  32. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Your DB is sloooow! My SQL is fast! Ryan McGuire / Gratisography
  33. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | SQL SQL SQL result result result Network Time In all this back-and-forth, most time is spent on the network
  34. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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…
  35. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  36. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  37. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  38. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  39. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  40. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PL/SQL result Using stored procedures enables you to change all the data in one call to the database
  41. Copyright © 2020 Oracle and/or its affiliates. Image by kliempictures

    from Pixabay But what about backend processes, like shipping and invoice reconciliation?
  42. Order orders[] = getUnshippedOrders (); for ( Order o :

    orders ) { o.ship (); } This reuses existing methods to send outstanding shipments …but there's a problem lurking
  43. 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…
  44. 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
  45. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  46. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  47. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  48. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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!
  49. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  50. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  51. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  52. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  53. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  54. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  55. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  56. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  57. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  58. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  59. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  60. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | product lookups Image by 200degrees from Pixabay Finally the business wants sales reports such as the running total of sales
  61. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  62. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    select … some_tab … some_tab … some_tab Image by geralt from Pixabay Using one table many times in the same SQL statement
  63. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  64. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  65. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  66. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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!
  67. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  68. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  69. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  70. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  71. Copyright © 2020 Oracle and/or its affiliates. Find most recent

    order Get the last order Image by 200degrees from Pixabay
  72. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  73. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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!
  74. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  75. Copyright © 2020 Oracle and/or its affiliates. Find most recent

    order Get the last order For each customer Image by 200degrees from Pixabay
  76. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  77. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  78. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  79. 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
  80. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 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
  81. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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