I am just gonna brief about the troubleshooting steps though. Four major steps:-

• Analyzing Optimizer Statistics


• Analyzing an Execution Plan


• Using Hints to Improve Data Warehouse Performance


• Using Advisors to Verify SQL Performance

Analyzing Optimizer Statistics:-Optimizer statistics are a collection of data that describes more details about the database and the objects in the database. The optimizer statistics are stored in the data dictionary. They can be viewed using data dictionary views similar to the following:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Because the objects in a database can constantly change, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle Database or you can maintain the optimizer statistics manually using the DBMS_STATS package.

Analyzing an Execution Plan:-General guidelines for using the EXPLAIN PLAN statement are:

• To use the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema.


• To include the EXPLAIN PLAN FOR clause prior to the SQL statement.

After issuing the EXPLAIN PLAN statement, to use one of the scripts or packages provided by Oracle Database to display the most recent plan table output.

The execution order in EXPLAIN PLAN output begins with the line that is indented farthest to the right. If two lines are indented equally, then the top line is normally executed first.

To analyze EXPLAIN PLAN output:

EXPLAIN PLAN FOR (YOUR QUERY);

For Example:-

EXPLAIN PLAN FOR SELECT p.prod_name, c.channel_desc, SUM(s.amount_sold) revenue

FROM products p, channels c, sales s

WHERE s.prod_id = p.prod_id

AND s.channel_id = c.channel_id

AND s.time_id BETWEEN '01-12-2001' AND '31-12-2001'GROUP BY p.prod_name, c.channel_desc;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Using Hints to Improve Data Warehouse Performance:-Hints let you make decisions usually made by the optimizer. As an application developer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to instruct the optimizer to use the optimal execution plan.

To use a hint to improve data warehouse performance:

SELECT /*+ PARALLEL(s,16) */ SUM(amount_sold)

FROM sales s

WHERE s.time_id BETWEEN TO_DATE('01-JAN-2005','DD-MON-YYYY') AND TO_DATE('31-DEC-2005','DD-MON-YYYY');

Using Advisors to Verify SQL Performance:-Using the SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a given SQL statement, or set of SQL statements, and provide recommendations to improve their efficiency. The SQL Tuning Advisor and SQL Access Advisor can make various types of recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Oracle Enterprise Manager enables you to accept and implement many of these recommendations in very few steps.

Hope you're clear now.