Page History

Perform one of the following options:

• Try to determine whether the changes in object structure and changes in performance are related by comparing the In Oracle graph and the Changes graph in the Read/Write Operations tab. If it seems that performance deteriorated as a result of the change in table or index structure, consider rolling back the change.

• Consider adjusting index structure and execution plans.

Examine if there is a correlation between table growth and performance degradation in the

Read/Write Operations tab.

Perform the following options:

• Examine the In Oracle activity of the partition, in the Partitions tab. Check if the massive activity spent accessing the partition is abnormal.

• Examine the activity of statements accessing the partition in the Activity tab.

Perform one of the following options:

• If the partition is a table partition:

• • Create local or global indexes for statements that access the partition.

• • Subpartition the partitions.

• • If the partitions are not balanced well, consider building the partitioned table with new partition keys.
• If the partition is an index partition:

• • Subpartition the partitions.

• • Add more columns to the index to improve filtering.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on the All access type in the Access

• Types table.

• Examine buffer cache usage in the Statistics tab. Check if there is an overall wait on the Free

• Buffer event.

If the index structure does not fit the Where predicates of the top statements consider doing one of the following:

• Add columns to the index in the right sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages.

• Change the sequence of columns in the index to the optimal sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1,C2,C5)

Statement: select * from TAB1 where C1=:h and C5=10; Execution plan uses IX1 in Index Range Scan

In this statement the matching level of the index is 1. This means that Oracle uses only C1 to filter index leaf pages, it cant match C5=10 against the index tree because of the absence of a C2= predicate. Because C1 is not selective, many irrelevant index leaf pages can be read. Oracle will apply the C5=10 predicate on the index keys to screen irrelevant table ROWIDs. An index on C1 followed by C5 would be more efficient for the query.

Perform the following:

• Examine associated statements in the Statements tab.

• Focus on the Full Index Scan access type in the Access Types table.

• Examine column usage, for each statement in the Columns table.

Perform one of the following options:

• Full index scan, can be eliminated by:
  • Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.

• Full index scan, can be improved by switching to Fast Full Index Scan when the index is not used to save sorts (a fast full scan retrieves the rows according to the index key). Perform one of the following options to enable this access path:
  • Ensure that the Fast_Full_Scan_Enabled parameter = yes.
  • Use the "Index_ffs" hint for major statements that use the full index scan, to check if the fast full scan improves performance. When the full index scan doesn't save sort results, performance may improve considerably.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1)

Statement: select C1 from TAB1 Order by C1; Execution plan uses IX1 in Full Index Scan

In this case a full index scan is the best option. Because there are no filtering predicates, there is no need to access the table blocks and the sort operation is avoided.

Perform the following:

• Examine associated statements in the Statements tab.

• Focus on the Fast Full Index Scan access type in the Access Types table.

• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Fast Full index scan can be eliminated by:
  • Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.

• Fast Full Index scan can be improved by:
  • Moving the index to another space with a higher block size.
  • Increasing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.