Finding Slow SQL

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