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

DBA Masterclass Application Tuning

D7b6e701f0155fc189bbca6c89223b3c?s=47 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

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

September 29, 2020
Tweet

Transcript

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

    https://blogs.oracle.com/sql
  2. 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
  3. 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
  4. 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
  5. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    Image by geralt from Pixabay
  6. Image by mobinovyc from Pixabay These "crimes" are mistakes almost

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

    showing developers the path to enlightenment
  8. DBA Masterclass Application Tuning Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL

    https://blogs.oracle.com/sql
  9. 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
  10. 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
  11. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    string || concat Image by geralt from Pixabay Concatenating user input into SQL statements
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Syntax Semantic Optimize Row Source Execute SQL intensive Optimization and row sourcing is
  20. 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
  21. 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
  22. 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
  23. 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
  24. Copyright © 2020 Oracle and/or its affiliates. Use Bind Variables!

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

    | select * from … where id = ?; Value placeholder Inputs never part of SQL =>
  26. 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?"
  27. 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
  28. 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
  29. Copyright © 2020 Oracle and/or its affiliates. PL/SQL binds by

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

    loop SQL end loop Image by geralt from Pixabay Putting SQL inside loops
  35. 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…
  36. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Your DB is sloooow! My SQL is fast! Ryan McGuire / Gratisography
  37. 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
  38. Copyright © 2020 Oracle and/or its affiliates. Use Bulk Processing

    To avoid all the roundtrips
  39. 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…
  40. 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
  41. 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
  42. Copyright © 2020 Oracle and/or its affiliates. Use PL/SQL! To

    further reduce the roundtrips
  43. CallableStatement order = conn.prepareStatement ( "{ call create_order ( …

    ) }"; ); order.executeUpdate ();
  44. 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
  45. 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
  46. 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
  47. 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
  48. Copyright © 2020 Oracle and/or its affiliates. Image by kliempictures

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

    orders ) { o.ship (); } This reuses existing methods to send outstanding shipments …but there's a problem lurking
  50. 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…
  51. 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
  52. 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
  53. 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
  54. Copyright © 2020 Oracle and/or its affiliates. 1 Statement Many

    Rows The key to fast SQL is
  55. 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
  56. 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!
  57. 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
  58. 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
  59. 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
  60. 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
  61. Copyright © 2020 Oracle and/or its affiliates. Batched Bulk Processing

    To avoid memory limits with bulk collection
  62. 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
  63. 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
  64. 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
  65. 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
  66. 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
  67. 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
  68. 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
  69. 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
  70. 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
  71. 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
  72. 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
  73. Copyright © 2020 Oracle and/or its affiliates. Analytic Functions To

    avoid multiple table accesses use
  74. 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
  75. 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
  76. 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!
  77. 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
  78. 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
  79. 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
  80. 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
  81. Copyright © 2020 Oracle and/or its affiliates. Find most recent

    order Get the last order Image by 200degrees from Pixabay
  82. 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
  83. 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!
  84. 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
  85. Copyright © 2020 Oracle and/or its affiliates. Find most recent

    order Get the last order For each customer Image by 200degrees from Pixabay
  86. 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
  87. 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
  88. 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
  89. Copyright © 2020 Oracle and/or its affiliates. Crimes against SQL

    Image by geralt from Pixabay
  90. 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
  91. 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
  92. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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