Question: I want to know when it is
OK to flush my shared pool. Is it OK to flush the
shared pool when testing SQL statement execution?
Answer: Yes, when testing the SQL it
is a good idea to periodically flush the shared pool to
remove older execution plans.
alter system flush shared_pool;
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:
- Pinning all
commonly-used PL/SQL packages.
- 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.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
recreate indexes and constraints
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
