Do you have any statistics about a database are necessary for database tuning?

---

Request for Question Clarification by maniac-ga on 26 Jul 2003 16:21 PDT

Hello Helloworld,

There are a large number of statistics that may be collected. However,
depending on the application, the statistics that are "necessary" will
vary. For example, some database applications are CPU bound, so
measuring and optimizing I/O is not appropriate. Would an answer that
lists a few statistics as "most important" and then expand on that
list for special cases be acceptable?

  --Maniac

---

Clarification of Question by helloworld-ga on 27 Jul 2003 00:04 PDT

I think so! 
Thanx for asking before you post the answer.

Hello Helloworld,

The most important statistics you can collect about (or in) a data
base will be related to
 - input / output from disk or network
 - memory utilization (e.g., buffers)
 - CPU time
in roughly that order. The reasons for that get back to the
characteristics of a computer system. Using a 1 GHz processor as an
example, the duration of typical operations will be:
 - access to disk storage (10's of thousands of cycles)
 - access to "main memory", not caches (10's to 100's of cycles)
 - instruction execution (a handful of cycles)
so if you minimize disk, and then memory access, you will generally
get the best performance out of an application. However, as I noted in
the request for question clarification - the application may have
already exhausted CPU resources - so it is more important to fix that
first before working on the other factors.

Specific to a data base, include the following types of data
collection and analysis:

 - identify the operations that use the most memory (in Oracle -
buffer gets)
 - identify the operations that do the most I/O (in Oracle - disk
reads)
http://www.ced.com/ced/cedweb3.nsf/0/2938ead73266d6d6862569dd0007158d?OpenDocument
  Explanation plus scripts to extract data that Oracle can collect
related to these statistics. This also describes a number of other
tuning techniques that use other kinds of data (e.g., data base
growth, fragmentation).

 - number of rows in a table
 - number of queries / joins performed
 - hit ratio for caches
 - segment or free space fragmentation
  http://udlcomputing.cjb.com/udl/training/o8dba/dbaextramaterials-9.htm
  This is a pretty long explanation of an Oracle tuning training
course but includes many of the items that can be collected in the
data base and how they are used to tune the data base. For example,
the number of rows in two tables can be compared when doing a join
operation to process the largest table once and speed the join.

Note that both of these references focus on the amount of I/O and
memory used by the data base management system (e.g., more
fragmentation will require more I/O). Many of the data base
applications will benefit from improving these basic operations.

There is a particularly easy to read example near the end of the
following data base introduction:
  http://www.edm2.com/0612/msql7.html
The example basically states that if collected statistics indicate
that most index items are the same value, a query using that value
should be done by reading the records directly (and ignoring the
index). This is an example of how the data base system can modify its
operation based on statistics (reducing I/O).

An extensive report describing performance tuning in general but
includes a few data base examples (sections 6 and 7) which illustrate
the steps taken and feedback on the changes made. Note particularly
the comments in 7.3 which describe specific changes to Oracle
parameters based on measurements and the results that occurred. This
also shows that the measures may indicate a change that will be
ineffective. Be sure to measure before and after you tune the data
base to ensure the change was helpful. This document addresses all
three aspects (I/O, memory, CPU).
  http://members.value.com.au/christie/auug93.htm

Oracle includes an extensive set of statistics that may be referenced.
See
  http://www.oracledbaexpert.com/oracle/tuningStatistics.html
for a list of these items (near the end) along with more general
suggestions on data base tuning. It also has an interesting caution
about how statistic collection can affect other tuning techniques. If
you need a "long list" of what to collect - this can certainly meet
that need.

The title includes "data modeling" but the focus is on changes you can
make to improve data base performance. Recommends collecting data and
work load values to feed index optimization and design advisor tools.
  http://www.idug.org/idug/member/journal/mar98/dmodeldb.html

For further information / references, try
  http://dblab.ssu.ac.kr/development/tuning/tuning.htm
  An extensive list of on line and off line tuning resources.

Search phrases included
  relational database tuning statistics
  relational database tuning statistics -training

There are a lot of statistics that can be collected about the data
base. As noted by more than one author, tuning based on that data is
still more of an art than science. Please let me know if you need
further explanation of the answer or need some part expanded.

  --Maniac

---

Request for Answer Clarification by helloworld-ga on 30 Jul 2003 16:13 PDT

Sorry, I make this question confusing, what i am wondering is - What
statistics about a database are necessary for database tuning?

---

Clarification of Answer by maniac-ga on 30 Jul 2003 18:45 PDT

Hello Helloworld,

Again - there is no absolute list of "necessary statistics". That is
why I asked for the clarification and why the answer is structured the
way it is. What is necessary for one application will not be in
another. If any could be considered "necessary" are the big three
 - I/O
 - memory
 - CPU
and then use indicators from those three to help refine the selection
of other statistics. Let me provide a constructive example.

Many data base systems have a lot of data and not a lot of processing
on that data. The architecture of a main frame is based on that
tradeoff. In those cases, it is far more important to minimize the
number of read (and write) operations to the disk. Using the first
reference in the answer - you would look at disk reads by query. You
can then increase the number of buffers, and then repeat the test. If
disk reads goes down - you can then handle more data base accesses in
the same amount of time.

You would measure the "big three" to determine the initial
chracteristics (I/O bound, CPU and memory available) and then measure
in the data base
 - disk reads
 - buffer gets
to further refine what needs to be changed. This is generally the
approach used in
  http://members.value.com.au/christie/auug93.htm
to get the results they did (though they did a *lot* more measures
than this simple example).

  --Maniac