如何建立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