Donald K. Burleson Oracle Tips

Secrets of Oracle flushing

There are cases where flushing the Oracle shared pool can dramatically improve performance.  This is normally true in an environment where the Oracle application issues a large amount of non-reusable SQL statements.  The library cache becomes floored with non-reusable SQL and there will be significant slowdowns as Oracle futilely parses incoming SQL looking for a pre-parsed matching statement.

For example, the following SQL query contains a hard-coded literal, and cannot be re-used:

Select customer_name from customer where region = ‘WEST’;

Most Oracle professionals will look at the V$SQL view to see if their database has lot’s of non-reusable SQL.  In ad-hoc SQL environments such as data warehouses, we commonly see quite a large amount of non-sharable SQL in the shared pool.  The performance problem arises when because Oracle only parses the first 200 bytes of an SQL statement.  When the first 200 characters of ad-hoc SQL statements are identical, Oracle may parse through thousands of SQL statements looking for a match.  This overhead can be avoided by:

1. Pinning all commonly-used PL/SQL packages.

2. Periodically issuing the ALTER SYSTEM FLUSH SHARED POOL command;

In cases where the shared pool is clogged with non-reusable (ad-hoc) SQL, this strategy can greatly improve performance.

Also, note that in Oracle8i (release 8.1.6) we have a new feature called cursor_sharing.  When cursor_sharing=force, Oracle will dynamically change the literal value in the SQL statement and replace it with a host variable.  This makes the SQL statement reusable by subsequent tasks.  In the example above, cursor_sharing=force changes the SQL as follows:

select customer_name from customer where region = ‘:var1’;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: