smruti11

 Profile Answers by smruti11 

• Feb 13th, 2009

 

CPU bottlenecks
Undersized memory structures
Inefficient or high-load SQL statements
Database configuration issues

aravindhreturns

 Profile Answers by aravindhreturns 

• Jul 7th, 2009

 

What smruti11 mentioned is absolutely right, That is why I rated his answer as good.

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.

Kuddus

 Profile Answers by Kuddus 

• Feb 13th, 2010

 

Disk I/O could be an issue too

charandba

 Profile Answers by charandba 

• Oct 4th, 2010

 

Bottlenecks in DB Server are

1)  I/O Overload  ::
                                     ::::>>>>Both scattered reads, such as full-table scans, and sequential
                                                     reads, such   as index probes, are monitored.( I/O and RAM)
2) RAM Overload ::

2) CPU Overload  :   The display tracks processor consumption.

3) NETWORK Overload  :  The SQL*Net metrics are monitored and can easily spot when an     
                                                 application is network-bound.

kumardba

 Profile Answers by kumardba 

• Oct 7th, 2010

 

1) IO Contention because of  improper placement of datafiles,redo log files,archived log file.
  Solution:Use ASM which handles automatic IO Performance Tuning with mirroring features

2)Short Pool of Connections because of every client connection is treated as Dedicated server mode.

   Solution:Configure client connections be in Shared Server mode and increase the number of connections in a Pool.

3)Improper Sizing of SGA,PGA 
    
    Solution:Better to choose automatic memory management while database creation.

4)Improper size of archived log files. It sometimes results in server hang up when arch process is archiving redo log files to archived logs and archived logs are full.
   Solution: Increase size of archived log files.