《深入浅出ORACLE》读书笔记
?
?
怎样重建undo表空间：在进行DB迁移的时候（同平台），由于undo空间过大，不打算迁移，准备新建。
启动的时候会报找不到undo表空间的数据文件的错误。可以删除文件启动数据库：alter database datafile 'undo.dbf' offline drop;
然后alter database open;重建undo表空间，并切换：create undo tablespace undotbs datafile '*.dbf' size 100M;
alter system set undo_tablespace='undotbs';
?
通过切换释放过度扩展的表空间：创建新的undo表空间undo2 -》通过v$rollstat来确认原来的回滚表空间所有的回滚段都正常的offline。 -》删除原来的undo表空间drop tablespace undo1 including contents;
?
很多情况我们使用隐含参数强制打开数据库，可能会出现ORA-00600 [4194]的错误。4194通常说明undo段出了问题，如果没有备份我们可以通过特殊的初始化参数强制启动。首先确认当前回滚段的名称，从alert文件获得：undo segment 11 onlined undo segment 12 onlined。对应AUM下的回滚段名称为：'_SYSSMU11$','_SYSSMU12$'?修改参数文件，使用隐含参数 _corrupted_rollback_segments将回滚段标记为损坏，强制启动数据库。._corrupted_rollback_segments=：'_SYSSMU11$','_SYSSMU12$'??数据库正常open后，重建UNDO表空间，删除出问题的表空间。最后修改参数文件，变更undo表空间，取消_corrupted_rollback_segments参数。再次启动数据库。最后建议exp全库导出。
?
?
第8章 等待事件
?
8.1系统有多少等待事件？
SQL>select count(*) from v$event_name;
v$event_name中的parameter1 2 3非常重要，对于不同的等待事件参数的意义各不相同。可以通过v$system_wait_class来获得主要等待事件的等待时间和等待次数信息：
SQL>select * from v$system_wait_class order by time_waited;
?
8.2空闲等待事件有哪些？
SQL>select * from stats$idle_event;
?
8.3获取各进程等待事件：当系统运行缓慢的时候可以用下列语句，看看系统正在等待什么。
SQL>select sid,event,p1,p1text from v$session_wait;
?
从v$session和v$session_wait可以发现系统的瓶颈。10g中oracle对v$session视图进行了加强，把原来v$session_wait的中的所有字段都整合到了v$session中。同时还将v$dba_waiters中的blocking_session字段加入进来。sql_trace等字段内容也加了进来。
?
8.4捕获进程等待事件的SQL，通过session的sid获得SQL
SQL>select sql_text from v$sqltext a where a.hashvalue=
????(select sql_hash_value from v$session b where b.SID='&sid')
????order by piece ASC;
?
8.5查询某个表索引的键值
SQL>select index_name,column_name from user_ind_columns where table_name='表名';
?
v$session_wait记录的信息很重要，但它的信息随session的消失而消失，想获得DB的历史状态及session的历史等待信息是不可能的。然而从10g开始，这一切发生改变。
v$session_wait_history:记录活动session最近10次的等待事件。
ASH的介绍：ASH以v$session为基础，每秒采样一次，记录活动会话等待的事件，采用工作由后台进程MMNL完成。ASH的启用和采用间隔受两个隐含参数的影响：_ash_enable; _ash_sampling_interval;
ASH的信息在内存中滚动，可以通过v$active_session_history来访问，对于每个活动的session，每次采样会在这个视图记录一行信息。这部分内存在SGA中分配,ASH buffers的最小值为1M，最大不超过30M。
产生ASH报告的方式有两种：脚本；OEM。
脚本：$ORACLE_HOME/rdbms/admin/ashrpt.sql????OEM:点击“运行ASH报告”
?
AWR的介绍：内存中的ASH信息最后写入磁盘。AWR以固定时间间隔（默认1小时）为重要统计信息和负载信息执行一次快照，并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间（默认一周），然后被清除。
AWR的采样由后台进程MMON每60分钟执行一次，ASH的信息同样会被采样写出到AWR负载库。虽然ASH buffers被设计成保留1小时的信息，但这个内存是不够的。当ASH buffer写满后，MMNL进程会将ASH信息写出，写出的时候需要过滤，数据量一般是采样数据的10%，写出通过direct path insert完成，减少日志生成。
?
WRH$_ACTIVE_SESSION_HISTORY是v$active_session_history在awr的存储地，它是一个分区表。v$active_session_history中记录的信息会定期（每小时）的刷新到AWR，并缺省保留一个星期用于分析。DBA_HIST_ACTIVE_SESS_HISTORY是WRH$_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现，通过这个视图进行历史数据的展现。AWR的信息在10g中存储在SYSAUX表空间。
?
ADDM的介绍：根据AWR的信息自动进行诊断。
?
关于等待事件的一些信息可以参考我的另一篇笔记：性能调整心得。这里主要介绍enqueue，latch。enqueue等待常见的有ST,HW,TX,TM等。ST enqueue用于空间管理和字典管理的表空间的区间分配。在DMT中典型的是对与uet$和fet$数据字典表的争用。推荐使用LMT或手工预分配一定数量的extent，减少动态扩展时发生的严重队列竞争。
redo copy latch：一个进程在修改数据时产生redo，redo首先在PGA中保存，当进程需要将redo信息copy进入redo log buffer时，需要获得redo copy latch。
redo allocation latch：分配redo空间需要。在一个繁忙的生产系统，该latch通常也是竞争激烈的latch之一。在9iR2中，通过log_parallelism定义oracle中的redo allocation的并发级别。如果大于1，数据库将分配多个redo log buffer区域，每个区域都按log_buffer大小分配。并行redo的生成能够增加更新密集型数据库的吞吐量。通过以下查询来获得redo allocation latch竞争的累计等待时间：
select substr(ln.name,1,20),gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln
where ln.name in('redo allocation','redo copy') and ln.latch#=l.latch#;
如果misses对gets的比率超1%，或者immediate_misses 对（immediate_gets+immediate_misses ）的比率超1%，那么通常认为存在latch竞争。
当主机拥有16-64个CPU时，oracle公司推荐设置log_parallelism在2-8之间。9iR2的缺省值为1。10g中，log_parallelism变为隐含参数，且引入了另外两个参数并允许log_parallelism动态调整，缺省_log_parallelism_dynamic为true。_log_parallelism_max设置为不同于_log_parallelism的参数值。
?
8.6段级统计信息的收集－》v$segment_statistics
???收集的统计信息类数－》v$segstat_name (9iR2为11个，10g15个)
?
8.7通过v$sql_plan可以获得大量的有用信息（记录Library Cache中SQL语句的执行计划）
???获取全表扫描的对象：
???SQL>select distinct object_name,object_owner from v$sql_plan p
???????where p.operation='TABLE ACCESS' and p.options='FULL'
???????and object_owner='CHERVON80'
?
8.8获取全索引扫描对象：
???SQL>select distinct object_name,object_owner from v$sql_plan p
???????where p.operation='INDEX' and p.options='FULL SCAN';
?
8.9查找全表扫描的SQL语句
???select sql_text from v$sqltext t,v$sql_plan p
???where t.hashvalue=p.hash_value
???and p.operation='TABLE ACCESS' AND p.options='FULL'
???order by p.hash_value,t.piece;
?
8.10查找FAST FULL INDEX扫描的SQL语句可以参考以下语句：
????select sql_text from v$sqltext t,v$sql_plan p
????where t.hashvalue=p.hash_value
????and p.operation='INDEX' AND p.options='FULL SCAN'
????order by p.hash_value,t.piece;
?
?
?
第9章 性能诊断与SQL优化
?
9.1使用AUTOTRACE
9i：运行utlxplan脚本，该脚本用来创建plan_table表。为该表创建public同义词，并授权给public: SQL>create public synonym plan_table for plan_table; SQL>grant all on plan_table to public;
运行plustrce.sql创建plustrace角色。DBA用户首先被赋予了plustrace角色，然后可以手工把plustrace赋予public，这样所有用户也具有了使用autotrace的权限。SQL>grant plustrace to public;
set autotrace的几个常用选项：
set autotrace off:不生成autotrace报告。
set autotrace on explain：只显示优化器执行路径报告。
set autotrace on statistics: 只显示执行统计信息。
set autotrace on: 显示执行计划和统计信息。
set autotrace traceonly: 同autotrace on，但不显示查询输出。
?
为了获得格式化或更友好的输出，可以使用dbms_xplan。举例：
SQL>explain plan for select count(*) from dual;
SQL>@?/rdbms/admin/utlxplp;
实质上utlxplp调用了dbms_xplan，从脚本内容就可以看出：select * from table(dbms_xplan.display());
?
10g:增加了一个PLAN_TABLE$字典表，然后基于这个字典表创建公用同义词供用户使用。9i的上述操作对10g来说都是自动的。
?
使用autotrace功能的另一个好处就是能发现各种视图的底层表，可以作为深入研究oracle的一个手段。启动autotrace在数据库内部，实际上启动了2个session连接，一个用于查询操作，另一个用于记录执行计划和输出最终结果。结合v$session,v$process，这通常可以作为一个进程可能对应多个session的范例来讲。通过10046事件的设置，我们可以看到详细的过程。
?
使用autotrace辅助优化SQL的例子： select * from sys_user where user_code='zhangyong' or user_code in (select grp_code from sys_grp where sys_grp.user_code='zhangyong')
通过autotrace发现执行计划的逻辑读很高。改写后的SQL如下：
select * from sys_user where user_code='zhangyong'
union all
select * from sys_user where user_code<>'zhangyong' and user_code in (select grp_code from sys_grp where sys_grp.user_code='zhangyong')
?
一个诊断案例：系统运行缓慢，已影响到了正常的使用。
vmstat发现CPU已经耗尽。--》top没有发现明显过高的CPU使用进程，排除单进程异常CPU消耗的问题。 --》观察当前系统进程的数量：ps -ef|grep ora|wc -l 在持续增加。--》基本判断数据库或应用出现问题导致进程任务无法完成。--》查看各进程等待事件：select sid,event,p1,p1text from v$session_wait; 发现大量的db file scattered read，db file sequential read等待。--》找出引起这些全表扫描的SQL。这里用到一个脚本：select sql_text from v$sqltext a where a.hashvalue=(select sql_hash_value from v$session b where b.SID='&sid') order by piece ASC
-->获得相关SQL后，用autotrace检查SQL的执行计划，发现使用了全表扫描，而这个表有22w行记录。--》获得整个系统全表扫描的情况：select name,value from v$sysstat where name in('table scans(
short tables)','table scans(long tables)'); -->观察表的索引和索引键值，主要通过user_indexes,user_ind_columns来获得。发现有个字段有很好的区分度，但没有建立索引。 --》建立索引，系统恢复正常。
?
对于大小表的定义，oracle内部提供了一个参数_small_table_threshold。缺省值为2%的buffer数量。小于这个值为小表，反之为大表。区分大小表的目的是全表扫描可能引起buffer cache的抖动，缺省下，大表的全表扫描被置于LRU的末端，以尽快老化。从8i开始，ORACLE有了多缓冲池管理技术，使内存使用更加有效。
?
使用SQL_TRACE/10046事件进行数据库诊断。
可以启用实例级别的SQL_TRACE，也可以启用session级别的SQL_TRACE，还可以对特定的session启用SQL_TRACE。实例级别的SQL_TRACE要谨慎使用，需要保证以下条件：
a.至少25%的cpu idle。b.为user_dump_dest分配足够的空间。c.条带化磁盘减轻IO负担。
使用sql_trace前，有两个参数要设置一下：tiemd_statistics=true;??max_dump_file_size=unlimited（9i已经默认是unlimited了）
?
当前session的设置：SQL>alter session set sql_trace=true;??要跟踪的语句；??SQL>alter session set sql_trace=false;
跟踪其他session的设置： SQL>exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,true)???session执行任务??SQL>exec dbms_system.set_sql_trace_in_session(SID,SERIAL#,false)
?
如果要对其他用户session的参数进行设置，需要用到dbms_system的另外的过程。举例：
SQL>begin
????sys.dbms_system.set_bool_param_in_session(SID,SERIAL#,'timed_statistics',true);
????sys.dbms_system.set_int_param_in_session(SID,SERIAL#,'max_dump_file_size',unlimited);
????dbms_system.set_sql_trace_in_session(SID,SERIAL#,true)
????end;
????/
?
10046事件的设置： 10046是oracle的内部事件，是对SQL_TRACE的增强。它分4个级别：
level 1: 等价于sql_trace
level 4: 等价于level 1+绑定值
level 8: 等价于level 1+等待事件跟踪
level 12:等价于level 1+level 4 +level 8
?
全局设置：在参数文件增加：event="10046 trace name context forever,level 12"
当前session的设置：SQL>alter session set events '10046 trace name context forever';???SQL>alter session set events '10046 trace name context forever,level 8';
SQL>alter session set events '10046 trace name context off';
对其他session的设置：SQL>exec dbms_system.set_ev(SID,SERIAL#,10046,8,'USERNAME');???SQL>exec dbms_system.set_ev(SID,SERIAL#,10046,0,'USERNAME');??
?
获取跟踪文件的脚本：
SELECT????d.VALUE
???????|| '/'
???????|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
???????|| '_ora_'
???????|| p.spid
???????|| '.trc' trace_file_name
??FROM (SELECT p.spid
??????????FROM v$mystat m, v$session s, v$process p
?????????WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
???????(SELECT t.INSTANCE
??????????FROM v$thread t, v$parameter v
?????????WHERE v.NAME = 'thread'
???????????AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
???????(SELECT VALUE
??????????FROM v$parameter
?????????WHERE NAME = 'user_dump_dest') d
?
?
获取当前session设置的参数： 当通过alter session的方式设置了SQL_TRACE，这个设置是不能通过show parameter方式获得的。需要通过dbms_system.read_ev来获得，如下：
SQL>set feedback off
SQL>set serveroutput on
SQL>declare
????event_level number;
????begin
?????for event_number in 10000..10999 loop
?????sys.dbms_system.read_ev(event_number,event_level);
?????if(event_level>0) then
???????sys.dbms_output.put_line(
?????'Event'||
?????to_char(event_number)||'is set at level'||to_char(event_level));
?????end if;
???end loop;
??end;
?/
?
案例1：SQL中存在潜在的数据类型转换导致索引失效。使用函数也能导致索引失效。这些是我们需要注意的。
案例2：利用sql_trace可以跟踪后台递归操作。
案例3：10046比sql_trace强，它能提供非常有用的等待事件。提到等待事件，有几个视图需要提到：v$session_wait;v$system_event.
?
增大db_file_multiblock_read_count的设置，会使全表扫描成本降低，CBO会更倾向于是用全表扫描而不是索引访问。
?
案例4：物化视图的一个案例。
系统出现临时表空间不能扩展。尝试捕获引发排序的SQL：
select /*+ rule */ distinct a.SID,a.process,a.serial#,TO_CHAR(a.logon_time,'YYYYMMDD HH24:MI:SS') LOGON,a.osuser,TABLESPACE,b.sql_text
from v$session a,v$sql b,v$sort_usage c
where a.sql_address=b.address(+) AND a.sql_address=c.sqladdr
查看排序SQL的执行计划，发现该SQL调用了3个底层表，逻辑读很高，排序查询的表实际是个视图。通过DBA_VIEWS获得这个视图的定义。这个视图调用了三张记录很多的表。结合业务逻辑根据视图定义创建
物化视图。创建物化视图后，依然对其进行了全表扫描。我们可以根据orader by后面的子句创建降序索引（直接在字段后加入desc）。（降序索引本质上基于函数索引的，只有在CBO下才能被用到）降序索引的定义可以在$DBA_IND_EXPRESSIONS或USER_IND_EXPRESSIONS视图中获得。
?
案例5：当出现checkpoint not complete时，需要关注一下IO,用iostat观察一下，读写速率是否正常。
