Oracle Tuning Approach
In this article, we’ll be discussing about the Oracle tuning approach but before that, let’s go through the Oracle understanding first.
Oracle database consists of many logical storage structures such as data blocks, segments, schema objects etc. The physical storage space in the data files is logically allocated and de-allocated in the form of Oracle data blocks. The three types of physical database files are data files, redo logs, and control files.
Data files contain the actual data of the database that is represented in the form of tables or indexes.
Redo logs are used to record all changes made to the database. In order to function properly, every oracle database should have at least two redo logs.
The control file describes the physical structure of the database. It contains information such as the database name, date and the time of creation etc. Oracle process architecture is depicted below.
Oracle background processes are created at the time of initializing the database. Certain background processes are required for normal operation of the system and others are only used to perform maintenance and recovery operations.
Oracle uses two major types of memory structures for storing and retrieving data in the system. They are System Global Area (SGA) and Program Global Area (PGA).
SGA is a shared memory region used to hold data and internal control structures of the database. PGA (also called as Process Global Area) is the collection of non shared memory regions, each containing the data and control information for an individual server process. When a server process starts, the PGA memory gets allocated for that process.
This white paper reveals oracle performance tuning best practices that any administrator, database designer and performance tester must focus on.
Managing OS Resources
The performance issues in operating system normally involve memory management, process management and scheduling. After tuning the Oracle database instance, if you still need to enhance the performance then you may reduce system time. You should also make sure that there is enough CPU power, swap space and I/O bandwidth. For instance, the number of system calls increases in case of excessive buffer busy waits. You can decrease the total number of system calls by minimizing such buffer busy waits.
Note: If you have more than one instance on a node and you want to distribute resources among them, each instance should be assigned to a dedicated resource manager group. To run multiple instances in the managed entity, you need to use instance caging to manage CPU resource distribution among the instances. While managing CPU resources, the Oracle Database Resource Manager expects a fixed amount of CPU resources for the instance. With instance caging, it assumes the available CPU resources to be equal to the value of the CPU_COUNT initialization parameter. Without instance caging, it assumes the available CPU resources to be equal to the total number of CPUs in the managed entity.
In order to address CPU issues, you need to determine whether enough CPU resources are available and also recognize when the system is consuming too many resources.
You can also capture various workload snapshots by using the Statspack, Automatic Workload Repository, or the UTLBSTAT utility. Workload is an important factor while evaluating the utilization of CPU. During peak hours, 90% CPU utilization with 10% waiting time could be acceptable. Even 25% utilization at a time of low workload is understandable. But there will be no scope for peak workload if your systems show high utilization at normal workloads.
For this example, let us assume that at peak workload, Oracle may use 90% of the CPU resource. As per the calculation shown below, the database may use around 15% of the CPU resource for a period of average workload.
(20 tpm / 120 tpm) * 90% = 15% of the CPU resource.
[tpm = transactions per minute]
If the system requires around 40 to 50 percent of the CPU resource to achieve just 20 tpm then this could be an issue. However, if you can properly tune the system so that it achieves 20 tpm by using only 15% of the CPU, the system can easily support 120 transactions per minute by using 90% of the resources.
Query Optimizer – Oracle tuning approach
The query optimizer is built-in software that chooses an effective method in executing SQL statements.
SQL statements could be executed by a database in many ways such as index scans, full table scans, hash joins and nested loops. The optimizer can acknowledge many factors pertaining to the objects and the conditions mentioned in the query while examining the execution plan. This is an important step in SQL processing and can considerably affect the execution time.
The three main query optimizer operations are Query transformation, Estimation and Plan generation. The following figure illustrates the components of optimizer.
Each query portion is considered as a query block. The input to the query transformer is a parsed query that is defined by a set of query blocks.
The transformer decides whether it is beneficial to rewrite the existing statements into semantically equivalent statements that could be processed effectively.
The estimator evaluates the total cost of an execution plan. Estimator normally uses the available statistics in computing the measures. These statistics can enhance the degree of accuracy.
By trying out different access paths and join methods, the plan generator investigates a variety of plans for a query block. The generator actually selects the plan with the lowest cost.
The query transformer employs many transformation methods including View merging and Predicate pushing.
The view merging optimization is applicable to views that contain selections, joins and projections. In order to authorize the optimizer to use view merging, you should grant the MERGE ANY VIEW privilege to the user. You need to grant the MERGE VIEW privilege to the user on specific views to allow the optimizer to use view merging. In case of predicate pushing, the optimizer pushes relevant predicates into the view query block.