Monthly Archives: 1 月 2012

SQL调优01–SQL优化介绍

Posted on by

Introduction to SQL Tuning

  1. SQL效率太低的原因:
    1. Stale or missing optimizer statistics:缺失优化统计信息或者信息太旧;
    2. Missing access structures:缺少索引,考虑索引的效率;
    3. Suboptimal execution plan selection:不是最好的执行计划;
    4. Poorly constructed SQL:SQL语句写的不好;
    5. 最重要的是表的数据量太大,归档历史数据.小的数据量可以解决一切问题;
    6. 解决办法:
      1. 尽量不要用子查询,可以通过关联查询解决;
      2. 不要再表列上面使用函数,导致索引无效;
      3. 如果发生隐式转换也不走索引,因为oracle内部总是转换表的列;
      4. 尽量使用UNION ALL而不用UNION;
      5. 排序,去重复,分组现在默认使用hash去除重复,对CPU消耗很大;
  2. 性能监控的解决方案;                                                                
  3. Oracle中监控和调优的工具;                                                      
  4. 调优的工具:
    1. Automatic Database Diagnostic Monitor (ADDM);
    2. SQL Tuning Advisor;
    3. SQL Tuning Sets;
    4. SQL Access Advisor;
    5. SQL Performance Analyzer;
    6. SQL Monitoring;
    7. SQL Plan Management:在11g中的工具,可以控制某个sql的执行计划;
  5. SQL调优的任务:
    1. 查找高负载的SQL语句;
    2. 收集统计信息;
    3. 收集系统统计信息;
    4. 重建已存在的索引;
    5. 维护执行计划;
    6. 创建新的索引;
  6. CPU和Wait Time的调优
    1. db_time=cpu_time+wait_time;
    2. 如果db_time增加,cpu_time和wait_time等比例增加,说明这是一个可扩展的系统,只需增加硬件即可;
    3. 如果db_time增加,cpu_time远大于wait_time的增加,说明SQL效率不高,需要SQL的优化;
    4. 如果db_time增加,cpu_time远小于wait_time的增加,说明内部有争用或者IO效率太低;
  7. 客户系统的常见问题:
    1. Bad connection management:可以使用连接池解决;
    2. Bad use of cursors and the shared pool:适当调大SGA和PGA,并指定动态管理;
    3. Excess of resources consuming SQL statements:sql要反复执行;
    4. Use of nonstandard initialization parameters:使用了隐含参数或者参数使用不当;
    5. Poor database disk configuration:IO问题;
    6. Redo log setup problems:至少使用三组在线日志组,每组设置足够大,保证20分钟切换一次;
    7. Excessive serialization:串行化扫描,添加索引,尽量使用单列索引,可控性比较强;
    8. Inappropriate full table scans:全表扫描,主要是加索引解决;
    9. Large number of space-management or parse-related generated SQL statements:如果使用本地管理表空间的的话一般不会出现递归SQL;
    10. Deployment and migration errors:部署时出错,这个是人为原因;
  8. 应用的设计:
    1. 简化设计;
    2. 数据模型:
      1. 主要还是要与业务逻辑相结合;
      2. 可以使用建模工具如Oracle Designer,但是最好是使用详细的文档;
      3. 考虑是OLTP系统还是DW系统;
    3. 表设计:
      1. 考虑使用默认值,约束,物化视图,分区表等特性;
      2. 分区列一定要在where条件上,而且最好不要更新;
      3. 不建议使用触发器;,触发器的目的是做check,而不是做DML;
      4. 不建议使用外键,可以使用程序保证数据的完整性;
    4. 索引设计:
      1. 索引的列一定要经常出现在WHERE后面;
      2. 在DW中建议使用外键,在OLTP中考虑到性能可以不用外键,加外键的话就一定要加索引;
    5. 视图:
      1. 可以使用视图,但是最好不要嵌套视图;
      2. 嵌套视图影响执行计划;
  9. Share Cursors
    1. 尽量使用存储过程和函数;
    2. cursor_sharing初始化参数尽量不要修改;                                             

SQL调优04–阅读执行计划

Posted on by

Interpreting Execution Plans

  1. 执行计划的解释:
    1. SQL语句的执行计划是由语句中行源的执行计划组成;
    2. 执行计划是使用父子关系来描述的,像一个树的结构;
  2. 如何查看执行计划:
    1. PLAN_TABLE:是由EXPLAIN PLAN命令或者SQL/PLUS的autotrace产生的执行计划,是理论上的执行计划;
    2. v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
    3. v$sql_plan_monitor:11g中的执行计划监控;
    4. dba_hist_sql_plan:由AWR报告产生的执行计划;
    5. stats$sql_plan:是由Statspack生成的执行计划;
    6. SQL Management Base:是由SQL Plan Management Baselines产生的执行计划;
    7. SQL tuning set;
    8. DBMS_MONITOR产生的trace文件:相当于10046事件;
    9. 由10053事件产生的trace文件;
    10. 10gR2之后的dump跟踪文件;
  3. 查看执行计划的视图:
    1. 如果直接查看基表的话,根本无法直接看到执行计划间的关系,自己编写SQL语句查看很麻烦,可以使用DBMS_XPLAN包下面的函数来完成;
    2. DBMS_XPLAN.DISPLAY():用来显示plan_table中的执行计划;
    3. DBMS_XPLAN.DISPLAY_CURSOR():用来显示v$sql_plan中的执行计划;
    4. DBMS_XPLAN.DISPLAY_AWR():用来显示AWR中的执行计划;
    5. DBMS_XPLAN.DISPLAY_SQLSET():用来显示SQL tuning set中的执行计划;
    6. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE():用来显示SQL Plan Management Baselines中的执行计划;
  4. EXPLAIN TABLE命令:
    1. 生成一个最优的执行计划,把它存在PLAN_TABLE中,但是并不实际执行SQL语句;
    2. 语法:EXPLAIN PLAN [SET STATEMENT_ID = ‘text’] [INTO plan_table] FOR statement;默认插入到PLAN_TABLE表中;
    3. PLAN_TABLE:
      1. 当执行EXPLAN_PLAN命令时自动创建PLAN_TABLE,它是一个同义词,指向sys.plan_table$的临时表;SELECT * FROM dba_synonyms WHERE synonym_name = ‘PLAN_TABLE’;SELECT table_name, TEMPORARY, duration FROM dba_tables WHERE table_name = ‘PLAN_TABLE$’;               
      2. 可以根据$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本创建自己的表,因为默认是临时表,只能在当前session查看,导入到自己的表中就可以永久保存;
      3. 优点是SQL语句么有真正执行;缺点是可能不是真正的执行计划,只有使用绑定变量时执行计划不准,其它情况都准确;
      4. 表中的内容是层级结构,可以通过ID和PAREANT_ID列来关联;
    4. DBMS_XPLAN.DISPLAY函数语法:DBMS_XPLAN.DISPLAY(table_name, statement_id, format, filter_preds):
      1. table_name:默认是PLAN_TABLE表;
      2. statement_id:默认是空,可以根据这个参数获得指定的语句的执行计划;
      3. format:默认是TYPICAL类型,其他类型查帮助文档,显示的信息多少;
      4. 默认只查看上一条语句的执行计划;                                 
      5. 查看指定statement_id的执行计划;                            
      6. 查看更多的执行计划的信息;                                    
  5. AUTOTRACE:
    1. AUTOTRACE是sql*plus的功能,在oracle7.3版本后出现,也是把记录存放在PLAN_TABLE表中;
    2. 需要PLUSTRACE角色从v$视图中检索统计信息,使用$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本创建;
    3. 默认情况下,在执行完查询语句后会生成执行计划和统计信息;
    4. 相当于执行了一次EXPLAIN PLAN命令然后执行了一次语句,如果使用绑定变量的话可能不是真实的计划;
    5. 语法:SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]];
      1. ON:要显示结果和trace信息;
      2. TRACEONLY:不显示结果;
    6. 查看当前的设置:show autotrace;
  6. 阅读统计信息:
    1. recursive calls:递归的调用,读取数据字典,权限,列的信息.第一次执行会很大,以后执行会变小;如果使用存储过的话,这个值一般会很大,属于正常;可以通过清除shared_pool测试:alter system flush shared_pool;
    2. db block gets:修改当前状态的数据块的block的块数.只有当DML语句会引起db block gets增加,因为当前块会被更新,SELECT语句的话不会增加,因为可以读取REDO或者构造的CR块;
    3. consistent gets:逻辑读的数量(不是BLOCK),表示返回记录的批次数,跟当前的arraysize有关;
      1. arraysize:表示一次返回的记录数,通过show arraysize命令查看;
      2. 粗略是算法是:consistent gets=rows processed/arraysize,记录越多越接近;
      3. 优化时应该关心在相同的arraysize下减小此值,即减小逻辑读;
    4. physical reads:物理读,即从硬盘读取的BLOCK的数量,BUFFER CACHE越大这个值越小,可以通过清除BUFFER CACHE测试:alter system flush buffer_cache;
    5. redo size:产生的日志的数量,一般DML语句才会产生;
    6. bytes sent via SQL*Net to client:服务器发送到客户端的字节数;
    7. bytes received via SQL*Net from client:服务器接收到客户端的字节数;
    8. SQL*Net roundtrips to/from client:SQL的网络流量的次数,也跟arraysize参数有关;
    9. sorts (memory):内存中的排序数量,主要是PGA;
    10. sorts (disk):在硬盘的排序,应该避免这个值;
    11. rows processed:处理的记录数;
  7. v$sql_plan:
    1. v$sql_plan:查看library cahce中真正使用的执行计划;PLAN_TABLE只是理论上的执行计划;
    2. 可以通过sql_id列与v$sql表关联,也可以使用address和hash_value的值;
    3. 主要的列:
      1. HASH_VALUE:父语句在library cache中的哈希值;
      2. ADDRESS:访问SQL语句的句柄,即内存地址;
      3. CHILD_NUMBER:使用此执行计划的子CURSOR数量;
      4. POSITION:具有相同PARENT_ID的操作的执行顺序;
      5. PARENT_ID:跳出过程的下一个执行的过程ID,这个很抽象,看到执行计划,很容易理解这一点;
      6. ID:每一个步骤的编号;
      7. PLAN_HASH_VALUE:执行计划的哈希值;
    4. 查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
    5. v$sql_plan_statistics:提供实际执行时的统计信息
      1. 当STATISTICS_LEVEL设置为ALL时才会收集;
      2. 或者语句中指定了GATHER_PLAN_STATISTICS的hint;
      3. v$sql_plan_statistics_all:获得所有的实际执行的统计信息;
    6. v$sql_workarea:提供了SQL CURSOR使用的工作区的信息;                                            
  8. AWR:
    1. AWR是为了检测和自调整为目的的收集,处理,维护性能统计信息;
    2. 统计信息包括:
      1. 对象统计信息;
      2. 时间模型统计信息;
      3. 一些系统和session的统计信息;
      4. ASH(Active Session History)统计信息;
    3. 自动生成性能数据的快照;
    4. 重要的AWR视图:
      1. V$ACTIVE_SESSION_HISTORY;
      2. V$metric views;
      3. DBA_HIST views:
        1. DBA_HIST_ACTIVE_SESS_HISTORY;
        2. DBA_HIST_BASELINE;
        3. DBA_HIST_DATABASE_INSTANCE;
        4. DBA_HIST_SNAPSHOT;
        5. DBA_HIST_SQL_PLAN;
        6. DBA_HIST_WR_CONTROL;
    5. 指定sql_id查看AWR中的sql的执行计划: SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(‘g22czkqq3pxmb’));
    6. 从AWR数据生成一个SQL报告:@$ORACLE_HOME/rdbms/admin/awrsqrpt;
  9. SQL Monitoring:11g;
  10. 阅读执行计划:
    1. 读执行计划的顺序:
      1. 从上往下看,第一个没有儿子节点的节点最先执行;
      2. 执行执行其兄弟节点;
      3. 最后执行父节点;
    2. 就是二叉树中的后序遍历的方式:
      1. 前序遍历:对任一子树,先访问根,然后遍历其左子树,最后遍历其右子树;
      2. 中序遍历:对任一子树,先遍历其左子树,然后访问根,最后遍历其右子树;
      3. 后序遍历:对任一子树,先遍历其左子树,然后遍历其右子树,最后访问根;
    3. 例子:
      1. 执行的顺序为:356421;                                                                                                          
      2. 执行的顺序为:43652871;                                                                                                        
      3. 执行顺序为:325410;                                          
    4. 查看执行计划的建议:
      1. 要使驱动表保持最好的过滤条件,即驱动表有最小的记录;
      2. 每一步返回的数据尽量最小;
      3. 正确使用视图,只是用一层,尽量不要嵌套;
      4. 避免使用笛卡尔积;
  11. 仅仅靠一个执行计划不能说明它是否是最好的,可以借助SQL Tuning Advisor工具;

