 |
 |
| |
Consulting :: We are experts in Oracle Database Solutions with the experience and knowledge to bring any project to completion, applying best practices.
Remote DBA Services :: Database Specialists company provides top of the line expertise in remote database administration backed up by over 15 years of experience.
Database Rx :: Our platform remotely track, monitor and manage client's databases. Our clients can access information and receive 24/7 live support anytime, anywhere. |
|
|
 |
 |
| |
|
HUMOR |
|
Database Administrator
Builds Database Using Nose
Who says
database administrators don't have a funny bone?
Watch this
YouTube video of a database administrator
building an Oracle database with his nose. We're
sure you'll enjoy it as much as we did.
|
|
TUNING LIMBO: HOW LOW CAN YOU GO? |
|
It Takes Two to Tango
Iggy Fernandez of the Database Specialists team
contributed this installment of Tuning Limbo
from his upcoming book on Oracle 11g
database administration. The winner will receive
their choice of an iPod Shuffle or a $50 Amazon
gift certificate, mailed anywhere in the world.
Consider two tables, modeled after
DBA_TABLES and DBA_INDEXES respectively; the
first table contains information about
tables and the second table contains
information about indexes. The requirement
is to print the details of all tables which
have at least one bitmap index.
Here is
one possible solution:
SELECT DISTINCT t.owner,
t.table_name,
t.tablespace_name
FROM my_tables t, my_indexes i
WHERE t.owner = i.table_owner
AND t.table_name = i.table_name
AND i.index_type = 'BITMAP';
Any
solution is only as good as the number
of data blocks it needs to read in order
to find the data; the fewer the better.
That's why we call it the tuning limbo.
Refer to the helpful
hints and submission instructions on
our website and send your solution to
askdbspecialists@dbspecialists.com; all
options are on the table, including
statistics, hints, optimizer settings,
and any other tricks that you may have
up your sleeve.
The contest ends on May 14, 2008, and
the judge's decision is final!
|
|
STUMP THE SPECIALISTS |
|
Efficiency Ratios Redux
This month's question came to us from one of
our clients:
"The rollback per transaction percentage in one
of my databases is very high, according to the
Statspack reports. What should I do about it?"
Terry Sutton of the Database Specialists
team responds:
In general,
efficiency ratios are not a reliable indicator of
database efficiency; Cary Millsap explains this in a
paper titled "Why
a 99%+ Database Buffer Cache Hit Ratio is Not
Ok." Focus on the "Top Timed Events," "SQL
ordered by Gets," and "SQL ordered by Reads"
sections of the Statspack report instead of
efficiency ratios because the biggest performance
gains come from focusing on the biggest components
of the database workload (the sum of the elapsed
times of all the SQL queries processed during the
period of concern). If you are using Oracle Database
10g or 11g, ASH (Active Session
History), AWR (Automatic Workload Repository), and
ADDM (Automatic Database Diagnostic Monitor) are
even better tools for the purpose though Enterprise
Edition and Diagnostics Pack are required in order
to use them.
To answer your specific question, the "rollback
per transaction percentage" metric is defined in
spcpkg.sql (Statspack installation script) and
spcrepins.sql (Statspack report generation
script); it is the percentage of rollback
operations initiated by users in the total of
rollback and commit operations initiated by
users. Note that a rollback operation may
not find any changes that need to be undone; the
number of operations that actually find changes
that need to be undone is tracked by the
"transaction rollbacks" metric which is also
recorded in the Statspack report. Another metric
to watch is "rollback changes
- undo records applied" which is an even
better measure of rollback activity.
Why are there so
many rollback operations? Perhaps the application is
trying to be database agnostic. Other databases such
as Microsoft SQL Server and IBM DB2 lock data when
it is read by a user; a rollback operation would
therefore be the appropriate way to unlock the data.
The more important question is whether you will
improve database efficiency by eliminating the
rollback operations; as we have said already, focus
on the "Top Timed Events," "SQL ordered by Gets,"
and "SQL ordered by Reads" sections of the Statspack
report instead of efficiency ratios. You can find a
lot of information on interpreting timed events in
Interpreting Wait Events to Boost System Performance
by Roger Schrag and
More Examples of Interpreting Wait Events to Boost
System Performance by Roger Schrag and Terry
Sutton.
I
hope this short answer helps you. Best of luck to
you.
|
|
PERFORMANCE TUNING BY EXAMPLE |
|
The Case of the Invalidated Index
In his most excellent book
The Art and Science of Oracle Performance
Tuning, Chris Lawson, a past member
of the Database Specialists team, outlined a
systematic five-step method for solving any
performance tuning problem. Oracle Database
versions may change and software tools may
change but the five steps always remain the
same. A problem may be simple and require only a
few minutes of your time or it may be
tremendously complex and require weeks of your
time but the five steps always remain the same.
Every month, The Specialist presents a summary
of a case study from the Performance Tuning by
Example series of case studies created by the
Database Specialists team.
-
The first step was to
define
the problem; after patient listening and
careful observation it was determined that
the problem was restricted to certain parts
of the application only.
-
The second step was to
investigate
the problem and collect as much pertinent
evidence as possible; this was an internet
application and the appropriate
investigative technique was the one
described in
Tracing Individual Users in
Connection-pooled Environments with Oracle
10g by Terry Sutton.
-
The third step was to isolate
the cause of the performance problem by analyzing
the data collected in the second
step; we used the tkprof utility
and quickly homed in on the SQL statement
that was at the heart of the problem.
-
The fourth step was to
solve
the problem by creating a solution that
addresses the root cause. The Execution
Plan Changes section of the Database Rx
Performance Portal told us that the
execution plan for the SQL statement had
changed; it was no longer using an
index. The Alerts and Events
sections of the portal listed an
unusable index. The database administrator
confirmed that maintenance had been
performed on the table to reduce its size;
the MOVE operation on the table had
invalidated the index. The solution would
be to rebuild the index.
-
The fifth step was to
implement
the solution in a safe and controlled manner
with the appropriate amount of testing and
"before" and "after" measurements; we
postponed the rebuilding activity to the
late evening to mitigate locking activity.
In summary, we see that, no matter how simple or
complex the performance problem, the steps
leading to its solution are always the same.
Read the
original paper written by Chris Lawson and
take the Database Rx Performance Portal for a
test drive if you like.
|
|
DOING IT RIGHT |
|
Use Historical Information When Adding Space to
Databases
In the absence of historical information, the
database administrator does not know whether newly
added space will suffice for a day, a week, a month,
or a year. The Database Rx monitoring agent
regularly checks the sizes of all the tablespaces in
a database and stores the information in the
Database Rx performance portal; you can take
the portal for a
test drive if you like.
|
|
SCRIPT OF THE MONTH |
|
Historical Information in
Statspack Tables
Most of us are content with using the spreport.sql
script to generate reports on individual pairs of
Statspack snapshots but Tim Gorman never loses the
opportunity to remind anyone who is willing to
listen that the Statspack tables are a gold mine of
historical information. Check out his
sp_systime_9i.sql and
sp_systime_8i.sql scripts; they display the
components of response time for each hour and for
each day for which information is available;
historical information such as this is invaluable in
diagnosing and solving performance problems.
|
|
BEST OF THE WEB |
|
|
Oracle Database 11g:
The Top New Features for DBAs and Developers
|
|
GEMS FROM THE ARCHIVE |
|
|
Interpreting Wait Events
|
|
CONFERENCE ROUNDUP |
|
RMOUG Training Days 2008
RMOUG Training Days was held on February 13 and 18
at the Colorado Convention Center in Denver;
Jonathan Lewis' presentation Playing Russian
Roulette with Silver Bullets alone was worth
the price of admission. Iggy Fernandez represented
Database Specialists on the technical agenda with
two presentations: Oracle Annoyances for Geeks:
Deadlocks and Livelocks, and Optimal Query
Execution Plans: An Impossible Dream?
If you were not
one of the lucky few who could attend, you can
download most of the whitepapers
and slide presentations from the RMOUG website
and, if you live in the San Francisco Bay Area, you
can attend the spring conference of the Northern
California Oracle Users Group where Rich Niemiec
will be delivering the same keynote address that he
delivered at RMOUG: How Oracle Came to Rule the
Database World.
NoCOUG Winter Conference
The Northern California Oracle Users
Group held its winter conference on February
19 at the Oracle Conference Center in
Redwood Shores. The keynote address was
delivered by Juan Loiaza, a
twenty-year Oracle veteran who knows more
about Oracle's vision and strategy than most
people. Most of the
whitepapers and slide presentations can
be downloaded from the NoCOUG website.
NoCOUG Spring Conference
The Northern California
Oracle Users Group will
hold its spring
conference, a whole day
event, on May 15 at the
Crowne Plaza hotel in
Foster City. The keynote
address How Oracle
Came to Rule the
Database World will
be delivered by Rich
Niemiec, author of
several books on Oracle
and CEO of TUSC; Rich
will also deliver a talk
on the new features of
Oracle 11g. If you work
with SQL a lot, you
won't want to miss Dan
Tow's presentations:
Natural Data Clustering:
Why Nested Loops Win So
Often and
Getting SQL Right the
First Try. The
complete agenda can be
found on the
NoCOUG website.
Rich Headrick of the
Database Specialists
team reports that he was
able to able to make
dramatic performance
improvements in a RAC
database system by using the
simple suggestions made by a
speaker at a past NoCOUG
conference. An annual NoCOUG
membership costs only $80
and includes free admission
to four whole day
conferences and four issues
of the NoCOUG Journal.
Walk-ins are welcome at the
conferences; pay just $40
for the day or purchase an
annual membership at the
registration desk.
COLLABORATE '08
The joint annual conference of
the Independent Oracle Users
Group (IOUG), the Oracle
Applications Users Group (OAUG),
and Quest International
Users Group will be held from
April 13 to April 17 at
the Colorado Convention Center
in Denver. Iggy Fernandez will
represent Database Specialists
on the technical agenda with
three presentations: Stop
Doing Transactions Wrong!,
Oracle Annoyances for Geeks:
Deadlocks and Livelocks,
and Optimal Query Execution
Plans: An Impossible Dream?
More information on COLLABORATE
'08 can be found on the
IOUG website.
|
|
|
|
|
|
 |
|
 |
|
 |
