To administer Oracle Database 10g and keep it running smoothly, the DBA must regularly monitor its performance to locate bottlenecks and correct problem areas.Finding bottlenecks and problematic areas before they become an issue is in the DBA's best interest.

There are hundreds of performance measurements the DBA can look at, covering everything from network performance and disk input/output (I/O) speed to the time spent working on individual application operations. These performance measurements are usually referred to as database metrics.

The SQL Tuning Advisor

------------------------------------------------------------------------

The SQL Tuning Advisor is the primary driver of the tuning process. It calls the Automatic Tuning Optimizer (ATO) to perform four specific types of analysis:

Statistics Analysis
SQL Profiling
Access Path Analysis
SQL Structure Analysis

Automatic Shared Memory Management:

------------------------------------------------------------------------

Automatic Shared Memory Management is recommended to simplify management.

Enables you to specify the total SGA memory through one initialization parameter
Enables the Oracle server to manage the amount of memory allocated to the shared pool, Java pool, buffer cache, streams pool, and the large pool

Manually setting shared memory management:

Sizes the components through multiple individual initialization parameters
Uses the Memory Advisor to make recommendations.

Automatic Shared Memory Management (ASMM) can be enabled/disabled in the Enterprise Manager.

Oracle recommends that you use ASMM.

If not enabled you will need to manage memory sizes via the following Initialization parameters:

SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
DB_CACHE_SIZE
STREAMS_POOL_SIZE

The Memory Advisor:

------------------------------------------------------------------------

The Memory Advisor helps you tune the size of your memory structures. You can use this advisor only when automatic memory tuning is disabled.

The Memory Advisor comprises three advisors that give you recommendations on the following memory structures:

Shared pool in the System Global Area (SGA)
Buffer cache in the SGA
Program Global Area (PGA)

Navigation to the Memory Advisors :

Click Advisor Central in the Related Links region on the Database home page.
Click Memory Advisor on the Advisor Central page. The Memory Parameters page appears.

Invalid/Unusable Objects:

------------------------------------------------------------------------

If you find PL/SQL objects with a status of INVALID, the first question that you need to answer is “Has this object ever been VALID in the past?”

If the PL/SQL object is invalid because of code error, then there is little that can be done until that error is fixed. If the procedure was valid at sometime in the past and has recently become invalid, you have two options to fix the problem:

Don't do anything. Most PL/SQL objects automatically recompile if needed when they are called.

Manually recompile the invalid object.

Unusable (corrupt) indexes need to be rebuilt.

Fixing broken objects:

ALTER PROCEDURE HR.add_job_history COMPILE;

ALTER PACKAGE HR.maintainemp COMPILE;
ALTER PACKAGE HR.maintainemp COMPILE BODY;

ALTER INDEX HR.emp_empid_pk REBUILD;
ALTER INDEX HR.emp_empid_pk REBUILD ONLINE;
ALTER INDEX HR.email REBUILD TABLESPACE USERS;

If the TABLESPACE clause is left out, the index is rebuilt in the same tablespace where it already exists.

The REBUILD ONLINE clause enables users to continue updating the index’s table while the rebuild takes place. (Without the ONLINE keyword, users must wait for the rebuild to finish before performing DML on the affected table.)