A Deep Dive Into the Oracle SQL Monitoring Report

The SQL Monitoring Report was introduced in Oracle Database 11g and has become the single most used diagnostic report by the Oracle Real-World Performance Group and Oracle database development for SQL statement performance issues. This session will start with a technical overview of the SQL Monitoring Report and what metrics and information it provides. From there we'll deep dive into numerous examples from the field explaining how this report was used to diagnose and validate performance issues. There is a wealth of information in the SQL Monitoring Report and this session will provide the necessary knowledge to best leverage it.

Transcript

1. 1 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. A Deep Dive Into the SQL Monitoring Report Greg Rahn Real-World Performance Group, Server Technologies
2. 2 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. About Me •  Over 7 years in Real-World Performance Group •  Twitter: @GregRahn •  Blog: http://structureddata.org NoCOUG 2011-11-09
3. 3 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Program Agenda •  Quick Intro •  Walkthroughs •  Summary NoCOUG 2011-11-09
4. 4 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. What is SQL Monitoring? •  Introduced in Oracle Database 11g •  Enables performance monitoring of SQL statements while they are executing •  Allows execution details stored in offline report NoCOUG 2011-11-09
5. 5 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. What statements get monitored? •  Monitors –  Serial statements with 5 seconds of total CPU/IO time –  All parallel statements –  Includes queries / DML / DDL –  Always on, enabled out of the box •  Hints –  MONITOR –  NO_MONITOR NoCOUG 2011-11-09
6. 6 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. How do I get a SQL Monitoring Report? •  GUI via Oracle Enterprise Manager –  Performance Page –  SQL Monitoring Link –  Click on completed or currently executing statement •  Command line via SQL*Plus –  pagesize 0 linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000! –  dbms_sqltune.report_sql_monitor(! sql_id=>’2foo4uy583wtf’, type=>’ACTIVE’)! –  Types: TEXT (default), or ACTIVE (same as EM) NoCOUG 2011-11-09
7. 7 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. 7 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 8 NoCOUG 2011-11-09
8. 8 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. 8 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 8 NoCOUG 2011-11-09
9. 9 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. 9 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 8 NoCOUG 2011-11-09
10. 10 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Monitored SQL Executions •  Let’s take a look… Oracle Enterprise Manager NoCOUG 2011-11-09
11. 11 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. 11 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 8 Status Duration Parallel Info DB Time IO Requests Start/End SQL Text Queued Running Completed Error Queued/Execution Parallel Degree PX Downgraded NoCOUG 2011-11-09
12. 12 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Active SQL Monitoring Report •  Serial Execution •  Parallel Execution •  PL/SQL Basic Overview NoCOUG 2011-11-09
13. 13 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Walkthrough Scenarios •  PDML disabled vs. PDML enabled •  Bad cardinality •  Undersized PGA •  Parallel skew NoCOUG 2011-11-09
14. 14 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Benefits of SQL Monitor Reports •  Always on •  Currently executing statements •  Recently completed statements •  Execution information •  Execution plans •  Performance profile •  Both on-line and off-line NoCOUG 2011-11-09
15. 15 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Q&A NoCOUG 2011-11-09
16. 16 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. NoCOUG 2011-11-09
17. 17 Copyright © 2011, Oracle and/or its affiliates. All rights reserved.