Factors that affect resource utilization

The performance of your database server application depends many factors, including hardware and software configuration, your network configuration, and the design of your database.

You must consider these factors when you attempt to identify performance problems or make adjustments to your system:
  • Hardware resources

    As discussed earlier in this chapter, hardware resources include the CPU, physical memory, and disk I/O subsystems.

  • Operating-system configuration

    The database server depends on the operating system to provide low-level access to devices, process scheduling, interprocess communication, and other vital services.

    The configuration of your operating system has a direct impact on how well the database server performs. The operating-system kernel takes up a significant amount of physical memory that the database server or other applications cannot use. However, you must reserve adequate kernel resources for the database server to use.

  • Network configuration and traffic

    Applications that depend on a network for communication with the database server, and systems that rely on data replication to maintain high availability, are subject to the performance constraints of that network. Data transfers over a network are typically slower than data transfers from a disk. Network delays can have a significant impact on the performance of the database server and other application programs that run on the host computer.

  • Database server configuration

    Characteristics of your database server instance, such as the number of CPU virtual processors (VPs), the size of your resident and virtual shared-memory portions, and the number of users, play an important role in determining the capacity and performance of your applications.

  • Dbspace, blobspace, and chunk configuration

    The following factors can affect the time that it takes the database server to perform disk I/O and process transactions:

    • The placement of the root dbspace, physical logs, logical logs, and temporary-table dbspaces
    • The presence or absence of mirroring
    • The use of devices that are buffered or unbuffered by the operation system
  • Database and table placement

    The placement of tables and fragments within dbspaces, the isolation of high-use fragments in separate dbspaces, and the spreading of fragments across multiple dbspaces can affect the speed at which the database server can locate data pages and transfer them to memory.

  • Tblspace organization and extent sizing

    Fragmentation strategy and the size and placement of extents can affect the ability of the database server to scan a table rapidly for data. Avoid interleaved extents and allocate extents that are sufficient to accommodate growth of a table to prevent performance problems.

  • Query efficiency

    Proper query construction and cursor use can decrease the load that any one application or user imposes. Remind users and application developers that others require access to the database and that each person's activities affect the resources that are available to others.

  • Scheduling background I/O activities

    Logging, checkpoints, page cleaning, and other operations, such as making backups or running large decision-support queries, can impose constant overhead and large temporary loads on the system. Schedule backup and batch operations for off-peak times whenever possible.

  • Remote client/server operations and distributed join operations

    These operations have an important impact on performance, especially on a host system that coordinates distributed joins.

  • Application-code efficiency

    Application programs introduce their own load on the operating system, the network, and the database server. These programs can introduce performance problems if they make poor use of system resources, generate undue network traffic, or create unnecessary contention in the database server. Application developers must make proper use of cursors and locking levels to ensure good database server performance.

Copyright© 2018 HCL Technologies Limited