business and management

Want a FAST Database?

Take a Drive on the M25!
by Connor McDonald

Log on to any public database forum as a metaphor for our database. what a mistake that assumption is.
nowadays, and more often than not, a So (with apologies in advance as I distort Similarly, your monitoring tools need to
technical question will be posted along the metaphor for my own needs) let us take peaks hour into account. Nobody
the lines of: explore what driving on the M25 can will care if performance looks good when
teach you about running a successful averaged over (say) a 24-hour or weekly
“HELP! I have a performance problem! database. timescale, if they are suffering during
I’ve also noticed my parallel mutex latch peak hours.
has 154,245 hits and 17,243 misses. “Peak Hour Blues”
How can I speed things up?” I absolutely love driving on the M25! I Conversely, think of the benefits if only
can drive at 70mph – the fastest I can go we could convince people not to drive
to which shortly thereafter, you’ll anywhere in the UK; there are no corners, their cars during peak hours. From a
inevitably see the cryptic reply… traffic lights, or pedestrians to hinder my database perspective, it’s a case of trying
progress; just long, sweeping bends as I to defer work to off-peak hours. In its
“Add 37 to the hidden spin count cross from one side of London to the simplest form, this could mean asking
parameter and restart the database” other that serves perfectly as a gateway to staff to schedule large jobs overnight,
the rest of the country. ranging up to sophisticated solutions such
…drawing gasps of amazement from the as suites of materialised views generated
rest of the mere mortals subscribing to the Most London commuters probably don’t overnight to give less query resource
forum, who have never heard of a parallel have similar experiences of the M25, in consumption during the day, or using 9i
mutex* latch or spin count, let alone fact, the previous paragraph may have resource management to disallow
arrive at the magical answer of 37! caused some to choke on their coffee! resource hogs from launching during
Unfortunately, whilst superficially Of course, I did neglect to mention that peak hour timeslots.
impressive, the question (and the supplied because I take the train to and from work,
answer) are typically irrelevant to tuning I never ever drive on the M25 during the But I stress again, distributing the load is
a database. Diving into the bowels of working week. much more easily tackled at the planning
Oracle internals is an entertaining stage. Remember the massive public
pastime, but as the database engine Many drivers hate the M25 with a backlash when a dedicated bus lane was
becomes more feature rich and complex, passion because they suffer at peak hours, proposed for the M25. They couldn’t see
many Oracle professionals have forgotten and your databases are no different. that the total throughput of traffic would
the most important weapon in designing, It (and you!) will be judged on its improve – only that their individual
and implementing a successful Oracle performance under high demand, not on journey could be impacted. If the bus lane
database – that is, commonsense. how it performs at off-peak times. You had been there from the start, there
must address this in the planning stage of would have been no opportunity for such
This article aims to encourage you step your project. Many sites make the a backlash to occur.
back from all of that complexity, and mistake of assuming that database load
remind you of the simple but important will be balanced throughout the day, or “Accident Ahead:
considerations for successfully running an week, and hence under-estimate the Lane closed “
Oracle database. To prove that we do resources that their database will need. The dreaded red cross indicating lane
not need to tackle this from a technical All they need to do is look out of the closure strikes fear into every M25 driver.
perspective, I’ll use the M25 (or as it is window at the M25 on a Monday Their 30 minute journey is about to
better known “Britain’s largest car park”) morning or Friday afternoon to realise become a dire six hour ordeal. Wouldn’t

* Before anyone runs to the manuals, to my knowledge there is no such thing as a "parallel mutex" latch
28 Oracle Scene Issue 10 Summer 2002 / The UK Oracle User Group Journal /
 Business Intelligence & Data Warehousing Made Simple
Oracle Express Consultancy Services
Express Server delivers on-line analytical processing (OLAP) with capabilities that include forecasting, what-if scenarios,
and financial modeling. Based on a multidimensional data model, Express Server is optimised for the query and analysis of
corporate data - such as sales, marketing, financial, manufacturing, or human resource data. plus implements all Oracle
DSS technologies including Oracle Financial Analyzer (OFA), Oracle Sales Analyzer (OSA), Oracle Expess Web Agent, Oracle
Express Objects & Analyzer.

Oracle Express Application & Database Healthcheck

Oracle Express Application & Database Healthcheck provides a service for customers to audit their existing Express
investment, and identify opportunities to improve per formance, manageability and functionality. Offered via plus
consultancy, leaders in the Express technology field since 1983, this five-day service allows customers to benefit from the
accumulated knowledge and experience of our consulting team.

Oracle Express to 9i OLAP Migration Assessment

Oracle 9i OLAP Migration Assessment helps customers understand the benefits of transitioning their OLAP databases from
Oracle Express technology to Oracle 9i OLAP. By moving to Oracle 9i OLAP, organisations can quickly benefit from
improved application per formance, scalability, availability, and manageability, whilst retaining the majority of their
investment in Oracle Express technology.

Quick Win Business Intelligence Solution

A Complete Web-based BI Portal using Oracle 9iAS Technology. Oracle9iAS Portal, a key component of Oracle9i, leverages
the security, reliability, and scalability of Oracle9i Application Server and the Oracle database.

Quick Win Data Warehousing Solution

Leverage Oracle 9i Data Warehousing Technology. Implementing a data warehouse can often appear as a daunting
proposition, suitable only for large organisations with patience and large budgets. However, with today's Oracle 9i
technology, core systems can be deployed fast with measurable, demonstrable benefits to the organisation.

