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

Finding Slow SQL

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
May 01, 2020
60

Finding Slow SQL

D7b6e701f0155fc189bbca6c89223b3c?s=128

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.

    | Welcome to Ask TOM Office Hours!
  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | The database is sloooooowww! Make it fast Ryan McGuire / Gratisography
  4. 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?
  5. 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
  6. How to trace Logging SQL Trace PL/SQL Profiler 1 2

    3
  7. 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;
  8. 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 ();
  9. 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 ();
  10. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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

    | DEMO
  14. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Which should I use? Ryan McGuire / Gratisography
  15. 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?   
  16. How to trace Profile the code Find the longest running

    parts Focus tuning efforts on these! 1 2 3
  17. 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
  18. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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