使用dbms_advisor来执行sql access advisor主要有以下几个步骤:
1.创建任务
2.定义工作量
3.生成建议
4.查看与实现建议
步骤1 创建任务
在任务建议被生成之前，必须创建一个任务。任务很重要因为它是与建议进程相关的所有信息的居住 地，包括建议处理的结果。如果使用dbms_advisor.quick_tune过程，那么会自动创建任务。在所有 其它的情况下，必须手动执行dbms_advisor.create_task过程来创建任务。通过使用dbms_adviosr.set_task_parameter过程来定义参数可以控制任务的执行。
步骤2 定义工作量
工作量是SQL Access Advisor的主要输入，并且它由一个或多个SQL语句组成，并且加上用来完全描 述每个SQL语句的各种统计信息与属性。如果工作量包含了一个目标业务系统的所有SQL语句，那么这 个工作量可以认为是一个完全工作量，如果工作量只包含了一部分SQL语句，那么就认为是部分工作 量。完全与部分工作量之间的差别是，对于完全工作量，SQL Access Advisor如果发现有些现有的物 化视图与索引没有被有效的使用，可能会建议删除特定的现有物化视图与索引。
通常来说，SQL Access Adviosr使用工作量作为所有分析活云贵的基础。虽然工作量可能包含各种各 样的SQL语句，它将根据特定的统计信息，业务重要性或统计信息与业务重要性的组合来仔细排列条 目。这种排列是关键，因为它能让SQL Access Advisor使用更少的业务影响来处理最重要的SQL语句
对于数据集合可以认为是一个有效的工作量，SQL Access Advisor可能需要存在特定的属性。如果丢 失某些条目虽然可以执行分析，那么建议的质量将会大幅度的降低。例如，SQL Access Advisor请求 一个包含SQL查询与谁执行这个查询的工作量。所有其它属性都是可选项，然而，如果工作量还包含 了I/O与CPU信息，那么SQL Access Advisor可能会更好的对语句评估当前的效率。工作量作为一个单 独的对象被存储，它是由dbms_advisor.create_sqlwkld过程所创建，并且可以很容易的在多个指导 任务之间共享内存。因为工作量是独立的，它必须使用dbms_advisor.add_sqlwkld_ref过程来链接到 一个指导任务。一旦这种链接被建立，直到所有指导任务删除了对工作量的依赖性之前，这种链接是 不能被删除的。当一个父指导任务被删除或者当工作量引用由用户执行 dbms_advisor.delete_sqlwkld_ref过程手动从指导任务中删除后，工作量引用也将被册子。
也可以在不使用工作量的情况下使用SQL Access Advisor，然而，为了最佳的结果，工作量必须以用 户提供的表形式被提供，SQL Tuning Set或从SQL Cache中导入。如果没有提供工作量，SQL Access Advisor可以生成并使用基于你用户方案所定义规模的假想工作量。
一旦工作量被加载到档案库或在生成建议时加载，可以对工作量应用过滤来限制分析什么。这提供了 一种基于不同工作量生成不同建议的能力。
建议处理与工作量的定制是由SQL Access Advisor参数来控制的。参数在任务或工作量对象的生命周 期内仍然是生效的。当使用dbms_advisor.set_task_parameter过程设置参数后，直到重新设置之前 参数值是不会发生改变的。
步骤3 生成建议
一旦任务创建并且工作量被链接到任务，且还设置了合适的参数，可以使用 dbms_advisor_execute_task过程来生成建议。这些建议会存储在SQL Access Advisor档案库中。
建议处理机制会生成一些建议并且每个建议将由一个或多个操作组成。例如，创建物化视图，然后为 了收集统计信息而对其进行分析。
任务建议可能是一个复杂解决方案的简单建议，它要求实现一组数据库对象，比如索引，物化视图与 物化视图日志。当一个指导任务被执行时，它将仔细分析收集的数据与用户调整的任务参数。SQL Access Advisor将试图基于内置的机制来格式化解决方案。解决方案然后会被精致的以结构化建议的 形式被存储可以由用户查看与实现。
步骤4 查看与实现建议
查看建议有两种方式:使用目录视图或使用dbms_advisor.get_task_script过程来生成脚本。不是所有的建议你都需要接受，并且可以在建议脚本中标记你要接受的建议。最后的步骤就是实现建 议然后验证查询性能是否有所提高。
SQL Access Advisor档案库
SQL Access Advisor所需要与所生成的所有信息都存储在SQL Access Advisor档案库中，它是数据库 数据字典的一部分。使用档案库有以下优点:
.为SQL Access Advisor收集完整的工作量
.支持历史数据
.由服务器进行管理
使用SQL Access Advisor需要的系统权限
需要有advisor权限来管理或使用SQL Access Advisor。当处理一个工作量时，SQL Access Advisor 为了试图验证每个语句需要识别引用的表与列。通过处理每个语句就像原始用户执行语句一样来完成 验证。如果用户对于特定的表没有select权限，SQL Access Advisor将跳过语句所引用的表。这可能 造成许多语句从分析操作变成了执行操作。如果SQL Access Advisor在一个工作量中执行所有语句， 工作量将会失效并且SQL Access Advisor将会返回如下信息:
QSM-00774, there are no SQL statements to process for task TASK_NAME
为了避免丢失关键的工作量查询，当前数据库用户必须有对被分析的物化视图所引用的目标表有 select权限。对于这些表，select权限不能通过角色被获得。
设置任务与模板
1.创建任务
任务是你所定义需要分析什么以及分析结果将存储在什么地方。用户可以创建任意数量的任务，每个 有特定的设置。都是基于相同指导任务模型并且共享相同档案库的。使用dbms_advisor.create_task 过程创建任务的语法如下:
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL); DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL);
下面是创建任务的一示例:
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';
PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);
PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK
使用模板
当对一个任务或工作量识别一个空闲配置后，这个配置可以被作为模板进行保存。将来的任务可以基 于这个模板进行创建。
通过设置模板，当执行调整分析时可以节省时间。还能对业务操作制定一种合 适的调整分析。
为了使用模板创建任务，当创建新任务时需要指定要使用的模板。这时，SQL Access Advisor将从模 板中复制数据与参数到新创建的任务中。当创建任务时或使用dbms_advisor.update_task_attribute 过程来设置模板属性时可以将现有的任务设置为模板。
为了使用任务作为模板，可以告诉SQL Access Advisor当创建新任务时指定要使用的任务。在这时， SQL Access Advisor将复制任务模板的数据与参数设置到新创建的任务。可以通过设置模板属性将现 有的任务设置为模板。
工作量对象也可以用来作为模板来创建新的工作量对象。
创建模板
1.创建一个名叫my_template的模板
SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';
PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name, is_template => 'true');
PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template
2.设置模板参数
SQL> -- set naming conventions for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_ '); PL/SQL procedure successfully completed template_name --------- my_template SQL> -- set default tablespace for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template
3.使用模板来创建任务
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';
PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name, template=>'my_template');
PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask
下面的例子使用预先定义的模板SQLACCESS_WAREHOUSE来创建任务
execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name, template=>'SQLACCESS_WAREHOUSE');
管理工作量
管理工作量主要从以下方面进行:
.工作量对象
.使用工作量
.链接任务与工作量
.定义工作量内容
.向工作量增加SQL语句
.从工作量中删除SQL语句
.改变工作量中的SQL语句
.维护工作量
.删除工作量
工作量对象
因为工作量作为单独的工作量对象被存储，它可以很容易的在多个指导任务之间被共享。一旦一个工 作量对象被一个指导任务所引用，那么直到所有指导任务删除它们所依赖的数据之前，工作量是不能 被删除或修改的。当父指导任务被删除或者当工作量引用被手动从指导任务中被删除时，工作量引用 将会被删除。
当工作量可以使用时，SQL Access Advisor会执行的最好。SQL Access Workload档案库能够存储多 个工作量，因此真实世界数据仓库或事务处理环境中的不同用户可以在很长一段时间内查看并且可以 踊跃数据库的重启。
在工作量的真实SQL语句被定义之前，工作量必须使用dbms_advisor.create_sqlwkld过程来进行创建
然后，使用合适的import_sqlwkld过程来加载工作量。一个特定的工作量可以通过调用 dbms_advisor.delete_sqlwkld过程来进行删除。为了删除当前用户的所有工作量，可以在调用 dbms_advisor.delete_sqlwkld过程时传递一个常量advisor_all或%。
使用工作量
dbms_advisor.create_sqlwkld过程用来创建工作量并且它必须在执行任何其它工作量操作之前存在 ，比如导入或更新SQL语句。通过工作量名来识别工作量，因此应该定义一个唯一名称来标识操作。
语法如下:
dbms_advisor.create_sqlwkld ( workload_name in out varchar2, description in varchar2 := null, template in varchar2 := null, is_template in varchar2 := 'false');
创建工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- myworkload
使用模板来创建工作量
1.创建变量
SQL> variable template_id number; SQL> variable template_name varchar2(255);
2.创建名叫my_wk_template的模板
SQL> execute :template_name := 'my_wk_template'; PL/SQL procedure successfully completed template_name --------- my_wk_template SQL> execute dbms_advisor.create_sqlwkld(:template_name, is_template=>'true'); PL/SQL procedure successfully completed template_name --------- my_wk_template
3.设置模板参数。下面设置过滤只有insur_changde方案中的表被优化:
SQL> -- set USERNAME_LIST filter to insur_changde SQL> execute dbms_advisor.set_sqlwkld_parameter(:template_name, 'USERNAME_LIST', 'insur_changde'); PL/SQL procedure successfully completed template_name --------- my_wk_template
4.使用模板来创建工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld (:workload_name, 'this is my first workload', 'my_wk_template'); PL/SQL procedure successfully completed workload_name --------- myworkload
将任务与工作量进行链接
在建议进程开始之前，任务必须被链接到一个工作量。可以通过调用 dbms_advisor.add_sqklwkld_ref过程，通过使用它们的名字来将任务与工作量进行链接。这个过程 会在指导任务与工作量之间创建一个链接。一旦在指导任务与工作量之间创建了链接，工作量将会受 保护从而避免被删除。语法如下:
dbms_advisor.add_sqlwkld_ref (task_name in varchar2,workload_name in varchar2);
下面将任务mytask与工作量myworkload进行链接.
SQL> execute dbms_advisor.add_sqlwkld_ref('mytask', 'myworkload');
PL/SQL procedure successfully completed
定义工作量的内容
一旦工作量被创建，它必须要加载一些信息。理想情况下，一个工作量将由SQL语句（除非它是一个 假想的工作量）组成，它们是在数据库中正在执行的SQL语句。SQL Access Advisor可以从以下来源 获得工作量:
.SQL调优集
.加载用户定义的工作量
.加载SQL缓存工作量
.使用假想工作量
.使用汇总的9i工作量
SQL调优集
SQL调优集是工作量档案中的一种工作量。可以使用SQL调整集作为SQL Access Advisor的工作量通过 dbms_advisor.import_workload_sts过程进行导入。下面是使用语法:
dbms_advisor.import_sqlwkld_sts (workload_name in varchar2, sts_owner in varchar2, sts_name in varchar2, import_mode in varchar2 := 'new', priority in number := 2, saved_rows out number, failed_rows out number); dbms_advisor.import_sqlwkld_sts (workload_name in varchar2, sts_name in varchar2, import_mode in varchar2 := 'new', priority in number := 2, saved_rows out number, failed_rows out number);
在工作量被收集并且过滤语句后，SQL Access Advisor使用工作量中的DML语句来计算使用统计数据 ，下面的例子使用SQL调优集创建一个名叫my_sts_workload的工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and command_type<>2',
7 null,
8 null,
9 null,
10 null,
11 1,
12 null,
13 'all')) p;
14 dbms_sqltune.load_sqlset(sqlset_name => 'MY_STS_WORKLOAD',
15 populate_cursor => cur);
16 end;
17 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'my_sts_workload';
PL/SQL procedure successfully completed
sqlsetname
---------
my_sts_workload
SQL> execute :workload_name := 'my_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_workload
SQL> execute dbms_advisor.create_sqlwkld (:workload_name);
PL/SQL procedure successfully completed
workload_name
---------
my_workload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW', 1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_workload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13
加载用户定义工作量
为了加载用户定义工伤脑筋量，使用dbms_advisor.import_sqlwkld_user过程。这个过程从用户结构 表或视图中收集应用程序工作量并将其保存在指导档案库中。owner_name与table_name两个参数识别 表是从那种类型的工作量中获得的。对于工作量存储在那个方案，表名，或有多少个用户定义的表存 在都没有限制。唯一的要求就是用户表的结构必须与USER_WORKLOAD相关，并且用户要对工作量表或 视图有select访问权限。语法如下:
dbms_advisor.import_sqlwkld_user ( workload_name in varchar2, import_mode in varchar2 := 'NEW', owner_name in varchar2, table_name in varchar2, saved_rows out number, failed_rows out number);
下面的例子加载之前创建的工作量MYWORKLOAD，使用用户表SH.USER_WORKLOAD。假设表 USER_WORKLOAD已经加载了SQL语句，并且它的结构与USER_WORKLOAD Table Format相符
variable saved_stmts number; variable failed_stmts number; execute dbms_advisor.import_sqlwkld_user( 'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
加载SQL Cache工作量
可以使用dbms_advisor.import_sqlwkld_sqlcache过程来获得SQL Cache工作量。在调用这个过程的 时候，SQL Cache中的当前内容将会被分析并且加载到工作量中。 dbms_advisor.import_sqlwkld_sqlcache过程从SQL Cache中加载SQL工作量，语法如下:
dbms_advisor.import_sqlwkld_sqlcache ( workload_name in varchar2, import_mode in varchar2, priority in number := 2, saved_rows out number, failed_rows out number);
下面的例子从SQL Cache中加载之前创建的工作量MYWORKLOAD。加载工作量语句的优先级为2:
variable saved_stmts number;
variable failed_stmts number;
execute dbms_advisor.import_sqlwkld_sqlcache('MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);
SQL Access Advisor可以从SQL Cache中检索工作量信息。如果收集的数据是从实例参数 cursor_sharing设置为similar或force的服务器中所检索到的，那么使用文本值的查询将会被转换为 包含系统生成变量的语句。如果使用SQL Access Advisor来建议物化视图，那么服务器应该将参数 cursor_sharing设置为exact，因此有where子句的物化视图会被建议。
使用假想工作量
在许多情况下，应用程序工作量是不存在的。 在这种情况下，SQL Access Advisor可以检查当前逻 辑方案设计与基于表之间所定义的关系的格式化建议。这种类型的工作量也称为假想工作量。SQL Access Advisor可以产生一组初始化建议，并且将会成为优化应用程序的坚实基础。
使用假想工作量的优点:
.只需要方案与表关系
.对于假定场景建模是有效的
使用假想工作量的缺点:
.只有定义了维度才能工作
.在建议访问结构方面没有提供关于影响DML操作的任何信息
.不一定是完整的
为了成功导入一个假想工作量，目标方案必须包含维度信息。可以使用 dbms_advisor.import_sqlwkld_schema过程，语法如下:
dbms_advisor.import_sqlwkld_schema ( workload_name in varchar2, import_mode in varchar2 := 'NEW', priority in number := 2, saved_rows out number, failed_rows out number);
下面的例子创建一个名叫SCHEMA_WKLD的假想工作量，将valid_table_list设置为sh,并调用 dbms_advisor.import_sqlwkld_schema过程来生成假想工作量。
variable workload_name varchar2(255); variable saved_stmts number; variable failed_stmts number; execute :workload_name := 'SCHEMA_WKLD'; execute dbms_advisor.create_sqlwkld(:workload_name); execute dbms_advisor.set_sqlwkld_parameter(:workload_name,VALID_TABLE_LIST, 'SH'); execute dbms_advisor.import_sqlwkld_schema(:workload_name, 'NEW', 2, :saved_stmts, :failed_stmts);
当使用dbms_advisor.import_sqlwkld_schema过程时，valid_table_list参数不能包含通配符比如 SCO%或SCOTT.EMP%。只支持唯一的通配符格式SCOTT.%，它将指定用户方案中的所有表。
使用9i汇总指导工作量
使用9i汇总工作量可以创建工作量。这些工作量可以被SQL Access Advisor使用，通过使用 dbms_advisor.import_sqlwld_sumadv过程来进行导入。为了使用这个过程，必须知道Oracle 9i的工 作量ID。
这个过程从汇总指导工作量中收集SQL工作量。这个过程的目标是为了帮助Oracle 9i汇总指导用户迁 移到SQL Access Advisor。语法如下:
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, sumadv_id IN NUMBER, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
下面的例子从Oracle 9i汇总指导工作量来创建一个SQL工作量。oracle 9i的工作量workload_id为 777
1.创建一些变量
variable workload_name varchar2(255); variable saved_stmts number; variable failed_stmts number;
2.创建工作量WKLD_9I
execute :workload_name := 'WKLD_9I'; execute dbms_advisor.create_sqlwkld(:workload_name);
3.从Oracle 9i汇总指导工作量导入SQL工作量
execute dbms_advisor.import_sqlwkld_sumadv (:workload_name, 'NEW', 2, 777, :saved_stmts, :failed_stmts);
SQL Access Advisor工作量参数
一个SQL工作量可以通过dbms_advisor.set_sqlwkld_parameter过程设置一个或多个参数来在加载时 进行过滤操作。
下面的例子设置了SQL工作量参数。将SQL_LIMIT设置为3,ORDER_LIST设置为OPTIMIZER_COST。当导入 工作量时，这意味着，语句将通过OPTIMIZER_COST来进行排序并且前三个语句将会被保留。
-- Order statements by OPTIMIZER_COST
execute dbms_advisor.set_sqlwkld_parameter ('MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST');
-- Max number of statements 3
execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'SQL_LIMIT', 3);
向工作量增加SQL语句
一种替导入工作量的方法是手动指定SQL语句并且使用dbms_advisor.add_sqlwkld_statement过程将 它们加入工作量。这个过程允许向指定的工作量增加SQL语句。语法如下:
dbms_advisor.add_sqlwkld_statement ( workload_name in varchar2, module in varchar2, action in varchar2, cpu_time in number := 0, elapsed_time in number := 0, disk_reads in number := 0, buffer_gets in number := 0, rows_processed in number := 0, optimizer_cost in number := 0, executions in number := 1, priority in number := 2, last_execution_date in date := 'SYSDATE', stat_period in number := 0, username in varchar2, sql_text in clob);
下面的例子向MYWORKLOAD工作量增加一个单独的SQL语句:
variable sql_text varchar2(400);
execute :sql_text := 'select avg(amount_sold) from sales';
execute dbms_advisor.add_sqlwkld_statement ('MYWORKLOAD', 'MONTHLY', 'ROLLUP', priority=>1, executions=>10,username => 'SH', sql_text => :sql_text);
从工作量中删除SQL语句
使用dbms_advisor.delete_sqlwkld_statement过程可以从指定的工作量中删除SQL语句。在删除SQL 语句时需要指定sql_id。
dbms_advisor.delete_sqlwkld_statement (workload_name in varchar2,sql_id in number);
下面的例子将从MYWORKLOAD工作量中从邮sql_id为10的SQL语句:
execute dbms_advisor.delete_sqlwkld_statement('MYWORKLOAD', 10);
如果工作量当前被一个活动任务所引用，那么工作量是不能被修改或删除的。如果工作量不是处于初 始状态，那么就可以认为工作量处于活动状态。dbms_advisor.reset_task过程可以将工作量设置为 初始状态。
改变工作量中的SQL语句
可以通过dbms_advisor.update_sqlwkld_statement过程来修改工作量中的SQL语句。这个过程将会更 新指定工作量中的现有SQL语句。通过指定sql_id来更新SQL语句，语法如下:
dbms_advisor.update_sqlwkld_statement ( workload_name in varchar2, sql_id in number, application in varchar2 := null, action in varchar2 := null, priority in number := null, username in varchar2 := null); dbms_advisor.update_sqlwkld_statement ( workload_name in varchar2, search in varchar2, updated out number, application in varchar2 := null, action in varchar2 := null, priority in number := null, username in varchar2 := null);
下面的例子将sql_id为10的SQL语句的优先级修改为3
维护工作量
有以下几种操作可以用来维护工作量:
.设置工作量属性
.重设工作量
.删除工作量与任务之间的链接
设置工作量属性
dbms_advisor.update_sqlwkld_attributes过程可以用来修改工作量对象或模板的各种属性。有些这 样的属性是用来描述的，比如描述它是否是一个模板或是只读。语法如下:
dbms_advisor.update_sqlwkld_attributes ( workload_name in varchar2, new_name in varchar2 := null, description in varchar2 := null, read_only in varchar2 := null, is_template in varchar2 := null, how_created in varchar2 := null);
下面的例子将MYWORKLOAD工作量修改为只读:
execute dbms_advisor.update_sqlwkld_attributes('myworkload', read_only=> 'true');
重设工作量
dbms_advisor.reset_sqlwkld过程可以将一个工作量设置为初始状态。当工作量数据保持原样时这将 会删除所有的日记和日志信息，并重新计算易波动的统计信息。这个过程应该在任何工作量被调整后 ，比如增加或删除SQL语句后执行这个过程。下面的例子将重新设置MYWORKLOAD工作量.
execute dbms_advisor.reset_sqlwkld('myworkload');
删除工作量与任务之间的链接
在任务或工作量被删除之前，如果它被分别链接到一个工作量或任务，那么在任务与工作量之间的链 接必须使用delete_sqlwkld_ref过程来进行删除。下面的例子将会把任务MYTASK与SQL工作量 MYWORKLOAD之间的链接删除。
execute dbms_advisor.delete_sqlwkld_ref('mytask', 'myworkload');
删除工作量
当工作量不再需要时，可以使用dbms_advisor.delete_sqlwkld过程来将其删除。可以删除所有工作 量或者一个特定的集合，但如果工作量仍然被链接到一个任务，那么这个工作量将不会被删除。
下面的例子用来删除一个特定的工作量。
dbms_advisor.delete_sqlwkld (workload_name in varchar2);
execute dbms_advisor.delete_sqlwkld('myworkload');
处理建议
处理建议包含以下方面的内容:
.建议选项
.评估模式
.生成建议
.查看建议
.SQL工作量日记
.停止建议处理
.标记建议
.修改建议
.生成SQL脚本
.何时将不再需要建议
建议选项
在建议生成之前，任务的参数首先必须使用dbms_advisor.set_task_parameter过程来进行定义。如 果没有定义参数，那么将会使用缺省值。使用dbms_advisor.set_task_parameter过程设置参数的语 法如下:
dbms_advisor.set_task_parameter ( task_name in varchar2, parameter in varchar2, value in [varchar2 | number]);
在下面的例子中，将任务MYTASK的存储大小修改为100MB。这将指示对于建议将有额外的100MB空间。 如果设置为0,说明没有额外的空间被分配。如果设置为一个负值，则说明指导必须试图削减当前空间 利用的大小，其大小是参数所指定。
execute dbms_advisor.set_task_parameter('mytask','storage_change', 100000000);
在下面的例子中，将使用valid_table_list参数来过滤掉所有不包括sh.sales和sh.customers表的查询。
execute dbms_advisor.set_task_parameter('MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');
评估模式
当执行一个任务时，SQL Access Advisor有两种操作模式:问题解决与评估。缺省情况下，SQL Access Advisor将试图通过寻找索引结构，物化视图与物化视图日志的改进来解决访问方法的问题。 当只进行评估操作时，SQL Access Advisor将只会给出提供什么访问结构让工作量使用的见意。例如 ，一个问题可能可以通过创建一个新索引，增加一个新列到物化视图日志，等等的方法来解决。而当 进行评估操作时，只会生成比如保留索引，保留物化视图等建议。当进行评估操作时不会考虑对访问 方法的调整。它是一种严格的方法用来查看现有访问方法结构和它们如何被提供给工作量所使用。
生成建议
通过执行dbms_advisor_execute_task过程并指定任务名来生成建议。在这个过程执行完成后，可以 检查dba_advisor_log表来检查真实的执行状态与生成的建议数量与操作数。可以使用任务名来查询
{dba,user}_advisor_recommendations视图来查看建议，查询{dba,user}_advisor_actions视图来查 看这些建议的操作。
dbms_advisor.execute_task
dbms_advisor.execute_task过程用来对特定任务执行SQL Access Advisor分析或评估。执行任务是 一个同步操作，所以直到操作完成之前不会将控制返回给用户。任务在执行时或执行完成后，可以检 查dba_advisor_log表来查看真实的执行状态。
执行dbms_advisor.execute_task过程来生成建议，建议是由一个或多个操作组成，比如创建物化视 图日志或物化视图。语法如下:
dbms_advisor.execute_task (task_name in varchar2);
下面的例子执行任务MYTASK:
execute dbms_advisor.execute_task('MYTASK');
查看建议
由SQL Access Advisor生成的每个建议可以使用目录视图来进行查看，比如{dba,user} _advisor_recommendations视图。然而，最简单的方法是使用dbms_advisor.get_task_script过程或 使用EM中的SQL Access Advisor，它是一个图形工具来显示建议并且提供了一个超链接来快速查看那 个语句将会受益于这个建议。由SQL Access Advisor生成的每个建议被链接到受益于它的SQL语句。
下面显了由SQL Access Advisor由生成的建议(rec_id)，以及它们的排名与总收益。排名是一种测量 标准判断建议对于查询的重要性。收益是所有查询使用建议后它的执行成本（优化器成本）的提高总量。
variable workload_name varchar2(255);
variable task_name varchar2(255);
execute :task_name := 'MYTASK';
execute :workload_name := 'MYWORKLOAD';
select rec_id, rank, benefit
from user_advisor_recommendations where task_name = :task_name;
REC_ID RANK BENEFIT
---------- ---------- ----------
1 2 2754
2 3 1222
3 1 5499
4 4 594
为了识别那个查询受益于那个建议，可以使用dba_*与user_advisor_sqla_wk_stmts。precost与 postcost是分别对不使用与使用建议的发生改变的访问结构来评估优化器成本的项目(在explain plan中所示)。为了查看每个查询，执行以下语句:
select sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost as percent_benefit from user_advisor_sqla_wk_stmts where task_name = :task_name and workload_name = :workload_name; SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT ---------- ---------- ---------- ---------- --------------- 121 1 3003 249 91.7082917 122 2 1404 182 87.037037 123 3 5503 4 99.9273124 124 4 730 136 81.369863
每一个建议都是由一个或多个操作组成，这些操作必须都执行才能受益于建议。SQL Access Advisor 提供了以下类型的操作:
.create|drop|retain materialized view .create|alter|retain materialized view log .create|drop|retain index .gather stats
create操作关联新的访问结构，retain建议指示现有的访问结构必须保留，drop建议只有在 workload_scope参数设置为full时才会生成。gather stats操作将生成调用dbms_stats过程的语句用 来对新生成的访问结构收集统计信息。需要注意的是可能多个建议引用了相同操作，然后在生成的建 议脚本中，对于每个操作只会看到一次。
在下面的例子中，可以看到对于这组建议有许多不同的操作。
select 'action count', count(distinct action_id) cnt
from user_advisor_actions where task_name = :task_name;
'ACTIONCOUNT CNT
------------ ----------
Action Count 20
-- see the actions for each recommendations
select rec_id, action_id, substr(command,1,30) as command
from user_advisor_actions where task_name = :task_name
order by rec_id, action_id;
REC_ID ACTION_ID COMMAND
---------- ---------- ------------------------------
1 5 CREATE MATERIALIZED VIEW LOG
1 6 ALTER MATERIALIZED VIEW LOG
1 7 CREATE MATERIALIZED VIEW LOG
1 8 ALTER MATERIALIZED VIEW LOG
1 9 CREATE MATERIALIZED VIEW LOG
1 10 ALTER MATERIALIZED VIEW LOG
1 11 CREATE MATERIALIZED VIEW
1 12 GATHER TABLE STATISTICS
1 19 CREATE INDEX
1 20 GATHER INDEX STATISTICS
2 5 CREATE MATERIALIZED VIEW LOG
2 6 ALTER MATERIALIZED VIEW LOG
2 9 CREATE MATERIALIZED VIEW LOG
...
每个操作有多个属性，它是关于访问结构的属性。每个访问结构的名称与表空间被分别存储在 dba_advisor_actions视图中的attr1与attr2列中。每个新访问结构所占用的空间大小存储在 dba_advisor_actions视图中的num_attr1列中。每个操作的所有其它属性是不同的。
下面的PL/SQL过程可以用来打印建议的一些属性.
create or replace procedure show_recm (in_task_name in varchar2) is
cursor curs is
select distinct action_id, command, attr1, attr2, attr3, attr4
from user_advisor_actions
where task_name = in_task_name
order by action_id;
v_action number;
v_command varchar2(32);
v_attr1 varchar2(4000);
v_attr2 varchar2(4000);
v_attr3 varchar2(4000);
v_attr4 varchar2(4000);
v_attr5 varchar2(4000);
begin
open curs;
dbms_output.put_line('=========================================');
dbms_output.put_line('Task_name = ' || in_task_name);
loop
fetch curs into
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
exit when curs%notfound;
dbms_output.put_line('Action ID: ' || v_action);
dbms_output.put_line('Command : ' || v_command);
dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
dbms_output.put_line('Attr4 : ' || v_attr4);
dbms_output.put_line('Attr5 : ' || v_attr5);
dbms_output.put_line('----------------------------------------');
end loop;
close curs;
dbms_output.put_line('=========end recommendations============');
end show_recm;
/
-- see what the actions are using sample procedure
set serveroutput on size 99999
execute show_recm(:task_name);
A fragment of a sample output from this procedure is as follows:
Task_name = MYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "SH"."CUSTOMERS"
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "SH"."SH_MV$$_0004"
Attr2 (tablespace): "SH_MVIEWS"
Attr3 : REFRESH FAST WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 19
Command : CREATE INDEX
Attr1 (name) : "SH"."SH_IDX$$_0013"
Attr2 (tablespace): "SH_INDEXES"
Attr3 : "SH"."SH_MV$$_0002"
Attr4 : BITMAP
Attr5 :
SQL工作量日记
在执行分析处理(execute_task)时，SQL Access Advisor保存了关于分析日记的有用信息。可以使用
user_advisor_journal视图来查看日记。输出的信息量依赖于任务参数journaling的设置。
在导入工作量时，各种信息被导入到SQL工作量日记中。它们可以使用user_advisor_sqlw_journal视 图来进行查看。例如，如果一个特定的SQL语句引用了无效的表，表丢失统计信息或有权限错误，这 些信息会被记录在日记中。输出的信息量可以通过journaling。
为了关闭日记，执行以下语句:
execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 0);
为了查看信息，执行以下语句:
execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 4);
为了查看致命信息，执行以下语句:
execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'JOURNALING', 1);
停止建议处理
如果SQL Access Advisor执行dbms_advisor.execute_task过程花了太长时间来生成建议，可以执行 dbms_advisor.cancel_task过程并传入任务名来进行停止处理。如果执行dbms_advisor.cancel_task 过程，将不会生成建议。另外可以执行dbms_advisor.interrupt_task过程来终止建议处理。
dbms_advisor.interrupt_task过程会造成一个Advisor操作被终止就像正常结束一样。因此用户可以 看到到终止时间点已经格式化的任何建议。
一个终止的任务不能被重启。语法如下:
dbms_advisor.interrupt_task(task_name in varchar2);:
终止任务MY_TASK
execute dbms_advisor.interrupt_task ('my_task');
dbms_advisor.cancel_task过程会造成一个当前执行操作被终止。一个Advisor操作可能花费了几秒 来响应这个调用。因为所有Advisor任务过程是同步进行的，为了取消一个操作，必须使用一个单独 的数据库会话。
取消命令有效的将任务还原到它启动取消操作开始的条件状态。因此，取消的任务或数据对象不需要重启。
dbms_advisor.cancel_task (task_name in varchar2);
取消任务MYTASK:
execute dbms_advisor.cancel_task('MYTASK');
标记建议
缺省情况下，所有SQL Access Advisor建议已经准备好被实现了，然而，用户可以通过执行 dbms_advisor.mark_recommendation过程来选择跳过或执行所选择的建议。 dbms_advisor.mark_recommendation过程允许用户使用reject或ignore设置来注释一个建议，当生成 实现过程时它将造成dbms_advisor.get_task_script过程来跳过它。语法如下:
dbms_advisor.mark_recommendation ( task_name in varchar2 id in number, action in varchar2);
下面的例子将ID为2的建议标记为reject。这个建议与任何依赖的建议将不会出现在脚本中。
execute dbms_advisor.mark_recommendation('MYTASK', 2, 'REJECT');
修改建议
可以使用dbms_advisor.update_rec_attributes过程，SQL Access Advisor名字与指派给新对象的关 系，比如在分析操作时的索引与物化视图。然而，它不是必须要选择合适的名字，因此可以手动设置 所有者，名字与新对象的表空间名。对于建议引用的现有数据库对象，所有者与名字不会改变。语法 如下:
dbms_advisor.update_rec_attributes ( task_name in varchar2 rec_id in number, action_id in number, attribute_name in varchar2, value in varchar2);
其中,attribute_name参数可以有以下参数值:
.owner:指定建议对象的所有者
.name:指定建议对象名字
.tablespace:指定建议对象表空间
下面的用来修改SH_MVIEWS操作ID为1,建议ID为1的tablespace属性
execute dbms_advisor.update_rec_attributes('MYTASK', 1, 1,'TABLESPACE', 'SH_MVIEWS');
生成SQL脚本
一种查看建议的替代方法就是查询元数据，它将使用dbms_advisor.task_script过程来为SQL语句创 建建议脚本。最终脚本是一个可以执行的SQL文件，它可能包含drop,create和alter语句。对于新对 象，物化视图名字，物化视图日志名与使用用户定义模板自动生成的索引。你也能在试图执行它之前 生成SQL脚本。
这里有四个任务参数控制着命名规则(mview_name_template与index_name_template),这些对象的所 有者(def_index_owner与def_mview_owner)与表空间(def_mview_tablespace与 def_index_tablespace)。
下面的例子显示了如何生成包含建议的CLOB:
execute dbms_advisor.create_file(dbms_advisor.get_task_script ('MYTASK'),'ADVISOR_RESULTS', 'advscript.sql');
为了保存脚本文件，必须提供目录路径，因此dbms_advisor.create_file过程需要知道脚本存储位置 。另外，必须要对这个目录持有读写权限。下面的例子显示了如何保存一个CLOB类型的指导脚本文件
SQL> create directory advisor_results as '/bak';
Directory created
SQL> grant read,write on directory advisor_results to public;
Grant succeeded
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');
PL/SQL procedure successfully completed
下面是由这个过程生成的脚本内容的一部分。这个脚本包含了对建议访问结构收集统计信息的调用并 且在最后将建议标记为implemented。
Rem Access Advisor V10.1.0.0.0 - Production
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date: 15/04/2005 11:35
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
create materialized view log on "sh"."products"
with rowid, sequence("prod_id","prod_subcategory")
including new values;
alter materialized view log force on "sh"."products"
add rowid, sequence("prod_id","prod_subcategory")
including new values;
..
create materialized view "sh"."mv$$_00510002"
refresh fast with rowid
enable query rewrite
as select sh.customers.cust_state_province c1, count(*) m1 from
sh.customers where (sh.customers.cust_state_province = 'ca') group
by sh.customers.cust_state_province;
begin
dbms_stats.gather_table_stats('"sh"', '"mv$$_00510002"', null,
dbms_stats.auto_sample_size);
end;
/
如何不再需要建议
dbms_advisor.reset_task过程可以将一个任务重新设置为初始启动状态。这将会删除所有建议和任 务的中间数据。任务的实际状态被设置为initial。语法如下:
dbms_advisor.reset_task (task_name in varchar2);
下面的例子将任务MYTASK进行重设置:
execute dbms_advisor.reset_task('mytask');
执行快速优化
如果只想要优化单个SQL语句，dbms_advisor.quick_tune过程将接受任务名与一个SQL语句作为参数 来执行。它将创建一个任务与工作量，并且执行任务。使用dbms_advisor.quick_tune过程所得到的 结果没有差异。它们实际上与使用dbms_advisor.execute_task过程得到的结果是一样的，但是对于 只优化一个SQL语句来说，快速优化这种方法更容易。其语法如下:
dbms_advisor.quick_tune ( advisor_name in varchar2, task_name in varchar2, attr1 in clob, attr2 in varchar2 := null, attr3 in number := null, task_or_template in varchar2 := null);
下面是快速优化一个SQL语句的例子:
SQL> variable task_name varchar2(255);
SQL> variable sql_stmt varchar2(4000);
SQL> execute :sql_stmt := 'select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = ''1'' and b.indi_id = j.indi_id and (j.urban_type = ''all'' or ''all'' = ''all'') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = ''2'' and b.biz_type <> ''52'' and b.biz_type <> ''17'' and b.pers_type_detail in (2) and nvl(b.finish_flag, ''0'') = ''1'' and b.end_date between to_date(''2014-01-01 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'') and to_date(''2014-12-31 23:59:59'', ''yyyy-mm- dd hh24:mi:ss'')';
PL/SQL procedure successfully completed
sql_stmt
---------
select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = '1' and b.indi_id = j.indi_id and (j.urban_type = 'all' or 'all' = 'all') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
SQL> execute :task_name := 'my_quicktune_task';
PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
SQL> execute dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,:task_name, :sql_stmt);
PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
sql_stmt
---------
select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = '1' and b.indi_id = j.indi_id and (j.urban_type = 'all' or 'all' = 'all') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
SQL> create or replace procedure show_recm (in_task_name in varchar2) is
2 cursor curs is
3 select distinct action_id, command, attr1, attr2, attr3, attr4
4 from user_advisor_actions
5 where task_name = in_task_name
6 order by action_id;
7
8 v_action number;
9 v_command varchar2(32);
10 v_attr1 varchar2(4000);
11 v_attr2 varchar2(4000);
12 v_attr3 varchar2(4000);
13 v_attr4 varchar2(4000);
14 v_attr5 varchar2(4000);
15 begin
16 open curs;
17 dbms_output.put_line('=========================================');
18 dbms_output.put_line('Task_name = ' || in_task_name);
19 loop
20 fetch curs into
21 v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
22 exit when curs%notfound;
23 dbms_output.put_line('Action ID: ' || v_action);
24 dbms_output.put_line('Command : ' || v_command);
25 dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
26 dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
27 dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
28 dbms_output.put_line('Attr4 : ' || v_attr4);
29 dbms_output.put_line('Attr5 : ' || v_attr5);
30 dbms_output.put_line('----------------------------------------');
31 end loop;
32 close curs;
33 dbms_output.put_line('=========end recommendations============');
34 end show_recm;
35 /
Procedure created
SQL> set serveroutput on size 99999
SQL> execute show_recm('my_quicktune_task');
=========================================
Task_name = my_quicktune_task
Action ID: 1
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : REFRESH FORCE WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
Action ID: 2
Command : GATHER TABLE STATISTICS
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : -1
Attr4 :
Attr5 :
----------------------------------------
=========end recommendations============
PL/SQL procedure successfully completed
管理任务
每次建议被生成，任务被创建，并且除非对这些任务执行一些维护操作，它们将会随着时间而增长并 且将会占用存储空间。有些任务可以想要保留并且防止意外删除。因此对于任务有多个管理操作可以 执行。
.更新任务属性
.删除任务
.设置days_to_expire参数
更新任务属性
使用dbms_advisor.update_task_attributes过程，可以执行以下操作:
.改变任务名称
.指定任务描述
.设置任务为只读状态，因上不能被修改
.将任务设置为模板让其它任务可以依赖它进行定义
.改变任务或任务模板的各种属性
其语法如下:
dbms_advisor.update_task_attributes ( task_name in varchar2 new_name in varchar2 := null, description in varchar2 := null, read_only in varchar2 := null, is_template in varchar2 := null, how_created in varchar2 := null);
下面的例子将任务名称MYTASK更新为TUNING1:
execute dbms_advisor.update_task_attributes('MYTASK', 'TUNING1');
下面的例子将任务TUNING1标记为只读:
execute dbms_advisor.update_task_attributes('TUNING1', read_only => 'TRUE');
下面的例子将把任务MYTASK标记为模板
execute dbms_advisor.update_task_attributes('TUNING1', is_template=>'TRUE');
删除任务
dbms_advisor.delete_task过程将从档案库中删除指导任务。其语法如下:
dbms_advisor.delete_task(task_name in varchar2);
下面的例子将任务MYTASK删除:
execute dbms_advisor.delete_task('MYTASK');
设置DAYS_TO_EXPIRE参数
当一个任务或工作量被创建后，参数days_to_expire被设置为30天。这个参数指示直到创建时间达到 指定天数时任务或对象将会由系统自动删除。如果你想要无限期的保存一个任务或工作量，参数 days_to_expire应该被设置为advisor_unlimited。
使用SQL Access Advisor进行SQL优化的例子
使用SQL调优集来加载工作量执行SQL Access Advisor
1.创建任务
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';
PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);
PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK
2.创建工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- myworkload
3.将任务与工作量进行链接
SQL> execute dbms_advisor.add_sqlwkld_ref('JYTASK', 'myworkload');
PL/SQL procedure successfully completed
4.从SQL调优集加载工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and command_type<>2',
7 null,
8 null,
9 null,
10 null,
11 1,
12 null,
13 'all')) p;
14 dbms_sqltune.load_sqlset(sqlset_name => 'MY_STS_WORKLOAD',
15 populate_cursor => cur);
16 end;
17 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY_STS_WORKLOAD';
PL/SQL procedure successfully completed
sqlsetname
---------
MY_STS_WORKLOAD
SQL> execute :workload_name := 'myworkload';
PL/SQL procedure successfully completed
workload_name
---------
myworkload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW',1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
myworkload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13
5.执行任务
SQL> exec dbms_advisor.execute_task('JYTASK');
PL/SQL procedure successfully completed
6.查看建议
SQL> select rec_id, rank, benefit from user_advisor_recommendations where task_name ='JYTASK';
REC_ID RANK BENEFIT
---------- ---------- ----------
1 1 0
2 2 511856
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 17712
9 9 0
10 10 171589
11 11 352280
12 12 0
13 13 35201233
14 14 249249
15 15 0
16 16 0
17 17 0
18 18 0
19 19 67650
20 20 24901
21 21 0
22 22 45756
23 23 0
24 24 0
25 25 0
26 26 44170
SQL> select sql_id, rec_id, precost, postcost,
2 decode((precost-postcost),0,0,round((precost-postcost)*100/precost,2)) as percent_benefit
3 from user_advisor_sqla_wk_stmts
4 where task_name ='JYTASK' and workload_name ='myworkload';
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
2450 5 4440 4440 0
2451 13 19012 13580 28.57
2452 1 2908 2908 0
2453 3 4907 4907 0
2454 9 2665 2665 0
2455 4 7180 7180 0
2456 12 4230 4230 0
2457 9 1521 1521 0
2458 85 14322 434 96.97
2459 27 9809 9809 0
2460 2 31086 8478 72.73
2461 5 4050 4050 0
2462 6 15432 15432 0
2463 1 935 935 0
2464 307 14490 420 97.1
2465 20 2900 2175 25
2466 5 1401 1401 0
2467 7 1980 1980 0
2468 128 224180 67254 70
2469 23 8098 8098 0
下面的PL/SQL过程可以用来打印建议的一些属性.
SQL> create or replace procedure show_recm (in_task_name in varchar2) is
2 cursor curs is
3 select distinct action_id, command, attr1, attr2, attr3, attr4
4 from user_advisor_actions
5 where task_name = in_task_name
6 order by action_id;
7
8 v_action number;
9 v_command varchar2(32);
10 v_attr1 varchar2(4000);
11 v_attr2 varchar2(4000);
12 v_attr3 varchar2(4000);
13 v_attr4 varchar2(4000);
14 v_attr5 varchar2(4000);
15 begin
16 open curs;
17 dbms_output.put_line('=========================================');
18 dbms_output.put_line('Task_name = ' || in_task_name);
19 loop
20 fetch curs into
21 v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
22 exit when curs%notfound;
23 dbms_output.put_line('Action ID: ' || v_action);
24 dbms_output.put_line('Command : ' || v_command);
25 dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
26 dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
27 dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
28 dbms_output.put_line('Attr4 : ' || v_attr4);
29 dbms_output.put_line('Attr5 : ' || v_attr5);
30 dbms_output.put_line('----------------------------------------');
31 end loop;
32 close curs;
33 dbms_output.put_line('=========end recommendations============');
34 end show_recm;
35 /
Procedure created
SQL> set serveroutput on size 9999999
SQL> execute show_recm('JYTASK');
=========================================
Task_name = JYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 3
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."FC_BIZ_POLICY
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
Action ID: 5
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_TREAT_TYPE
Attr2 (tablespace):
Attr3 : ROWID, PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 7
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_PERSON_TYP
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 9
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."PFS_INSUR_DET
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 11
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 13
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_STAC"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CULTURE_ST
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 17
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CALLING"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 19
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OCCUPATION
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 21
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_BIZ_LICE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 23
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 25
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SALARYSYS"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 27
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_JOIN_STA"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 29
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_REVEN
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 31
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_MEDI_ITEM_
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 33
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_HIRED_TYPE
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 35
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_CHARG
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 37
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_ECON_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 39
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."CG_INDI_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 41
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_FINAL
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 43
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_LOWINSR"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 45
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SUB_CONN"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 47
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL_I
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 49
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 51
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CHARGE_COD
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :ant succeeded
SQL> create directory advisor_results as '/bak';
Directory created
SQL> grant read,write on directory advisor_results to public;
Grant succeeded
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');
PL/SQL procedure successfully completed
[IBMP740-1:root:/bak]#more jy_advscript.sql
Rem SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem Username: INSUR_CHANGDE
Rem Task: JYTASK
Rem Execution date: 06/09/2016 09:48
Rem
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."FC_BIZ_POLICY"
WITH ROWID, SEQUENCE ("POLICY_CODE","POLICY_VALUE","CENTER_OR_HOSP","VALID_FLAG","CENTER_ID")
INCLUDING NEW VALUES;
...省略...
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A0069"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
'0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
DECODE(SUBSTR('110',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
'000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM BS_CATALOG_MATCH
A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
'430701' AND A.HOSPITAL_ID = '4307000001' AND A.HOSP_CODE = '111965**'
AND A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE(
'2016-09-02','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-02'
,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
= (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-02'
,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-02','yyyy-mm-dd'
) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
= C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
C.VALID_FLAG(+) AND TO_DATE('2016-09-02','yyyy-mm-dd') >= C.EFFECT_DATE(+)
AND TO_DATE('2016-09-02','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
'2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A0069"',NULL,dbms_stats.auto_sample_size);
end;
/
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A006A"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
'0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
DECODE(SUBSTR('120',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
'000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM BS_CATALOG_MATCH
A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
'430722' AND A.HOSPITAL_ID = '4307220004' AND A.HOSP_CODE = '00358' AND
A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE(
'2016-09-01','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-01'
,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
= (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-01'
,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-01','yyyy-mm-dd'
) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
= C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
C.VALID_FLAG(+) AND TO_DATE('2016-09-01','yyyy-mm-dd') >= C.EFFECT_DATE(+)
AND TO_DATE('2016-09-01','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
'2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A006A"',NULL,dbms_stats.auto_sample_size);
end;
/
...省略...
使用任务模板来加载工作量执行SQL Access Advisor
1.创建任务模板my_template
SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';
PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name, is_template => 'true');
PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template
2.设置模板参数
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_ '); PL/SQL procedure successfully completed template_name --------- my_template SQL> -- set default tablespace for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template
3.使用模板来创建任务mytask
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';
PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name, template=>'my_template');
PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask
4.创建工作量my_template_workload
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'my_template_workload'; PL/SQL procedure successfully completed workload_name --------- my_template_workload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- my_template_workload
5.从SQL调优集加载工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY__TEMPLATE_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE''
7
8 and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and
9
10 command_type<>2',
11 null,
12 null,
13 null,
14 null,
15 1,
16 null,
17 'all')) p;
18 dbms_sqltune.load_sqlset(sqlset_name => 'MY__TEMPLATE_STS_WORKLOAD',
19 populate_cursor => cur);
20 end;
21 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY__TEMPLATE_STS_WORKLOAD';
PL/SQL procedure successfully completed
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
SQL> execute :workload_name := 'my_template_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW',1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
saved_stmts
---------
650
failed_stmts
---------
38
6.创建任务与工作量之间的链接
SQL> execute dbms_advisor.add_sqlwkld_ref('mytask','my_template_workload');
PL/SQL procedure successfully completed
7.执行任务
SQL> execute dbms_advisor.execute_task('mytask');
PL/SQL procedure successfully completed
8.生成建议脚本
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('mytask'),'ADVISOR_RESULTS', 'jy_mytask_advscript.sql');
PL/SQL procedure successfully completed
[IBMP740-1:root:/bak]# more jy_mytask_advscript.sql
Rem SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem Username: INSUR_CHANGDE
Rem Task: mytask
Rem Execution date: 06/09/2016 10:42
Rem
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_PERSON_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HIRED_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSP_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OFFICIAL_IDEN"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TOWNS"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OFFICIAL"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_VILLAGE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_BANK_INFO"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_FOLK"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TAX_INFO"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_STREET"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_FUND_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SEX"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OCCUPATION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_COMMUNITY"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_POSITION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_KINDRED"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CM_OUTHOS_CIRCS"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_DISTRICT"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CG_INDI_REASON"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."SYS_QUESTIONNAIRE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_GROUP"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSP_BIZ_PERS"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSPITAL_COLLATE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSPITAL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_BIZTYPE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CG_INDI_ALT_DATA"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."MS_RECEIVE_SQL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."MQ_SQL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."LV_PRD"
WITH ROWID, SEQUENCE("CALC_PRD","CURR_YEAR","CENTER_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TREAT_TYPE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_DISEASE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."APPLICATION_VERSION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."SH_MV$$_0008"
TABLESPACE "USERS"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SUM(CITY_CODE) CITY_CODE FROM ( SELECT '1' CITY_CODE FROM BS_OUT O WHERE
O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '1' AND O.INDI_ID = '208227'
AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' UNION SELECT
'1' CITY_CODE FROM BS_OUT O, (SELECT BOC.LEAVE_NO, BOC.INPUT_DATE FROM
BS_OUT_CHANGE BOC, BS_OUT O WHERE BOC.LEAVE_NO = O.LEAVE_NO AND BOC.VALUE_AFTER
= '异地正式取消' AND O.INDI_ID = '208227') C WHERE O.LEAVE_NO = C.LEAVE_NO(+)
AND O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '4' AND O.INDI_ID = '208227'
AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' AND TO_CHAR(C.INPUT_DATE,
'yyyy-mm-dd') > '2016-09-06' UNION SELECT NVL(BI.CITY_CODE, 0) CITY_CODE
FROM BS_INSURED BI WHERE BI.INDI_ID = '208227' AND NOT EXISTS (SELECT
1 FROM BS_OUT BO WHERE BO.INDI_ID = BI.INDI_ID AND BO.LEAVE_TYPE <> 2
AND BO.LEAVE_STATUS = 1) );
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"SH_MV$ $_0008"',NULL,dbms_stats.auto_sample_size);
end;
/
使用SQL Caceh加载工作量来调用SQL Access Advisor
1.创建名为my_cache_workload的工作量
SQL> execute :workload_name := 'my_cache_workload'; PL/SQL procedure successfully completed workload_name --------- my_cache_workload SQL> execute dbms_advisor.create_sqlwkld(:workload_name); PL/SQL procedure successfully completed workload_name --------- my_cache_workload
2.设置过滤
只加载只包含INSUR_CHANGDE用户所拥有表的SQL语句
SQL> execute dbms_advisor.set_sqlwkld_parameter('my_cache_workload', 'USERNAME_LIST', 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
3.从SQL Cache中加载工作量
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> variable workload_name varchar2(255);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :workload_name := 'my_cache_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
SQL> EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (:workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
saved_stmts
---------
120672
failed_stmts
---------
1515
SQL> SELECT num_select_stmt, create_date
2 FROM user_advisor_sqlw_sum
3 WHERE workload_name = :workload_name;
NUM_SELECT_STMT CREATE_DATE
--------------- -----------
84547 2016/9/6 11
workload_name
---------
my_cache_workload
SQL> SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)
2 FROM user_advisor_sqlw_stmts
3 WHERE workload_name ='my_cache_workload'
4 ORDER BY sql_id;
SQL_ID USERNAME OPTIMIZER_COST SUBSTR(SQL_TEXT,1,30)
---------- ------------------------------ -------------- --------------------------------------------------------------------------------
4319 INSUR_CHANGDE 0 insert into bs_indi_freeze_his
4320 INSUR_CHANGDE 0 update bs_pres_insur set
4321 INSUR_CHANGDE 0 select a.serial_match,
4322 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4323 INSUR_CHANGDE 0 select nvl(catalog_center,cent
4324 INSUR_CHANGDE 0 select scene_value from mt_biz
4325 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4326 INSUR_CHANGDE 0 insert into bs_mdi_indi_acc( I
4327 INSUR_CHANGDE 0 select biz_type,center_id from
4328 INSUR_CHANGDE 0 select bi.indi_id,bi.pers_type
4329 INSUR_CHANGDE 0 select nvl(cp.indi_join_flag,0
4330 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4331 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4332 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4333 INSUR_CHANGDE 0 select a.dataobj_name from
4334 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4335 INSUR_CHANGDE 0 select password from bs_cards
4336 INSUR_CHANGDE 0 select t.old_value,t.new_value
4337 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4338 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4.创建名为my_cache_task的任务
SQL> execute :task_name := 'my_cache_task';
PL/SQL procedure successfully completed
task_name
---------
my_cache_task
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name);
PL/SQL procedure successfully completed
task_id
---------
45556
task_name
---------
my_cache_task
5.在任务与工作量之间创建链接
SQL> execute dbms_advisor.add_sqlwkld_ref('my_cache_task','my_cache_workload');
PL/SQL procedure successfully completed
6.执行任务
SQL> execute dbms_advisor.execute_task('my_cache_task');
PL/SQL procedure successfully completed
7.生成脚本
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('my_cache_task'),'ADVISOR_RESULTS', 'my_cache_workload_script.sql');
PL/SQL procedure successfully completed