SQL调优03–执行计划的访问路径

Posted on by

Optimizer operators

  1. 行源操作
    1. Unary Operations:一元运算,即单表的查询;
    2. Binary Operations:二元运算,两表的连接;
    3. N-ary Operations:多元运算;
  2. 主要的结构和访问路径:
    1. 表:
      1. Full Table Scan;
      2. Rowid Scan:很少使用,多用在内部的某一个步骤;
      3. Sample Table Scan:很少使用;
    2. 索引:
      1. Index Scan(Unique);
      2. Index Scan(Range);
      3. Index Scan(Full);
      4. Index Scan(Fast Full);
      5. Index Scan(Skip);
      6. Index Scan(Index Join);
      7. Using Bitmap Indexes;
      8. Combining Bitmap Indexes;
  3. 索引的基本概念:
    1. B-Tree Indexes:平衡树索引,最常见的索引;
      1. 正常索引;                                                                         
      2. 基于函数的索引:
        1. 创建函数索引相当于在表上添加一个伪列;                      
        2. 查看定义;                                             
      3. IOT(Index-Organized Table):将表结构整体放入索引中,而且按照主键进行排序,一定要有主键,非主键的列一定要落在索引条目里;
      4. Bitmap Indexes;
        1. 可以索引空值;
        2. 适当发生转换:TO ROWIDS/FROM ROWIDS/COUNT;
        3. 可以进行的操作:MERGE/AND/OR/MINUS/KEY ITERATION,位运算的速度很快;
        4. 位图索引可以进行SINGLE VALUE/ RANGE SCAN/ FULL SCAN扫描;
        5. 缺点是位图索引不能经常更新,效率很差;
      5. Cluster Indexes;
        1. 如果要做两个表的关联查询则最少查询两个块;
        2. CLUSTER把两个表按照关联的字段把记录存放在同一个块上;这样只用查一个块即可;查找时效率提高一倍;
        3. 用在总是关联查询两个表的情况,一般是不用的;ORACLE内部大量使用;
        4. cluster上的索引不能指定列,必须使用所有的列;
        5. 基于cluster的表没有segment;
    2. 索引的属性:
      1. 键压缩;
      2. 反转键值(考点):可以防止索引块争用(buffer busy wait),只支持等式连接,不支持范围扫描;
      3. 顺序/倒序;
    3. 索引和NULL值:
      1. NULL值与索引的关系:
        1. 基于单列的唯一索引,可以多次插入NULL值(NULL <> NULL),因为索引并不存储NULL值;
        2. 基于多列的符合索引,尽管全为NULL的值可以多次插入([NULL, NULL] <> [NULL, NULL]),索引也不会存储,但不全为NULL的重复行则不能重复插入,;
      2. NULL值与执行计划:
        1. 如果列的属性允许为NULL,条件为IS NULL的话,肯定走全表扫描,因为索引不保存NULL值;
        2. 如果列的属性允许为NULL,条件为IS NOT NULL的话,会走全索引扫描;
        3. 如果列的属性为NOT NULL,条件为IS [NOT] NULL的话,走索引扫描;
        4. 组合索引的话,如果条件中只出现一列的话跟单列索引一样;
        5. 组合索引的话,如果条件中出现两列,会优先选择走索引;
      3. IS NULL使用索引的办法:
        1. 在NULL的列上创建函数索引:nvl(column_name, -1),查询的时候条件指定函数索引: where nvl(column_name, -1) = -1;
        2. 为NULL的列添加默认值;
    4. 索引的管理:
      1. 插入数据后再创建索引,对于DW来言;
      2. 在适当的表和列上加索引;
      3. 注意组合索引的顺序;
      4. 控制索引的数量:每添加一个索引,DML的效率下降3倍,官方推荐最多加7个索引;
      5. 删除不使用的索引;
      6. 为索引指定单独的表空间;
      7. 创建索引时使用并行,NOLOGGING参数;
      8. COALESCING是合并相邻叶子节点,rebuild则可以减少索引树的高度;
    5. 检测索引是否被使用了:
      1. 添加对某个索引的监控:ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE;
      2. 查看监视的对象使用情况:SELECT * FROM v$object_usage;默认是没有任何的监视的;                
      3. 使用此索引后再查看;                                               
      4. 取消索引监控:ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE;                                                   
      5. 缺点:每次只能添加一个索引,而且不记录索引使用的次数;
      6. 不使用索引的原因:
        1. 被检索的列上用了函数;
        2. 数据类型不匹配;发生隐士转换是转化左边的列,而不是右边的列;
        3. 统计信息是否最新;
        4. 列是否是空值;
        5. 索引效率太低;
  4. 各种访问路径的原理及使用场景:
    1. Full Table Scan:
      1. 会执行Multiblock Reads,参考初始化参数:db_file_multiblock_read_count;                   
      2. 会读取HWM(High-Water Mark)以下所有被格式化的块;
      3. 过程中可能会过滤某些记录;
      4. 用在要获得大量记录的时候,比索引扫描更快;
      5. 使用的场景:
        1. 没有合适的索引;
        2. 过滤条件不好,甚至是没有过滤条件;
        3. 表太小,记录数很少;
        4. 需要并行扫描,并行扫描一定不走索引,如果确定是全表的话可以考虑并行:SELECT /*+ PARALLEL(d 4) */ * FROM departments d;                 
        5. 加全表扫描的hint时:SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;                             
        6. IS NULL的操作;                                           
    2. ROWID Scan:
      1. 根据记录的rowid查询,最快的访问方式,但不经常使用,可能会出现在执行计划的某个步骤中;
      2. 使用的方法:                                                
    3. Sample Table Sacns:基本不用,SELECT * FROM departments SAMPLE BLOCK (10) SEED (1);
    4. Index Unique Scan:条件中指定了主键列或者唯一键的列就走唯一键扫描;                    
    5. Index Range Sacn:
      1. 过滤的条件上不是主键/唯一索引,就会走索引范围扫描;                                        
      2. 如果对有索引的列排倒序就会有索引倒序扫描;(因为索引本身是排序的,所以执行计划中不会有排序的步骤,按照索引列排序效率会高;)                          
      3. 走函数索引的例子,也是索引范围扫描的一种;                                    
    6. Index Full Sacn vs Index Fast Full Sacn:
      1. 出现的条件:
        1. SELECT与WHERE子句出现的所有的列必须存在索引,而且为非空列,因为索引不存放NULL值;
        2. 返回的数据总行占据索引的10%以上的比例;
      2. Index Full Sacn:
        1. 完全按照索引存储的顺序依次访问整个索引树,当访问到叶子节点时,按照双向链表方式读取相连的节点值;
        2. 使用Single Read,会产生db file sequential reads事件;
        3. 对于索引列上的排序,总是会使用Index Full Scan;
        4. 索引列上is not null的操作,会走全索引扫描;
      3. Index Fast Full Sacn:
        1. 对于索引的分支结构只是简单的获取,然后扫描所有的叶节点,导致索引结构没有访问,获得的数据没有根据索引键的顺序排序,读取效率高.但是如果SQL语句中有排序操作的话,还要额外多做一次排序;
        2. 在使用Index Fast Full Sacn时,使用Multiblock Read,会产生db file scattered reads,db_file_multiblock_read_count参数的设置很重要;
        3. 统计行数,如count(*)的操作总是会使用Index [Fast] Full Scan的;
        4. 会使用大量的内存和CPU资源;
      4. Index [Fast] Full Scan的例子;                                                        
    7. Index Skip Scan:
      1. 创建了复合索引,但是条件中只有复合索引中的第二列,而且当第一列的distinct值不多时,会发生跳跃扫描;
      2. 创建一个测试表,和一个联合索引,当第一列可选值少而条件中只查找第二列时,发生跳越扫描;                     
      3. 如果第一列的可选值很多,条件中查找第二列的话,发生全表扫描;                                               
    8. Index Join Scan:查询的列都不为空,而且都有索引才会出现联合扫描;               
    9. AND-EQUAL操作:两列都有索引,分别扫描两列获得记录的rowid,然后再取rowid的交集;
    10. Bitmap Index:
      1. Bitmap的单值扫描;                                                   
      2. Bitmap的范围扫描;                                                  
      3. Bitmap的迭代操作操作;                                         
      4. Bitmap的AND操作;                                                
  5. 排序操作:
    1. Sort Operator:
      1. AGGREGATE:在group操作用会用到,统计结果;
      2. UNIQUE:评估是否重复;
      3. JOIN:做合并操作;
      4. GROUP BY,ORDER BY:在group by和order by的时候使用;
    2. Hash Operator:
      1. GROUP BY:在group by操作时使用;
      2. UNIQUE:跟SORT UNIQUE一样;
    3. 10g之后结果默认不排序,如果想要排序后的结果,应该总是使用ORDER BY字句;
  6. Buffer Sort:
    1. BUFFER SORT不是一种排序,而是一种临时表的创建方式;
    2. BUFFER表示在内存中存放了一张临时表;
    3. SORT来修饰BUFFER表示具体再内存的什么地方:在PGA的SQL工作区的排序区;
    4. BUFFER SORT的例子:                                                 
  7. INLIST ITERATOR:
    1. 是由于IN操作引起的,要关注迭代的次数,一次迭代就要有一次访问,如果没有索引可能性能问题会很严重;
    2. 可以使用UNION ALL操作代替;
    3. INLIST ITERATOR的例子;                                     
  8. 视图的操作:
    1. Merge View:是将View的定义和外部查询合并,高效的方式;
    2. No Merge View:先将View的数据取出来再做外部条件的过滤,效率低;
  9. 执行计划中的Count和Count Stopkey:oracle数据库的优化关于rownum操作;
    1. 在查询中有时使用到伪列rownum,对使用伪列rownum的查询,优化器要么使用count操作,要么使用count stopkey操作来对rownum计数器进行增量(注意:这里的count操作和count stopkey操作与count函数没有任何关系).如果对rownum伪列应用一个限定条件,如:where rownum<10,则使用count stopkey操作;如果不为Rownum伪列指定限定条件,则是使用count操作;
    2. 不在Rownum伪列上使用限定条件:SELECT employee_id, ROWNUM FROM employees;(employee_id是主键)为了完成这个查询,优化器执行一个全索引扫描(主键索引),后跟一个count操作生成每个行的rownum值,count操作不需要等待得到整个记录集,随着从employee表中返回记录,rownum计数器进行增量,从而确定每个记录的rownum;
    3. 在rownum伪列上使用一个限定:SELECT employee_id, ROWNUM FROM employees WHERE ROWNUM < 10;为了实施限定条件,优化器用count stopkey操作代替count操作,它将rownum伪列的增量值与限定条件中指定的值进行比较,如果rownum伪列的值大于限定条件中指定的值,则查询不再返回更多的行;
    4. 在where子句中不能使用rownum>10这样的操作,只能使用rownum<10这样的操作;
  10. Min/Max and First Row操作:当使用MAX/MIN函数时发生;                           
  11. 连接的方式:
    1. 一个连接定义了两个行源的关系,也是合并两个行源间数据的方法;
    2. 主要由连接的谓词所控制,定义了对象间的关系;                                                   
    3. 连接的方法:
      1. Nested Loops:
        1. 对于被连接的数据子集较小的情况,嵌套循环是个较好的选择;
        2. 返回第一条记录最快的方式;
        3. 这种情况下,内表被外表驱动,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(eg:<1w);
        4. 要把返回子集较小的表作为驱动表,而且内标的连接字段上一定要有索引;
        5. 使用USE_NL(table_name1 table_name2)可是强制CBO执行嵌套循环连接;
      2. Sort-Merge Join:
        1. 通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接;
        2. 可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接;
        3. Sort Merge join使用的情况:
          1. 用在没有索引;
          2. 数据已经排序的情况;
          3. 不等价关联;
          4. HASH_JOIN_ENABLED=FALSE;
      3. Hash Join:
        1. 散列连接是CBO做大数据集连接时常用的方式,优化器使用两个表中较小的表(行源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行;
        2. 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和,但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O的性能;
        3. 也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接,如果使用散列连接HASH_AREA_SIZE初始化参数必须足够的大,如果是10g以后,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可;
    4. 连接方式的比较:
      1. Hash join的工作方式是将一个表(通常是小一点的那个表)做hash运算,将列数据存储到hash列表中,从另一个表中抽取记录,做hash运算,到hash列表中找到相应的值,做匹配;
      2. Nested loops工作方式是从一张表中读取数据,访问另一张表(通常是索引)来做匹配,nested loops适用的场合是当一个关联表比较小的时候,效率会更高;
      3. Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配,因为merge join需要做更多的排序,所以消耗的资源更多,通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能,Merge Join太消耗PGA;
    5. 连接的类型:
      1. [不]等值连接和自然连接;
      2. 外连接:全连接,左外连接,右外连接;(外连接:+号放那边,哪边记录少;)
      3. 半连接:EXISTS子句;
      4. 反连接:NOT IN字句;
  12. 多行源的操作
    1. FILTER;
    2. CONCATENATION;
    3. UNION [ALL]
    4. INTERSECT;
    5. MINUS;
— Full Table Scan;
SELECT * FROM departments WHERE manager_id = 100;
SELECT /*+ PARALLEL(d 4) */ * FROM departments d;
SELECT * FROM departments d WHERE department_id = 10;
SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10;
— ROWID Sacn;
SELECT * FROM departments WHERE ROWID = ‘AAAMiZAAFAAAAA4AAI’;
SELECT * FROM departments WHERE ROWID = (
SELECT rowid FROM departments  WHERE manager_id = 100);
— 函数索引的例子;
CREATE INDEX idx_employees_fun_firstname ON employees (upper(first_name));
SELECT * FROM user_tab_cols WHERE table_name = ‘EMPLOYEES’;
SELECT * FROM user_ind_expressions WHERE index_name = ‘IDX_EMPLOYEES_FUN_FIRSTNAME’;
— Index Skip Scan的例子;
CREATE TABLE skip_test AS
SELECT object_id, object_name, decode(object_type, ‘VIEW’, ‘VIEW’, ‘TABLE’) AS object_flag, object_type
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX idx_skip_test ON skip_test(object_flag, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id = 100;
— 如果联合索引第一列的候选值太多,则发生全表扫描;
DROP INDEX idx_skip_test;
CREATE INDEX idx_skip_test ON skip_test(object_type, object_id);
EXEC dbms_stats.gather_table_stats(USER, ‘skip_test’, CASCADE => TRUE);
SELECT * FROM skip_test WHERE object_id=100;
— 位图索引的例子;
CREATE TABLE bitmap_test AS
SELECT ROWNUM rn, MOD(ROWNUM, 4) bit,
CASE MOD(ROWNUM, 2) WHEN 0 THEN ‘M’ ELSE ‘F’ END gender
FROM dual CONNECT BY ROWNUM < 1000;
CREATE BITMAP INDEX bmp_bitmap_bit on bitmap_test (bit);
CREATE BITMAP INDEX bmp_bitmap_gender on bitmap_test (gender);
EXEC dbms_stats.gather_table_stats(USER, ‘bitmap_test’, CASCADE => TRUE);
ALTER SESSION optimizer_mode = ‘FIRST_ROWS_1000’;
SELECT * FROM bitmap_test WHERE bit = 3;
SELECT * FROM bitmap_test WHERE bit > 2;
SELECT * FROM bitmap_test WHERE bit IN (2, 3);
SELECT * FROM bitmap_test WHERE bit IN (2, 3) AND gender = ‘M’;

SQL调优02–CBO优化器的原理

Posted on by

Intorduction to the CBO Optimizer

  1. 介绍CBO优化器的书:Jonathan Lewis写的<<Cost-Based Oracle Fundamentals>>;
  2. 结构化查询语言的类型:
    1. DML(Data Manipulation Language):INSERT, UPDATE, DELETE, MERGE, SELECT;
    2. DDL(Data Definition Language):CREATE, DROP, ALTER, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT;
    3. ESS(Environmental Sustainability Statement):DECLARE, CONNECT, OPEN, CLOSE, DESCRIBLE, WHENEVER, PREPARE, EXECUTE, FETCH;
    4. TCS(Transactoin Control Statement):COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION;
    5. SystemCS(System Control Statement):ALTER SYSTEM;
    6. SessionCS(Session  Control Statement):ALTER SESSSION, SET ROLE;
  3. SQL语句的实现过程:相同的SQL语句可以使用相同的执行计划;                                     
  4. Cursor
    1. PL/SQL中的cursor:记录行的rowid,用来表示结果集;
    2. sql解析过程中的cursor:是SHARED POOL中的内存块;
  5. SQL语句的处理过程:
    1. Create a cursor:
      1. Cursor是private SQL area的句柄或者名称;
      2. 其中包含了语句执行的信息;
      3. Cursor的结构与它包含SQL语句是互相独立的;
    2. Parse the statement:
      1. SQL语句通过用户进程发送到Oracle实例;
      2. 在PGA中检查语法和语义,然后检查权限,分配private SQL area,然后检查是否已经在Library Cache中存在,如果没有的话,会把sql放入到Shared SQL area中,产生硬解析;
      3. 如果SQL的执行计划已经存在的话可以被重用;
    3. Describe query results:
      1. 提供了SELECT后面的要查找的列的信息,与动态执行SQL有关;
      2. 9i时使用DBMS_SQL包,之后使用EXECUTE IMMEDIATE来动态执行SQL;
    4. Define query output:定义要查找的列的位置,大小,数据类型信息;
    5. Bind variables:
      1. 开启内存地址来保存数据的值;
      2. 允许共享sql;
    6. Parallelize the statement:
      1. 可以并行的语句:SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE, ALTER;
      2. 11g中自动判断是否需要并行;
    7. Execute the statement:执行SQL语句,产生想要的结果;
    8. Fetch rows of a query:以表格的形式返回数据;
    9. Close the cursor:关闭游标,PGA中指向cursor的指针关闭,但是内存区域还是被占用,之后可以被覆盖;
    10. 使用DBMS_SQL包可以看到完整的SQL执行的过程;
  6. SQL语句解析的过程;                                                                            
  7. 需要优化器的理由:它可以选择一种资源消耗最小的方式;
  8. 硬解析的操作步骤:                                                                                         
  9. Transformer
    1. 优化器首先会把语句分成一个一个的查询块,然后进行转换;
    2. 虽然进行了转换操作以提高效率,但是在内存中保存的还是原来的SQL语句;
    3. OR转换为UNION ALL操作;                                                               
    4. IN转换为内连接操作(11g);                                                
    5. IN改写为exists;
    6. NOT IN改写为外连接+IS NULL,11g中自动转换,10g中需要修改;
    7. IN改写为外连接+IS NOT NULL;
    8. 视图合并:查询的时候直接查询视图中的基表,非常适合于视图的记录数很大,查询视图的记录数小的情况;               
    9. 视图不合并:查询时把制图当成基表,这样效率比较低.CBO会自动合并第一层的视图,所以不要使用嵌套视图;
    10. Predicate Pushing:把条件推到最查询的最低端;                                                                        
    11. 条件的传递性:employees的department_id列没有索引,department表department_id列是主键,转换过之后就会先走主键扫描;                                
  10. Cost-Based Optimizer
    1. 由Estimator和Plan Generator组成;
    2. Estimator决定执行计划的成本消耗的建议;
      1. 它是基于概率论的,理论依据是数据是均匀分布的;
      2. 它的基础数据是定期收集并存放在数据字典的统计信息;
    3. Plan Generator:
      1. 产生各种不同的执行计划;
      2. 使用Estimator计算各个执行计划的成本;
      3. 基于成本选择最好的优化建议;
      4. 生成最优的执行计划;
    4. OPTIMIZER_MODE的两个参数:ALL_ROWS, FIRST_ROWS_n:
      1. FIRST_ROWS_n:
        1. CBO优先考虑将结果集中的前N条记录以最快的速度返回,而其它的结果集并不需要同时返回;
        2. 可以使用在BBS的分页上:SELECT /*+ first_rows(10) */ FROM tbname;
        3. 这种执行计划对于SQL整体的执行时间不是最快的,但是在返回前N条记录的处理上绝对是最快的;
        4. 使用的排序字段必须有索引,否则CBO会忽略FIRST_ROWS(n),而使用ALL_ROWS;
      2. ALL_ROWS:
        1. CBO考虑最快的速度返回所有的结果集,和FIRST_ROWS_n相反;
        2. 在OLAP系统中使用较多,总体效率高;
  11. Estimator
    1. Selectivity:选择度;
      1. Selectivity is the estimated proportion of a row set retrieved by a particular predicate or combination of predicates;选择度是由一个特定的谓词或者组合谓词检索行集的估计比例;
      2. 计算公式:Selectivity=满足条件的记录数/总记录数;
      3. 它的取值在0.0-1.0之间:
        1. High Selectivity:得到大比例的记录数;
        2. Low Selectivity:得到小比例的记录数;
      4. 如何获得Selectivity:
        1. 如果没有统计信息则采用动态采样(Dynamic Sampling);
        2. 如果没有直方图信息则采用行分布;
      5. 存放统计信息的视图:
        1. dba_tables;
        2. dba_tab_statistics(NUM_ROWS, BLOCKS, AVG_ROW_LEN);
        3. dba_tab_col_statistics(NUM_DISTINCT, DENSITY, HIGH/LOW_VALUE);
    2. Cardinality:基数;
      1. 通过执行计划期望能检索出来的记录数;
      2. 计算公式:Cardinality=Selectivity*总记录数;
      3. 对于join, filters和sort的成本是重要的指标;
    3. 举例:SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;
      1. employees表的job_id列的取值个数为:19;                                  
      2. employees表中的记录数:107;                                                               
      3. Selectivity=1/19=0.0526315789473684,即DENSITY的值;
      4. Cardinality=(1/19)*107=5.63,向上取整为6;
    4. Cost:
      1. Cost是执行特定的语句所花费的标准I/Os数量的最优评估;
      2. Cost的单位是一个标准块的随机读取:1 cost unit = 1 SRds(Standardized Random Reads);
      3. 执行计划中Cost(%CPU):一次IO读取一个IO块需要的时间;
      4. Cost的值由三部分组成;                                                           
  12. 控制优化器的初始化参数:
    1. CURSOR_SHARING:SIMILAR|EXACT(default)|FORCE,控制可以共享Cursor的SQL语句类型;
    2. DB_FILE_MULTIBLOCK_READ_COUNT:它是一个可以在表扫描时最小化IO的参数,指定了在顺序扫描时一次IO操作可以读取的最大的块数;(在OLTP系统中一般指定4-16,在DW系统中可以尽量设置的大一点);
    3. PGA_AGGREGATE_TARGET:PGA自动管理时指定server processes可以使用的PGA内存的总和;
    4. STAR_TRANSFORMATION_ENABLED:参数设置为TRUE时使用CBO可以使用位图索引的特性,不过貌似现在这个参数不重要;
    5. RESULT_CACHE_MODE:MANUAL,FORCE,11g
    6. RESULT_CACHE_MAX_SIZE:11g;
    7. RESULT_CACHE_MAX_RESULT:11g;
    8. RESULT_CACHE_REMOTE_EXPIRATION:11g;
    9. OPTIMIZER_INDEX_CACHING:在Buffer Cache中缓存索引的比例,默认为0;
    10. OPTIMIZER_INDEX_COST_ADJ:索引扫描/全表扫描的成本,默认为100%,即索引扫描成本等于全表扫描成本;优先会选择全表扫描;比较悲观的配置;
    11. OPTIMIZER_FEATURES_ENABLE:希望启用哪个版本的CBO;
    12. OPTIMIZER_MODE:ALL_ROWS|FIRST_ROWS|FIRST_ROWS_n
      1. 默认是all_rows:资源消耗比较小;
      2. first_rows_n:n的取值为1|10|100|1000,速度优先,但是消耗很大的资源;
    13. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;
    14. OPTIMIZER_USE_SQL_PLAN_BASELINES;
    15. OPTIMIZER_DYNAMIC_SAMPLING:动态采样的特性,10g后默认为2;
    16. OPTIMIZER_USE_INVISIBLE_INDEXES;
    17. OPTIMIZER_USE_PENDING_STATISTICS;
  13. OPTIMIZER_INDEX_COST_ADJ参数设置的例子:
    1. 首先创建表,索引,并收集统计信息;                                       
    2. 打开执行计划,执行查询语句,默认的值为100%,即索引扫描成本等于全表扫描成本,则执行计划走全表扫描;                      
    3. 修改参数值为50,即索引扫描成本是全表扫描成本的1/2,查看执行计划;                      
  14. Selectivity值的例子:
    1. 因为CBO是基于数据均匀的概率分布的,所以它估计的Selectivity是一个理论值;
    2. 创建一个1200条记录的表,里面的值分布是1-12,代表1-12月出生的人;                             
    3. 如果要查找某个月份出生的人,那么在不明白任何情况下,每个月份出生的人的概率都是1/12,即有100个人,CBO也是这么思考问题的;                            
    4. 实际值往往跟理论值不相符,但是数据量越大,越接近;                               
  15. 10053事件测试:
    1. 开启10053事件,并执行一条sql语句;                                       
    2. 查看当前的session_id和process_id来确定生成的内容是放在哪个udump文件中;                         
    3. 查看udump文件,里面包含了一些缩写的含义和当前生效的优化参数的值;                                
    4. 关闭10053事件;                                                      
— Estimator例子的脚本;
SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;
SELECT COUNT(DISTINCT job_id) FROM hr.employees;
SELECT owner, table_name, column_name, num_distinct, density
FROM dba_tab_col_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’ AND column_name = ‘JOB_ID’;
SELECT owner, table_name, num_rows, blocks, avg_row_len
FROM dba_tab_statistics
WHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’;
— OPTIMIZER_INDEX_COST_ADJ参数的例子;
CREATE TABLE t1 AS
SELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2
FROM dba_objects WHERE ROWNUM <= 3000;
CREATE INDEX t_i1 ON t1(n1);
EXEC dbms_stats.gather_table_stats(USER, ‘t1’, CASCADE=>TRUE);
SET autotrace traceonly exp;
SELECT * FROM t1 WHERE n1 = 50;
ALTER SESSION SET optimizer_index_cost_adj = 50;
SELECT * FROM t1 WHERE n1 = 50;
— Selectivity的例子;
CREATE TABLE t2(ID, month_id) AS
SELECT ROWNUM, trunc(dbms_random.value(1, 13))
FROM dba_objects WHERE ROWNUM <= 1200;
EXEC dbms_stats.gather_table_stats(USER, ‘t2’, CASCADE => TRUE);
SET autotrace traceonly exp;
SELECT * FROM t2 WHERE month_id = 5;
SELECT COUNT(*) FROM t2 WHERE month_id = 5;
— 10053时间测试例子;
ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context forever, level 8’;
SELECT * FROM employees WHERE employee_id = 100;
ALTER SYSTEM/SESSION SET EVENTS ‘10053 trace name context off’;
SELECT s.sid, p.spid FROM v$session s
INNER JOIN v$process p ON s.paddr = p.addr AND
s.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);

ORACLE中工具的使用06–ORADEBUG

Posted on by
oradebug工具的使用:

  1. oradebug主要是给oracle支持人员使用的,从8i开始,它是个sql*plus命令行工具,有sysdba的权限就可以使用,无需特别设置,主要用途为:
    1. 追踪进程,自己的或者是外部的;
    2. 确定进程写入哪个trc文件;
    3. 转储:数据文件头,内部oracle结构等;
    4. 暂时挂起进程;
    5. 确定实例使用了哪些共享内存块和信号量;
    6. 找出RAC实例使用了哪些互联地址和协议;
    7. 修改SGA中的数据结构;
  2. oradebug使用步骤:
    1. 启动sql*plus并以sysdba身份登入;
    2. 连接到一个进程;
    3. 设置一个事件或者进行诊断转储;
    4. 查看trc文件名;
    5. 与连接到的进程断开;
  3. oradebug的语法;
  4. 连接到一个进程的方法:
    1. oradebug setmypid:连接到当前的进程:
    2. oradebug setorapid pid:根据pid,连接到其它进程(v$process.pid);
    3. oradebug setospid spid:根据spid,连接到其它进程(v$process.spid);
  5. 查看共享信息内存方法:ipcs [-m|-s|-q],默认会列出共享内存,信号量,队列信息;
    1. 清除命令是ipcrm [-m|-s|-q] id;
    2. 参数:
      1. -m:列出共享内存信息;
      2. -s:列出共享信号量信息;
      3. -q:列出共享队列信息;
    3. 例子;
  6. 如果一个程序挂起,那么程序调用栈就可以显示它在调用路径中的哪一步挂起的:oradebug short_stack;                        
  7. 文件转储:
    1. 可以转储的类型:oradebug dmplist;
    2. 转储控制文件:oradebug dump controlf 10;
    3. 实现10046事件,例子;
    4. oradebug dump events的级别和范围:
      1. level 1,会话级别,对应alter session命令;
      2. level 2,进程级别,对应oradebug event命令;
      3. level 4,实例级别,对应alter system命令;
  8. oradebug对于解决问题和诊断性能是必不可少的工具;
——————————– oradebug语法 ——————————–
sqlplus / as sysdba
SQL> oradebug help
HELP           [command]                 Describe one or all commands
SETMYPID                                 Debug current process
SETOSPID       <ospid>                   Set OS pid of process to debug
SETORAPID      <orapid> [‘force’]        Set Oracle pid of process to debug
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
DUMPSGA        [bytes]                   Dump fixed SGA
DUMPLIST                                 Print a list of available dumps
EVENT          <text>                    Set trace event in process
SESSION_EVENT  <text>                    Set trace event in session
DUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
SETVAR         <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable
PEEK           <addr> <len> [level]      Print/Dump memory
POKE           <addr> <len> <value>      Modify memory
WAKEUP         <orapid>                  Wake up Oracle process
SUSPEND                                  Suspend execution
RESUME                                   Resume execution
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G             <Inst-List | def | all>   Parallel oradebug command prefix
-R             <Inst-List | def | all>   Parallel oradebug prefix (return output
SETINST        <instance# .. | all>      Set instance list in double quotes
SGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names
WATCH          <address> <len> <self|exist|all|target>  Watch a region of memory
DELETE         <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints        Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
CORE                                     Dump core without crashing process
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]…[argn]  Invoke function with arguments
——————————– oradebug语法 ——————————–
——————————– 查看共享信息内存的例子 ——————————–
— 1.生成信息到trace文件;
1.操作系统层面查看信息:host ipcs -m | grep ora
2.设置进程为当前进程:oradebug setmypid
3.导出信息到trace文件:oradebug ipc
4.查看trace文件的路径:oradebug tracefile_name
— 2.查看相应的trace文件;
Processing Oradebug command ‘ipc’
Dump of unix-generic skgm context
areaflags            000000f7
realmflags           0000001f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00400000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdc6af20
stackdir                   -1
mode                      640
magic                acc01ade
Handle:              0xf66058 `/u01/app/oracle/product/11.2.0/db_1ORCL’
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0/db_1ORCL’, flags = 00
000000
Area #0 `Fixed Size’ containing Subareas 0-0
Total size 0000000000149294 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
0        0   196608 0x00000020000000 0x00000020000000
Subarea size     Segment size
000000000014a000 0000000000800000
Area #1 `Variable Size’ containing Subareas 4-4
Total size 0000000032400000 Minimum Subarea size 00400000
Area  Subarea    Shmid      Stable Addr      Actual Addr
1        4   229377 0x00000020800000 0x00000020800000
Subarea size     Segment size
0000000032400000 0000000032400000
Area #2 `Redo Buffers’ containing Subareas 1-1
Total size 00000000004e3000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
2        1   196608 0x0000002014a000 0x0000002014a000
Subarea size     Segment size
00000000004e3000 0000000000800000
Area #3 `Base Allocator Control’ containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
3        3   196608 0x000000207fe000 0x000000207fe000
Subarea size     Segment size
0000000000002000 0000000000800000
Area #4 `Slab Allocator Control’ containing Subareas 2-2
Total size 00000000001d1000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
4        2   196608 0x0000002062d000 0x0000002062d000
Subarea size     Segment size
00000000001d1000 0000000000800000
Area #5 `skgm overhead’ containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
5        5   262146 0x00000052c00000 0x00000052c00000
Subarea size     Segment size
0000000000002000 0000000000400000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec        0
used region        0: start 0000000012000000 length 0000000000400000
used region        1: start 0000000020000000 length 0000000033000000
used region        2: start 00000000af800000 length 0000000010800000
Maximum processes:               = 1000
Number of semaphores per set:    = 125
Semaphores key overhead per set: = 4
User Semaphores per set:         = 121
Number of semaphore sets:        = 9
Semaphore identifiers:           = 9
Semaphore List=
491520
524289
557058
589827
622596
655365
688134
720903
753672
————– system semaphore information ————-
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 196608     oracle    640        4096       0
0x00000000 229377     oracle    640        4096       0
0x671360a4 262146     oracle    640        4096       0
—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x4c1391ac 491520     oracle    640        125
0x4c1391ad 524289     oracle    640        125
0x4c1391ae 557058     oracle    640        125
0x4c1391af 589827     oracle    640        125
0x4c1391b0 622596     oracle    640        125
0x4c1391b1 655365     oracle    640        125
0x4c1391b2 688134     oracle    640        125
0x4c1391b3 720903     oracle    640        125
0x4c1391b4 753672     oracle    640        125
—— Message Queues ——–
key        msqid      owner      perms      used-bytes   messages
——————————– 查看共享信息内存的例子 ——————————–
——————————– oradebug实现10046事件 ——————————–
— 1.设置某个进程;
session 1:
sqlplus / as sysdba
conn hr/hr
SELECT pid, spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1));
— 29 22505;
session 2:
sqlplus / as sysdba
oradebug setorapid 29;
— 2.设置trace文件的大小:oradebug unlimit;
— 3.打开10046事件:oradebug event 10046 trace name context forever, level 12;
— 4.执行查询:SELECT COUNT(*) FROM hr.employees;(在session 1中)
— 5.查看trace文件的位置:oradebug tracefile_name;
— 6.关闭10046事件:oradebug event 10046 trace name context off;
— 6.查看trace文件,使用tkprof工具格式化;
——————————– oradebug实现10046事件 ——————————–

