Databases Reference
In-Depth Information
F
Resource contention
F
Operating System
F
CPU
When we want to tune a database in a proactive way, we can follow the previous list from
the top to the bottom.
Issues in the first two areas generally lead the database to very bad performance and to
scalability issues. The most common performance problems in an Oracle database related
to application design and coding are as follows:
F
Incorrect session management
F
Poorly designed cursor management
Binding variables
Cursor sharing
Non-set operations
F
Inadequate relational design
F
Improper use of storage structures
Let's explain each performance problem listed in the previous paragraph. Troubles related
to memory, input/output, contention, and operating systems will be explored in the following
chapters. A well-tuned application can lead to a significant performance improvement, so it's
natural to concentrate the first efforts on performance tuning to application design and coding.
Incorrect session management
Poor session management can lead to scalability problems. For example, if a web page logs
on to a database, gets some data, and logs off; the time spent for the log on procedure could
be an order of magnitude greater than the time required to execute the queries needed to
bring the data which the user has requested.
Poorly designed cursor management
There are different problems related to cursor management.
The first rule in writing applications which connect to an Oracle database is to always use
bind variables, which means not to include parameters in SQL statements as literals.
