Hotsos Symposium Speaker – Tim Gorman

Tim Gorman is a returning Hotsos Symposium presenter.

Biography

Tim GormanTim Gorman has worked in IT with relational databases since 1984, as an Oracle PRO*C and PL/SQL application developer since 1990, as an Oracle DBA since 1993, and managing/designing very large data warehouses on Oracle since 1994. He is an independent consultant (www.EvDBT.com) specializing in data warehousing and database administration (particularly performance and availability). Mr Gorman is currently president of RMOUG (Rocky Mtn Oracle Users Group) where he has been a member since 1992 and a board member since 1995. He is currently a member of the board of directors of ODTUG (Oracle Developer Tools Users Group) and of Project SafeGuard, which provides legal advice to victims of domestic violence. He has co-authored five books (three with the Oak Table Press), performed technical review on eight more books, has been an Oracle ACE since 2007 and an Oracle ACE Director since 2012, a member of the Oak Table Network since 2002, and has presented at Oracle Open World, Collaborate, KScope, Hotsos, RMOUG, UKOUG, and Oracle users groups in lots of wonderful places around the world.

Presentation Title

Session 1: Real-Time SQL Statement Monitoring

Session 2: Three Types of Table Compression

Abstract

Session 1: Real-Time SQL Statement Monitoring — Have you ever just sat and stared at the bottom of a huge and complex SQL statement and just wished - prayed - that it would just finish so you could finally see what it has been doing? There is only so much information that you can extract from a 30-line execution plan ahead of time, never mind an execution plan that is hundreds of lines long. There is nothing more futile than knowing that the estimated row counts and elapsed timings used by the Oracle optimizer are just guesses based on faulty statistics. Not until it finishes will you be able to work with "actuals" instead of "estimates".

By combining and displaying all of the real-time information captured in Active Session History (V$ACTIVE_SESSION_HISTORY) and the Cursor Cache (V$SQL) and stored SQL Execution Plans (V$SQL_PLAN) and a few other places, Oracle11g and beyond enables us to gain immediate and continual insight into what is going on behind that blinking cursor at the bottom of the SQL statement. Using this knowledge, we can understand exactly where the most time, effort, and resources are being expended and then take focused and targeted actions to correct the problem. Armed with this knowledge, it is possible to tune a SQL statement down from hours and days using hard empirical evidence, without ever having to test it to completion until after it is fully tuned.

This presentation will use real-world SQL statements and scenarios to demonstrate how to understand and effectively combine the information from the GV$ACTIVE_SESSION_HISTORY view to augment the DBMS_XPLAN package, providing SQL*Plus scripts and ideas to take back to work tomorrow and start seeing results.

Three Things Attendees Can Expect To Learn:

  1. How the ASH view and DBMS_XPLAN package can be related
  2. How this information can be used to expose the "black box" of SQL processing
  3. How to interpret and act upon the real-time information provided by these views

Session 2: Three Types of Table Compression — There are several different types of compression available in the Oracle database for compressing table data, and so the question must arise: which are most appropriate, and for which purpose? This presentation will discuss the two forms of table data compression in Oracle database (i.e. basic/advanced de-duplication compression and hybrid-columnar compression), as well as a third forgotten technique for fitting more rows into fewer blocks. The presentation will begin with a brief review of data compression in general, followed by a review of internal storage formats and other mechanisms closely related to understanding compression, such as table clusters and direct-path loads. All of this will be a prelude for a knowledgeable discussion of the two documented and the one undocumented form of table compression in Oracle database.

What attendees can expect to learn:

  1. How Oracle stores data in rows and in database blocks
  2. How Oracle compresses table data and how each method of compression works over the lifecycle of the data
  3. The advantages and disadvantages of the different types of table compression and the situations in which each works best

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: