Explain Plan - MSSQL style
By Christian Adams

I work with MS SQL Server as well as Oracle, and I like the information SQL Server's Query Analyzer gives you when you request the execution plan for a query. It shows you the cost of each task, the total cost of all the child tasks under each task (subcost), and percentages of these two figures relative to the total cost of the query.

Conversely, Oracle's EXPLAIN PLAN combines the cost of each task with the cost of it's children, which sometimes makes it tricky to interpret which individual tasks are more expensive than others.

I have a script I found a while back, EXPLAIN.SQL, which runs EXPLAIN PLAN against the query currently in the SQL*PLUS query buffer. I modified it to separate each task's cost from the costs of its children, and to compute the percentages.  Click Here for the script.
  

Transferring Alerts in Oracle Application
By Shankar Govindan

Oracle Application has so many modules that each module that you have licensed needs to be setup at the functional level for it to work and then tested for the functionality. The setup then goes in for a User Acceptance test. Once accepted it moves to production. Since the setups need to be done each time manually and is error prone, there are Object Migration tools that are available to migrate the required setups from one Application database to another. For Oracle Alerts though, oracle has its own transfer utility with which you can setup and test the Alerts in a test database and transfer the same to a QNA database and from there transfer to production.

I have broken this document into two sections. One that shows how to transfer Oracle Alerts and the other that talks about the basics and setting up a separate manager to run these Oracle Alerts to reduce the request clogs.

Click Here for the paper.  
 

Index Design for Performance
By Gbrielle Wiorkowski, www.gabrielleDB2.com

This article is an excerpt from the Index Design for Performance chapter of Gabrielle's latest book DB2 for z/OS & OS/390 Development for Performance. This 2 volume book is available from www.gabrielleDB2.com and Amazon.com

It is important for both DBAs and programmer/analysts to understand how to use indexes. DBAs are usually concerned with physical design and maintenance, and programmer/analysts must understand how to write SQL statements that make the best use of indexes. Chapter IV describes the methods used by the index manager to efficiently locate rows.

This chapter describes issues related to designing indexes. It identifies the characteristics of columns that should and should not be indexed, and it describes the characteristics of columns that benefit the most from having a clustering index. Information about designing composite indexes are addressed.

Click Here for the excerpt.
 

Loading External XML Files 
Topic Extracted from Knowledge Xpert for PL/SQL™

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, “How do I get XML files in there?” Here you’ll start simple and work through the steps of loading an existing XML file into a CLOB column.

CLOB (pronounced “klob” or “see-lob”) stands for C haracter L arge OB ject. Think of a column of type CLOB as a very, very large VARCHAR2. It can hold character-based data like XML documents as large as four gigabytes (4GB).

Click Here for the topic.
 

Microsoft T-SQL Performance Tuning - Part 1:  
Analyzing & Optimizing T-SQL Query Performance on Microsoft SQL Server using SET & DBCC
By Kevin Kline, Quest Software

This article is the first in a series that describes a variety of performance tuning techniques that you can apply to your Microsoft SQL Server Transact-SQL programs.  In many cases, you can use the graphical user interface provided in Microsoft SQL Enterprise Manager or Microsoft SQL Query Analyzer to achieve the same or similar results.  However, this series focuses on using Transact-SQL as the basis for our solutions.

SQL Server provides you with capabilities to benchmark transactions by sampling I/O activity and elapsed execution time using certain SET and DBCC commands.  In addition, some DBCC commands may be used to obtain a very detailed explanation of any index statistic, estimate the cost of every possible execution plan, and boost performance.

Click Here for the article.  
 

Communicating Status
Tom Mochal, www.tenstep.com 
Each month, Tom Mochal presents a set of project management tips and techniques for handling various aspects of planning and managing a project.  Tom has over 23 years of IT experience.  He has developed a comprehensive, scalable project management process called TenStep, which can be viewed at www.tenstep.com

Properly communicating on a project is a critical success factor for managing the expectations of the customer and the stakeholders. If these people are not kept well informed of the project progress there is a much greater chance of problems and difficulties due to differing levels of expectations. In fact, in many cases where conflicts arise, it is not because of the actual problem, but because the customer or manager was surprised.  Click Here for tips and techniques on communicating status.
 

TOAD User Groups in 20 Cities - Sign Up Today!
 

Quest Software is proud to present the 2002 TOAD User Groups, where TOAD and his entourage are hitting the road and coming to a city near you.  That's right, we are bringing you a series of free, full-day local user groups to help you get more out of your favorite development tool and exchange ideas with the real TOAD experts -- users like you!  Click Here to register.

Interactive Crossword Puzzle: "Star Wars"   

1 Down - Oracle, for Example

Test your knowledge with the Pipeline Newsletter's Monthly Crossword Puzzle.  Click Here to Play! Correction to last month's puzzle: 4 Down should be Bonesaw, not Hacksaw.

We love getting white papers, tips, articles, and code examples/archives from our readers around the world.  Send your submission to newsletter@quest-pipelines.com.  If your article is published, we will mail you a FREE gift from Quest as a gesture or our appreciation.

Our mission at Quest Software - RevealNet Labs is to anticipate the daily responsibilities and challenges faced by database professionals.  Our products help thousands of people solve problems and implement solutions every day.  This newsletter is designed to help facilitate the sharing of information among database professionals.  It is currently circulated to 28,000 readers.

About the Newsletter

This newsletter is distributed to Quest Software - RevealNet Labs customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please send a note to unsubscribe@quest-pipelines.com and we will make every effort to remove you from our list.

Subscribe a Friend!

Do you know someone who would like to receive the Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.

Past Issues of the Pipeline Newsletter

June, 2002 May, 2002 April, 2002 March, 2002

February, 2002 November, 2001 October, 2001 September, 2001

August, 2001 July, 2001 June, 2001 May, 2001