Basics of Optimization and tuning of Oracle Database Part-1

System optimization and tuning activities are complex and need specific experience. This document examines the overall aspects of optimizing and tuning a system.

The coordinate system may be a Unix server with an Oracle RDBMS hosting local and client/server applications. However, the knowledge provided and therefore the references present have general validity.

- Advertisement -

The distinction between optimization and tuning:

The optimization movement is administered to increase the performance of a system at the time of the initial design. Of course, this activity requires specific experiences since it’s necessary to gauge different possibilities with different impacts on the entire system (eg a program is often implemented with different algorithms, and the foremost efficient algorithms often require more memory or disc space or other system resources).

The tuning activity is administered on every single implementation or realization. A system is usually composed of several parts, all with a particular degree of configurability and parameterization. With the tuning, we attempt to find the simplest solution among all the configurable elements of the system. The monitoring of the system is prime for the tuning, the tuning must be administered whenever the monitored values give indications of the present effect.

Levels of intervention:

Wanting to enter greater detail on the aspects concerning a true system, it’s important to acknowledge the presence of several levels on which it’s possible to hold out optimization and tuning interventions.

The order used is guided by the performance impact that the intervention on this level can generate.

- Advertisement -

•  System architecture
•  Database (logical) design
•  Application algorithms
•  The physical design of the database (definition of indexes, clusters, … correct and proper use)
•  SQL (tricks for optimization, single-user tuning)
•  RDBMS (disk usage, buffers, parameters, ..)
•  Application environments (use of cursors, compilation)
•  UNIX OS (parameters, buffer cache, load balancing, ..)
•  UNIX kernel (disk partitioning, kernel reduction, ..)
•  Client systems configuration/tuning
•  Network configuration/tuning

It is of great importance to approach performance issues at the proper level. By working on already well-defined or optimized elements, improvements are often obtained that have a lower level of magnitude within the final impact on the user.

When developing programs that access complex databases, of considerable size, with distributed elements or with particular accesses (and during this project these conditions are met) it’s of fundamental importance to regulate performance. For efficient control of the programs, it’s necessary to create test databases of sufficient size, and then perform tests on volumes. In this way, instantaneous feedback on the info access times is feasible.

In the following points, some elements of the right methodology are going to be examined. Finally, most elements concerning a number of the previously defined optimization levels are going to be reported.

Approach to optimization and tuning:

As far as performance improvement cares, the strategy to be followed to get good results is guided by some considerations.

It is necessary to exclude programs that are little used or not very strategic; exclude programs that, albeit they will be improved, are already sufficiently performing; avoid modifying parts that might require too expensive system modifications.

To achieve concrete results, you would like to obviously define your goals and specialize in them. There’s no point in talking about performance improvement in the least. There are limits beyond which it’s absolutely not advisable to travel down, also because the utmost performance from a system is obtained only at the expense of other elements (safety, simplicity, flexibility, ..).

Once the points on which to intervene and therefore the objective to be reached are defined, it’s advisable to follow an accurate methodology of study and determination of the matter.

Although the subsequent isn’t completely rigorous, the sort of activity to be administered for every optimization intervention is often summarized as follows:

Study of the knowledge involved within the process and of the programs implemented

Rigorous performance evaluation, both overall and of serious parts of the programs within the current version

Preparation of a punctual list of proposed changes:

evaluation of every single proposal, execution of what is often immediately executed, evaluation of the impact on programs, time recording

•  prototype preparation with program modifications
•  time evaluation and comparison
•  actual implementation

The listed steps must be performed for every identified intervention. The identification of the only interventions isn’t trivial (since it requires in particular specific experience) and it’s difficult to estimate. What generally happens is that, after some initial recycling, the impact on performance is gradually less until it becomes irrelevant. it’s possible that some interventions require such a burden for implementation to suggest the postponement to a subsequent version of the programs.

For more detail, check our Oracle Database Turning Part -2.

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -