Configure the database environment to support optimal data access performance

性能调优常见的错误就是以为问题的现象就是问题的本质，有一些性能问题现象没有足够的性能统计数据来确定问题的实质，例如：
1.缓慢的物理IO。通常是由于磁盘配置不佳，也有可能是由于较差的SQL开销了大量不必要的物理IO。
2.Latch争用。很少能通过重新配置实例来减少latch争用，通常都是通过调整应用解决。
3.过高的CPU使用率。系统CPU空闲率不高，可能是系统本身配置不高，为性能较差的SQL或应用程序导致。
 
有2类方法调优：
1.主动监控：通常通过定期的调度，检查一些性能统计指标，发现系统性能和资源使用的变化。
2.消除瓶颈：针对性能问题进行调整优化。通常是为了减少资源使用和减少操作完成所需的时间。

配置和使用内存
正确地配置数据库内存有效地使用，可以大幅度提高数据库性能。
 
内存的分配
ORACLE数据库将信息存储在内存和磁盘上。内存的访问要比磁盘快得多，磁盘访问通常要花费10ms左右。物理IO也会增加CPU的资源使用，即是wio。出于这个原因，频繁访问的数据应该从内存操作，而不是从磁盘请求。
该项性能目标是尽可能地减少物理IO的开销，尽可能地从内存得到数据，提高效率。
 
自动内存管理
数据库自动内存管理可以通过配置初始化参数SGA_TARGET和SGA_MAX_SIZE。10g中自动内存管理只能管理SGA，11g开始自动内存管理可以管理PGA，初始化参数变成了MEMORY_TARGET和MEMORY_MAX_TARGET。
数据库调整目标内存大小，再重新分配所需的SGA和PGA。ORACLE推荐使用自动内存管理，如果必须要配置内存分配，可以使用Memory Advisor来考虑。
自动SGA管理，自动分配以下内存池：
1.默认的buffer cache
2.共享池
3.大池
4.java池
5.streams池
如果这些池设置了非0值，自动内存管理会将设置值作为最小值来调节大小。
 
以下池的大小需要手动设置，不受自动内存管理的影响：
1.log buffer
2.其它buffer cache，如KEEP，RECYCLE，其它非标准块的缓冲池。
3.SGA固有开销和其它内部分配的内存。
 
手动设置这些池，需要设置LOG_BUFFER，DB_KEEP_CACHE_SIZE，DB_RECYCLE_CACHE_SIZE，DB_nK_CACHE_SIZE这些参数。内存分配这些池也从SGA_TARGET中扣除，剩余的SGA_TARGET，在自动内存管理下自动调节分配。

操作系统内存使用
减少换页
使用操作系统监控工具检查操作系统，是否存在大量换页的情况。如果是，说明系统的总内存并没有足够大，可以hold住分配的所有内存。需要增加系统内存，或者减少内存分配。
将SGA固定在内存中
因为SGA的目的是为了存储数据在内存中使其可以被快速访问，那么SGA就应该一直在主要内存里。如果SGA被换置到磁盘，数据就不再能够快速访问了。在许多操作系统上，换页的缺点大大超过了SGA的优势。
使用LOCK_SGA参数可以将SGA锁在内存中避免被换出。当启用LOCK_SGA时，数据库就不再使用MEMORY_TARGET和MEMORY_MAX_TARGET参数了，使用LOCK_SGA参数也需要操作系统支持，如果不支持会忽略该参数设置。
考虑有足够的内存给其它用户
调整SGA大小时，需要确保有足够的内存给个别服务进程和其它运行在系统上的程序使用。

有效地使用Buffer Cache
先确认被频繁执行的SQL语句和逻辑读较大的SQL语句已优化，再考虑调整buffer cache的大小。
使用V$DB_CACHE_ADVICE
该视图在参数DB_CACHE_ADVICE设置为ON时有效，该视图显示了buffer cache在一定大小范围内的模拟命中率。

SELECT size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM
V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

该视图可以为buffer cache的调整提供一些参考。

计算buffer cache命中率
较高的命中率说明大部分数据都是从缓冲池中直接获得，而不是从磁盘请求的。

增大buffer cache的内存分配
增大cache大小，首先设置参数DB_CACHE_ADVICE为ON，让cache的统计稳定，然后查看V$DB_CACHE_ADVICE视图，根据预测的增大cache会减少的物理IO，如果需要增大cache内存，尽可能分配了内存之后不会引起系统的换页。增大buffer cache，可以增大DB_CACHE_SIZE参数。DB_CACHE_SIZE参数指定了数据库标准块的默认cache大小。如果要创建非标准块的表空间，必须设置其他块的单独cache，使用DB_nK_CACHE_SIZE参数设置非标准块cache。

多种Buffer Pool
单个默认的buffer pool通常可以满足大多数系统，但可能某些应用需要配置多种buffer pool，更精细地使用buffer pool。根据段非典型的访问模式，将段中的块放置到KEEP池和RECYCLE池中。频繁访问的小对象可以放在KEEP池中，偶尔访问的大对象，例如一天批量作业只访问一次的大段，放在RECYCLE池中。KEEP池可以让频繁访问的对象一直在cache中，RECYCLE池让大对象不过多地占用cache空间。
使用多种buffer pool
定义对象的默认缓冲池，使用STORAGE子句中的BUFFER_POOL，该子句在CREATE/ALTER TABLE、CLUSTER、INDEX等SQL语句中，在buffer pool被指定之后，该对象随后的所有块读到内存中都放到相应的池中。
如果定义的是分区表或分区索引，每个分区会从表或索引那里继承buffer pool的设置。

SQL> CREATE TABLE t1 (
  2  my_date   DATE NOT NULL,
  3  my_number NUMBER(12,10) NOT NULL,
  4  my_row    NUMBER(12) NOT NULL)
  5  STORAGE (BUFFER_POOL KEEP);

使用ALTER语句修改了对象的buffer pool，该对象在缓冲池中的块仍然会留在原来的缓冲池中，直到老化或重新装载才会放到新的缓冲池中。

配置和使用共享池和大池
Oracle使用共享池cache许多不同类型的数据，cache的数据包括PL/SQL块和SQL语句的文本和可执行格式，字典缓存数据，结果缓存数据，和其它一些数据。合理使用和设置共享池可以至少减少以下4种情况的开销：
1.避免解析开销。如果SQL语句已经在共享池中，就节省了主机CPU资源，也减少了SQL整体运行时间。
2.latch资源使用显著降低。这可以导致更好的扩展性。
3.共享池内存的需求降低。理想情况下是所有的应用程序都使用池中相同SQL语句和字典资源。
4.节省IO资源。不需要频繁地从磁盘获得数据字典的内容。

共享池的概念
数据字典缓存和库缓存中的命中率比buffer cache的命中率更为重要，因为一旦miss，开销比buffer cache更大。
有几个数据特性将会在共享池中分配大内存，例如：共享服务器，并行查询，RMAN。ORACLE建议这些特性分开对SGA内存的使用，通过配置大池来实现。
共享池中分配的内存是chunks，这种chunking结构使得在装载大对象(超过5K)时，不需要分配一段连续的空间。这种方式可以减少数据库运行中由于没有足够大的连续空间而产生的碎片问题。
很少出现，java，pl/sql，SQL语句的游标需要共享池分配超过5K的内存。ORACLE数据库将共享池隔出一小部分，如果共享池中没有足够的空间时，将使用该部分空间，从共享池隔离出来的这部分空间称为保留池。
字典缓存概念
该缓存的信息包括：用户名，段信息，概要数据，表空间信息，序列号，元数据，关于schema对象的信息。
库缓存概念
该缓存持有了SQL游标、PL/SQL存储过程、java类的可执行格式。执行硬解析比软解析需要更大的开销。
SQL的共享标准
ORACLE数据库会自动判断SQL语句或PL/SQL块在共享池中是否存在相同的语句。
SQL语句生成hash值，如果没有找到匹配的hash值，SQL语句不存在于共享池中，就需要执行硬解析。
通常只有文字有差异的SQL语句才不能共享SQL area，例如下面2条SQL，不能解析到同一个SQL area：
SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;
此规则的唯一例外的情况，是设置了参数CURSOR_SHARING，将参数CURSOR_SHARING设置为SIMILAR或FORCE时，类似的语句可以共享SQL area。

有效地使用共享池
共享池也可以支持数据仓库系统中非共享SQL的应用，该类型应用的SQL有着低并发，高资源开销的特点。在这种情况下，最好使用非共享的文本内容SQL，而不使用绑定变量，这样可以让优化器更好地从字段的选择性中预估，生成一个更好的数据访问计划。

在OLTP系统中，有以下几种方式可以确保有效地使用共享池和相关资源：
共享游标
1.只要数据访问方式不会有变化，尽可能使用绑定变量代替文字SQL。
2.应用程序设计中，避免出现用户大量发出动态非共享的SQL。
3.确保应用用户不会改变个别会话的优化方法和目标。
4.应用设计要建立以下策略：
  - 在SQL语句和PL/SQL中标准化绑定变量的命名和空间间隔的约定。
  - 尽可能使用存储过程，用户发起存储过程调用时，自动共享PL/SQL area。因为存储过程已被存储为解析格式，减少了运行解析的时间。
5.如果SQL语句是相同的，但没有被共享，可以查询V$SQL_SAHRED_CURSOR视图确认原因。通常是由于优化器设置或绑定变量不匹配。

避免执行DDL
在段的高峰访问时期，避免对该段执行DDL操作，由于执行DDL操作，往往导致涉及该段的SQL语句失效，需要重新解析。

调整共享池大小
在大多数OLTP系统中，共享池大小时应用程序性能的重要因素。如果应用系统发出的SQL语句数量较少，且离散，例如DSS，决策支持系统，共享池的大小就不是太重要。
理想环境下，共享池应该足够大cache住所有频繁访问的对象。

Shared Pool: Library Cache Statistics
V$LIBRARYCACHE视图的RELOADS列，统计了之前cache的SQL老化之后又重新装载(重新解析)的总数。在一个有效使用共享池，共享池有一个最优大小的系统中，RELOADS的统计应该趋于0。
V$LIBRARYCACHE视图的INVALIDATIONS列，统计了库缓存数据无效，不得不重新解析的次数。INVALIDATIONS的统计也应该趋于0，该指标较高意味着某些操作(例如DDL)导致本该共享的SQL语句变得无效。在OLTP系统中，高峰时段该指标统计应该要趋于0。

使用大池
不同于共享池，大池不使用LRU算法，没有LRU列表。ORACLE数据库不会老化大池中的对象，如果实例有以下情况，需要考虑配置大池：
1.并行查询。并行查询使用共享池内存来cache并行执行的消息buffer。
2.RMAN。在备份和resotre操作时，RMAN使用共享池来cache IO buffer。ORACLE数据库需要分配几百K大小的buffer，用于IO服务进程和备份恢复操作。
3.共享服务器。在共享服务器体系中，每一个客户端进程的会话内存保存在共享池中。

配置了大池，以上操作就从大池中分配内存，其中值得注意的是，只有当设置了SGA_TARGET，并行查询才从大池中分配buffer。

使用Cursor_space_for_time
如果没有发生过库缓存miss的情况，可以考虑设置参数CURSOR_SPACE_FOR_TIME来加快执行调用。该参数指定了是否从库缓存中回收空间，给新的SQL语句使用。参数CURSOR_SPACE_FOR_TIME设置的值有以下含义：
1.如果设置参数CURSOR_SPACE_FOR_TIME为false（默认值），不论应用游标相关的SQL语句是否open，都有可能回收游标。在这种情况下，ORACLE数据库就必须校验包含SQL语句的游标是否在库缓存中。
2.如果设置参数CURSOR_SPACE_FOR_TIME为true，只有当与应用游标相关的所有语句都关闭时才回收游标。在这种情况下，ORACLE数据库不用校验游标是否在cache中，因为游标只要open就不会被回收。

设置该参数为true，可以节省ORACLE数据库的一小部分时间，也可以提升执行调用的性能，但同时也需要更大的共享池支持。
当发现在执行调用时出现库缓存miss的情况，不要设置该参数为true。

Caching Session Cursors
会话游标缓存，包括SQL和PL/SQL关闭的游标，包括递归SQL。
游标缓存对应用来说很有帮助，应用如果需要反复执行相同的SQL，不断地解析调用，虽然是软解析但也有一定的开销，因为需要重新打开游标，如果直接使用已经打开了的游标，就减少了软解析，同时也提升了性能。
启用会话游标缓存
以下参数设置与游标缓存相关：
1.SESSION_CACHE_CURSORS。该参数设置了每个会话可以缓存的已关闭游标的最大值。11g版本默认设置是50，之前版本是0。设置该参数时，也需要避免会话打开的游标过多，从而导致填满库缓存或强制过多的硬解析。

2.OPEN_CURSORS。该参数设置了一个会话可以同时打开的游标最大数量。例如设置了该参数为1000，那么每个会话一次性可以打开多达1000个游标。

配置保留池
如果共享池的剩余空间不足，ORACLE数据库必须寻找足够的内容来满足请求。该操作需要检测并持有一段时间的latch资源，从而也给其他同时发生的内存分配造成一些轻微影响。
因此，ORACLE数据库内部保留了一部分共享池内存，当共享池空间不足时可以使用。保留池可以有效地分配大chunks。
通常不需要修改默认的保留池设置，保留池大小可以设置SHARED_POOL_RESERVED_SIZE参数。如果常发生需要大分配共享池，可以增大一些保留池。
保留池reserved pool，保留池默认大小是共享池的5%，最小值是5000，最大设置值是共享池的一半。

