1) 自动捕获
可以在session和system级别设置optimizer_capture_sql_plan_baselines参数来决定是否开启自动捕获baseline。默认为false，不开启。
|
test@DLSP>alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
test@DLSP>select count(*) from test;
COUNT(*) ---------- 50000
test@DLSP>alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
test@DLSP>select sql_handle, plan_name, origin, accepted,fixed 2 from dba_sql_plan_baselines 3 where sql_text like '%count(*)%';
SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED -------------------- -------------------------------- --------------- ------ ---- SQL_5cc64969ffe36537 SQL_PLAN_5tjk9d7zy6t9r6b581ab9 AUTO-CAPTURE YES NO
test@DLSP>select count(*) from test;
COUNT(*) ---------- 50000
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID 7b2twsn8vgfsc, child number 1 ------------------------------------- select count(*) from test
Plan hash value: 1950795681
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 51 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 50000 | 51 (2)| 00:00:01 | -------------------------------------------------------------------
Note ----- - SQL plan baseline SQL_PLAN_5tjk9d7zy6t9r6b581ab9 used for this statement |
开启自动捕获后，执行过的SQL已经生成了baseline，执行SQL后，Note部分的显示也说明了这一点。
2) 从SQL调优集加载
SQL调优集是符合一系列约束条件的特定SQL的集合，DBA可以指定创建SQL调优集的条件，如符合逻辑读大于多少的、执行次数大于多少的SQL被创建进SQL调优集，如下的代码创建了一个SQL调优集，条件是：由test这个schema解析、逻辑读大于1000，执行次数大于100的SQL。可以通过DBA_SQLSET视图来查看创建的SQL调优集的相关信息，如此SQL调优集包含多少SQL。可以通过视图dba_sqlset_statements来查看SQL调优集包含的具体的SQL信息。
|
sys@DLSP>DECLARE 2 cur DBMS_SQLTUNE.SQLSET_CURSOR; 3 BEGIN 4 DBMS_SQLTUNE.CREATE_SQLSET('test'); 5 OPEN cur FOR 6 SELECT VALUE(P) 7 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME=''test'' and force_matching_signature<>0 and BUFFER_GETS>1000 and EXECUTIONS>100', 8 NULL, 9 NULL, 10 NULL, 11 NULL, 12 1, 13 NULL, 14 'ALL')) P; 15 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'test', 16 populate_cursor => cur); 17 END; 18 /
PL/SQL procedure successfully completed.
sys@BUSS>SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='test';
NAME OWNER CREATED STATEMENT_COUNT ---------- ---------- ------------------- --------------- test SYS 2014-07-29 09:57:25 341
sys@BUSS>select sql_id, substr(sql_text,1, 30) text 2 from dba_sqlset_statements 3 where sqlset_name = 'test' 4 order by sql_id;
SQL_ID TEXT -------------------------- ------------------------------------------- 00c1xhp5bncs3 select id as id, 省略
|
调优集创建完成后，我们就可以通过dbms_spm.load_plans_from_sqlset函数来加载创建的SQL调优集，调优集中的SQL都会被创建baseline。
|
sys@BUSS> variable a number; sys@BUSS> exec :a := dbms_spm.load_plans_from_sqlset(sqlset_name => 'test');
PL/SQL procedure successfully completed.
sys@BUSS>select count(*) from dba_sql_plan_baselines;
COUNT(*) ---------- 330 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-1241478/ ，如需转载，请注明出处，否则将追究法律责任。
