baseline学习1
1.有关的两个参数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> create table t1 nologging as select object_id,created from dba_objects;
Table created.
SQL> alter session set nls_data_format='yyyy/mm/dd hh24:mi:ss';
alter session set nls_data_format='yyyy/mm/dd hh24:mi:ss'
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> exec :object_id :=72443
PL/SQL procedure successfully completed.
SQL> exec :created :='2010/09/05 06:16:41'
PL/SQL procedure successfully completed.
SQL>
SQL> set autot traceonly exp stat
SQL> select * from t1 where object_id=:object_id and created=:created;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 176 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 176 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
40 recursive calls
52 db block gets
289 consistent gets
181 physical reads
5848 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 176 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 176 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
257 consistent gets
41 physical reads
0 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> exec dbms_stats.gather_table_stats(user,'t1');
PL/SQL procedure successfully completed.
SQL> l
1* select * from t1 where object_id=:object_id and created=:created
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
184 consistent gets
0 physical reads
0 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> create index idx_object_id on t1(object_id);
Index created.
SQL> create index idx_created on t1(created);
Index created.
SQL> l
1* create index idx_created on t1(created)
SQL> select * from t1 where object_id=:object_id and created=:created;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
26 recursive calls
35 db block gets
219 consistent gets
2 physical reads
4056 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
5 db block gets
185 consistent gets
0 physical reads
96 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> l
1* select * from t1 where object_id=:object_id and created=:created
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 46 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:OBJECT_ID) AND "CREATED"=:CREATED)
Note
-----
- SQL plan baseline "SQL_PLAN_975htc2jdg7m8dbd90e8e" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
183 consistent gets
0 physical reads
0 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--将全表扫描的PLAN_NAME=‘SQL_PLAN_975htc2jdg7m8dbd90e8e’ DISABLE
SQL> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => 'SQL_93961960a2d79e68',
6 plan_name => 'SQL_PLAN_975htc2jdg7m8dbd90e8e',
7 attribute_name => 'ENABLED',
8 attribute_value => 'NO');
9 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
10 END;
11 /
Plans Altered: 1
PL/SQL procedure successfully completed.
可以看到已经走回正常的执行计划
1* select * from t1 where object_id=:object_id and created=:created
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2724989281
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"=:CREATED)
2 - access("OBJECT_ID"=TO_NUMBER(:OBJECT_ID))
删除baseline
SQL> declare
2 n PLS_INTEGER;
3 begin
4 n :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (
5 sql_handle=>'SQL_93961960a2d79e68',
6 plan_name=> NULL)
7 ;
8 dbms_output.put_line(n);
9 end;
10 /
PL/SQL procedure successfully completed.
相对应的文档语法
DROP_SQL_PLAN_BASELINE Function
This function drops a single plan, or all plans associated with a SQL statement.
Syntax
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2
这里有个问题，删除完baseline后，在同一个会话中，再次执行同样的主句，并不会被系统重新自动捕获；
1* select * from t1 where object_id=:object_id and created=:created
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2724989281
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"=:CREATED)
2 - access("OBJECT_ID"=TO_NUMBER(:OBJECT_ID))
而需要重新新开一个会话执行
1* select * from t1 where object_id=:object_id and created=:created
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2724989281
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"=:CREATED)
2 - access("OBJECT_ID"=TO_NUMBER(:OBJECT_ID))
Note
-----
- SQL plan baseline "SQL_PLAN_975htc2jdg7m890704d19" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
406 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed