Because of its central role in database operations, a shared pool that is too
small may have a greater impact on performance than a database buffer cache that is
too small. If the requested database block isn??™t in the database buffer cache, Oracle
will perform an I/O to retrieve it, resulting in a one-time performance hit.
A shared pool that is too small will cause poor performance for a variety of reasons,
affecting all users. These reasons include the following:
??? Not enough data dictionary information can be cached, resulting in frequent
disk access to query and update the data dictionary.
??? Not enough SQL can be cached, leading to memory ???churn,??? or the flushing of
useful statements to make room for incoming statements. A well-designed application
issues the same statements repeatedly. If there isn??™t enough room to cache
all the SQL the application uses, the same statements get parsed, cached, and
flushed over and over, wasting valuable CPU resources and adding overhead to
every transaction.
??? Not enough stored procedures can be cached, leading to similar memory churn
and performance issues for the program logic stored and executed in the
database.
Pages:
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397