Oracle Performance Pages in the Database Control

Three major tuning areas the OEM Database Control will show you: CPU and wait classes, top SQL statements, and top sessions in the instance.

This page shows the three main items:

Host  - The Host part of the page shows two important graphs:

o Average Run Queue: This shows how hard the CPU is    running.

o Paging Rate: This shows the rate at which the host server is    writing memory pages to the swap area on disk.

The sessions graph shows which active sessions are on the CPU and which are waiting for resources like locks, disk I/O, and so on.

If your instance throughput is decreasing, along with an increasing amount of contention within the database, you should start looking into tuning your database.

In Oracle Database 10g, the SKIP_UNUSABLE_INDEXES parameter is a dynamic initialization parameter and its default value is TRUE. This setting disables error reporting of indexes and index partitions marked as UNUSABLE.

Note: This setting does not disable error reporting for  unusable indexes that are unique because allowing insert and update operations on the table might violate the corresponding constraint.

Note: The database still records an alert message in the alert.log file whenever an index is marked as unusable.

• In Oracle 10g, you can create hash-partitioned global indexes. (Previous releases support only range-partitioned global indexes.)

• You can hash-partition indexes on tables, partitioned tables, and index-organized tables.

• This feature provides higher throughput for applications with large numbers of concurrent insertions.

• If you have queries with range predicates, for example, hash partitioned indexes perform better than range-partitioned indexes.

• You can’t perform the following operations on hashpartitioned global indexes: ALTER INDEX REBUILD, ALTER TABLE SPLIT INDEX PARTITION, ALTER TABLE MERGE INDEX PARTITITON, and ALTER INDEX MODIFY PARTITION.

CREATE INDEX sales_hash
on sales_items (sales_id) GLOBAL
PARTITION BY HASH (sales_id) (
partition p1 tablespace tbs_1,
partition p2 tablespace tbs_2,
partition p3 tablespace tbs_3)
CREATE INDEX sales_hash
on sales_items (sales_id) GLOBAL
PARTITION BY HASH (sales_id)
partitions 4
store in (tbs_1,tbs_2,tbs_3,tbs_4)

• To add a new index partition

ALTER INDEX sales_hash ADD PARTITION p4
TABLESPACE tbs_4 [PARALLEL]

Notice the following for the previous command:

o The newly added partition is populated with index entries rehashed from an existing partition of the index as determined by the hash mapping function.

o If a partition name is not specified, a systemgenerated name of form SYS_P### is assigned to the index partition.

o If a tablespace name is not specified, the partition is placed in a tablespace specified in the index-level STORE IN list, or user, or system default tablespace, in that order.

• To reverse adding a partition, or in other words to reduce by one the number of index partitions, you coalesce one of the index partitions then you destroy it. Coalescing a partition distributes index entries of an index partition into one of the index partitions determined by the hash function.

ALTER INDEX sales_hash COALESCE PARTITION
PARALLEL

Using the new UPDATE INDEXES clause during a partitioned table DDL command will help you do two things:

• specify storage attributes for the corresponding local index segments. This was not available in previous versions.

• have Oracle automatically rebuild them.

ALTER TABLE MY_PARTS
MOVE PARTITION my_part1 TABLESPACE new_tbsp
UPDATE INDEXES
(my_parts_idx
(PARTITION my_part1 TABLESPACE my_tbsp))

Oracle Database 10g provides enhancements for handling DML operations involving bitmap indexes.

These improvements eliminate the slowdown of bitmap index performance, which occurs under certain DML situations. Bitmap indexes now perform better and are less likely to be fragmented when subjected to large volumes of single-row DML operations.