ORACLE中工具的使用05–TKPROF

Posted on by
tkprof工具的使用

  1. tkprof工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),用于格式化trace文件(也可以使用第三方的工具,格式化出来的更加美观和全面),从而可以非常方便的跟踪和诊断sql语句的执行效率;
  2. tkprof的语法;
    1. table:手动生成explain plan时,存储中间信息的临时表,默认为PROF$PLAN_TABLE;
    2. explain:手动生成explain时,连接数据库的用户名和密码;
    3. print:仅仅处理前integer数量的sql语句,如果我们需要生成脚本,该参数对脚本中包含的sql数量是不影响的;
    4. insert:生成脚本,该脚本会创建表,并把相关统计信息插入表,从而可以在数据库中查看;
    5. record:生成不包含递归sql的脚本文件;
    6. sys:是否包含sys用户执行的sql,大多数是递归sql;
    7. aggregate=no:如果设置为yes的话,会合并相同的sql语句,一般设置为no,分别查看每次的执行;
    8. waits:是否记录等待事件;
    9. sort:对sql语句排序的规则;
    10. 常用的语法:tkprof tracefiles outputfile sys=no aggregate=no;
  3. SQL Trace文件的内容:
    1. Parse, execute, and fetch counts:解析,执行,获取三个动作的执行次数;
    2. CPU and elapsed times:消耗的cpu时间和总时间,单位是秒;
    3. Physical reads and logical reads:物理读和逻辑读的次数;
    4. Number of rows processed:处理的记录数;
    5. Misses on the library cache:没有命中缓存的次数;
    6. Username under which each parse occurred:执行sql语句的用户;
    7. Each commit and rollback:每次的提交和回滚操作(tkprof不会处理这些信息);
    8. Wait event and bind data for each SQL statement:针对每条sql语句的等待事件和绑定变量信息;
    9. Row operations showing the actual execution plan of each SQL statement:sql语句的实际执行计划;
    10. Number of consistent reads, physical reads, physical writes, and time elapsed for each operation on a row;
  4. Sql Trace与执行计划:
    1. 在sql trace期间,如果sql语句的游标已经关闭,则在sql trace中会包含相应的执行计划,Example 1;
    2. 在sql trace期间,如果sql语句的游标没有关闭,则在sql trace中不会包含相应的执行计划;
    3. 如果之前sql语句已经执行过,则会包含执行计划;
    4. 如果在trace文件中不存在执行计划的相关信息,可以通过tkprof的explain参数来登陆数据库,并执行explain plan命令,把执行计划写入到trace文件中;
  5. 指定aggregate=yes,tkprof会汇总相同的sql语句信息,并在文件的最后汇总所有语句的相关信息;
  6. 使用insert和records参数的例子,Example 2;
  7. 注意一些陷阱:
    1. Avoiding the Argument Trap:如果在运行时不注意绑定变量的问题,很可能会陷入参数陷阱,EXPLAIN PLAN命令不会检查SQL语句中绑定变量的类型,总是认为是VARCHAR类型;所以,如果绑定变量如果实际为number或者date类型的话,tkprof会进行一个隐式转换,导致生成错误的执行计划;为了避免这种情况,需要自己执行转换;
    2. Avoiding the Read Consistency Trap:如果要查询的数据被更新了,而且没有提交,那么很可能就会进入一致性读的陷阱,因为如果有很多相同的查询的操作的话,会一直构建CR块;
    3. Avoiding the Schema Trap:当看到一个只有少量数据返回却发现扫描了非常多的块的统计是,有可能是一下原因:
      1. 表被经常的更新/删除,造成了水位线很高;
      2. 可能tkprof分析的trace文件记录的是之前没有在表上创建索引情况,而之后加上了索引;
    4. Avoiding the Time Trap:当执行一个简单的sql,但是看到elapsed time时间特别长时,可能是再等待其它的锁;
