Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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?

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

How to trace Logging SQL Trace PL/SQL Profiler 1 2 3

Slide 7

Slide 7 text

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;

Slide 8

Slide 8 text

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 ();

Slide 9

Slide 9 text

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 ();

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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%

Slide 12

Slide 12 text

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%

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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?   

Slide 16

Slide 16 text

How to trace Profile the code Find the longest running parts Focus tuning efforts on these! 1 2 3

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography