1. What’s cursor
A handle or name for a private SQL area inthe PGA. Because cursors are closely associated with private SQL areas, theterms are sometimes used interchangeably.
2. Open_cursors
OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas)
a session can have at once. You can use this parameter to prevent a session from opening an excessivenumber of cursors.
It is important to set the value of OPEN_CURSORS highenough to prevent your application from running out of open cursors. The numberwill vary from one application to another. Assuming that a session does notopen the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.
3. Shared pool包括
(1). Library cache：存储最近的可执行状态（解析过/编译过）的SQL语句和PL/SQL代码。
(1.1) library cache或 data dictionary cache的miss比buffer cache的miss代价大的多。例如软解析只需要CPU和library cache latch资源，而硬解析需要大量的CPU、library cache latch和shared pool latch。硬解析在SQL语句的解析和执行阶段都会发生。所以分配足够的内存是非常必要的。
(1.2) 如果没设置large_pool的情况下，Sharedserver连接，共享查询，RMAN都消耗了大量的shared pool内存。所以在SGA内存设置large pool来处理这些操作很有必要。
(1.3) shared pool中的内存分配以块的形式进行分配，以减少碎片的产生。
(2). Data dictionary cache: 数据字典缓存
(3). Server result cache：缓存查询和PL/SQL执行结果。可选的缓存区，由result_cache_max_size参数控制。
4. 在默认的CURSOR_SHARING=EXACT情况下，不同SQL文本、空格，大小写和注释的SQL语句都不能共享。例如
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT * FROM employees;
Continue reading