24 April 2010

Your Database is slow, and now…..

When your Database is slow there are a few possible sources to look for the bottleneck(s) in your Database.

1)    The OS is not tuned correctly.

For Linux and UNIX set your semaphores, shared memory realms and shmmax and shmmin parameters and max_nproc parameters.

For windows set the virtual memory parameter correct.

2)   The Database is not tuned correctly.

Set the init.ora parameters correctly and use AMM and ASMM. Size the UNDO tablespace and the undo segments correctly. For RAC determine whether to use a logical standby Database or a Physical standby Database.

3)   The SQL and PL/SQL statements are not tuned well enough.

Use automatic SQL tuning in oracle 10g and 11g. In earlier releases tune the statements with reprogramming and SQL plan management.

Query Optimizer and Execution Plans

When a SQL statement is executed on an Oracle database, the query optimizer determines the most efficient execution plan after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
During the evaluation process, the query optimizer reviews statistics gathered on the system to determine the best data access path and other considerations. You can override the execution plan of the query optimizer with hints inserted in SQL statement.

4)   In Oracle 11g use Native dynamic SQL or Static SQL depending on your objective. There is an expert presentation on that from Seven Feuerstein on the TOAD website.

5)   Use Native compiling of Java and PL/SQL procedures and packages in Oracle 11g. Oracle proved substantial improvements in performance of up to a 100 percent!

A new option is available from Oracle:

Oracle TimesTen In-Memory Database (TimesTen) is a memory-optimized relational database that empowers applications with the responsiveness and high throughput required by today's real-time enterprises and industries such as telecom, capital markets and defense. Oracle In-Memory Database Cache (IMDB Cache) uses the Oracle TimesTen In-Memory Database as its RDBMS engine. Deployed in the application tier as an embedded database, Oracle TimesTen In-Memory Database operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication.


1 comment:

  1. Amazing post. You have suggested so many promising ways to improve the efficiency of the database. Its necessary to tune the database in timely manner so as to improve the efficiency and work flow and in worst case scenarios the above points will help out. Thanks for sharing all these ideas.
    sap upgrade automation