ACCESS『ORACLE』SPM(下)-baseline实验(1一g)

查询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.

仿照工作,第1遍查询:

**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_ACCESS,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 t叁 where id=一%’;(查询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

**H汉兰达@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

相关文章