tuning 第十章（三）

四、Real-time sql monitoring
sql实时监控。默认情况下在一个语句并行执行或者一次执行消耗了cpu或i/o时间超过5秒时，就自动开始sql监控。

查看v$sql_monitor和v$sql_plan_monitor得到统计数据。可以和以下视图一起使用：
■V$ACTIVE_SESSION_HISTORY
■V$SESSION
■V$SESSION_LONGOPS
■V$SQL
■V$SQL_PLAN

监听初始化后，数据库在v$sql_monitor上增加一个动态性能入口。这个入口跟踪关键性能数据，包括elapsed time、cpu time、number of reads and writes、i/o wait time 和various other wait times。这些数据是近似实时更新的，大概每秒一次。

v$sql_monitor视图包含了一系列在v$sql可用的统计数据。不同的是，监控数据不是累积的，v$sql_monitor中一行对应一条sql语句的执行。如果一个语句执行两次，会在该表产生两条语句，而v$sql是一条。

为了唯一定为两条语句中的一个，调用执行键生成混合键。这个执行键后三个属性：
sql_id
sql_exec_statr
sql_exec_id

1.sql plan monitoring
在v$sql_plan_monitor中有实时语句监控中重要的一部分——执行计划的监控。
这个视图类似于v$sql_monitor，随着语句执行数据每条更新，执行结束后数据保留下来。
每条被监控的语句在v$sql_plan_monitor中有多条记录，每行对应一步操作。

2.parallel execution monitoring
随着语句执行的开始，数据库自动监控并行查询、dml、ddl语句。
v$sql_monitor和v$sql_plan_monitor记录为并行执行的每个进程都创建一条记录。

v$sql_monitor中每个并行执行的进程都有一条记录。每条记录对应v$sql_plan_monitor中多条记录。
由于为并行语句分配的执行计划是相同的，共享相同的执行键（sql_id\sql_exec_start\sql_exec_id的结合）。
因此可以通过这个混合键确定整个语句。

3.生成sql监听报告
通过sql监听报告可以查看sql监听数据。监听报告使用以下视图中的数据：
■GV$SQL_MONITOR
■GV$SQL_PLAN_MONITOR
■GV$SQL
■GV$SQL_PLAN
■GV$ACTIVE_SESSION_HISTORY
■GV$SESSION_LONGOPS

以下是生成sql监控报告的方法：
variable my_rept CLOB；
begin
  ：my_rept：=dbms_sqltune.report_sql_monitor();
end;
/

print :my_rept

dbms_sqltune.report_sql_monitor函数默认生成一个文本报告。

4.sql monitoring的启禁用
通过statistics_level=all或typical开启，此外control_management_pack_access必须设置为diagnostic+tuning（默认值）。
（因为sql monitoring是tuning pack的一个特性）

两个hint可以强制使用或禁用sql语句监控：
select /*+monitor*/ from dual;  control_management_pack_access设置为diagnostic_tuning时有效
禁用方式为no_monitor

五、实例恢复性能的调整：fast-start fault recovery

1.实例恢复
发生crash或系统失败时，重做日志几率到oracle数据块的写入是自动完成的，实例和故障恢复自动应用。
正常情况下，如果实例正常关闭，是在内存中的修改已经被写入数据文件后提交检查点，然后完成的。
然而，如果一个单实例数据库失效或者RAC中所有实例失效，oracle需要在下一次启动时进行恢复操作。如果RAC环境下部分实力失效，则存货的节点自动进行实例恢复。
实例和失效恢复发生包括两步骤：事务恢复、失效恢复（Instance and crash recovery occur in two steps: cache recovery followed by transaction recovery.）
数据库需要在恢复完成后才能打开。因此提高失效恢复的性能对于提高可用性也是有提升的。

（1）cache recovery（rolling forward）
实例恢复时，oracle在受影响的数据块上应用所有提交和未提交的修改。实例恢复的过程与数据库的修改比例和检查点间隔成比例。

（2）transaction recovery（rolling back）
为了保证数据库的一致性，未提交的修改在恢复时必须被回滚掉。在事务恢复过程中，数据库应用回滚段撤销未提交的修改。

（3）checkpoints and cache recovery
oracle定期记录检查点，检查点就是当前系统的最高SCN号，表示此SCN及之前的操作都已经写入数据文件了。如果发生实例失效，恢复时只需要将此SCN号之后的数据重新应用。恢复过程的快慢由两个因素决定：数据块的最大SCN与检查点SCN的差值，找到这些修改需要读取的日志块数量。

顺序检查点将脏buffer写入数据文件很频繁，因此减少了实例恢复的时间。如果检查点经常发生，那么将当前检查点和日志包含的日志结尾的重做记录相关数据块更少，意味着实例恢复时间少。
但是，在更新操作频繁的系统中，频繁的检查点会降低醒呢光，由于检查点出发了DBWn进程的写操作。

为了最小化实例恢复时间，应该强制oracle经常进行检查点操作，以便保持重做日志记录在恢复时应用的最少。但是更新频繁地系统中又不能太频繁的生成检查点。如果日常操作的效率比实例恢复的时间重要，则可以减少检查点的生成频率。

2.配置实例恢复的时间：fast_start_mttr_target
此特性减少了实例恢复的时间，对恢复进行限制，通过限制脏buffer的数量和最近的重做记录与最近检查点间生成的重做记录生成数量来预估回复时间。