Oracle 9i Business Intelligence for SAP R/3 Customers Accelerator

This service, from plus consultancy, has been designed for SAP R/3 customers who wish to take advantage of Oracle
Business Intelligence and Data Warehousing. Every business transaction conducted using SAP R/3 applications generates
important pieces of data - valuable data on the organisation's human resources, financials, manufacturing, and supply
chain management.

Oracle 9i Data Warehouse Migration Assessment

Oracle 9i Data Warehouse Migration Assessment helps customers understand the benefits of transitioning their existing
Data Warehouse to Oracle 9i, and provides a high-level view of what it will take to accomplish that transition.

plus consultancy are also able to offer public and bespoke training courses.

For More Information

To learn more about the services offered by plus consultancy , please feel free to contact us at:

48A Old Steine, Brighton, East Sussex BN1 1NH

T: +44 (0) 1273 20 65 55 F: +44 (0) 1273 38 77 69 E: sales@uk.yourplus.com

www.plusconsultancy.co.uk
Oracle Logo Copyright © 2002, Oracle Corporation.Content Copyright © 2002 plus consultancy ltd.
it have been wonderful if, when the M25 maintenance is difficult because of that often we see embarrassed faces when that
was constructed, the project team had same high usage. In fact, its rare to see additional (and very expensive) CPU has
factored in an extra lane on the any maintenance being done on the M25 no benefit at all.
assumption that it was important to have – it is done in the early hours after
traffic flowing smoothly even when one midnight to avoid disruption. Because no Conclusion
lane was unavailable? contingency was made for maintenance Let’s look back at the original question
work, it becomes twice as expensive (since
Ask any Londoner about the M25 and workers don’t come out at 3am out of the “HELP! I have a performance problem!
they will typically launch into some goodness of their hearts), and twice as My parallel mutex latch has 154,245 hits
anecdote about how they spent their frantic, as those same workers race about and 17,243 misses. How can I speed
teenage years on the M25 after a lorry in a mad panic trying to finish before peak things up?”
broke down some miles ahead. As a hour. The result is poor and pricey.
database professional, even when you Hopefully, you’ll now realise that this is
have managed to plan and cope with the Of course, there are some tasks that equivalent to
peak hour problems, it is just as simply cannot be completed within a
important to be able to provide adequate single evening’s work. In these cases, for “HELP! I’m stuck in traffic on the M25!
services when one of your database ‘lanes’ weeks beforehand, signs are posted telling I’ve counted 17,243 lane markings since
has to close. commuters that maintenance work will junction 7. How I can speed things up?”
be occurring. Yet when the maintenance
For example, consider the use of RAID-5 starts, drivers are incensed at the delays Oracle can handle massive databases and
for a database. Many people are critical that they incur. Your database users will huge user populations without needing
of RAID-5, quoting a severe write be the same. You need to address any exotic and complicated configuration
performance penalty, but in these maintenance requirements for your adjustments. At sites where the customers
modern times of fast disks and large database before it becomes publicly are very happy with their database
cache sizes, and the fact that the vast available. If regular maintenance will be performance, it has been the simple
majority of databases are predominantly required, you must get buy-in from the things: good planning, good people, and
read and not write intensive, this users so that they understand and tolerate good monitoring that have yielded the
criticism can rarely be justified. this maintenance. results, not bizarre tweaks to the database
infrastructure.
However, I too am critical of RAID-5 but “Nice Ferrari”
for different reasons. When a RAID-5 On those occasions where I have been There are some rare occasions when you
disk fails, access to the data remains but stuck in traffic, I always find it amusing may need an expert to come in and
performance is dramatically impacted. when a Ferrari rolls up alongside in the examine your system, and possibly make
We have an ‘M25 scenario’ – traffic can next lane. The amusement comes from some changes at an internal level. But
still get through but only at a snail’s pace. the fact that my little 1990 Rover diesel don’t get swallowed up in complexity.
We can minimise the duration with with its failing exhaust and leaky oil Keep your ‘commonsense hat’ on – and
additional disk serving as “hot spares” system can generally keep pace with it (as next time your database looks like it has a
but now to store 4 disks worth of data in long as the traffic is sufficiently heavy to problem, simply grab your keys and get
RAID-5, we require 6 disks (1 disk for keep us both down to about 15mph, out on the M25. The solution is out there
parity, and a hot spare). This is not which also happens to coincide with my (or if the traffic is bad, then at least your
looking that much cheaper than a RAID- car’s top speed). I wonder what goes database problems will no longer appear
0+1 set of 8 disks – which lets me keep the through a Ferrari driver’s mind before as significant!)
lanes open and traffic flowing all the time! they go out and purchase it?
About the author
"Roadworks start here: “Hmmmm… I’m stuck in traffic…I Connor McDonald is a freelance DBA
March 2002" know!! I need a faster car!” with 12 years experience of Oracle.
The M25 also illustrates a Catch-22 His passions are database configuration,
problem that is common in high activity Amazingly, such a nonsensical school of performance tuning, training, presenting,
databases. Due to the traffic volume, the thought seems to be totally acceptable in and not driving on the M25! He can be
road surface degrades quickly and the database arena. When a bottleneck emailed at
requires regular maintenance to ensure occurs, the first response often seems to connor_mcdonald@yahoo.com, or via his
driver safety. But scheduling that be “we need more CPU” and nearly as web site www.oracledba.co.uk

30 Oracle Scene Issue 10 Summer 2002 / The UK Oracle User Group Journal /