Knowledge Base Article-Database Tuning
|Performance testing is an ideal way to generate a workload against a database. Great, now what? How about looking at the performance of the database? Many performance testers treat the database as a black box. Leave it to the experts they say. Unfortunately, if a performance problem is to occur anywhere in an n-tier application, it is most likely to occur in the database. Evaluation of the database is not really that difficult and comes down to understanding just a couple of key concepts.
This must be minimised as much as possible. Physical I/O to disk is not only slow, it also consumes CPU. There are many ways of reducing Physical I/O, traditionally this would involve:
* Ensuring that the database has sufficient memory available for it to be able to store commonly accessed data in a data cache.
* Ensuring that data retrieving queries efficiently find target data without having to trawl through large tables.
While a low CPU utilisation for a database server may appear to be good, it could by symptomatic of a poorly configured database. If the database is I/O bound, or lacks a sufficiently tuned configuration, it is possible that the database is not unable to service more requests for data but is not receiving those requests very quickly, i.e., those requests for data are queuing.
In a well configured database server, busy periods will see CPU utilisation around 80% with occasional peaks in workload taking the CPU utilisation closer to 100%
CPU utilisation can be reduced by investigating the following areas;
As a request for data arrives at the database, the SQL query must be parsed. This basically means that Oracle investigates the statement and decides how it will access the database tables so as to satisfy the query request. If the query has already been executed, it may still be in the area of the cache where queries are stored. Reusing a query which has already been parsed and is in the cache uses much less CPU than parsing a query for the first time. To ensure this reduction in CPU utilisation, the performance tester should ensure two things: