Problem with Oracle SQL performance

by Paramesh
(Singapore)

From: Paramesh in Singapore - We are using Oracle and I have a problem with SQL. We are retrieving data from 6 tables using joins (returning approximately 500 rows). What happens is that more than one user call the function and each and everytime it retrieves the data from the tables (the same data). Some times the system times out.

Is it possible to store the data somewhere in some Object instead of the table so that whenever you want you can retrieve data from object instead of from the table directly? How can you do this in Oracle? . thkx Param

There are a few ways this problem could be solved in Oracle.

First, if the same data is being requested several times by each user then storing the result of the query in an Oracle pl/sql associative array (index-by table) could be an option.

Using Oracle PL/SQL associative arrays would enable you to cache the data in memory so susbsequent access of the data would be much quicker.

However this won't help much if the data needs to be accessed by several users because the PL/SQL data is held seperately for each user.

For help with Oracle PL/SQL, we highly recommend Steven Feurstein/Bill Pribyl's book Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide) as a great resource for dbas and developers alike (it gets 4 stars on Amazon and is the best selling book on Oracle PL/SQL).


An alternative to using PL/SQL is to use Oracle Materialised Views which are pre-run queries that store their results in special tables in the database.

Materialised views have the advantage that they can be set to be refreshed automatically by the database whenever any of the underlying tables change, or they can be refreshed on a pre-detrmined frequency or refreshed manually on demand. Note that the initialisation parameter QUERY_REWRITE_ENABLED must be set for the optimizer to be able to dynamically rewrite queries to use any materialized views.

Another option (if the table is small) is to cache the whole table in the Oracle SGA. This can be done with the CREATE TABLE and ALTER TABLE statements and causes the table's data to be placed at the "most recently used" end of the least recently used (lru) list in the system global area (SGA).

The final option we can think of for reducing access time for this data is to use the RESULT_CACHE hint for queries using this table (in Oracle 11g and above). This will have a similar affect to using the CACHE option mentioned above - the results of the query are cached in memory and are re-used in future executions of the query.

A good book on performance tuning is Oracle Database 10g Performance Tuning Tips & Techniques (Osborne ORACLE Press Series) by Richard Niemiec which covers indexes, initialisation parameters, stored outlines, hints, SQL tuning and many other topics.