| April, 2002 |
|
|||||||||||||
|
All articles have been reprinted with the written consent of their respective authors.
| |||||||||||||
|
|||||||||||||
|
Space Check Before Data LOAD (DW) Running a script like this before loading millions of records will help the person loading get a fair idea of how much space is required to sustain the load and not fail. This also helps the person loading estimate and inform the DBA that a load is to happen and space does not seem to be sufficient in a tablespace where the loading table exists. Basically, the script gives us the approximate space required for the load based upon the size of the records existing in the table multiplied by the number of records. So, the table needs to have some data in order to get this information and calculate. This will not work for a table that is empty. To get the exact values, we need to
analyze the table. Once this is done, the DBA_TABLE views get
populated with the AVG_ROW_LENGTH and then we calculate from there.
The analyze for a table of DW size would run for hours and that would also
upset the query plans. Click Here for the script. |
|||||||||||||
|
|||||||||||||
|
Migrating to Oracle9i The long awaited moment approaches. The long heralded release of Oracle9i into production has/is happening. So, what do we as DBAs and application developers do? We migrate. Of course we should take the proper steps to ensure that we migrate like graceful geese flying across a sunset rather than lemmings dashing forward over a cliff. At TUSC we prefer the graceful flight across the sunset. Click Here for the paper. Tuning Without Cache-Hit Ratios From time immemorial, Oracle performance tuning has seemed like witchcraft, secretly practiced by an elite group of individuals, who appear to use voodoo to cure the performance problems of an Oracle system. To compound this misperception, many thousands of pages of published material has been written that propagates the idea of tuning Oracle with cache-hit ratios. There are many references to how performance is good when the ratios are high and, when those ratios are below a "desired percentage" there is great cause for concern. All of this causes confusion, with very scarce mention about unearthing the actual source of the bottlenecks that are inflicting performance pain on the entire system. Oracle tuning efforts should be based on isolating and pinpointing bottlenecks (the disease) not cache-hit ratios (the symptoms). The purpose of this paper is to dispel any misconceptions that correlate Oracle database performance with cache-hit ratios. It also provides a methodology that guarantees repeated success in unearthing the actual source of performance problems. Click Here for the paper.
|
|||||||||||||
|
|||||||||||||
|
Building a Scalable e-Business Platform In today's fluctuating economy, any strategic investment must be given careful consideration. Building an information technology infrastructure that is only focused on serving the needs of today, could spell disaster for tomorrow. Companies deploying e-commerce must insure that the building blocks of that infrastructure will also expand to meet their future challenges, maximizing their return on investment. In short, they want a cost-effective platform that will meet their growing demands. Today's business environment carries enough risk and uncertainty without building an e-business infrastructure on unknowns. It is important to have an infrastructure that will grow in a predictable manner to serve the dynamic business needs. This paper documents the
highlights of a joint effort by IBM, Quest Software, and Intel Corporation to
demonstrate how IBM's e-business software, WebSphere and DB2, running on IBM's
Intel-based servers, provides such an infrastructure. A set of robust
benchmark tests, designed to emulate an online brokerage firm, were conducted at
IBM's xSeries Teraplex Integration Center to measure the scalability
characteristics of representative configurations as they grow to service more
users and more transactions. Quest's Benchmark FactoryTM was used to
simulate volumes of customers accessing trading services via web browsers.
Test results demonstrated convincingly that WebSphere and DB2 running on IBM
Intel-based servers deliver a scalable, cost effective solution with the
predictability that is critical to manage growing e-business systems. Click Here for the paper. |
|||||||||||||
|
|||||||||||||
|
Data Modeling, Part 3: Reality Requires Super
and Sub Types Welcome to this third paper in an ongoing series regarding common data modeling mistakes and their impact. The key premise throughout is that poor database design:
Each paper in the series focuses upon a narrow range of the more common and costly data modeling mistakes, exposing their impacts to project success and espousing sound data modeling practices to overcome them. This issue we’ll examine the often overlooked and under utilized technique of entity super and sub typing, also known as generalization hierarchies or inheritance – with the goal of answering two key questions:
Click Here for the paper. Introduction to Oracle8i Analytic Functions Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which by its very nature is usually less efficient than a single SQL statement. There are three types of SQL extensions that fall under the banner of "analytic functions" (though the first could be said to provide "analytic functionality" rather than actually be analytic functions):
Each of these will be dealt with in turn. Click Here for the paper. |
|||||||||||||
|
|||||||||||||
|
The Curse and Blessings of
Dynamic SQL In this article I will discuss the use of dynamic SQL in stored procedures in MS SQL Server, and I will show that this is a powerful feature that should be used with care. I first recapitulate why we use stored procedures at all, before I explain the feature as such. I then look at the conflicts between the virtues of stored procedures and the effects of dynamic SQL. There are two sections of other general problems with dynamic SQL. I conclude with discussing several cases where dynamic SQL is often given as a solution, but not always is a good solution, and I discuss alternative strategies for these problems. Click Here for the article. Product Update: The
Knowledge Xpert for SQL Server v1.1 fully integrates with Microsoft's Visual
Studio suite, including Visual Basic, Visual C++, and Visual
InterDev. Click Here
for the announcement. |
|||||||||||||
|
|||||||||||||
|
Getting a Project Back on
Schedule Just because you monitor your
project on an ongoing basis does not mean that you will never miss
deadlines. The good thing about managing the workplan is that you will
know very quickly if you are trending over the end date. This will give
you an opportunity to put a proactive plan in place to get back on
schedule. A project trending over its deadline is always caused by the
critical path trending over its deadline. There is not a simple process
that will do the trick in every case. However, there are some techniques
you can apply to get the job done. Click Here for this month's article. |
|||||||||||||
|
|||||||||||||
|
TOAD User Groups in 20 Cities -
Sign Up Today!
Free e-Seminars
|
|||||||||||||
|
|||||||||||||
|
|||||||||||||
|
|||||||||||||
|
Interactive Crossword Puzzle: "High Minded"
|
|||||||||||||
|
|||||||||||||
We love getting white papers, tips, articles, and code examples/archives from our readers around the world. Send your submission to campbell.white@quest.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 25,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 info@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
|
|||||||||||||
