『ORACLE』SPM(下)-baseline实验(11g)
来源:互联网 发布:香蕉网络tv免费频道 编辑:程序博客网 时间:2024/06/03 22:02
查询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
- 『ORACLE』SPM(下)-baseline实验(11g)
- SPM(SQL Plan baseline)(11g)
- 『ORACLE』SPM(上)(11g)
- 『ORACLE』 约束实验(11g)
- Oracle 11g 学习笔记 Using Baseline
- 执行计划的管理---baseline(11g)
- 执行计划的管理---baseline(11g)
- Oracle 11g AWR 系列三:AWR baseline 的管理
- oracle 11g sql plan baseline(1)基本使用
- Oracle性能优化——SQL基线(SQL baseline)的载入与进化(11g中引入的基线)
- 『ORACLE』 Linux和oracle用户下的常用命令(11g)
- 创建oracle用户(11g 下)
- 11g new feature 之 SPM
- oracle 11g logminer实验
- oracle 11g logminer实验
- Oracle SPM(SQL Plan Management)介绍及演示SQL
- Oracle 11g AWR 系列四:AWR baseline template 的管理
- oracle 11g BaseLine(基线)指定application中不可修改sql的执行计划
- CentOS7下LVM的简单创建
- C++ const成员函数
- Python学习笔记(四)—— Tuple
- 文件搜索命令
- android MemeoryFile和Parcel操作文件描述符fd
- 『ORACLE』SPM(下)-baseline实验(11g)
- session跨域处理方法
- 为网页标题添加图标
- PCblayouty高速培训学习
- Mysql双机热备实现
- cuda学习(linux公社下载地址: http://linux.linuxidc.com/东西比较多)
- 详细介绍Android开发集成微信支付(仅客户端版)
- 多线程synchronized用法
- javascript正则表达式