|
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:
|
|
10:45 - 11:15
|
- Break
|
|
11:15 - 12:15
|
- Parallel Session #2:
|
|
12:15 - 1:15
|
- Lunch
|
|
1:15 - 2:15
|
- Parallel Session #3:
|
|
2:15
- 2:30
|
- Break
|
|
2:30
- 3:30
|
- Parallel Session #4:
|
|
3:30 - 4:00
|
- Break and Raffle
|
|
4:00 - 5:00
|
- Parallel Session #5:
|
|
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.
|
