Problem with Oracle SQL performance

by Paramesh

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 every time 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 and foremost check the execution plan of your query to ensure that your query has an efficient execution plan that uses the appropriate indexes. Ensuring that the primary key and foreign key constraints are declared and enabled and that statistics are fresh helps the Oracle optimizer to come up with an efficient plan. Use the USER_TAB_MODIFICATIONS view to check how fresh your statistics are. If the query uses indexes to fetch the data rather than using full table scans then the data is likely to stay in the sga for longer and therefore be available for other users.

If having followed step 1, you still have performance issues, then if the same data is being requested several times by the same user (who stays logged on) 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 subsequent access of the data would be much quicker.

However this may not help much if the data needs to be accessed by several different users because the PL/SQL data is held separately for each user (this could obviously increase the database's memory requirements) and wouldn't help if the data changes frequently. Also, you would obviously have to modify your application to make use of this.

An alternative to using PL/SQL is to use Oracle Materialized Views which are pre-run queries that store their results in special tables in the database. Materialized 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 predetermined frequency or manually on demand. Note that the initialization parameter QUERY_REWRITE_ENABLED must be set for the optimizer to be able to dynamically rewrite queries to use any materialized views instead of the base tables.

Another option (if the tables are small) is to cache the tables 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) block 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 for tables as described above - the results of the query are cached in memory and are re-used in future executions of the query.

For more help, see this question and answer on Oracle sql performanceas well as this one oracle performance tuning.

If you're looking for more formal training on Oracle, why not take a training course with one of our partners? See our Oracle training page for more details.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.