In this section let us look at the shared pool advisory and sga target advisory generated in the AWR report.
Shared Pool Advisory :-
Current statistics on my database
NAME TYPE VALUE
------- ------ -----
shared_pool_reserved_size big INTEGER 40M
shared_pool_size big INTEGER 400M
So the value of shared pool “400M‘and Size Factor “1.0” in the shared pool advisory diagram is the same and hence we start looking from the value 1.0.
Lets look at the column “Est LC Time Saved (s)” which has a value of 1,781,789. Rows up and down will have an increase and decrease in the time saved column.
And comparing the value of Est Library Cache Time Saved seconds from all other rows which are before and after the sizing factor “1.0” you will see there are no drastic improvements and hence we can consider current value of shared pool to be in acceptable range.
However if you are using ASMM then shared_pool value need not be assigned as Oracle will automatically assign the value to optimize the performance.
Note : The Shared Pool is the cache for data dictionary objects and the cache for parsed and compiled SQL cursors and PL/SQL objects. If you do not allocate enough memory to the Shared Pool, then there will not be enough room to store information that the applications need to complete quickly. Setting shared pool to a small value will result SQL cursor to be reparsed resulting in increase of Wait Events.
SGA Target Advisory :-
SGA OR System Global Area advisory
We start from the line size factor “1.0” which corresponds to a value assigned in the pfile for sga_target.
Sizing Factor “1.0” has a SGA Target (M) value of “1,024M“.
This report uses a value called DB Time as a measure of the increase or decrease in performance relative to the memory change made. Also the report will summarize an estimate of physical reads associated with the listed setting for the SGA.
From the above diagram of SGA Target Advisory you can see that if we change the value of SGA to “1280M” we would see some performance improvement as we can see the change in the “DB Time seconds” and also “Physical Reads” has a big difference as compared to the value in Sizing Factor “1.0“.
Changing SGA value to “1536M” will not give us any benefit as there is a very low decrease in the value of “Est Physical Reads”.