About NoCOUG
Join NoCOUG
Sponsorship
Vendors
Online Survey
NoCOUG Board
Site Map


 

NoCOUG's November 13, 2003 Conference

 

The NoCOUG November 13, 2003 Conference was held at PG&E in San Francisco.  Some of the presentations from the meeting are available for download.

Conference Description

The November 13, 2003 Conference had two parallel tracks of technical presentations covering topics such as database administration, application development, and data warehousing.

Here is the agenda, followed by session descriptions:

8:00 - 9:00

- Registration and Continental Breakfast – Refreshments Served

9:00 - 9:30

- General Session - Vendor Introductions

9:45 - 10:45

- Parallel Session #1:

Auditorium

Room A / B

“Managing an Oracle Optimization Project” by Gary Goodman, Hotsos

 

Focus Area: DBA

“Frequently Asked Questions: A Smorgasbord of Common Questions and Problems Received by World Wide Support – and How to Resolve Them. (Part 1)” by David Austin, Oracle Corporation

Focus Area: DBA/Dev

10:45 - 11:15

- Break

11:15 - 12:15

- Parallel Session #2:

Auditorium

Room A / B

“Performance Problems from the Field” by Gary Goodman, Hotsos

 

Focus Area: DBA

“Frequently Asked Questions: A Smorgasbord of Common Questions and Problems Received by World Wide Support – and How to Resolve Them. (Part 2)” by David Austin, Oracle Corporation

Focus Area: DBA/Dev

12:15 - 1:15

- Lunch

1:15 - 2:15

- Parallel Session #3:

Auditorium

Room A / B

"What a DBA Needs to Know about Oracle’s Bitmap Indexing to Retrieve Data Quickly." by Vilin Roufchaie, Cingular Wireless

Focus Area: DBA

“Help! I Got a Request for ANSI SQL – What Is It, and What Do You Do With It?” by Walter Guerrero, Computer Associates

Focus Area: DBA/Dev

2:15  - 2:30

- Break

2:30  - 3:30

- Parallel Session #4:

Auditorium

Room A / B

“What a DBA Needs to Know about Oracle’s “Star Transformation Query” Processing in a Star Schema.” by Vilin Roufchaie, Cingular Wireless

Focus Area: DBA

“How Statspack Was Used to Solve Common Performance Issues” by Brian Hitchcock, Sun Microsystems

Focus Area: DBA/Dev

3:30 - 4:00

- Break and Raffle

4:00 - 5:00

- Parallel Session #5:

Auditorium

Room A / B

“A Quantitative Basis for Measuring Data Quality Using Metadata” by John Murphy, Apex Solutions, Inc.

Focus Area: DBA

“Working With Partitioned Tables  -- The Unpleasant Details” by Brian Hitchcock, Sun Microsystems

Focus Area: DBA/Dev

5:00 - …

- NoCOUG networking and happy hour at Beale Street Bar & Grill, 133 Beale St, San Francisco, CA (Leaving the conference, walk left on Beale about one block.)

 


 

"Managing an Oracle Optimization Project" Many business leaders lack the background to effectively manage Oracle performance issues.  This presentation describes a proven method for targeting and prioritizing optimization investments based on profit, ROI and cash flow, not system metrics.  We will explore many of the common missteps in dealing with Oracle optimization and explain why they often fail while wasting time and money.

 

"Performance Problems from the Field" This will be a very interactive presentation where we will review 3 actual case studies from our field experience at Hotsos.  We will examine our client's performance problems and how a user-action based method was utilized to rapidly determine and repair the root cause issues.  We will use these case studies to show the effectiveness of using 10046 level 8 trace data as the basis for Oracle optimization.

 

"What a DBA Needs to Know about Oracle’s Bitmap Indexing to Retrieve Data Quickly."  (This presentation is for Data Warehouse designers, as well as performance DBAs and capacity planners). Bitmapped indexing is a query execution optimization technique predominantly used in Data Warehousing and DSS environments. The details of bitmap indexing will be discussed and fully illustrated, including:

- Contrasting index entries with rowids (as in a B-tree) against bitmaps

- How bitmap vectors map, and convert, into rowids

- Performance implications of concurrent SQL operations on bitmapped index columns

- B-tree vs. Bitmap index, when should one be opted over the other

- Storage requirements of bitmap indexes on table columns with varying cardinalities

- Bitmap index creation in parallel mode

- Oracle’s bitmap compression overview

- Bitmap access methods details

- How Oracle handles various types of predicates, such as AND/OR/=/!=/RANGE, etc., and how it combines these predicates in a multi-predicate query’s where clause.

- What makes bitmapped indexes so powerful?

 

"What a DBA Needs to Know about Oracle’s “Star Transformation Query” Processing in a Star Schema." (This presentation is for Data Warehouse designers, as well as performance DBAs and capacity planners). “Star Transformation Query” (STQ) was launched by Oracle Corporation in Oracle 8 as a result of “Star Query’s” (SQ) inability to efficiently and cost effectively address the needs of certain Star Schemas due to excessive number of join operations it had to perform between unmatched column values from dimension and fact tables.  In this presentation I will delve into the heart of STQ and demonstrate how it works and what conditions would warrant its utilization to expedite query processing, followed by illustration and coverage of a real-world benchmark and Explain plan details.  Time permitting, I will detail the pros and cons of STQ and SQ, and demonstrate how these two methods can actually complement each other by addressing SQLs of varying characteristics.  The following topics will be covered:

