Hotsos Symposium Speaker – Kerry Osborne

Kerry Osborne is a returning Hotsos Symposium presenter.

Biography

Kerry OsborneKerry Osborne began working with Oracle in 1982. His first job was to upgrade v2 to v3. The early part of his career was primarily spent as a developer using OCI Fortran, Pro-FORTRAN, PRO-C, PRO-COBOL, Oracle Forms, etc. Over time, he became more interested in the system side of things and spent the later part of his career calling himself a DBA. He currently works for Enkitec, a Dallas-based, Oracle-focused consulting company, which he co-founded in 2004. Keep up with Kerry at his blog, kerryosborne.oracle-guy.com.

Presentation Title

Session 1: Controlling Execution Plans (Without Touching the Code)
and
Session 2: My Favorite Scripts — 2010

Abstract

Session 1: Controlling Execution Plans (without Touching the Code): One of the most frustrating problems we face as DBAs is not being able to fix bad code. Our inability to change the code occurs for many reasons. In some cases we are dealing with packaged applications where the code is just not available. In other cases, the politics of an organization can dictate lengthy delays in making changes to code. And finally, Oracle sometimes just can't make up its mind about how best to deal with a specific SQL statement. Regardless of the reasons, Oracle specialists often find themselves in the unenviable position of being asked to make things better without touching the code.

Fortunately, Oracle provides many options for doing just that. Among the most effective approaches are techniques that focus on modifying execution plans of individual statements. Oracle has provided various mechanisms for accomplishing this over the years including Outlines, SQL Profiles, and SQL Baselines. These techniques provide laser-like specificity, by limiting their effect to a single statement. While these constructs are extremely powerful, they are not well documented and therefore not particularly well understood. Also, they suffer from some "quirkiness". For example, despite what the documentation implies regarding Outlines locking execution plans, there are situations where creating an Outline on an existing statement will actually cause the plan to change. This "quirk" is not limited to the older Outline construct. It has been carried forward to the newer SQL Profiles and SQL Baselines as well.

Kerry Osborne, the PirateThis presentation provides a general overview of the basic methods available as well as the underlying hint-based mechanism that they rely on. It also gives a historical perspective as to how we got where we are. Examples will be provided with specific scripts for making use of them, modifying them, and reporting on them. The presentation addresses where these techniques are appropriate and where they are not, and finally, offers some ideas on how to pitch the use of these features to others within your organization.

Session 2: My Favorite Scripts — 2010: I admit it. I'm a command line guy. GUI's are nice for some things; moving files around, trend analysis, etc., but when I am trying to solve a problem, I prefer to know where my data is coming from. So in most situations, I prefer low-level tools like SQL*Plus and standard unix utilities.

While I am constantly writing one-off scripts for specific situations, there are a handful of scripts that I use over and over. This presentation will cover as many of these scripts as time will allow. They will be broken down into a few broad categories:

  • Finding Specific Statements: This will include execution plans, execution statistics, associated bind variable values, and associated wait events. This will necessitate a brief discussion of how SQL statements and associated data are stored in Oracle.
  • Using Variance to Identify Potential Issues: We'll look at a couple of examples including how to find statements with multiple plans that have very different elapsed times (often caused by bind variable peeking).
  • Current Database State: what's being executed, what's being waited on, etc.
  • Historical Performance: We'll dig into what's available in AWR history tables.
  • Tracing: Scripts in this category deal with various approaches to turning tracing on and off and dealing with the output.
  • Miscellaneous: Everything else like flushing single statements from the shared pool, tracking parameter modifications, dealing with embedded XML in sql_plan.other_xml to get bind variable values and outline hints, a few uses of regular expressions, etc.

This presentation will have a few slides but will primarily be a hands-on demonstration of the scripts.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: