BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]OCP题解-Q001

2017-07-15

OCP每日一题

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.

题解:

Answer:B

首先了解:“DB_nK_CACHE_SIZE”参数 “DB_nK_CACHE_SIZE”参数的取值有很多种,有2k、4k、8k、16k、32k。在设置此参数时,我们需要注意的是,与数据库默认的块尺寸相同的那个参数是不能被设定的。例如,如果数据库的默认块尺寸是8k,那么当我们尝试设置“DB_8K_CACHE_SIZE”参数时便会遭遇报错(报错内容:ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size)。这是由于这个信息已经体现在“db_cache_size”参数上。

测试(使用db_16k_cache_size来测试):

SQL> alter system set db_8k_cache_size=100m;
alter system set db_8k_cache_size=100m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size


SQL> 

默认是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 log_buffer

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_buffer			     big integer 200M
SQL> show parameter cache_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
data_transfer_cache_size	     big integer 0
db_16k_cache_size		     big integer 124M
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 0
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0
SQL> alter system set db_16k_cache_size=140m;

System altered.

SQL> show parameter cache_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
data_transfer_cache_size	     big integer 0
db_16k_cache_size		     big integer 140M
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 0
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0
SQL> 
SQL> alter system set db_16k_cache_size=300m;
alter system set db_16k_cache_size=300m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache


SQL> 

Comments