————————————— tkprof的语法 —————————————
[oracle@singleton11g ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename   Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
print=integer    List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename  List SQL statements and data inside INSERT statements.
sys=no           TKPROF does not list SQL statements run as user SYS.
record=filename  Record non-recursive statements found in the trace file.
waits=yes|no     Record summary for any wait events found in the trace file.
sort=option      Set of zero or more of the following sort options:
prscnt  number of times parse was called
prscpu  cpu time parsing
prsela  elapsed time parsing
prsdsk  number of disk reads during parse
prsqry  number of buffers for consistent read during parse
prscu   number of buffers for current read during parse
prsmis  number of misses in library cache during parse
execnt  number of execute was called
execpu  cpu time spent executing
exeela  elapsed time executing
exedsk  number of disk reads during execute
exeqry  number of buffers for consistent read during execute
execu   number of buffers for current read during execute
exerow  number of rows processed during execute
exemis  number of library cache misses during execute
fchcnt  number of times fetch was called
fchcpu  cpu time spent fetching
fchela  elapsed time fetching
fchdsk  number of disk reads during fetch
fchqry  number of buffers for consistent read during fetch
fchcu   number of buffers for current read during fetch
fchrow  number of rows fetched
userid  userid of user that parsed the cursor
————————————— tkprof的语法 —————————————
————————————— Example 1 —————————————
— 1.使用hr用户登录,并查看sql语句的执行计划;
sqlplus / as sysdba
conn hr/hr
EXPLAIN PLAN FOR SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
— 2.打开sql trace功能,然后设置标识符为E1,并执行sql语句;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E1′;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 100;
— 3.格式化trace文件,然后查看执行计划:tkprof *_E1.trc e1.out sys=no aggregate=no;
————————————— Example 1 —————————————
————————————— Example 2 —————————————
— 1.使用hr用户登录,打开sql trace功能,然后设置标识符为E2,并执行sql语句;
sqlplus / as sysdba
conn hr/hr
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’E2′;
SELECT USER FROM DUAL;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200;
SELECT COUNT(*) FROM HR.DEPARTMENTS;
— 2.格式化trace文件,然后查看执行计划:tkprof *_E2.trc e2.out sys=no insert=insert.sql record=record.sql;
— 3.查看record.sql文件;
[oracle@singleton11g trace]$ less record.sql
SELECT USER FROM DUAL ;
SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200 ;
SELECT COUNT(*) FROM HR.DEPARTMENTS ;
— 4.查看insert.sql文件;
REM  Edit and/or remove the following  CREATE TABLE
REM  statement as your needs dictate.
CREATE TABLE  tkprof_table
(
date_of_insert                       DATE
,cursor_num                           NUMBER
,depth                                NUMBER
,user_id                              NUMBER
,parse_cnt                            NUMBER
,parse_cpu                            NUMBER
,parse_elap                           NUMBER
,parse_disk                           NUMBER
,parse_query                          NUMBER
,parse_current                        NUMBER
,parse_miss                           NUMBER
,exe_count                            NUMBER
,exe_cpu                              NUMBER
,exe_elap                             NUMBER
,exe_disk                             NUMBER
,exe_query                            NUMBER
,exe_current                          NUMBER
,exe_miss                             NUMBER
,exe_rows                             NUMBER
,fetch_count                          NUMBER
,fetch_cpu                            NUMBER
,fetch_elap                           NUMBER
,fetch_disk                           NUMBER
,fetch_query                          NUMBER
,fetch_current                        NUMBER
,fetch_rows                           NUMBER
,ticks                                NUMBER
,sql_statement                        LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 2000, 2136, 0, 0, 0, 1
, 1, 0, 46, 0, 0, 0, 0, 0
, 2, 0, 26, 0, 0, 0, 1, 12753692
, ‘SELECT USER FROM DUAL
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 3, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 0, 356, 0, 0, 0, 0, 0
, 10, 0, 448, 0, 40, 0, 10, 50095736
, ‘select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spa
re2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 4, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 2, 0, 267, 0, 8, 0, 2, 1674
, ‘select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluc
ols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blk
cnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.proper
ty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.tri
gflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cacheh
it,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj#
(+)
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 5, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 118, 0, 0, 0, 0, 0
, 10, 2000, 2037, 0, 16, 0, 8, 3642
, ‘select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pct
free$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clu
fac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1
),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.def
errable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.p
ctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist
.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(t
o_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from
cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 6, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 1000, 301, 0, 0, 0, 0, 0
, 17, 0, 410, 0, 34, 0, 9, 2435
, ‘select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 7, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 200, 0, 0, 0, 0, 0
, 17, 1000, 402, 0, 6, 0, 15, 1796
, ‘select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(s
cale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,sc
ale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property,
nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where o
bj#=:1 order by intcol#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 8, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 10, 999, 293, 0, 0, 0, 0, 0
, 10, 0, 459, 0, 30, 0, 10, 3519
, ‘select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(li
sts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(
bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 9, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 8, 0, 199, 0, 0, 0, 0, 0
, 8, 0, 330, 0, 24, 0, 8, 2328
, ‘select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$
o where o.obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 10, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 1000, 149, 0, 0, 0, 0, 0
, 2, 0, 119, 0, 4, 0, 0, 1135
, ‘select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ wher
e obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 11, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 65, 0, 0, 0, 0, 0
, 5, 0, 179, 0, 6, 0, 3, 762
, ‘select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$
where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 12, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 51, 0, 0, 0, 0, 0
, 9, 1000, 269, 0, 18, 0, 7, 1321
, ‘select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj
#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 13, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 2, 0, 80, 0, 0, 0, 0, 0
, 16, 0, 290, 0, 32, 0, 14, 2173
, ‘select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),r
owid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 14, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 14, 0, 336, 0, 0, 0, 0, 0
, 28, 0, 499, 0, 56, 0, 14, 4305
, ‘select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 1, 0, 1, 1999, 1376, 0, 0, 0, 1
, 1, 2000, 2335, 0, 0, 0, 1, 0
, 1, 0, 26, 0, 2, 0, 1, 4365
, ‘select condition from cdef$ where rowid=:1
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 16, 1, 0, 0, 0, 0, 0, 0, 0, 0
, 3, 0, 71, 0, 0, 0, 0, 0
, 3, 0, 102, 0, 9, 0, 3, 905
, ‘select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample
_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, av
gcln from hist_head$ where obj#=:1 and intcol#=:2
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 0, 84, 1, 17997, 16136, 0, 0, 0, 1
, 1, 0, 23, 0, 0, 0, 0, 0
, 2, 0, 78, 0, 2, 0, 1, 1010
, ‘SELECT employee_id, email, salary FROM HR.EMPLOYEES WHERE employee_id = 200
‘)
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 15, 0, 84, 1, 8999, 9177, 0, 0, 0, 1
, 1, 1000, 113, 0, 0, 0, 0, 0
, 2, 0, 254, 0, 1, 0, 1, 2161
, ‘SELECT COUNT(*) FROM HR.DEPARTMENTS
‘)
/
COMMIT;
set sqlterminator on
————————————— Example 2 —————————————

ORACLE中工具的使用04–TRCSESS

Posted on by
trcsess工具的使用

  1. trcsess工具位于$ORACLE_HOME/bin目录下(查看命令which trcsess),它可以把USER_DUMP_DEST目录下多个trc文件根据一定规则合并成一个trc文件,然后使用tkprof工具进行格式化;
  2. 语法:trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>;                                                                               
  3. 可以根据session,clientid,service,action,module和trace file的文件名(支持通配符)等不同的维度来合并USER_DUMP_DEST目录下的trc文件;相关的信息都可以从v$session视图中查看的到:SELECT sid, client_identifier, service_name, module, action, sql_trace FROM v$session;同样的可以从trace文件中查看的到;(其中sid是[sid.serial#]格式)
  4. trace文件的命名规则是<ORACLE_SID>_ora_<spid>_<TRACEFILE_IDENTIFIER>.trc,默认的TRACEFILE_IDENTIFIER是空,可以通过ALTER SESSION SET TRACEFILE_IDENTIFIER='<TRACEFILE_IDENTIFIER>’命令来修改trace文件的标示符;
  5. 可以通过DBMS_SESSION.SET_IDENTIFIER(‘<TRACEFILE_IDENTIFIER>’)过程来设置client id;
  6. 该工具主要应用于共享服务器模式或者采用连接池模式,我们很难针对某用户进行跟踪的情况下;
  7. 用例:根据tracefile_identifier,service/module来合并trace文件;
———————————- 根据tracefile_identifier来合并trace文件 ———————————-
— 1.清空user_dump_dest目录下的trace文件(仅仅为了测试);
— 2.打开session 1做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 1’ FROM DUAL;
— 3.打开session 2做如下操作;
sqlplus / as sysdba
ALTER SESSION SET TRACEFILE_IDENTIFIER=’ti1′;
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT ‘SESSION 2’ FROM DUAL;
— 4.查看生成的trace文件;

— 5.合并trace文件;
根据service_name合并:trcsess output=ti1_service.out service=’SYS$USERS’ *_ti1.trc
根据module_name合并:trcsess output=ti1_module.out module=’sqlplus@singleton11g.snda.com (TNS V1-V3)’ *_ti1.trc
———————————- 根据tracefile_identifier来合并trace文件 ———————————-

ORACLE中工具的使用03–LogMiner

Posted on by
LogMiner工具的使用:

  1. 用来分析Oracle数据库运行过程中产生的redo logfile和archived logfile来获取对数据库操作的DML语句;
  2. 以SYS用户安装LogMiner工具:
    1. 安装DMBS_LOGMNR包:@?/rdbms/admin/dbmslm.sql;                                 
    2. 安装DMBS_LOGMNR_D包:@?/rdbms/admin/dbmslmd.sql;                                 
  3. LogMiner包的介绍:
    1. DMBS_LOGMNR包:包含了初始化LogMiner工具,打开和关闭LogMiner会话的子程序;
      1. DMBS_LOGMNR.ADD_LOGFILE(LogFileName, options):开启一个LogMiner的会话,并添加redo logfile文件到这个队列里面;
        1. LogFileName:指定要分析的redo logfile;
        2. options:打开一个新的LogMiner会话并添加一个redo logfile(DMBS_LOGMNR.NEW);添加一个redo logfile到现存的LogMiner会话(DBMS_LOGMNR.ADDFILE);
      2. DMBS_LOGMNR.REMOVE_LOGFILE(LogFileName):通过指定名称,从被分析的redo logfile队列中移除一个redo logfile;
      3. DMBS_LOGMNR.START_LOGMNR(startScn, endScn, startTime, endTime, DictFileName, options):加载LogMiner数据字典并启动LogMiner来分析redo logfiles,最后填充动态视图;
        1. startScn:LogMiner返回大于此SCN的redo记录(分析后可以查询select filename, low_scn, next_scn from v$logmnr_logs来查询每个redo logfile包含的SCN范围);
        2. endScn:LogMinfer返回小于此SCN的redo记录;
        3. startTime:默认为1988-01-01,如果指定了startScn,则忽略此参数;
        4. endTime:默认为2110-12-31,如果指定了endScn,则忽略此参数;
        5. DictFileName:,默认为空,指定包含LogMiner字典的文本文件.用来生成v$logmnr_contents视图.必须指定DBMS_LOGMNR_D.BUILD过程中的全路径;
        6. options:分析日志时的操作选项,查看帮助文档;
      4. DMBS_LOGMNR.END_LOGMNR:结束LogMiner的会话,当退出Database Session的时候会自动调用此过程;
    2. DMBS_LOGMNR_D包:用来创建LogMiner的数据字典;
      1. DMBS_LOGMNR_D.BUILD(dictionary_filename, dictionary_location, options):即导出数据库的数据字典到一个文本中,数据字典发生变化都要重新创建一次;;
        1. dictionary_filename:指定LogMiner字典的文件名;
        2. dictionary_location:指定LogMiner字典文件的路径;
        3. options:指定LogMiner字典写的位置,文本文件(STORE_IN_FLAT_FILE,默认)或者是在线日志文件(STORE_IN_REDO_LOGS);
        4. 指定文本文件,则必须设置UTL_FILE_DIR初始化参数(ALTER SYSTEM SET UTL_FILE_DIR=’\tmp’ SCOPE=spfile;然后重启数据库服务使其生效):
          1. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’);
          2. EXECUTE dbms_logmnr_d.build(‘dictionary_filename’, ‘dictionary_location’, options => dbms_logmnr_d.store_in_flat_file);
        5. 指定在线日志文件,不常用,影响性能:EXECUTE dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
      2. DBMS_LOGMNR_D.SET_TABLESPACE(new_tablespace);
        1. 默认情况下,LogMiner的表会被创建到SYSAUX表空间,使用此函数可以修改LogMiner表存放的表空间;
        2. DBMS_LOGMNR_D.SET_TABLESPACE(‘tablespace_name’);
  4. 打开数据库的附加日志:ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;                   
  5. 创建数据字典:
    1. 创建表空间:CREATE TABLESPACE LOGMINER DATAFILE ‘/u01/app/oracle/oradata/AUX/logminer01.dbf’ SIZE 100M AUTOEXTEND ON;                     
    2. 设置LogMiner表的表空间:EXEC dbms_logmnr_d.set_tablespace(‘LOGMINER‘);                                     
    3. 设置UTL_FILE_DIR参数,然后重启数据库使之生效:alter system set utl_file_dir=’/u01/app/oracle/oradata/AUX/’ scope=spfile;                      
    4. 生成数据字典:EXEC dbms_logmnr_d.build(‘dictionary.lm’, ‘/u01/app/oracle/oradata/AUX/’);                             
  6. 模拟一个人为的错误;                                                                    
  7. 打开LogMiner的session并添加日志文件,只是在当前的session有效(查询归档日志的路径:select name from v$archived_log);              
  8. 分析日志,指定生成的LogMiner数据字典:EXEC dbms_logmnr.start_logmnr(DictFileName=>’/u01/app/oracle/oradata/AUX/dictionary.lm’);                 
  9. 此时就可以查询LogMiner的视图了,可以在IDE环境下去做,可以更好的过滤想要的结果;
  10. 退出LogMiner的会话:EXEC dbms_logmnr.end_logmnr;                                      
  11. LogMiner的视图:
    1. V$LOGMNR_CONTENTS:用来存放LogMiner分析日志后的结果;
      1. SELECT scn, TIMESTAMP, log_id, operation, sql_redo, sql_undo FROM v$logmnr_contents WHERE username = ‘HR’;
      2. 分析结果仅在运行过程dbms_logmrn.start_logmnr这个会话的生命期中存在.因为所有的LogMiner存储都在PGA内存中,所有其他的进程是看不到它的,同时随着进程的结束,分析结果也随之消失;
    2. V$LOGMNR_DICTIONARY:使用的数据字典的信息;
    3. V$LOGMNR_LOGS:包含所分析的日志的信息,SELECT log_id, filename, db_name, low_scn, next_scn, low_time, high_time, status FROM v$logmnr_logs;
    4. V$LOGMNR_SESSION:包含LogMiner当前的Session信息;
  12. 注意事项:
    1. 利用LogMiner工具来分析其它数据库实例产生的redo logfiles,使用LogMiner分析其它数据库实例时的注意事项;
    2. LogMiner必须使用被分析数据库实例产生的字典文件,而不是安装LogMiner的数据库产生的字典文件,另外必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同;
    3. 被分析数据库平台必须和当前LogMiner所在数据库平台一样,也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 8i产生的,那么也必须在一个运行在UNIX平台上的Oracle实例上运行LogMiner,而不能在其它如Microsoft NT上运行LogMiner,当然两者的硬件条件不一定要求完全一样;
    4. LogMiner日志分析工具仅能够分析Oracle 8以后的产品,不过它可以分析Oracle8的日志,对于8以前的产品,该工具也无能为力.另外,Oracle8i只能对DML操作进行分析,从Oracle9i开始不仅可以分析DML操作,而且也可以分析DDL操作;
  13. Oracle11g中EM继承了LogMiner工具(需要开启附加日志):
    1. LogMiner的路径:Avaliability->Manage->View and Manage Transactions;            
    2. 可以通过时间/SCN,查询某些表/用户下的所有的事务/DDL;                             
    3. 结果页面;                                                                         
— 模拟人为错误,并切换归档;
conn hr/hr
create table lm tablespace logminer as select * from employees;
delete from lm where employee_id < 200;
commit;
conn / as sysdba
alter system switch logfile;
— 打开LogMiner的session并添加日志文件;
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_3_85j72xlx_.arc’, options=>dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile(LogFileName=>’/u01/app/oracle/flash_recovery_area/AUX/archivelog/2012_09_18/o1_mf_1_4_85j76znp_.arc’, options=>dbms_logmnr.addfile);

ORACLE中工具的使用02–DBVERIFY

Posted on by
DBVERIFY工具的使用

  1. DBVERIFY工具介绍:
    1. DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems;
    2. DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs;
    3. 只支持镜像备份,不支持备份集验证;测试发现控制文件可以验证,但是在线日志不可以;
  2. DBVERIFY的语法:dbv key=value;
    1. USERID=Username/Password:指定用户名密码,如果检查的文件是ASM文件系统,需要提供验证信息;
    2. FILE=file_name:要检查的datafile文件名;
    3. START=start_block:要检查的文件的开始块号,默认是文件第一个块;
    4. END=end_block:要检查文件的结束的块号,默认是文件最后一个块;
    5. BLOCKSIZE=size:指定块的大小,默认是8k,如果使用的不是8k的块需要指定此参数;
    6. LOGFILE=log_file:指定日志文件,如果不指定就显示在终端;
    7. FEEDBACK=n:返回检查的进度,n个单位打一个点;
    8. PARFILE=parameter_file:指定一个参数文件;
    9. SEGMENT_ID:指定段的id(由三部分组成:tablespace_id.header_file.header_block),可以通过sql语句查询;
  3. DBVERIFY的用法:
    1. 对datafile文件进行检查;
    2. 对segment进行检查;
    3. 对备份集的检查;
    4. 对控制文件的检查;
  4. 检查datafile中的block:
    1. 这种模式下,会扫描一个datafile中的一个或者多个blocks,并生成一份检查结果;
    2. 如果datafile是ASM系统文件的话,需要提供USERID连接到这个ASM文件;
    3. 检查system表空间:dbv file=$ORACLE_BASE/oradata/PROD/system01.dbf feedback=1000;
  5. 检查segment:
    1. 在这种模式下,可以用来检查一个对象(表/索引)使用的segment的可用性,指定segment的所有链表都会被检查;
    2. 被检查的相应的对象会被加锁,如果是索引的话,则引用的表会被加锁;
    3. 需要提供sysdba的身份验证,因为需要读取segment的数据字典;
    4. 检查scott用户下emp表的segment:dbv userid=scott/tiger segment_id=4.4.2003 feedback=10;                    
  6. 验证控制控制文件:
    1. 常规验证,发现错误,因为块大小不对,修改验证语法:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl feedback=100;     
    2. 验证:dbv file=$ORACLE_BASE/oradata/PROD/control01.ctl blocksize=16384 feedback=100;
— 查询某个SEGMENT的id;
SELECT a.ts# || ‘.’ || b.header_file || ‘.’ || b.header_block
FROM v$tablespace a, dba_segments b
WHERE a.NAME = b.tablespace_name AND b.segment_name = ‘segment_name’ AND owner = ‘owner’;
— 块损坏的几种检查;
————————————————————————————–
特性                      坏块侦测类型                    能否修复损坏块
————————————————————————————————
DBVERIFY                  物理                         否
ANALYZE                   逻辑                         否
DB_BLOCK_CHECKING         逻辑                         否
DB_BLOCK_CHECKSUM         物理                         否
exp                       物理                         否
FlashBack                 逻辑                         是
DBMS_REPAIR               逻辑                         是
Block media recovery      未知                         是

ORACLE中工具的使用01–DBNEWID

Posted on by
DBNEWID工具的使用

  1. DBNEWID工具介绍:
    1. 手动创建一个数据库的拷贝时,在重建控制文件时可以指定一个新的DBNAME,但是不能修改DBID.DBID是内部唯一标示一个数据库的标示符;RMAN是使用DBID区分数据库的,所以就不能把这两个数据库注册到同一个RMAN的资源库中了;
    2. DBNEWID工具可以:
      1. 只修改DBID(无法控制修改为多少);
      2. 只修改DBNAME;
      3. 同时修改DBID和DBNAME;
    3. 如果修改了DBID,之前所有的备份,归档日志都会失效,而且打开数据库时需要指定RESETLOGS参数;
    4. 强烈建议在修改DBID之前对数据库进行备份;
    5. 使用DBNEWID工具不会影响global_name,它只能通过ALTER DATABASE语法进行修改,由DB_NAME和域组成:ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db_domain;
    6. DBNEWID工具也不会修改INSTANCE_NAME,但是会修改DB_NAME,DB_UNIQUE_NAME和SERVICE_NAMES;
  2. DBNEWID的语法:nid key=value;
    1. TARGET=user/pwd@tns_name:登陆目标数据库;
    2. DBNAME=newname:指定要修改的新的数据库的名称,如果不指定此参数,则只修改DBID;
    3. SETNAME=YES/NO:默认为NO,如果指定为YES,表示值修改数据库名称;
    4. REVERT=YES/NO:默认为NO,如果为YES,表示如果失败了回退到之前的状态;
    5. LOGFILE=log_file:指定日志文件;
    6. APPEND=YES/NO:指定是追加日志还是覆盖日志;
    7. 例子:
      1. 只修改DBID:nid target=user/pwd@tns_name logfile=nid.log;
      2. 只修改DBNAME:nid target=user/pwd@tns_name dbname=newname setname=yes logfile=nid.log;
      3. 同时修改DBID和DBNAME:nid target=user/pwd@tns_name dbname=newname logfile=nid.log;
      4. 发生错误回退到之前的状态:nid target=user/pwd@tns_name revert=yes logfile=nid.log
  3. 修改DBID和DBNAME:
    1. 对数据库做一次全备;
    2. 配置好监听(静态注册数据库)和网络名,如果是在本机操作则不需要此步骤;
    3. 查看当前的DBID和DBNAME:SELECT dbid, name FROM v$database;             
    4. 正常关闭数据库,并启动到mount状态;                                       
    5. 调用nid工具,使用sysdba登陆:nid target=sys/oracle dbname=OMR,修改完成后数据库处于shutdown状态;                  
    6. 修改spfile文件中的DB_NAME参数(如果只修改DBID的话,不用这个步骤);               
    7. 重置密码文件(如果只修改DBID或者没有修改ORACLE_SID的话,不用这个步骤,因为密码文件只跟ORACLE_SID有关):
    8. 启动数据库到mount状态,然后以resetlogs方式打开数据库:ALTER DATABASE OPEN RESETLOGS;(如果只是修改了DB_NAME的话,不用这个步骤,之前的日志还可以使用)
    9. 查看新的DBID和DB_NAME;