（1）
fast-start fault recovery的基础是fast-start checkpointing。摒弃了常规的（写入量大的）事件驱动检查点，fast-start检查点递增发生。
每个dbwn进程定期将缓存写入磁盘并更新检查点位置。最先修改的块优先写入，以确保每次写操作能够使检查点前移。快速检查点限制了常规检查点中的大量写入及随之发生的i/o。
使用快速检查恢复特性时，fast_Start_mttr_target参数缩短了恢复时间，明确了恢复的时间限制（MTTR）mean time to recover，也就是从实例恢复到结束能花费多少秒。设置了fast_start_mttr_target后，数据库尝试用增量检查点写入来达到此目标，可以预估一下系统实例失败所需要花费的平均恢复时间并设置。
设置该参数时，应该讲fast_Start_io_target\log_checkpoint_interval\log_checkpoint_timeout参数禁用。

这个参数最大值是3600s，如果超过了此值，oracle改为3600.
设置语句为alter system set fast_Start_mttr_target=30；

原则上讲，该值最小可以设置为1秒。但设置不一定能够生效，数据库启动也是需要时间的。
指定的fast_start_mttr_target下数据库的mttr目标成为effective MTTR target。可以通过v$instance_recovery的target_mttr列获取。

mttr target的时间实际在最小是可用effective MTTR target，最大为启动和所有块都是脏的情况下的恢复时间之间。

如果设置的值小于最小限制，将采用最小限制的值，时间是数据库能达到的最小值了，但检查点也会最多，影响正常的数据库性能。如果将fast_start_mttr_target设置为一个比实际还要长的时间，性能不会比最坏情况下还差。

（2）减少检查点的频率来优化实时性能
为了减少检查点频率，优化实时性能，可以进行如下操作
设置fast_start_mttr_target=3600,但同时如果发生实例失败则恢复时间会很长；
根据系统生成的redo数量来设置重做日志文件的大小。使日志切换最多二十分钟一次。如果日志文件太小，会增加检查点活动，降低性能。还需要注意，所有重做日志文件大小应该相等。

（3）用v$instance_recovery监控恢复
这个视图展示了当前恢复的参数设置。还可以使用这些数据来判断对检查点影响大。

下面的表列出了估算实例恢复性能的重要的参数：
target_mttr effective MTTR target的时间，如果fast_start_mttr_target未设置，该值为0
estimated_mttr 当前估算的MTTR值，根据当前脏块的数量和日志块数量。

还可以用V$INSTANCE_RECOVERY.TARGET_MTTR与FAST_START_MTTR_TARGET进行比较。两个值应该设置为相等。

3.调整fast_start_mttr_target和使用mttr advisor
通过下面几个步骤来确定fast_start_mttr_target的值：
■Calibrate the FAST_START_MTTR_TARGET
■Determine the Practical Range for FAST_START_MTTR_TARGET
■Evaluate Different Target Values with MTTR Advisor
■Determine Optimal Size for Redo Logs

（1）fast_start_mttr_target的校准
为了限制重做日志的长度和脏数据缓存的数量，此参数使数据库计算内部系统触发值。这种计算是使用读一个redo块的预估时间，估算读写数据块时间和正常负载下的字符，例如多少脏缓存对应多少修改变量等。

初始情况下，使用默认值进行计算。这个默认值会被系统运行中的i/o性能数据及缓存恢复数据所代替。

为了使fast_start_mttr_target的值更准确，需要判断fast_start_mttr_target是否由于数据库失败或硬件失败而校准。如果数据库文件存放在文件系统或者i/o子系统有内存缓存时需要考虑这些，因为在读写时间上与文件是否已经缓存的磁盘系统是不同的。fast_Start_mttr_target的值决定于哪种失败问题发生的可能性更大。
为了有效校准这个值，确保系统负载正常且运行足够长一段时间，并通过实例恢复来验证恢复过程中读redo块的时间和读或写数据块的时间。

（2）Determine the Practical Range for FAST_START_MTTR_TARGET
在校准之后可以通过测试来决定该参数的大小。
将这个参数设置为1，可以知道其最小值

ALTER SYSTEM SET FAST_START_MTTR_TARGET=1;
SELECT TARGET_MTTR, ESTIMATED_MTTR
FROM V$INSTANCE_RECOVERY;

由于当前数据库是启动状态，脏缓存数量比较少，得到的estimated_mttr会比较小，不能很好地反映真实情况。
这时候我们将target_mttr的值作为待设置的最小值。

然后将fast_start_mttr_target设置为3600，得到最大值。

如果得到的两个值相差很小，比如17至19，可以采用19s，因为结果没什么大差别，而19s对于检查点、系统性能最小；如果是18至40s，可能选择30s。

（3）用MTTR advisor评估目标值
STATISTICS_LEVEL设置为typical或all，fast_start_mttr_target设置为非零值即可启用mttr advisor

然后将fast_start_mttr_target的值在合理范围内变化四次，并运行较长时间

通过v$mttr_target_advice可以看到建议信息

（4）为重做日志设置优化
使用v$instance_recovery的optimal_logfile_size判断当前重做日志的大小。这列数据显示基于当前的fast_start_mttr_target值，重做日志文件应该是多少MB。如果该列包含有比当前在线日志文件最小值大的值，应该将在线日志文件的大小设为此值。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-752832/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-752832/