ORACLE Tuning

 

sql_tuning_hierarchy

With this post we are going to make ths first of a serie to aproach to
a big oracle topic, tuning, u can read a entire book only talking about tuning, so here only will write some tips, for a detail lecture please take a book or visit a tuning guru website.


Recomended Books:

Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
url: http://docs.oracle.com/cd/E11882_01/server.112/e41573.pdf

Oracle Performance Firefighting by Craig Shallahamer


The Art and Science of Oracle Performance Tuning by Christopher Lawson

Oracle Performance Tuning by Mark Gurry


Recomended Websites:

1. Jonathan Lewis: http://jonathanlewis.wordpress.com/all-postings/
2. Christian Antognini: http://www.antognini.ch/
3. Tanel Poder: http://blog.tanelpoder.com/
4. Richard Foote: http://richardfoote.wordpress.com/
5. Cary Millsap: http://carymillsap.blogspot.com/

 

So lets start,  what tune?

1.- Aplication Design
2.- Aplication Tuning, SQL Tuning, heavy SQLs
3.- Tuning: Memmory, Instance. Parameter tuning
4.- Database Tuning: Object definition, Indexes(fk with index,etc..), statistics, table compact,etc..
5.- Tuning CPU, I/O (disk use), Network
6.- Contention Tuning (locks)
7.- OS tuning

1) Aplication Design

Is during design phase where you will find better results, rather than waiting to tune after implementing your system.

 

Tuning process does not begin when users complain about poor response time. When response time is this poor, it is usually
too late to implement some of the most effective tuning strategies. At that point, if you are unwilling to completely
redesign the application, then you may only improve performance marginally by reallocating memory and tuning I/O.

In the data design phase, you must determine what data is needed by your applications. You must consider what relations are important, and what their attributes are. Finally, you need to structure the information to best meet performance goals.

A normalization stage when data is analyzed to eliminate data redundancy, after the data is normalized, however, you may need to denormalize it for performance reasons. You might decide that the database should retain frequently used summary values

Another data design consideration is avoiding data contention. Consider a database 2 terabyte in size on which one thousand users access only 0.5% of the data. This “hot spot” in the data could cause performance problems.

No matter how well designed your database is, poor table design will lead to poor performance. Not only that, but overly rigid adherence to relational table designs will lead to poor performance. That is due to the fact that while fully relational table designs (said to be in the third normal form) are logically desirable, they are usually physically undesirable.

2) Application Tuning Tips

Application design and SQL problems cause most of the performance problems in properly designed databases. A well-designed application may still experience performance problems if the SQL it uses is poorly tuned. The key to tuning SQL is to minimize the search path that the database uses to find the data.

Here are some tips:

1) Improve performance by suggesting more efficient queries or table and index-organization schemes.
2) If you’re a production DBA, you’ll be dealing with user perceptions of a slow database, batch jobs taking longer and longer to complete and so on.
3) Performance tuning focuses primarily on writing efficient SQL, allocating appropriate computing resources and analyzing wait events and contention in the system.
4) Analyze the SQL explain plan, wait time, locking.


We can reduce a waiting:
– Check SQL statements [use index scans] , Tune SQL Statements. Use SQL Advisor.
– Check buffer cache, A larger buffer cache can help; (increase SGA)
– Partition TABLE and INDEX , that can help to reduce the amount of data you need to look at.

Example of waiting time:

If see waiting time “db file sequential read” event, we should investigate V$SQLAREA to see SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time. You may consider it excessive when the db file sequential read wait represents a large portion of a process response time.

To be continued …

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s