1.1 A database is an organized collection
of data. The data are typically organized to model aspects of reality in a
way that supports processes requiring this information. For example, modelling
the availability of rooms in hotels in a way that supports finding a hotel with
The DBA and Database Performance Factors
1. Response Time
Time” is what users (and application servers!) see. They do not see ‘consistent
gets’ or ‘redo size’ or ‘enq: TX – row lock contention”.
perception of a system’s usability is significantly impacted by Response Time.
“fit for use” (the application is usable) must co-exist with “fit for purpose”
(the application does what it is supposed to do).
On the other hand, Response Time for a batch job can vary from execution time
for a (significant) single SQL call to the elapsed time for a key stage in the
are very many tiers through which a response reaches a user (or an application
server, depending on who/what has “response issues”).
From the desktop, via a browser, over the internet/intranet to an application
server, rewritten as an SQL call to the database, parsed and executed by the
database, CPU and I/O cycles consumed to fetch, filter and compute values,
round-trips between the application server and database server, formatting on
the application server, latency down to the user’s desktop; there are very many
tiers that are comprised in an application’s performance. Such tiers also exist
in a batch job – often ignored are the round-trips between the application
server and database server.
“component” (be it the User’s Desktop or the WAN Link or the App Server CPU or
the App Server RAM etc … down through the Tiers) has a defined Capacity –
theoretical and practical. Within a database instance, also, there are capacity
parameters – e.g. SGA sizing parameters, the processes parameter etc.
of the available capacity of any component varies from time to time. Any tool
that “measures” usage has to collect a snapshot of usage at a certain point in
time. Multiple snapshots must be analyzed together.
is the volume of “load” (Transactions/Queries/Rows/Users – each is a different
facet of “load”) that is being serviced by the “system”.
is a constraint. Concurrency is a constraint as well. Two
users/processes/sessions may not be permitted to modify the same row/resource
at the same time.
Capacity is not Unlimited and because there are Constraints
(automatic/system/artificial/user-defined), there may well be some points in
application code or database code or the operating system where serialisation
requirements, usability requirements and control requirements are defined by
users / analysts and must be built into the “system”. Requirements also add to
of the system is it’s ability to handle additional workload without more than a
proportional increase in component resources (CPU, RAM, I/O) usage. Scalability
is adversely impacted by points of contention or serialisation in the
requirements / design / code.
systems are non-linear. If a query that processes ten thousand rows that are
always in memory and never overflows to disk for Group/Sort operations takes
1second to run, it doesn’t necessarily follow that a hundred thousand rows
would take 10seconds. The hundred thousand row query may require multiple disk
reads because not all rows are cached in memory and, furthermore, the
Group/Sort operations also overflow to disk.
database server may be configured to host multiple databases. The CPU and I/O
load of one or more “other” databases may well be “interference” in the
performance of a database under review. The “cost” of such “interference” must
be computed and accounted for. Similarly, within a database, Batch reports may
interfere with online queries. Also, when multiple schemas (e.g. for different
“applications”) are provided for within a database, they share and contend for
shared pool, library cache and buffer cache resources as well as for CPU and
basic Factors apply to any System. They apply to Airports and Aeroplanes. They
apply to Factories and Refineries. They apply to Hotels and Restaurants. They
apply to Applications using Oracle Databases.
As an Oracle Database Performance Analyst (a DBA or a Developer or a System
Administrator), it is necessary to be aware of these Factor
Some database terms to know
organizes your information into tables: lists of rows and columns
reminiscent of an accountant’s pad or a spreadsheet. In a simple database, you might
have only one table. For most databases you will need more than one. For
example, you might have a table that stores information about products, another
table that stores information about orders, and another table with information
row is more correctly called a record, and each column, a field.
A record is a meaningful and consistent way to combine information about
something. A field is a single item of information — an item type that
appears in every record. In the Products table, for instance, each row or
record would hold information about one product. Each column or field holds
some type of information about that product, such as its name or price.
1.3 The Importance of Database Design
reviews are an important facet of the system development lifecycle for database
applications. It is during the design review that all aspects of the database
and application code are reviewed for efficiency, effectiveness, and accuracy.
It is imperative that all database applications, regardless of their size, are
reviewed to assure that the application was design properly, efficient coding
techniques were used, and the database is accessed and modified correctly and
efficiently. The design review is an important process for checking the
validity of design decisions and correcting errors before applications and
databases are promoted to production status.
design reviews should be conducted over the course of an application’s life.
There are seven basic design review phases for a database application: