Adrian Billington

Subscribe to Adrian Billington feed
Articles and utilities available at oracle-developer.net
Updated: 13 hours 30 min ago

Mystats utility

Tue, 2015-12-29 14:45
A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. Designed to work under constrained developer environments, this version has enhancements such as time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). June 2007 (updated November 2015)

Working with long columns

Thu, 2012-03-22 03:00
Various techniques for querying and manipulating long columns. July 2010 (updated March 2012)

Profiling trace files with preprocessor external tables in 11g

Tue, 2011-11-22 02:00
Generate and read TKProf and OraSRP reports in a single SQL*Plus window (or other IDE of choice) using preprocessor external tables. November 2011

Xplan utility

Sat, 2011-10-22 03:00
A utility to add parent ID and execution order information to plans reported by DBMS_XPLAN. XPlan includes DISPLAY, DISPLAY_CURSOR and DISPLAY_AWR functionality for use in exactly the same way as the DBMS_XPLAN equivalents. Supports versions from 10g onwards. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a collection of three free-standing SQL*Plus scripts (i.e. no installation/database objects needed). January 2009 (updated October 2011)

Runstats utility

Sat, 2011-10-22 03:00
A variation on Tom Kyte's invaluable RUNSTATS utility that compares the resource consumption of two alternative units of work. Designed to work under constrained developer environments and builds on the original with enhancements such as "pause and resume" functionality, time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). January 2007 (updated October 2011)

Moats

Tue, 2011-03-22 03:00
The (M)other (O)f (A)ll (T)uning (S)cripts. A tuning and diagnostics utility for SQL*Plus co-developed with Tanel Poder. Includes active session sampling and a TOP-style utility for displaying instance activity at regular refresh intervals. Supports versions from 10g Release 2 onwards. September 2010 (updated March 2011)

Dictionary long application

Thu, 2010-07-22 03:00
Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. August 2007 (updated July 2010)

Listagg function in 11g release 2

Tue, 2010-06-22 03:00
A new built-in function for string aggregation. June 2010

Dynamic sql enhancements in 11g

Tue, 2010-06-22 03:00
Oracle completes its dynamic SQL implementation for PL/SQL. February 2008 (updated June 2010)

Pl/sql function result cache in 11g

Tue, 2010-06-22 03:00
Oracle provides a cross-session cache for user-defined PL/SQL function results. January 2008 (updated June 2010)

Improving performance with pipelined table functions

Fri, 2010-01-22 02:00
Using pipelined functions as a performance tuning tool. January 2010

Collect enhancements in 11g release 2

Sun, 2009-11-22 02:00
Aggregate unique and ordered collection elements with the COLLECT function in 11g. November 2009

Collection sorter utility

Sun, 2009-11-22 02:00
A package of two functions to sort collections, including support for descending and distinct sorts. Works with versions from 9i Release 2 onwards but can be easily edited to support 8i. November 2009

Collection cardinality utility

Sun, 2009-11-22 02:00
A utility to provide the CBO with the correct cardinality of small collections used in TABLE() queries (such as variable in-lists). This helps to achieve better execution plans without having to use the undocumented CARDINALITY hint. Supports versions from 10g onwards. November 2009

Sorting collections

Sun, 2009-11-22 02:00
Various techniques for sorting collections. November 2009

Listing files with the external table preprocessor in 11g

Thu, 2009-10-22 03:00
Using the 11g external table preprocessor to get directory listings in SQL. October 2009

External table enhancements in 11g

Tue, 2009-09-22 03:00
Encryption, compression and preprocessing for external tables in Oracle 11g. September 2009

Pl/sql functions and cbo costing

Mon, 2009-06-22 03:00
Associating statistics with PL/SQL functions for greater CBO accuracy. June 2009

Avoiding pls-00436 with forall

Mon, 2009-06-22 03:00
Workarounds to the FORALL PLS-00436 implementation restriction. July 2005 (updated June 2009)

Setting cardinality for pipelined and table functions

Thu, 2009-06-18 14:17
Various methods for setting accurate cardinality statistics for table/pipelined functions. June 2009

Pages