How to start tuning in steps
                                                 Last update (2011-04-28 12:28:51)
                                                                                                                   Date added (2008-02-10 20:56:15)

The performance tuning of a database, starts from a point(when you initialy setup the database) and continues with small tuning steps every week and especially when changes happen to the database source code (sql, pl/sql)

The most time consuming period is the begging and is based on very well documented best practices. When you create the database you must setup the following.
A. Initialization parameters
B. Physical design for files.
A. Use the right and most common initialization parameters (processes, memory, etc). You can follow a simple example here Parameters for 1TB OLTP Unix database

If you are an experienced DBA, you have already done this thousand times, so you know the steps. You also know the result!!!

Not big deal done! Changing the init parameters is something easy and can be done any time. So even though you have already set wrong initialization parameters, you can change them later. It is always just an
alter system set ..
command.

The junior DBA often expects very much from the initialization parameters, but as the years pass at the end finally learns that changing them is not the magic solution to every problem!

B. The other one time job is redistribution of objects, datafiles, redolog files. Distribute database files (datafiles, control files, redologs, archives) properly at the available filesystems, so that you can have redundancy and evenly distributed I/O.

Creating the tablespaces with uniform extends sizes and spreading the datafiles on different filesystems is a good practice for I/O performance. To check if you comply with this general rule use this simple script:
Datafiles Disk I/O

After a reasonable setting of parameters and physical design you must take care of the specific performance problems related to the application (Queries and PL/SQL code). Oracle Wait Interface (OWI) provides all the information needed to start investigating performance issues
Here is a short description of The new methodology for performance and troubleshooting
Reviews
Categories
Filters
Search