keeping大对象避免老化
使用DBMS_SHARED_POOL包，可以将对象KEEP在共享池中，让他们不根据正常的LRU机制老化。
DBMS_SHARED_POOL包在以下情况很有用：
1.当装载大的PL/SQL对象，例如STANDARD和DIUTIL包。
  当大PL/SQL对象装载时，可能会因为需要老化较小的对象出共享池而影响响应时间。有时候，可能没有足够的内存分配大对象。
2.触发器频繁地执行，可能想要将编译好的触发器KEEP在共享池中。

如下方式，使用DBMS_SHARED_POOL包将SQL area或PL/SQL area pin住：
1.确定哪些包或游标需要pin在内存里。
2.启动数据库。
3.调用DBMS_SHARED_POOL.KEEP来pin住对象。

--确认SQL语句的地址和hash值
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';

--替代进下面的语句

exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

Flag Values	Description
C	cursor
JC	java class
JD	java shared data
JR	java resource
JS	java source
P	procedure
Q	sequence
R	trigger
T	type

 

SQL> CREATE OR REPLACE PROCEDURE testproc IS
2 BEGIN
3 NULL;
4 END testproc;
5 /
Procedure created.
SQL> exec sys.dbms_shared_pool.keep('testproc', 'P');
PL/SQL procedure successfully completed.

SQL> SELECT owner, name, type
  2  FROM gv$db_object_cache
  3  WHERE kept = 'YES'
  4  AND owner = 'SYS' and name like 'TEST%';

OWNER     NAME              TYPE               
--------  --------------   ------------------
SYS       TESTPROC          PROCEDURE
 
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
BEGIN dbms_shared_pool.purge('SYS.TESTPROC'); END;

*
ERROR at line 1:
ORA-06596: object cannot be  purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 58
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 77
ORA-06512: at line 1

SQL> exec dbms_shared_pool.unkeep('SYS.TESTPROC', 'P');
PL/SQL procedure successfully completed.
SQL> exec dbms_shared_pool.purge('SYS.TESTPROC');
PL/SQL procedure successfully completed.

PGA内存管理
私有内存区包含了用于服务进程的数据和控制信息。
这些信息例如游标的实时区。
较大的work area可以显著地提升某些需要消耗大量内存的操作。
设置PGA_AGGREGATE_TARGET值，用于决定PGA的自动管理。
对于OLTP系统，PGA通常只占可用内存的一小部分，例如只占20%，剩下80%的可用内存给SGA用。
对于DSS系统，运行需要大量内存，PGA内存可能占到全部有效内存的70%。
*有效内存为可给数据库使用的物理内存。
设置PGA_AGGREGATE_TARGET参数的参考：
1.对于OLTP系统，PGA_AGGREGATE_TARGET=（内存总大小*80%）*20%
1.对于DSS系统，PGA_AGGREGATE_TARGET=（内存总大小*80%）*50%
视图V$PGASTAT可以查看一些PGA内存的使用统计和自动PGA管理的统计。
视图V$PROCESS每一行表示连接到实例的一个进程，字段PGA_USED_MEM，PGA_ALLOC_MEM，PGA_FREEABLE_MEM，PGA_MAX_MEM可以用来监控这些进程的PGA使用。

配置olap_page_pool_size
初始化参数OLAP_PAGE_POOL_SIZE设置了(单位是bytes)可以分配给OLAP会话的最大paging cache。由于性能的原因，通常设置较小的OLAP paging cache，使用DB_CACHE_SZIE分配较大的默认buffer pool。4M大小的OLAP paging cache比较典型，如果系统的内存有限，可能设置为2M。

优化访问路径
从数据库中获得数据的方法。
获得表数据一小部分子集时，通常使用索引访问。（OLTP系统）
获得表中大部分数据是，通常使用全扫描。（DSS系统）

全表扫描
需要扫描表中所有在高水位线下的块。
多块读，read块的范围大小、读块的数量，可以设置参数DB_FILE_MULTIBLOCK_READ_COUNT。使用多块读，可以使得全表扫描变得更有效率。每个块数据库只read一次。
如果优化器考虑到需要查询表中大多数的块，即使有索引，它也会选择全表扫描。
使用hint /*+FULL*/ 可以强制优化器使用全表扫描。

并行查询执行
当需要全表扫描时，ORACLE还提供了一个使用多个并行执行服务来改善响应时间。
通常并行查询使用在并发量较低的数据仓库，这样可以使用潜在的资源。