Oracle With Examples : Oracle Performance Tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site  -> Oracle DBA

-> Oracle Performance Tuning


Oracle Performance Tuning



Oracle tuning is considerate to be the most difficult area of the Oracle database management because we have to deal with hundreds of parameter interrelated which influence the overall Oracle database performance. 


     However tuning activity of the Oracle database is divided in:

     Database design tuning


     The database design define the objects which will be used, the relationship between them and the way the data is modified, accessed, used. This can be done in many ways but each manner have pro and contra and the better solution must be chosen for a particular case.   


     The database design is one of the most important factor in overall performance and some authors think that this is the most important think for an Oracle database performance. The database design must be carefully done, because, one time the project is running and the database is working the the database model is very hard (sometimes impossible) to be changed (if the database model is changed, the whole code must be changed in the same time with a very high cost). 


     In the 1070s, database redundancy was very expensive (the disk space was a big constraint) and because of this a new concept appeared in the database design. This new concept is the Third Normal Form (3NF) developed first by E.F. Codd and C.J. Date. The 3NF prevent the duplication of the data. Here is an example:



    Oracle Performance Tuning


      The same information could be stored in the database in the following manner, however the data will be duplicated (DNAME and LOC column will be duplicated) and more disk will be used:


Oracle Performance Tuning 


     This design, however permits fast access to the data, because data is stored in only one  location. Because of this advantage, starting from 1980s many designs use this approach. This kind of design is used in DataWarehouse where the speed is essential.



     Server & Network tuning


     Server tuning refers to improving the capacity of the server to work faster and use the resources more efficient. The server tuning always is related to the hardware which is used for the Oracle database. For this analyze the RAM, the CPU, the disks are the main variables. Sometimes, spending money on new hardware could fix a software problem (a poor PL/SQL code) which could be more expensive to fix by modifying the PL/SQL code. However, first we must try to ameliorate the PL/SQL code if the problem occurs only in a few packages/ procedures. 


     Network tuning refers to the parameters which change the traffic on the network and which could improve the response time for distributed databases. 



     Instance tuning


     A “database” (=the files which store the data) is not accessible by itself. In order to access this information, a collection of allocated memory (SGA) and the running processes (like SMON, PMON, LGWR, and DBWR) is called an "instance". So, tuning an instance means to optimize these processes/ SGA memory in order to have a better response time for our database. Tuning the SGA means tuning each component of the SGA (System Global Area): the Shared Pool, the Library Cache, the Buffer Cache, Large Pool, Java Pool, Redo Log Buffer. The tuning of the Oracle processes is done (more frequent) by some parameters which force the processes to write data in the files more often or less often in function of the needs. For more details about SGA tuning click here: Oracle SGA/ Instance Tuning.  



     Object tuning


     Each data object has a storage definitions and in function of this the data can be accessed or modified faster. A faster read could have a negative impact on the writing and vice-versa. For more details click here: Oracle Object Tuning .  



     SQL tuning


     The manner the PL/SQL code is written has a big impact on the way the statement is executed. The way a statement is coded could have a huge impact on the execution plan and so on the database performance.  For more details click here: Oracle SQL Tuning.  


  1. Oracle SGA/ Instance Tuning

  2. Oracle Object Tuning

  3. Oracle SQL Tuning

  4. tkprof utility

  5. Statspack utility

  6. The "Explain Plan" Command

  7. I/O tuning

  8. Enable the tracing in the database

  9. AUTOTRACE feature  -> Oracle DBA

-> Oracle Performance Tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...


     Copyright (c) 2018  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.