SQL Tuning Advisor(STA)到底做了什么(三)
matching from dba_sql_profiles;
NAME CATEGORY SIGNATURE SQL_TEXT CREATED TYPE STATUS FOR
------------ ---------- -------------------- ----------------------------------- ----------------- --------- -------- ---
hr_profile DEFAULT 8856746596263812636 SELECT /*+ ORDERED */ 20130607 14:27:33 MANUAL ENABLED NO
*
FROM employees e, locations l, de
partments d
WHERE e.department_id = d.departme
nt_id AND l.location_id = d.locatio
n_id AND e.employee_id < :bnd
------------------------------------------------------------------------------------------------------------
--下面是dba_sql_profiles数据字典中的列几个重要的说明
a、CATEGORY: Category of the SQL profile
-- 用于对SQL Profile实施分类管理，指明了当前的SQL profile属于哪一个分类，缺省情况下dafault类会被激活
-- 可以在接受SQL profile时指定分类，具体参考包dbms_sqltune.accept_sql_profile
-- 初始化参数sqltune_category用于控制系统和会话级别的category被激活，也就是说单个session仅仅支持单一的category激活
-- 如下查看当前设定的category及如何修改
-->查看系统的sqltune_category参数
hr@CNMMBO> show parameter sqltune
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sqltune_category string DEFAULT
-->通过下面的方式来修改缺省的catagory类别
alter session | system sqltune_category = category_name;
-------------------------------------------------------------------------------------------------------------------
b、SIGNATURE: Unique identifier generated from normalized SQL text
-- 用于控制标准化SQL文本的到唯一签名ID并将该ID的profile的信息存储到数据字典
-- 在category激活以及profile被启用的情形下，后续的SQL语句运行时如果得到的签名ID与存储到数据字典的ID相同，则SQL profile被使用
-------------------------------------------------------------------------------------------------------------------
c、TYPE: Type of the SQL profile (how it was created):MANUAL AUTO-TUNE
-- 该列表明对应的sql profile是自动还是手动产生的
-------------------------------------------------------------------------------------------------------------------
d、STATUS: Status of the SQL profile: ENABLED DISABLED VOID
-- 该列表明对于sql profile是否处于启用状态
-------------------------------------------------------------------------------------------------------------------
e、FORCE_MATCHING
-- If TRUE this causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values to bind variables.
-- (Note that if a combination of literal values and bind variables is used in the same SQL text, no transformation occurs).
-- This is analogous to the matching algorithm use by the FORCE option of the CURSOR_SHARING parameter.
-- If FALSE, literals are not transformed.
-- This is analogous to the matching algorithm used by the EXACT option of the CURSOR_SHARING parameter.
--FORCE_MATCHING描述了SQL文本是模糊匹配还是精确匹配
--为true时等同于CURSOR_SHARING参数的force，不区分空格，大小写及字面量。如果字面量与绑定变量混合则产生不同的签名ID
--为false时等同于CURSOR_SHARING参数的exact，不区分空格和大小写
--CURSOR_SHARING参数设置影响解析,字面量产生硬解析,绑定变量产生软解析(library cache中对应的sql存在时,可以参考Oracle硬解析与
| 评论 |
|
|