Presentation “Real-Time SQL Tuning” at Hotsos Sym2014 and UTOUG TD2014

This presentation was delivered at the Hotsos Symposium 2014 and at the Utah Oracle Users Group (UTOUG) Training Days 2014.

It describes a PL/SQL package that I put together called “ash_xplan.sql” which can be used to display real-time information about a SQL statement currently executing.  This package combines information from the Active Session History (V$ACTIVE_SESSION_HISTORY) or “ASH” view with information from the DBMS_XPLAN package.

As of Oracle12c v12.1, nothing in Oracle displays elapsed time while the SQL statement is still executing, not even SQL Monitor.

The source code for the “ash_xplan.sql” script is on the Scripts page of this website, along with sample spooled output.

Presentation “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 should be used, and when, for which purpose? This presentation will discuss all the different types of table data compression in Oracle database (i.e. basic/advanced de-duplication compression and hybrid-columnar compression), as well as some older often-forgotten techniques for fitting more data into less space. The presentation will begin with a brief review of data compression in general, followed by a review of the formats used for storage in database blocks and rows, the latter supported by block dumps. All of this will be a prelude for an knowledgeable discussion of the advantages and disadvantages of each type of table compression in Oracle.

This is the presentation slidedeck to accompany the white paper of the same title.

Paper “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 should be used, and when, for which purpose? This presentation will discuss all the different types of table data compression in Oracle database (i.e. basic/advanced de-duplication compression and hybrid-columnar compression), as well as some older often-forgotten techniques for fitting more data into less space. The presentation will begin with a brief review of data compression in general, followed by a review of the formats used for storage in database blocks and rows, the latter supported by block dumps. All of this will be a prelude for an knowledgeable discussion of the advantages and disadvantages of each type of table compression in Oracle.

This is the white paper to accompany the presentation slidedeck of the same title.

Presentation “RDBMS Forensics – Troubleshooting Using ASH”

Arthur C. Clarke wrote that any sufficiently advanced technology is indistinguishable from magic, and it sometimes makes troubleshooting seem like magic, or perhaps art. But it is neither magic nor art; it is process and this presentation uses a case study, such as resolving the ORA-03136 “connection lost” error, to demonstrate that process and to show how components such as ASH, typically thought of as performance tuning tools for DBAs, are also useful diagnostic tools for everyone, particularly developers.  Attendees will learn a forensic, empirical approach to troubleshooting and see a demonstration of a complex troubleshooting task resulting in a non-intuitive but empirically sound resolution.  This presentation should make you think of Active Session History as something like queryable trace information captured and stored within the database.

This is the presentation slidedeck to accompany the white paper of the same title.

Paper “RDBMS Forensics – Troubleshooting Using ASH”

Arthur C. Clarke wrote that any sufficiently advanced technology is indistinguishable from magic, and it sometimes makes troubleshooting seem like magic, or perhaps art. But it is neither magic nor art; it is process and this presentation uses a case study, such as resolving the ORA-03136 “connection lost” error, to demonstrate that process and to show how components such as ASH, typically thought of as performance tuning tools for DBAs, are also useful diagnostic tools for everyone, particularly developers.  Readers will learn a forensic, empirical approach to troubleshooting and see a demonstration of a complex troubleshooting task resulting in a non-intuitive but empirically sound resolution.  This presentation should make you think of Active Session History as something like queryable trace information captured and stored within the database.

This is the white paper to accompany the presentation slidedeck of the same title.

Paper “Scaling To Infinity: Partitioning Data Warehouses On Oracle Database”

Star schemas (a.k.a. dimensional data models) are demanded by BI analysts in data marts and data warehouses.  Star transformations are the optimal join method to access star schemas in Oracle.  Bitmap indexes are required by the star transformation process, but bitmap indexes quickly become literally infeasible without partitioning.  Why isn’t this documented?  How do all these crucial pieces fit together?  Find out what major features of Oracle Database are enabled by partitioning, as well as how and why.  This presentation is the result of long practical experience in logical and physical database design for data warehouses, driven by the combination of business requirements and systems requirements and painfully gained understanding of data warehousing realities.  It provides straight answers to help you utilize Oracle Database features to ensure data warehousing success.

The original presentation in the “Scaling To Infinity” series which describes how table partitioning is the key to scaling an Oracle database to support a data warehouse to the limits of the underlying hardware.

This is the white paper to accompany the presentation slidedeck of the same title.