SQLT Diagnostic Tool

1.SQLT介绍:SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

2.安全模式:SQLT在安装的过程中会创建两个用户和一个角色,这些用户和角色的名字都是固定的;

2.SQLTXPLAIN密码;
3.SQLTXPLAIN默认表空间:它必须具有50MB以上的可用空间;
4.SQLTXPLAIN临时表空间;
5.应用程序用户(可选):指定发出要分析SQL语句的用户(EBS中为APPS,Siebel中为SIEBEL,PeopleSoft中为SYSADM),也可以安装之后再添加用户,必须授予其SQLT_USER_ROLE角色;
6.授权的Oracle Pack(T,D,N):T表示Oracle Tuning,D表示Oracle Diagnostic,或N表示None;如果选择T或D,SQLT可以在它生成的诊断文件中包含授权的内容;默认值为T,如果选择N,SQLT将只安装限定的功能;

1. Oracle的outline技术和hint技术可以在特殊情况下保证执行计划的稳定,使用outline技术锁定执行计划的场景:
   1. 短时间内无法完成sql的优化任务;
   2. 在CBO模式下,当统计信息出现问题时,导致执行计划出现变化;
   3. 由于数据库的bug导致sql的执行计划出现异常;
   4. 使用第三方的系统,sql语句无法直接修改时;
2. OUTLINE相关的参数:
   1. CREATE_STORED_OUTLINES:会话中执行的sql语句是否自动创建并存储为OUTLINE,初始化参数;
      1. true:开启自动创建outline的功能,系统会自动指定名称并存储在DEFAULT类别下,如果在DEFAULT类别下已经存在的话不会重复创建;
      2. false:默认值,不开启自动创建功能,推荐不要修改此参数;
      3. category_name:跟true功能一样,只是默认存在category_name类别下;
   2. USE_STORED_OUTLINES:使用公有OUTLINE产生执行计划,不是初始化参数;
      1. true:使优化器使用DEFAULT类别的OUTLINE产生执行计划;
      2. false:默认值,不使用outline;
      3. category_name:是优化器使用category_name类别的outline产生执行计划;
   3. USE_PRIVATE_OUTLINES:使用私有OUTLINE产生执行计划,不是初始化参数;
      1. 参数的含义与USE_STORED_OUTLINES一致;
      2. 限制:只有USE_STORED_OULINES关闭的时候才能打开此参数;
   4. 都可以使用ALTER SYSTEM/ALTER SESSION语法来修改;
3. 创建OUTLINE的语法解析:
   1. 创建语法:CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE outln_name [FROM PUBLIC|PRIVATE source_outline] [FOR CATEGORY category_name] [ON statement];
   2. PUBLIC:默认值,创建一个公有的OUTLINE;
   3. PRIVATE:在当前的session创建一个私有的OUTLINE,存在当前的SCHEMA下;要创建私有的OUTLINE,必须要在当前SCHEMA下使用DMBS_OUTLN_EDIT.CREATE_EDIT_TABLES过程创建一个表来存储信息;
   4. FROM子句:从一个现有的outline中创建一个新的outine,如果指定FROM子句就不用指定ON子句了;
   5. FOR CATEGORY子句:指定OUTLINE所属的分类,如果不指定就默认属于DEFAULT分类,如果指定的分类不存在,系统自动创建该分类;
   6. ON子句:要创建OUTLINE的sql语句;
      1. 当有FROM子句时不用指定;
      2. 不能是多路插入语句;
      3. 不能是远程服务器的DML操作;
   7. 修改OUTLINE:ALTER OUTLINE outln_name;
   8. 删除OUTLINE:DROP OUTLINE outln_name;
4. 相关的包:
   1. DBMS_OUTLN:
      1. CLEAR_USED:清除outline的USED标识;
      2. CREATE_OUTLINE:使用shared pool中的sql生成一个outline;
      3. DROP_BY_CAT:删除某一个分类下的所有outline;
      4. DROP_UNUSED:删除从来没有被使用过的outline;
      5. UPDATE_BY_CAT:修改一个outline的分类;
      6. UPDATE_SIGNATURES:修改outline的signature到当前的版本;
   2. DBMS_OUTLN_EDIT:
      1. CHANGE_JOIN_POS:修改执行计划中步骤的顺序;
      2. CREATE_EDIT_TABLES:在当前schema下创建一个表用来保存私有的outline;
      3. DROP_EDIT_TABLES:删除表;
5. 相关的视图:
   1. DBA_OUTLINE:所有的outline信息,其中USED表示此outline是否被使用过,SIGNATURE是sql语句的唯一标示符;
   2. DBA_OUTLINE_HINTS:所有outline的执行计划;
   3. 与OUTLINE相关的数据都保存了OUTLN用户下的对象中了:
      1. ALTER USER outln IDENTIFIED BY outln ACCOUNT UNLOCK;
      2. outln.ol$:outline的信息;
      3. outln.ol$hints:保存执行计划;
      4. outln.ol$nodes:OUTLINE节点信息;
6. 创建outline;
7. 使用outline;
8. 清除outline:
   1. 可以使用DMBS_OUTLN包来实现删除某个分类的outline;
   2. 使用drop outline语法删除单个个outline;
9. 把一个环境中的outline应用于其它环境:使用expdp导出当前环境中outln schema,然后impdp导入到其它环境即可;
10. 如果想要创建一个使用变量的outline,可以在创建outline时使用绑定变量,例子;
11. 关于HINTS:
    1. 最后才使用的方法;
    2. hints是硬编码;
    3. Hints apply to only the statement block in which they appear:只能用在第一个sql上,如果有子查询,要单独使用hints;
    4. 建议使用表的别名;
    5. HINTS写错的话,会当成注释;

————————– 创建OUTLINE ————————–

— 1.在hr用户下创建测试表;

CREATE TABLE tbobjects AS SELECT * FROM dba_objects;

— 2.创建一个outline,并属于CATE分类;

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = 100;

— 3.查看outln.ol$hints中保存的执行计划;

SELECT hint_text FROM outln.ol$hints
WHERE ol_name = ‘OL_OBJECTS’
ORDER BY HINT#;

— 4.查看真正的执行计划;

————————– 创建OUTLINE ————————–

————————– 使用OUTLINE ————————–

— 1.在表上创建索引,来改变执行计划;

CREATE INDEX IDX_TBOBJECTS_ID ON TBOBJECTS (object_id);

— 2.查看当前实际的执行计划;

— 3.查看outline是否被使用过;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

— 4.强制会话使用outline中保存的执行计划,要首先设置当前会话的CATEGORY为CATE:ALTER SESSION SET USE_STORED_OUTLINES = CATE;

— 5.查看outline是否被使用;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

— 6..消除outline对sql执行计划的影响;

方法1.ALTER SESSION SET USE_STORED_OUTLINE = FALSE;

方法2.ALTER OUTLINE ol_objects DISABLE;

————————– 使用OUTLINE ————————–

CREATE OUTLINE ol_objects FOR CATEGORY cate
ON SELECT * FROM tbobjects WHERE object_id = :object_id;

— 2.设置当前会话使用此outline

ALTER SESSION SET USE_STORED_OUTLINES = CATE;

— 3.使用绑定变量调用;

variable object_id NUMBER;

exec :object_id := 100;

SELECT * FROM tbobjects WHERE object_id = :object_id;

— 4.查看outline是否被使用;

SELECT NAME, category, used, sql_text FROM user_outlines WHERE NAME = ‘OL_OBJECTS’;

————————– 创建OUTLINE时使用绑定变量 ————————–