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:
*TheSGA_TARGETparameter can be dynamically increased up to the value
specified for theSGA_MAX_SIZEparameter, 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.
Why the statement fails?
D.
It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within
SGA_MAX_SIZE.
D
D.
It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within
SGA_MAX_SIZE
of 1024M
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.
B.
Tested ( with db_16k_cache_size because my default block size is 8k):
SQL> show parameter sga_max_size
NAME TYPE VALUE
———————————— ———– ——————————
sga_max_size big integer 1G
SQL> show parameter sga_target
NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 700M
SQL> show parameter db_16k_cache_size
NAME TYPE VALUE
———————————— ———– ——————————
db_16k_cache_size big integer 124M
SQL> show parameter log_buffer;
NAME TYPE VALUE
———————————— ———– ——————————
log_buffer integer 209715200
SQL> alter system set db_16k_cache_size = 140M;
System altered.
SQL> alter system set db_16k_cache_size = 250M;
alter system set db_16k_cache_size = 250M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL> alter system set db_16k_cache_size = 200M;
System altered.
you have done great job testing with. It clearly says Fails in option D – why do you say B is answer.
B tested with :
db_block_size integer 2048
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
System altered.
Check out question 68. 2 components mentioned in this question not governed by ASMM are
LOG_BUFFER
DB_16k_CACHE_SIZE (or any db_*k_cache_size)
which proves that B is correct and the increase will succeed if memory is available to do so.
B is correct Answer!
B
B
Yes, indeed. B is correct. D is wrong.
B
B – http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11011
“However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M”
so A
Correct Answwer is A,
8k is the default size in 12c. you cannot change dynamically.
SQL> alter system set db_8k_cache_size=50m;
alter system set db_8k_cache_size=50m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
SQL> c/50/140
1* alter system set db_8k_cache_size=140m
SQL> /
alter system set db_8k_cache_size=140m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
SQL> c/140/14
1* alter system set db_8k_cache_size=14m
SQL> /
alter system set db_8k_cache_size=14m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
Correct Answwer is A,
Hey,
So if the DB is started with DB_8K_CACHE_SIZE = 124M in spfile it means default block size is not 8K (otherwise if 8k is the default you receive an error at parsing the spfile for ex:
ERROR at line 1:
ORA-01078: failure in processing system parameters
ORA-32003: error occured processing parameter ‘db_8k_cache_size’)
That means that you actually can dinamically modify DB_8K_CACHE_SIZE but only if there is enough memory to accept the setting in this case.
So B is correct.
SQL> show parameter sga
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1G
sga_target big integer 700M
SQL> show parameter log_buffer
NAME TYPE VALUE
———————————— ———– ——————————
log_buffer integer 209715200
SQL> show parameter db_4k
NAME TYPE VALUE
———————————— ———– ——————————
db_4k_cache_size big integer 124M
SQL> alter system set db_4k_cache_size=140M;
System altered.
SQL> alter system set db_4k_cache_size=200M;
System altered.
SQL> alter system set db_4k_cache_size=250M;
System altered.
SQL> alter system set db_4k_cache_size=512M;
alter system set db_4k_cache_size=512M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
Cheers, Z
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
Answer is surely B, as already confirmed by so many.
As D says …
It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within
SGA_MAX_SIZE.
while having value of 124M , and willing to increase it to 144M , which is 20MB extra . For this SGA size it’s with granule of 4MB. So it should work fine under SGA_TARGET.
SGA_MAX_SIZE is set to 1024M while SGA_TARGET=700M, so talking about SMS is not relevant here.