Top 60 Oracle Blogs

Recent comments

My database is slow - now what ?

Although this example was supplied as a simple template, it's worth using it as a link point to an OTN post where Randolf Geist explains how to ask for help when trying to sort out performance problems with a single SQL statement.


Just set the undocumented parameter _fast to true.

I hope you intended fun

This is one of the joke Tom cracks. I hope you have not taken it seriously.

Your Database is slow, and now…..

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.

A couple of points....

1) Virtual memory under Windows should be automatic anyway and should resize itself according to the requirements of not only the database, but also any Windows services running at the time. This is especially true when performing VSS operations or backups during the workday. If the SGA/PGA is incorrectly set then the database may actually be the cause of the operating system running slowly and not the other way around.

2 & 3) This comes back to the SGA/PGA story. If SGA_MAX_SIZE (in 10G) or MAX_MEMORY (in 11g) is set too high then it is possible that the operating system is being starved of memory that it needs in order to run propery. To that end, use DB_CACHE_ADVICE (10gR1), SGA_TARGET_ADVICE (10gR2) (and I have forgotten what the advisor is in 11g) and resize the SGA accordingly. The one downsize to this is that the SHARED_POOL might end up overloading the DB with hard parses if the SQL being executed uses concatenated variables and not bind variables. I would suggest a run on a test machine and making sure that SQL is optimised first before making any changes that may inadvertently have a detrimental effect on the DB. Setting QUERY_REWRITE = TRUE may help as Oracle will try (where possible) to optimise the SQL on-the-fly which can then used in the SQL Execution Plan.

Just a little something from me as someone that had to figure this out for himself on a 32-bit hardware platform running in Shared_Server using the PAE switch! It was painful.....