Oracle 19C 使用用户定义的工作量填充 SQL调整集

例如，输入以下命令：

DROP TABLE user_workload;
CREATE TABLE user_workload
(
  username             varchar2(128),   /* User who executes statement */
  module               varchar2(64),        /* Application module name */
  action               varchar2(64),        /* Application action name */
  elapsed_time         number,               /* Elapsed time for query */
  cpu_time             number,                   /* CPU time for query */
  buffer_gets          number,        /* Buffer gets consumed by query */
  disk_reads           number,         /* Disk reads consumed by query */
  rows_processed       number,         /* # of rows processed by query */
  executions           number,            /* # of times query executed */
  optimizer_cost       number,             /* Optimizer cost for query */
  priority             number,             /* User-priority (1,2 or 3) */
  last_execution_date  date,               /* Last time query executed */
  stat_period          number,          /* Window exec time in seconds */
  sql_text             clob                           /* Full SQL Text */
);
复制

例如，执行以下语句：

-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   t.week_ending_day, p.prod_subcategory, 
          SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
 FROM     sales s, times t, products p 
 WHERE    s.time_id = t.time_id
 AND      s.prod_id = p.prod_id 
 AND      s.prod_id > 10 
 AND      s.prod_id < 50
 GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id')
/
 
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM     sales s , times t
  WHERE    s.time_id = t.time_id
  AND      s.time_id BETWEEN TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
  AND      TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
  GROUP BY t.calendar_month_desc')
/
 
-- order by
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   c.country_id, c.cust_city, c.cust_last_name
  FROM     customers c
  WHERE    c.country_id IN (52790, 52789)
  ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;
复制

例如，执行以下PL / SQL程序：

DECLARE
  sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                     'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                     sysdate, 0, 0, null, 0, null, null)
    FROM USER_WORKLOAD;
  DBMS_SQLTUNE.LOAD_SQLSET('MY_STS_WORKLOAD', sqlset_cur);
END;
/
复制