- Combining indexes to rapidly handle joins

- What’s STQ and what will it take to enable it in Oracle

- The main two passes performed by the optimizer (semi-join & join)

- STQ summary of execution steps (Query rewrite, bitmap key iteration, join-back elimination, etc.)

- STQ benchmark case study

- Star Transformation Query vs. Star Query, what criteria to follow to choose one over the other.

 

"A Quantitative Basis for Measuring Data Quality Using Metadata" This presentation will explore the relationship between metadata, data standardization and data quality.  Metadata can act as a source of record to baseline data content and identifying specific key points in the information supply chain to audit and validate specific data content. Performing data audits within the information supply chain can result in the rapid identification and management of data irregularities.  The development of statistics related to data distribution and domain values along with historical trending of data values offers a mechanism to quantitatively determine data validity and accuracy under various conditions.  Data management teams can maintain credibility with their analytical or end user communities by creating certified data loads based on business-defined metadata attributes.  Developing automated mechanisms during acquisition and ETL processes for auditing and validating data is crucial to ensure minimal impact on the end-to-end processes supported.

 

The presentation describes:

 - Data quality as related to metadata

 - Development and management of data quality statistics

 - Metadata attributes used in data quality audits and assessments

 - Data Quality check points in the information supply chain.

 - Developing data quality baselines

 - Use of metadata in a data quality improvement program

 - Compliance and Data Integrity

 

Take aways include:

 - A Data and information validation topography in an example information supply chain.

 - A Sample metamodel to support data quality metadata components

 - Data quality calculations for developing baseline statistics

 - Data quality planning worksheets

 - A brief data quality vendor review

 - Data Quality and metadata bibliography

 

"Frequently Asked Questions: A Smorgasbord of Common Questions and Problems Received by World Wide Support and How to Resolve Them." The presentation covers a variety of topics that, based on statistics compiled by World Wide Support, cause problems for many database administrators.  A best practice or working solution is provided for each problem, in some cases, some of which may be release dependent.  The topics are not offered in any particular order and the audience is encouraged to select from available topics and to add their own contributions to the discussion of the problems and possible solutions.

 

"Help! I Got a Request for ANSI SQL - What Is It, and What Do You Do With It?" If you are starting to work with XSQL or SQLX, this is a presentation you will not want to miss.  As companies deploy more XML and exchange information across many different database types, they will need to implement ISO standards within their business models.  SQL statements must then move away from proprietary format and fit within the ISO/ANSI standards.  These ISO/ANSI standards provide a more readable and portable format that can be used by different database engines.  Oracle is making the move for Oracle developers to start using ISO/ANSI SQL constructs in the creation of SQL statements to exchange data between XML style sheets.  Even after changing the SQL statements into the ISO/ANSI format, developers still need to be concerned with the performance needs of the SQL statements running in an Oracle database server.  This session will educate the attendees so they can determine when and how to start using the new constructs as well as potential performance issues.

 

"How Statspack was Used to Solve Common Performance Issues" During the last year 4 specific performance issues came up and STATSPACK data was reviewed in each case.  In two of the cases, the STATSPACK output did not directly identify the performance issue.  In the two other cases, the STATSPACK output did result in significant performance improvements.  Even when the output did not directly lead to the cause of the performance issue, it did verify that many possible performance issues did not exist in the instance.  For the two cases where the STATSPACK output did lead to the root cause, the details of how this was done are examined.  The method of calculating the total run time and the wait event time are detailed.  Once the root cause was identified, how the performance issue was resolved is discussed.

 

"Working with Partitioned Tables -- The Unpleasant Details" Partitioned tables provide several significant performance benefits.  They also present several administrative headaches that will be reviewed from actual experience.  The challenges for the DBA become apparent as the process to move from one partitioning scheme to another is reviewed.  The details of such a move are covered as well as the mistakes made and how they were resolved. Such details include the syntax of the SQL to split existing partitions, fixing the minor mistakes made when the initial partitioning scheme was setup and moving the tablespaces as you change the table partitioning scheme.  All of which came from the experience of supporting an upgrade of a major third-party application.

 


If you have suggestions for future meetings or would like to offer feedback on previous conferences, then please complete our online survey or send us an email.

Directions to PG&E in downtown San Francisco

Address: 77 Beale Street, San Francisco, CA 94105
Upon arrival, sign in at the NoCOUG table.

From BART: Exit the Embarcadero station. Walk approximately one half block down Beale street.

From Bay Bridge: Exit on Harrison, continue onto Fremont.  Turn right on Market, then right on Beale.

From Golden Gate Bridge: Exit on Marina, continue onto Laguna.  Turn left on Bay, right on Columbus, right on Montgomery, left on Clay, right on Davis, then left on Beale.

Map

 

Copyright 2003 NoCOUG. All rights reserved.