1 © Tanel Poder tanelpoder.com Understanding Oracle SQL Monitoring Timeline Hacking Session by Tanel Põder I'll upload the video of this session to YouTube: tanelpoder.com/videos Subscribe to weekly updates: tanelpoder.com/contact Mark your calendars: Oracle Hinting Hacking Session on Tuesday 2. June! 

2 © Tanel Poder tanelpoder.com • Tanel Põder • A long time computer performance geek • Consultant, Trainer, Speaker • Performance & Troubleshooting • Oracle Database & Exadata • Unix, Linux OS & Applications • Hadoop & Spark etc • System reliability & ML J • Blog & Follow • tanelpoder.com • twitter.com/tanelpoder • youtube.com/tanelpoder • github.com/tanelpoder • [email protected] Intro: About me alumni 

3 © Tanel Poder tanelpoder.com • https://tanelpoder.com/downloads/ • TPT GitHub Repo • Open Source (Apache 2.0 license) • Pull & diff updates • https://github.com/tanelpoder/tpt-oracle • https://github.com/tanelpoder/tpt-oracle/archive/master.zip Where to get my scripts? $ cd /Users/tanel/work/oracle/tpt $ find . -type f -name "*.sql" | wc -l 1049 No, I don't remember what all of them do J But there's a @help.sql now! 

4 © Tanel Poder tanelpoder.com • Getting the Most Out of Oracle SQL Monitor • by Maria Colgan • https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/ • Previous SQL Monitoring Hacking Sessions • By me • https://tanelpoder.com/videos/ • Execution plans • by Jonathan Lewis • https://jonathanlewis.wordpress.com/2020/05/05/execution-plans-5/ • Virtual Conference (28-29 September 2020) • https://tanelpoder.com/conference Useful material (that I won't cover here much) 

5 © Tanel Poder tanelpoder.com Intro & Quick Revisit 

6 © Tanel Poder tanelpoder.com SQL Monitoring in 11g: Example Most time spent on data processing 

7 © Tanel Poder tanelpoder.com • A query bottlenecked by data processing, not retrieval • Hash joins and a GROUP BY spilling to TEMP SQL Monitoring: A query bottlenecked by data processing, not retrieval Most time spent on data processing 

8 © Tanel Poder tanelpoder.com SQL Monitoring in 12c+ CPU & waits normalized to a single column Almost all time spent on data retrieval 

9 © Tanel Poder tanelpoder.com Oracle SQL Monitoring – Data Retrieval Almost all time spent on data retrieval 

10 © Tanel Poder tanelpoder.com Oracle SQL Monitoring – Data Processing Most of time spent on data processing 

11 © Tanel Poder tanelpoder.com DB Time much smaller than Duration?! Duration = 15 seconds (wall-clock time) DB Time = 1.3 seconds (time spent inside DB) 332 000 rows were returned, fetched 10 rows at a time (33167 fetches). Most time spent on network roundtrips outside DB 

12 © Tanel Poder tanelpoder.com DB Time much bigger than Duration? Duration = 9 seconds (wall-clock time) DB Time = ~36 seconds (time spent inside DB) Parallel Execution! Each slave can use up to 1 second of DB Time per wall-clock second (plus QC) 

13 © Tanel Poder tanelpoder.com DB Time Anomalies – Database Time > Duration? (serial query!) 

14 © Tanel Poder tanelpoder.com Oracle Wait Interface Time Oracle DB execution (on CPU) OS syscall execution (on CPU) Disk queueing and service (off CPU) begin OWI wait T0= gettimeofday() end OWI wait T1= gettimeofday() IO queueing time IO service time CPU queuing time CPU service time CPU service time -> kcbgtcr() -> ksdfdread() -> skgfqio() Begin Wait End Wait wait event duration The CPU scheduling latency (CPU wait) may inflate wait event duration as Oracle sees it – during CPU starvation New scripts for estimating CPU scheduling latency: @schedlat.sql – Oracle 12c+ tools/unix/runqlat.sh - Linux https://tanelpoder.com/posts/schedlat-low-tech-script-for-measuring-cpu-scheduling-latency-on-linux/ 

15 © Tanel Poder tanelpoder.com DB Time Anomalies – ASH "time" > Database Time 

16 © Tanel Poder tanelpoder.com Execution Timeline 

17 © Tanel Poder tanelpoder.com SQL Developer 18.3 HTML SQL Monitoring reports No Flash needed! Rearrange columns Wait class tooltips https://blog.tanelpoder.com/posts/oracle-sql-monitoring-html-reports-using-sql-developer-v183/ 

18 © Tanel Poder tanelpoder.com Here's a tree 

19 © Tanel Poder tanelpoder.com Execution plan is a tree 

20 © Tanel Poder tanelpoder.com V$SQL_PLAN_MONITOR https://docs.oracle.com/en/database/oracle/oracle-database/20/refrn/V-SQL_PLAN_MONITOR.html#GUID-85354ECB-7E2E-4D4B-AE78-243A4BB6E523 

21 © Tanel Poder tanelpoder.com Timeline Demos! 

22 © Tanel Poder tanelpoder.com • Demo (if have time) SQL Monitoring Tracing 

23 © Tanel Poder tanelpoder.com Bonus Slides 

24 © Tanel Poder tanelpoder.com • x SQL Flame Graphs - @sqlflame.sql (experimental) SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(LENGTH(text)) FROM dba_source; SUM(LENGTH(TEXT)) ----------------- 22602500 SQL> @hash HASH_VALUE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ --------------- ------------------- ----------- 1676994225 9688zqdjz9tpj 0 2853908114 2019-09-26 06:21:40 16777216 SQL> @sqlflame 9688zqdjz9tpj 0 

25 © Tanel Poder tanelpoder.com • Visualizing SQL Plan Execution Time With FlameGraphs https://tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/ How to read a SQL Plan FlameGraph? 

26 © Tanel Poder tanelpoder.com Questions? Tanel Põder tanelpoder.com @tanelpoder