『ORACLE』SPM（下）-baseline实验（11g）
查询baseline信息：
SQL>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines;
no rows selected
SQL>alter system set optimizer_capture_sql_plan_baselines=true；（开启自动捕获）
System altered.
HR@PROD>create table t3 (id int);
Table created.
HR@PROD>insert into t3 select level from dual connect by level<=10000;
10000 rows created.
HR@PROD>commit;
Commit complete.
模拟业务，第一次查询：
HR@PROD>select count(*) from t3 where id=1;
COUNT(*)
----------
1
SQL>select sql_id,sql_text from v$sql where sql_text like 'select count(*) from t3 where id=1';（查询shared pool中的关于这条sql的信息）
SQL_ID SQL_TEXT
----------------- ----------------------------------------------
82w9knc509xx4 select count(*) from t3 where id=1
SQL>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines;
no rows selected
模拟业务，第二次查询：
HR@PROD>select count(*) from t3 where id=1;
COUNT(*)
----------
1
SQL>select sql_id,sql_text from v$sql where sql_text like'select count(*) from t3 where id=1';（查询shared pool中的关于这条sql的信息）
SQL_ID SQL_TEXT
----------------- ------------------------------------
82w9knc509xx4 select count(*) from t3 where id=1
82w9knc509xx4 select count(*) from t3 where id=1
SQL>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like '%select count(*) from t3 where id=1%';（查询baseline中的信息）
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION CREATED LAST_MODIF
-------------------- --------------- --------------- -------------- ---------- ---------- ----------
LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- --- --- ---
SQL_f83b4cd9c6676181 select count(*) SQL_PLAN_ghfucv AUTO-CAPTURE 11.2.0.4.0 23-APR-17 23-APR-17
from t3 where 736fsc114fae16c 09.06.44.0 09.06.44.0
id=1 00000 PM 00000 PM
23-APR-17 YES YES NO
09.06.44.0
00000 PM
HR@PROD>set autotrace traceonly（查看执行计划）
HR@PROD>select count(*) from t3 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 463314188
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T3 | 1 | 13 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_ghfucv736fsc114fae16c" used for this statement
Statistics
----------------------------------------------------------
53 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
