Identify the solution for what problem by looking the TKPROF.
If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.
If Disk reads are very high: Indexes are not being used or may not exist
If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.
If parse elapse time is high: There may be a problem with the number of open cursors.
If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.
If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.
If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.
If Disk reads are very high: Indexes are not being used or may not exist
If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.
If parse elapse time is high: There may be a problem with the number of open cursors.
If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.
If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.
