OCP 1Z0 052 15

15. Which two statements are true about Shared SQL Area and Private SQL Area? (Choose two.) 
A.Shared SQL Area will be allocated in the shared pool. 
B.Shared SQL Area will be allocated when a session starts. 
C.Shared SQL Area will be allocated in the large pool always. 
D.Private SQL Area will be allocated in the Program Global Area (PGA) always. 
E.Shared SQL Area and Private SQL Area will be allocated in the PGA or large pool. 
F.The number of Private SQL Area allocations is dependent on the OPEN_CURSORS parameter. 
Answer: AF 

Shared SQL Area 位于sga中。实例启动就就分配，并可在运行过程中动态或手动调整。
Private SQL Area 当使用共享服务器时位于sga中，当使用专用服务器时位于pga中
Private SQL Area 的个数依赖于参数 OPEN_CURSORS 的设置

可以以下语句观察该参数及其它类似参数的设置及应用情况

SQL> select * from v$resource_limit;
RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                                       27              34        150                  150
sessions                                        31              40        248                  248
enqueue_locks                                   24              37       3160                 3160
enqueue_resources                               23              47       1308            UNLIMITED
ges_procs                                        0               0          0                    0
ges_ress                                         0               0          0            UNLIMITED
ges_locks                                        0               0          0            UNLIMITED
ges_cache_ress                                   0               0          0            UNLIMITED
ges_reg_msgs                                     0               0          0            UNLIMITED
ges_big_msgs                                     0               0          0            UNLIMITED
ges_rsv_msgs                                     0               0          0                    0
gcs_resources                                    0               0  UNLIMITED            UNLIMITED
gcs_shadows                                      0               0  UNLIMITED            UNLIMITED
smartio_overhead_memory                          0               0          0            UNLIMITED
smartio_buffer_memory                            0               0          0            UNLIMITED
smartio_metadata_memory                          0               0          0            UNLIMITED
smartio_sessions                                 0               0          0            UNLIMITED
dml_locks                                        1              23       1088            UNLIMITED
temporary_table_locks                            0               2  UNLIMITED            UNLIMITED
transactions                                     1               2        272            UNLIMITED
branches                                         0               0        272            UNLIMITED
cmtcallbk                                        1               2        272            UNLIMITED
max_rollback_segments                           11              11        272                65535
sort_segment_locks                               5               9  UNLIMITED            UNLIMITED
k2q_locks                                        0               0        496            UNLIMITED
max_shared_servers                               1               1  UNLIMITED            UNLIMITED
parallel_max_servers                             0               2         80                 3600
27 rows selected
Executed in 0.125 seconds