Examine the current value for the following parameters in your database instance:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M
LOG_BUFFER = 200M
You issue the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
Which statement is true?
A.
It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
B.
It succeeds only if memory is available from the autotuned components if SGA.
C.
It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within
SGA_TARGET.
D.
It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within
SGA_MAX_SIZE.
Explanation:
* The SGA_TARGET parameter can be dynamically increased up to the value
specified for the SGA_MAX_SIZE parameter, and it can also be reduced.
* Example:
For example, suppose you have an environment with the following configuration:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced
until one or more of the automatically sized components reaches its minimum size. The exact
value depends on environmental factors such as the number of CPUs on the system. However,the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M
* DB_8K_CACHE_SIZE
Size of cache for 8K buffers
* For example, consider this configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken
away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M
to 112M means that the 16M is given to the automatically sized components.
I would suggest that [B] is actually the correct answer.
I created a 12c DB, set these params and then entered the resize command. Looking at v$sga_resize_ops I saw the memory being taken away from the DEFAULT buffer_cache.
It succeeds only if memory is available from the auto tuned components of the SGA.
The DB_8K_CACHE_SIZE memory will be taken from the auto tuned component of the SGA, which is the DEFAULT buffer cache, only if there is enough memory in the DEFAULT buffer cache.
Hence B is the correct answer.
The given answer, D is wrong.
Setting a value for SGA_TARGET enabled ASMM. This is the amount of memory allocated to the SGA in total, which can grow up to SGA_MAX_SIZE. With ASMM, some SGA components are autosized and some are not. The ones which are not include LOG_BUFFER and DB_nK_CACHE_SIZE. Given the information in the question, the SGA has 376 Mb (700-124-200) for the autosized components, plus 124Mb for DB_8K_CACHE_SIZE plus 200 for LOG_BUFFER giving a total of 700Mb. The SGA could grow to 1024Mb which means it has 324Mb of expansion potential. Increasing DB_8K_CACHE_SIZE by 16Mb (140-124) could potentially be accommodated within the 324Mb available for expansion. However, that is not one of the answer options. Assuming the SGA stays at 700Mb total, the increasing one of the manually sized components would mean taking memory away from one (or more) of the autosized components. Hence, B is the correct answer.
Note, DB_8K_CACHE_SIZE cannot be the default buffer cache if it has a value other than zero. The default block size is 8K, hence the block size of the default buffer cache (DB_CACHE_SIZE) is also 8K. However, if DB_CACHE_SIZE is in 8K blocks, you cannot also size the DB_8K_CACHE_SIZE buffer as well.
H*T*H
B
I’ve seen this question on a number of sites and oddly all of them chose D to be the answer. But it seems B is the more appropriate answer. Because increasing the DB_8K_Cache_size should take the memory from the automatically sized components and this can only happen if there is memory available. I suspect its just a typo because even the explanation given points it more to B than D
http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN13021
The manually sized parameters listed in Table 6-3, if they are set, take their memory from SGA_TARGET, leaving what is available for the components listed in Table 6-2.
6.3 :
The log buffer
The keep and recycle buffer caches
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Nonstandard block size buffer caches
DB_nK_CACHE_SIZE
so the extra 16M should be taken from SGA_TARGET. If required, it could grow 124M because 1024 – (700+200) = 124 Free Mo in SGA_MAX_SIZE.
Does anybody see what’s wrong in my thinkings ?
B
Great internet site! It looks extremely good! Maintain the excellent work!
CSGO http://www.968daili.com/comment/html/index.php?page=1&id=42980
We prefer to honor numerous other web internet sites around the net, even when they arent linked to us, by linking to them. Under are some webpages really worth checking out.
Below you will find the link to some web sites that we feel you’ll want to visit.
below you will obtain the link to some sites that we believe you must visit
here are some links to web-sites that we link to simply because we assume they are worth visiting
Sites of interest we’ve a link to
here are some links to internet sites that we link to simply because we consider they’re really worth visiting
B
It can be D!
Because by increasin 16MB to DB_8k_CACHE_SIZE, if it requires more space than SGA_MAX_SIZE. Then it must fail.
B