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

Finding Slow SQL

Chris
May 01, 2020
140

Finding Slow SQL

Chris

May 01, 2020
Tweet

Transcript

  1. Your SQL Office Hours begins soon… How to Find Slow

    SQL 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.

    | The database is sloooooowww! Make it fast Ryan McGuire / Gratisography
  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Which is the Slow SQL? insert … select … for rws in ( select … ) loop insert … end loop; 1 microsecond? 1 minute?
  4. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Stmt 4 7% Stmt 3 20% Stmt 2 28% Stmt 1 45% Tuning this gives most benefit… …and this the least
  5. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Logging insert … logger.log … select … logger.log … for rws in ( select … ) loop logger.log … insert … end loop;
  6. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | SQL Trace sys.dbms_monitor.session_trace_enable ( waits => true, binds => true ); … your code … sys.dbms_monitor.session_trace_disable ();
  7. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PL/SQL Profiler run := sys.dbms_hprof.start_profiling ( … ); … your code … sys.dbms_hprof.stop_profiling ();
  8. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | others 3% insert into orders 7% select … from orders 20% insert into order_items 21% select … from products 49%
  9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | insert into orders … select order_id from orders … for rws in ( select … from products ) loop insert into order_items … end loop; 7% 20% 70%
  10. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Which should I use? Ryan McGuire / Gratisography
  11. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Logging SQL Trace PL/SQL Profiler SQL stats?    Trace other sessions?    Context info?    PL/SQL?    Existing code?   
  12. How to trace Profile the code Find the longest running

    parts Focus tuning efforts on these! 1 2 3
  13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Logger – PL/SQL Logging https://github.com/OraOpenSource/Logger SQL trace, 10046, trcsess and tkprof in Oracle https://oracle-base.com/articles/misc/sql-trace- 10046-trcsess-and-tkprof Using the PL/SQL Hierarchical Profiler https://bit.ly/plsql-profiler-docs Further Reading
  14. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography