Sizing Shared Pool

SHARED POOL SIZING

Question 1:-
You have reserved memory within the Shared Pool by using the SHARED_POOL_RESERVED_SIZE
parameter to accommodate large space allocations while compiling PL/SQL blocks and database triggers.
While observing the performance of the reserved area, you find that the value of the REQUEST_FAILURES
column in the V$SHARED_POOL_RESERVED view is continuously increasing.
Which statement is true in this scenario?
A.The parsing of the statement fails due to invalidations.
B.The execution of the statement fails due to invalidations.
C.The space allocated for the reserved area is not enough.
D.The syntax in the PL/SQL blocks is causing the compilation failure.
Answer: C

REQUEST_FAILURES:-Number of times that no memory was found to satisfy a request.

Question 2:-
While doing a routine performance check of your test database, you find that a large number of statements are
getting invalidated in the library cache, thereby causing the statements to be reparsed. What could be the reason for the invalidations?
A.Stored outlines are being used.
B.There is not enough free space in the Shared Pool.
C.The Shared Pool Reserved Space is not configured.
D.The structures of dependent objects have been modified.
E.Statistics have not been collected on the referenced columns.
Answer: D
Invalidations are caused mainly due to two reasons A.When data is aged out B.When its structure is changed.

Question 3:-
 Identify the common causes of poor library cache performance. (Choose all that apply.)
A.large pool too small
B.shared pool too small
C.SQL cursor not being shared
D.User Global Area (UGA) too small
E.high number of invalidations occurring
Answer: BCE
Small size shared pool causes frequent aged out of data from LC and DDC as there size depends on Shared pool's Overall size.SQL cursor not being shared causes LC not to share parsing and execution plan,also invalidation causes poor performance of LC.

Question 4:-

Which three techniques should a DBA use to reduce the overhead on library cache? (Choose three.)
A.flush shared pool at regular intervals
B.configure Keep and Recycle buffer pools
C.ensure that the shared pool is sufficiently sized
D.set the CURSOR_SHARING parameter to SIMILAR or FORCE
E.set CURSOR_SPACE_FOR_TIME initialization parameter to FALSE
F.avoid data definition language (DDL) statements that cause invalidations
G.use literals in SQL statements when the CURSOR_SHARING parameter is set to EXACT
Answer: CDF

Question 5:-
Because of fragmentation, a frequently required large PL/SQL package is being flushed out of the library
cache. Which strategy would you recommend to ensure the availability of the package in the library cache?
A.pin the package in your Private SQL Area
B.set the LOCK_SGA parameter within the package to ensure memory space availability for the package
C.load the package in the library cache during instance startup using a startup trigger and use the dbms_shared_pool.keep procedure to keep the object in the shared pool
D.load the package in the library cache minimum 2 minutes after the instance startup, use the dbms_shared_
pool.keep procedure to keep the object in the shared pool, and ensure that you do not flush the shared pool
Answer: C
dbms_shared_pool.keep(pl/sql package) is used to pinned the package in LC which exempts it from being flushed out.

Question 6:-

View the Exhibits and compare the baseline and current data dictionary cache statistics in statspack reports.
The DBA had collected baseline statistics by using statspack six months ago. For the purpose of performance
tuning, the DBA collects the current statistics. The database system does not have a heavy insert load. What
recommendation would you make on the basis of the statistics provided in the Exhibits?

A.configure keep pool
B.increase shared pool size
C.configure recycle pool
D.configure shared pool reserved area
Answer: B
Number of request for free extends has been increased and hence we need to increase shared pool size to maintain its metadata.

Question 7:-
Which three types of objects can you keep in the shared pool by using the dbms_shared_pool.keep procedure?
(Choose three.)
A.views
B.tables
C.triggers
D.sequences
E.PL/SQL functions
Answer: CDE
Tables and Views are placed in Database buffer cache,where as triggers sequences,pl/sql functions,procedures,,packages,cursor objects..etc are placed in shared pool.

