Basics of Optimization and tuning of Oracle Database Part-2

Logical design of the database:

The logical design of the database must be administered, taking into due account the performance aspects of the ultimate system.

- Advertisement -

In addition to the required initial normalization of all data, it’s, therefore, necessary to verify the necessity to introduce further optimization elements such as:

• The denormalization of knowledge (to place them where they’re most often or easily accessed)
•  The normalization of the info (to make the info structures more frequently accessed, and more streamlined).

These activities are administered by creating redundancy within the database and properly managing these data structures within the applications.

Since the appliance impact is often strong and high and therefore the risk of misalignment on the info, the suggested operations must be administered only within the cases necessary and adequately documented.

The physical design of the database:

The logical design of the database concerns the physical structures on which the RDBMS rests.

- Advertisement -

A quick list of tips is as follows:

Use disks, I / O processors, SCSI chains … as distinct as possible for RDBMS data storage.

It’s generally advisable to use raw devices

Create separate tablespaces for tables, indexes, rollbacks, and temps. Creating a separate SYSTEM partition offers management benefits.

When the dimensions of the tables are large, use striping.

Correctly define the sizing of all data structures. All objects should have just one extent (except for striping and rollbacks, whose extents should be 2).

Correctly define the allocation of temps.

Use clusters if there are tables frequently accessed together and with well-defined cardinality.

Create all the required performance indices (columns utilized in the selections with the right order, with a high degree of selectivity [> 20%]).

Use small columns in indexes.

Absolutely avoid defining unused indexes. The load in the modification is high.

SQL optimization:

The SQL language is seemingly very simple, however, to require full advantage of the chances it offers it’s necessary to deeply know its particularities and specific elements that every different implementation can present.

The different RDBMS on the market offer different SQL language extensions that include new clauses and utility functions.

Also, for the purpose of view of performance improvement, optimizers offer several features.

Refer to the document Optimization of SQL Statements for more information.

RDBMS tuning:

Oracle features a very sizable amount of configuration parameters (however not all documented) and allows different types and methods of installation. This makes the tuning activity potentially complex. However, this activity is usually very effective.

The Oracle RDBMS tuning activity requires a series of recycles on the subsequent steps:

•  checking the progress of the system in configuration and with real load
•  determination of any bottlenecks present
•  modification of the related configuration parameters

The steps listed must be administered repeatedly, checking the various aspects of impact on performances. Database monitoring should be constant; an honest DBA (database administrator) is consistently conscious of the present situation of the database. Close collaboration with the systems engineers of the system hosting the RDBMS is usually necessary; Oracle parameters often affect the performance of the whole system.

The tools for monitoring the Oracle RDBMS which will be used are:

•  SQL statement on the info dictionary
•  SQLDBA / monitor
•  different kinds of trace
•  third-party tools.

Refer to the Oracle Performance Statistics document for more information on tuning the Oracle RDBMS.

Operating system tuning:

Also for the tuning of the system, used as a guest of an RDBMS, it’s advisable to follow the methodology indicated above.

The tools for monitoring the RDBMS to be used are the traditional Unix utilities (sar, ps, df, ..). Some systems offer more sophisticated tools, however, this example isn’t frequent.

The points on which it’s appropriate to focus are:

1.  memory: checking the absence of swapping, the dimensions of the varied shared memory allocated and therefore the buffer cache
2.  Disk I/O: checking the right balance of the hundreds on the disks and therefore the presence of free space, avoiding fragmentation of the disks
3.  CPU: evaluating the load (and the sort of load) in moments with differing types of activity
4. processes: evaluating the amount, type and job mix

Systems that haven’t been tuned generally have some bottlenecks. Generally, accurate tuning allows for limiting the impact on the general system performance. The identification of the bottlenecks may be a fundamental point of the system tuning phase.

The following suggestions (which are valid for the bulk of Unix systems) are often useful as estimation elements for accurate identification:

1.  The CPU idle doesn’t need to be constantly at 0. (CPU bound)
2.  The run-queue queue must not exceed 1 for an extended time. (CPU bound)
3.  The CPU System time should generally be but the User time. (check the sort of activity)
4.  Virtual paging faults should be limited. (Memory bound)
5.  the most paging activity must be page-in. (Memory bound)
6.  The activity of the page stealer must be limited. (Memory bound)
7.  There must be no swapping. (Memory bound, job mix)
8.  Queues on the disks must be on the brink of 1. (IO-bound)
9.  Discs must be utilized in a balanced manner. (IO balance)

The access times to the disks must be on the brink of the typical access times declared by the manufacturer (too many seeks, change the disk partitioning).

The number of possible interventions is however limited by the Unix configuration possibilities. The most possible activities are:

• Unix kernel reduction
• creation of the right job mix to be presented to the machine
• a correct definition of the paging and swapping areas
• load balancing on disks
• decreased seeking of discs
• tuning on system parameters (buffering, pagination, etc ..)

Check the prior blog post on Oracle Database Tuning Part -1.

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 -