如何创建SQL 调优集（—） 从共享池加载

1）创建一个新的SQL 调优集，新创建的调优集是空的，后面需要把SQL STATEMENT加载进来

exec dbms_sqltune.create_sqlset(sqlset_name=>'vodka_set');

2）从共享池加载SQL 调优集

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME=''VODKA'' and force_matching_signature<>0 and BUFFER_GETS>1000 and EXECUTIONS>100 and command_type<>2',
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  1,
                                                  NULL,
                                                  'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'vodka_set',
                           populate_cursor => cur);

END;
/
DBMS_SQLTUNE.SELECT_CURSOR_CACHE的第一个参数指定了要加载SQL的属性：SQL是由VODKA 用户产生的，签名不包含0的，BUFFER_GETS(总）大于1000的，执行次数（总）大于100的，命令类型不为INSERT的SQL都添加到调优集VODKA_SET里。

有两个参数值得特别说明：

1）SELECT_CURSOR_CACHE的第一个参数是basic_filter ，它可以取的值有：

  sql_id                   VARCHAR(13),
  force_matching_signature NUMBER,
  sql_text                 CLOB,
  object_list              sql_objects,
  bind_data                RAW(2000),
  parsing_schema_name      VARCHAR2(30),
  module                   VARCHAR2(48),
  action                   VARCHAR2(32),
  elapsed_time             NUMBER,
  cpu_time                 NUMBER,
  buffer_gets              NUMBER,
  disk_reads               NUMBER,
  direct_writes            NUMBER,
  rows_processed           NUMBER,
  fetches                  NUMBER,
  executions               NUMBER,
  end_of_fetch_count       NUMBER,
  optimizer_cost           NUMBER,
  optimizer_env            RAW(1000),
  priority                 NUMBER,
  command_type             NUMBER,
  first_load_time          VARCHAR2(19),
  stat_period              NUMBER,
  active_stat_period       NUMBER,
  other                    CLOB,
  plan_hash_value          NUMBER,
  sql_plan                 sql_plan_table_type,
  bind_list                sql_binds

2）SELECT_CURSOR_CACHE的最后一个参数是attribute_list

• BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

• TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

• ALL - return all attributes

• Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-708257/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-708257/