Question 8:-
Which three types of objects can you keep in the shared pool by using the dbms_shared_pool.keep procedure?
(Choose three.)
A.types
B.triggers
C.LOB segments
D.SQL cursor objects
E.PL/SQL procedures
Answer: BDE


Question 9:-
You want to reserve memory in your database instance to accommodate large memory allocations during
operations such as PL/SQL and trigger compilation. What must you do to achieve this?
A.configure Large Pool
B.increase the size of the Shared Pool
C.configure the User Global Area (UGA)
D.configure the Shared Pool Reserved area
E.increase the size of the System Global Area (SGA) by using the SGA_MAX_SIZE parameter
Answer: D
Accommodating large memory allocations can cause large amt of small memory flushing,which in turn causes fragmentation..to avoid such problem Oracle has developed Reserved area in shared pool for such operations to take place smoothly without effecting others.



Question 10:-
Users complain of slow response time in your online transaction processing (OLTP) system. You compare
current statistics with baseline statistics. You notice that in the current statistics library cache, the hit ratio has
decreased and there are request misses for shared pool reserved area. Given below are the sizes of shared pool and
shared pool reserved area:
SHARED_POOL_SIZE = 500 MB
SHARED_POOL_RESERVED_SIZE = 50 MB
What kind of strategy would you suggest for performance tuning?
A.increase the size of only the shared pool
B.decrease the size of the shared pool reserved area
C.increase the size of only the shared pool reserved area
D.increase the sizes of both the shared pool and the shared pool reserved area
E.increase the size of the shared pool and decrease the size of the shared pool reserved area
Answer: D
SHARED POOL RESERVED area has by default size of 5% of total Shared Pool and max goes to 50%,hence increasing only SHARED_POOL_RESERVED area will decrease space in Shared Pool,so we need to increase both of them.

Question 11:-
Which two statements are true regarding the User Global Area (UGA) for a database that is running in
Shared Server mode? (Choose two.)
A.The Stack space is a part of the UGA.
B.The UGA is always stored in the Shared Pool.
C.The UGA is stored in the Shared Pool if the large pool is not configured.
D.AIl the user information is stored in the UGA, therefore individual shared servers do not need to remain bound to a user session.
E.Any of the shared server processes can access users' session information, which is stored in the UGA.
Answer: CEUGA  is part of PGA in dedicated mode but in Shared server mode it resides in shared pool if Large Pool is not configured.If it is configured then UGA is allocated in Large Pool.

Question 12:-
Your database is running in Shared Server mode. You have set the LARGE_POOL_SIZE parameter to 1 MB.While the database is functioning, you realize that the large pool is running out of space because of the increasing number of user requests.
What will the effect be if there is no more free space left in the large pool to store new information?
A.The Shared Pool will be used to store the new information.
B.The large pool will be dynamically resized to store the new information.
C.User requests will fail but the database instance will continue to function.
D.The session will be switched to dedicated server mode and the PGA will be used.
E.The current information will be moved from the large pool to the Shared Pool, and the Shared Pool will
continue to be used.
Answer: C

Question 13:-
You executed the following SQL command in your production database. The results of this command are
displayed in the Exhibit

SQL> SELECT shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_saved
FROM v$shared_pool_Advice;
View the Exhibit.
Which statements are true regarding the result? (Choose all that apply.)
A.There are no additional hits on the size range for the library cache.
B.The estd_lc_size column value indicates the current size of the Shared Pool.
C.The pool_size column value indicates the range of the Shared Pool size that is estimated.
D.The estd_lc_time_saved column values are same for all pool sizes thereby indicating the Shared Pool size needs to be increased.
Answer: AC
For different shared pool size same size of LC is being Advised.hence we can conclude that as there are no additionally hits required the size of LC remains same despite of increasing shared pool size.

THANK YOU