Evergreen Database Technologies, Inc.
Downloadable Library
of white papers and presentations
Feel free to download as you wish. No
warranty is implied or provided -- use at your own risk!
Please provide credit for authorship
if you find these materials useful or if you pass them to others.
Unless otherwise noted, materials here
are written by Tim Gorman.
Content
last updated: 06-April 2011
|
RDBMS
Forensics using ASH WinZip archive containing
presentation and supporting files – 65Kb |
Authored by Tim Gorman This 120-minute presentation was presented at the Utah Oracle Users Group “Training
Days 2011” in Sandy UT on 30-March 2011. It was adapted from a 60-minute presentation
given at the Rocky Mountain Oracle Users Group “Training Days 2011” in This is based on two case studies illustrating how Active Session History can be used as the next best thing to trace files when diagnosing complex problems in the Oracle RDBMS. ASH is not just for performance tuning, it can also provide vital information for the forensic analysis following a specific problem. New on 06-April 2011 |
|
Tuning PL/SQL
using the DBMS_PROFILER MS-PowerPoint
presentation – 110Kb WinZip archive
containing presentation and supporting files – 91Kb |
Authored by Tim Gorman This 60-minute presentation was first presented at a general meeting of
RMOUG in November 2003. It was
presented at the IOUG-A “Live 2004” conference in There is a lot more going on inside a PL/SQL procedure other than SQL statements, so traditional SQL tuning does not always apply. The DBMS_PROFILER package provides the means to get a better glimpse inside the package or stored procedure, specifically to understand where it is spending the most time. Updated on 25-May 2009 |
|
SQL tracing
with TRCANLZR MS-PowerPoint
presentation – 160Kb WinZip archive
containing presentation and supporting files – 620Kb |
Authored by Tim Gorman This 60-minute presented at the Northeast Ohio Oracle Users Group in Every tracing mechanism needs an aggregation tool to summarize the detailed trace information into something useful. SQL tracing has the venerable TKPROF program, but that is available only to DBAs. The TRCANLZR package is available from Oracle Support’s MetaLink site and provides non-DBAs the ability to access and summarize SQL tracing information. Added on 25-May 2009 |
|
Understanding
AWR ·
MS-PowerPoint presentation – 71Kb |
Authored by Tim Gorman This paper was presented at the 21-Nov 2008
quarterly educational workshop of the Rocky Mtn Oracle Users Group in Denver
CO as well as the RMOUG “Training Days 2009” conference. This presentation explains the evolution from
BSTAT-ESTAT to STATSPACK to AWR (automatic
workload repository), how to configure and manage the AWR, how to use the
standard command-line reports provided with it, and most especially how to
exploit the wealth of information using custom-built reports. The intent is to empower DBAs to write
custom reports to use the full power of the AWR, above and beyond
functionality available in Enterprise Manager. Updated
on 01-March 2009 |
|
Scaling to
Infinity – Partitioning in Oracle Data Warehouses ·
MS-Word white paper – 90Kb ·
MS-PowerPoint presentation – 245Kb |
Authored by Tim Gorman This paper was presented at the Oracle Open World conference in 2009 and 2007
in San Francisco CA, and at the RMOUG Training
Days conference in 2009, 2006, and 2005 in Denver CO, and has been presented
numerous times at other conferences and as a 1-day and 2-day training
seminar. This paper explains why using the EXCHANGE
PARTITION operation during ETL leads a data warehouse onto the virtuous
cycle of effective use of advanced features and easy living. Not using EXCHANGE PARTITION during ETL
relegates a data warehouse onto the dreaded death spiral where life
becomes progressively more difficult and ROI shrinks to zero. Updated on 13-Oct 2009 |
|
Deploying
Applications using Oracle10g CRS §
MS-PowerPoint
presentation – 271Kb |
Authored by Tim Gorman This was presented at the RMOUG “Training Days 2008” conference in It overviews the Oracle “clusterware” layerin Oracle RAC, also known as the “CRS” (cluster-ready services), then uses a case study involving Teleran iSight to demonstrate how highly-available applications can be integrated and managed in an Oracle10g RAC clustered-server environment. New on 01-March 2008 |
|
Reporting from
the RMAN Repository §
MS-PowerPoint presentation
– 76Kb |
Authored by Tim Gorman This was presented at the RMOUG “Training Days 2008” conference in It describes how the Oracle RMAN utility, while providing some important reporting capabilities, does not answer the really important questions related to database recoverability, such as: Is the database recoverable? How long will it take to recover? When was the database last backed up? The presentation describes how this information can be pulled from the RMAN repository and provides links to some SQL*Plus script at http://www.EvDBT.com/tools.htm which answer these questions. New on 01-March 2008 |
|
Performance
Diagnostics using STATSPACK data Current
version focusing on treating the STATSPACK repository like a “performance
data warehouse” · MS-Word white paper – 112Kb · MS-PowerPoint presentation - 147Kb ·
WinZip archive containing both white
paper and slide show, as well as several sample outputs – 130Kb Original paper
focusing on Oracle E-Business Suite · MS-Word white paper – 76Kb · MS-PowerPoint slide show - 165Kb · WinZip archive containing both white paper and slide show– 53Kb |
Authored by Mary Crystal and Tim Gorman This paper, originally focused on Oracle E-Business Suites R11.0.3, was first presented by Mary and Tim at the Rocky Mtn Oracle Users Group (RMOUG) “Training Days 2002” conference on 20-21 February 2002 in Denver CO. Later, by Tim alone at the Twin Cities Oracle Users Group (TCOUG) on 18-July 2002 in Minneapolis MN, and at the Utah Oracle Users Group (UTOUG) “Vendor Day” on 01 October 2002 in Salt Lake City UT. The latest version of the paper focuses less on installation and configuration of STATSPACK itself, and not at all on Oracle E-Business Suites, and more on getting the most from the data collected by STATSPACK, in actually treating the STATSPACK repository tables like a data warehouse of performance information. This newer version was first presented to the Los Angeles Oracle Users
Group (LAOUG) meeting on 21 November 2002 in Norwalk, CA, then later at the
RMOUG “Training Days 2003” and “Training Days 2004” conferences in Denver CO,
and most recently at the NOUG “Training Days” in Jan 2006 in Waltham MA and
at the Hotsos Symposiums in Irving TX in March 2005 and March 2006. Updated on 10-March 2006 |
|
The Utility of
Logon Triggers ·
MS-Word
white paper – 49Kb |
Authored by Jeff Maresh Practical uses of AFTER LOGON database triggers. This paper is published in the IOUG “SELECT”
magazine in Winter (Q1) 2006. Added on
01-November 2005 |
|
Materialized
Views in Action ·
MS-Word
white paper – 220Kb |
Authored by Jeff Maresh This paper will be presented at the RMOUG
“Training Days 2005” conference in A practical explanation of the use of
materialized views on a large-scale within a near-real-time data warehouse. Added on 02-January 2005 |
|
Supercharging
STAR Transformations ·
MS-Word
white paper – 345Kb |
Authored by Jeff Maresh This paper was published in SELECT magazine in the
4th quarter of 2004. This is a thorough and practical explanation of
the pro’s and con’s of STAR transformations, how they work, and how they
should be implemented. Being able to
utilize this extraordinary feature requires the use of a dimensional data
model, which by itself indicates that you are doing something right in your
data warehouse. Added on
28-September 2004 |
|
ORADEBUG –
Undocumented Oracle Utility ·
Adobe
Acrobat PDF white paper – 146Kb |
Authored by Miladin Modrakovic This comprehensive and well-researched paper
forms an excellent baseline document for the undocumented ORADEBUG utility,
which resides within SQL*Plus and (on older Oracle versions) in Server
Manager (svrmgrl). Please be sure
to exercise caution when using this utility, as it is not supported by Oracle
Support. Added on
18-March 2004 |
|
Managing the
Data Lifecycle ·
MS-Word
white paper – 121Kb |
Authored by Jeff Maresh This paper was published in the IOUG “SELECT”
journal in the first quarter of 2004. This is an important paper that discusses how to
architect a data warehouse on Oracle to optimize the use of storage according
to the lifecycle of data. As data
ages, its usage patterns change and a smart data warehouse architect can use
those patterns to advantage. Added on
27-August 2003 |
|
Automatic UNDO
Internals ·
Adobe
Acrobat white paper – 214Kb |
Authored by Dan Fink This paper was presented at the IOUG-A “Live
2003” conference in This paper is a detailed examination of Oracle9i
undo segments, which replace rollback segments. It also discusses differences between MANUAL
undo mode and AUTO undo mode in Oracle9i. Excellent research and wonderful writing! Added on 09-May 2003 |
|
Understanding
Indexes ·
MS-Word white paper – 112Kb ·
MS-PowerPoint presentation – 188Kb ·
WinZip archive containing both white paper
and slide show – 112Kb |
Authored by Tim Gorman This was first presented at the Toronto Oracle Users Group in This white paper was published during 2005 in the IOUG “SELECT”
magazine, and was awarded “honorable mention” (a.k.a. “runner-up”) for IOUG
“Best Article of 2005”. This presentation is intended to provide basic understanding of indexes,
specifically to dispel some of the myths and rumors that surround
indexing. It discusses the strengths
and weaknesses of the Oracle B*Tree implementation, then discusses alternate
solutions and workaround where they exist (and do not exist) for each
weakness. |
|
Cats, Dogs,
and ORA-01555 ·
MS-Word white paper – 65Kb ·
MS-PowerPoint slide show – 133Kb ·
WinZip archive containing both white paper
and slide show – 73Kb |
Authored by Tim Gorman This paper has been presented at numerous local Oracle Users Group as well as IOUG-A “Live 2003”. It will be published in the IOUG “SELECT” journal in late 2003. This paper and presentation provides a clear understanding of the causes
of the dreaded ORA-01555 error message (i.e. “snapshot too old”), with a
side-trip through the parking lot at Disney World, and what can (and cannot)
be done to minimize or prevent it. Updated on
27-August 2003 |
|
Unraveling the
Sweater – Oracle Database Security Part 1 –
Adobe Acrobat white paper – 134Kb Part 2 –
Adobe Acrobat white paper – 144Kb |
Authored by Tim Gorman These articles were printed in the Winter 2003 and Spring 2003 issues of the RMOUG newsletter. Part 1 examines common issues in database security, namely loopholes and user authentication. The “oraprobe.sh” shell script is provided as a tool for these topics. Part 2 examines the security from the network and the TNS Listener in particular. The “tnsprobe.sh” shell script is provided as a testing utility described by this article for these topics. Added on 28-May 2003 |
|
Oracle9i Table Compression MS-PowerPoint
slide show – 108Kb |
Authored by Tim Gorman This 30-minute “quick-tip” was presented at the IOUG-A “Live 2003”
conference in Table compression is a new feature with Oracle9i, with attractive possibilities particularly for data warehouses and data archival situations. The presentation describes the mechanism used by Oracle9i R2 and above for compressing table data, indicating situations where the feature works well and where it does not, based on testing performed. Added on 09-May 2003 |
|
In Defense of
FULL Table Scans ·
MS-Word white paper – 116Kb ·
MS-PowerPoint
slide show – 457Kb ·
WinZip
archive containing both white paper and slide show – 391Kb |
Authored by Jeff Maresh This paper was presented at a general meeting of
the Rocky Mountain Oracle Users Group (RMOUG) during the Summer of 2000, and
again at the Pikes Peak Users Group (PPOUG) Training Day during the fall of
2002. Using a simple case study, this paper shows the
performance of full-table scans compared with index access methods. The clustering factor optimizer statistic
is used to show the differences in index performance. Guidelines and caveats for using full-table
scans are also discussed. Updated on
09-April 2003 |
|
Using Oracle8i and Oracle9i Log Miner ·
MS-Word white paper – 85Kb ·
MS-PowerPoint slide show – 170Kb ·
WinZip archive containing both white
paper and slide show – 94Kb |
Authored by Tim Gorman This was presented at the Utah Oracle Users Group (UTOUG) “Vendor Day 2002” conference on 01 October 2002 in Salt Lake City UT, at the Los Angeles Oracle Users Group (LAOUG) meeting on 21 November 2002 in Norwalk, CA, at the RMOUG “Training Days 2003” in March 2003 in Denver, CO, and at the IOUG-A “Live 2003” conference in April 2003 in Orlando, FL. This presentation provides a roadmap of the evolution of Log Miner from
its introduction in Oracle8i as a debugging tool into its current
metamorphosis into a critical component of log-based replication features
like Logical Standby Databases and Oracle Streams. Updated on 09-May 2003 |
|
Optimizing
Parallel Query in Oracle8i ·
MS-Powerpoint slide show – 173Kb ·
MS-Word document - 239Kb ·
WinZip archive with both paper and presentation -
194Kb |
Authored by Jeff Maresh Jeff originally presented this
paper at the Rocky Mtn Oracle Users Group (RMOUG) "Training Days
2001" conference at the |
|
Succeeding
with RMAN ·
MS-PowerPoint
slide show - 135Kb |
Authored by Tim Gorman This was presented at Los Angeles Oracle Users Group (LAOUG) on 21
November 2002 in This presentation loses the apologetic tone of its predecessor
presentation (“Is RMAN Really Worth The Trouble?”) and sets to prove
that RMAN is not only worth the effort but essential for fulfilling any
high-availability requirements for Oracle-based applications. Newly added on 28-November 2002 |
|
The Search for
Intelligent Life in the Cost-Based Optimizer ·
MS-Word
white paper – 83Kb ·
MS-PowerPoint
slide show – 131Kb |
Authored by Tim Gorman NOTE: This paper is ENTIRELY OBSOLETE on Oracle9i, Oracle10g, Oracle11g, and higher. It was written based on observations in Oracle8 and Oracle8i; please do not follow this paper’s recommendations regarding the OPTIMIZER_INDEX_* parameters on Oracle versions higher than 8.1.x. I am leaving this paper visible on the internet only so that this warning is visible. My apologies for any problems the recommendations in this out-of-date paper may have caused on Oracle9/10/11 databases. For Oracle9 and above, please instead use the procedure GATHER_SYSTEM_STATS in the standard Oracle DBMS_STATS package to achieve the same effect. This paper and presentation describes ways to make the cost-based
optimizer (CBO) come alive and start working.
The CBO has had a checkered past, but with enhancements in Oracle8 and
Oracle8i, it is finally time to put the venerable old rule-based
optimizer (RBO) to bed for good. Updated on 09-January 2009 |
|
Translation
of the paper “The Search for Intelligent Life in the Cost-Based
Optimizer” into Russian. |
Translated
by Eduard Shevtsov On his
own initiative and with my sincere gratitude… |
|
Is RMAN Really
Worth The Trouble? ·
Adobe Acrobat white paper - 32Kb ·
MS-PowerPoint slide show - 135Kb |
Authored by Tim Gorman This paper and presentation was presented at the Eastern Caribbean Oracle Users Group in Trinidad on 14 March 2001, at the Rocky Mtn Oracle Users Group (RMOUG) "Training Days 2000" on 10 February 2000 in Denver CO, and at the Ottawa Oracle Users Group “DBA/Developer Day” on 06 September 2002 in Ottawa ON. It explains the three important features of Oracle8 Recovery Manager that
absolutely no other backup/recovery solution is capable of matching, and why
these features might be crucial to help fulfill your requirements and
expectations. If you do not have these requirements or expectations, then
RMAN may not be worth the trouble to install and configure. But if you do
have these requirements, then RMAN may be the only viable solution for you. |
|
Understanding
Locking in Oracle · MS-PowerPoint presentation – 203Kb · Winzip archive containing slide show – 38Kb |
Authored by Tim Gorman This was presented at the Utah Oracle Users Group (UTOUG) DBA SIG on
26-April 2002 in |
|
SQL*Loader
Strategies for Data Warehouse Implementations · MS-Word white paper – 85Kb · MS-PowerPoint presentation – 153Kb ·
WinZip archive
with both paper and presentation – 50Kb |
Authored by Jeff Maresh This paper was presented at the RMOUG “Training Days 2002” conference on
20-21 February 2002 in |
|
Tuning Sort
and Hash Memory Areas ·
MS-Word
white paper – 31Kb |
Authored by Jeff Maresh This paper appeared in the “Winter 2001” issue of “SQL Update”, the
newsletter of the Rocky Mtn Oracle Users Group (RMOUG). It covers the little-known areas of
configuring and tuning the SORT_AREA_SIZE and HASH_AREA_SIZE parameters. |
|
Myths about
Extents and Performance ·
MS-Word white
paper – 65Kb |
Authored by Tim Gorman This paper appeared in the “Winter 2001” issue of “SQL Update”, the
newsletter of the Rocky Mtn Oracle Users Group (RMOUG). Although space management is a topic that
has been thoroughly covered by excellent papers by Cary Millsap and Craig
Shallahamer, I felt it was necessary to cover this ground one more time, to
debunk some of the most persistent myths in the world of Oracle technology. |
|
Oracle9i Data Guard ·
MS-PowerPoint slide
show – 135Kb |
Authored by Tim Gorman This was presented at the 09 November 2001 General meeting of the Rocky
Mtn Oracle Users Group (RMOUG) in This discusses the evolution of the Standby Database feature
introduced in Oracle7 v7.3 as it evolves into the more comprehensive Oracle9i
Data Guard product. This
presentation focused only on the Physical Standby Database features
introduced in Release 1 (v9.0.1) of Oracle9i; I expect to update it soon to include the Logical
Standby Database features of Release 2 (v9.2.0) in the near future… |
|
Choosing Among
High-Availability Architectures in Oracle ·
MS-Word white paper - 99Kb ·
MS-PowerPoint
slide show - 79Kb ·
WinZip archive with both paper and presentation - 46Kb |
Authored by Tim Gorman This
paper and presentation was presented at IOUG-A “Live 2001” on 02-May 2001 in It
explains the advantages and disadvantages of all of the current
high-availability options for Oracle8i and below, to help you make the
best architectural decision for your requirements. The purpose is to help you
choose the architectural option that best serves your expectations and
requirements. |
|
Performance
Problems Related to the Re-Parsing of SQL Statements ·
Adobe Acrobat white
paper - 22Kb |
Authored by Jeff Maresh This paper has been published in the newsletter of the Rocky Mtn Oracle
Users Group "SQL Update" as well as the magazine of the Int'l
Oracle Users Group - |
|
Oracle8i Virtual Private Databases ·
MS-PowerPoint slide show
- 83Kb |
Authored by Tim Gorman This was presented for the DBA SIG of the Utah Oracle Users Group (UTOUG) on 14 February 2001 in Salt Lake City, UT, at a general meeting of the Rocky Mountain Oracle Users Group (RMOUG) on 19 May 2000 in Golden, CO, and again at the annual meeting of the Maine'S Oracle Users Group (MSOUG) on 22 June 2000 in Sebasco Harbor Resort in Maine. It
explains the new VPD feature of Oracle8i, also known as row-level
security and fine-grain security. The mechanism used to implement
it is extremely interesting and I think that this feature will be utilized in
many creative ways possibly never imagined by Oracle Corporation! Comes with a very brief demo script that uses the standard SCOTT schema with
its ubiquitous EMP table. |
|
SQL Tuning for
DBAs ·
Adobe Acrobat white paper - 47Kb ·
MS-PowerPoint97 slide show - 101Kb ·
WinZip archive with both white paper and slide show -
63Kb |
Authored by Tim Gorman This presentation was presented at the Rocky Mtn Oracle Users Group (RMOUG) “Training Days 1996” conference in February 1996, at numerous Oracle Users Group conferences, at Oracle Open World Australasia 1997, and at various corporations as a private tutorial. It explains a methodology for determining where SQL tuning problems are
located and describes the tools that can be used to fix them. It predates (somewhat) the far-superior
YAPP methodology paper written by Mr. Anjo Kolk and available at http://www.oraperf.com/whitepapers.htm,
but it covers much of the same ground… |
|
Tuning A Data
Warehouse According To It's Usage ·
Abode Acrobat white paper - 50Kb ·
MS-PowerPoint97
slide show - 158Kb ·
WinZip
archive with both white paper and slide show - 61Kb |
Authored by Tim Gorman This was presented at Oracle Users Group conferences and at IOUG-A
"Live 99" in April 1999 in It describes an important piece of data warehouse infrastructure that is usually overlooked: SQL statement usage tracking and all of the benefits in performance tuning of the DW as well that become possible, as well as opportunities to detect the need for data model re-design due to changing end-user requirements. NOTE: the USAGE package mentioned in the
presentation is available below, in the "SQL Scripts" section… |
|
SQL*Net Basics ·
MS-PowerPoint97 slide show - 140Kb ·
WinZip archive - 39Kb |
Authored by Tim Gorman This 60-90 minute presentation is intended to provide a good basic
grounding in the way Oracle's SQL*Net product works; something I've never
found done adequately elsewhere. It briefly describes how connections are
established, the major components for a simple configuration, and some
debugging tips. |
|
RAID:
High-Performance, Reliable Secondary Storage ·
Adobe Acrobat white
paper - 280Kb |
Original definition of RAID technology by Peter Chen et al. |
|
Description of 10046 events and output ·
ASCII text document – 7Kb |
Original document by Richard Powell of Oracle |