往往实际工作中并不像上面样，乃至市面上的书也获得你想要的执行计划。
前几天 测试人员照常发来的测试ＡＷＲ过来。AWR的HOST CPU IDLE 空闲率为50%。虽然测试最高要求是使用率为80%，也就是说空闲率不能低于20%。每个查询语句不能高于200毫秒。观察该AWR等待事件都耗在DB CPU上。也就是说大部分DB TIME 数据库服务时间都在DB CPU处理工作中。那我们直接去看SQL状态部分，看ORDER BY CPU TIME列表。
发现所有的SQL都700毫秒，而且都涉及到一张表。CUST_INFO！
该表有6千万数据，最近分析时间11月20日。
拿个简单的SQL 去看看是怎么回事！
SELECT *
FROM CUST_INFO
WHERE PARE_ID=:1
AND PARE_TYPE=:2
AND CUST_NAME=:3;
拿到像这样的，工作环境只有SQLPLUS。这些：1:2:3都是绑定变量，如果你直接使用值替代方式，估计执行计划是不一样的。
一 首先我们要拿到绑定变量的值。
从V$SQL_BING_DATA 一般拿不到。只有开启SQL_TRACE方可拿到
ALTER SYSTEM SET EVENTS
'SQL_TRACE [SQL:&&SQL_ID] WAIT=TRUE,BIND=TURE,PLAN_STAT=ALL_EXECUTIONS,LEVEL=12';
##让测试人员在跑一次
ALTER SYSTEM SET EVENTS 'SQL_TRACE [SQL:&&SQL_ID] OFF';
##获取跟踪文件
1 SHOW PARAMETER USER_DUMP_DEST
ls -ltr .../*.trc
最前几个其中就是我们的跟踪文件
2 从视图回话获得
select TRACEFILE
FROM V$PROCESS
WHERE ADDR IN
(
SELECT PADDR
FROM V$SESSION
WHERE SID=&SESSID
);
二 其次 我们要把它放进SQLPLUS里执行一遍
2.1
SQL> SET LINESIZE 1000
SQL> SET AUTOT ON
var v_paertype varchar2(50);
var v_paperid varchar2(50);
var v_custname varchar2(50);
exec :v_paertype:='A';
....
select /*+ gather_plan_statistics */
*
FROM custinfo c
where c.PARE_TYPE=:v_papertype
and c.PARE_ID=:v_paperid
and c.CUST_NAME=:v_custname;
这里我们就模拟了绑定变量的SQL执行。虽然我们增加了收集的提示，会形成不同的SQLID。不过如果环境没有变的话，基本上生产的执行计划是一样的。var 是给变量定义类型，一般有VARCHAR2和NUMBER日期也用VARCHAR2，EXEC ：给变量赋值。
2.2 我们可以直接通过SQLPLUS显示该执行计划 SET AUTOT ON；
2.3 也可以通过查V$SQLAREA WEHRE SQL_TEXT LIKE '%gather%' 来获得SQLID，然后去V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL；获得其详细计划。
你说如果既然知道原来的SQLID不直接去PLAN获得呢？其实也可以，省得那么绕。从V$SQL获得。如下
SELECT SQL_ID,
PLAN_HASH_VALUE,
CHILD_NUMBER,
EXECUTIONS,
DISK_READS,
BUFFER_GETS,
CPU_TIME,
ELAPSED_TIME,
LAST_ACTIVE_TIME
FROM V$SQL S
WHERE SQL_ID='&SQLID'
ORDER BY S.LAST_ACTIVE_TIME DESC;
通过这个脚本可以看到该SQL最近使用哪个执行计划，并且你自己通过计算ELAPSED_TIME/EXECUTIONS 每次消耗时间与AWR是否符合。
2.4 我们这样对比下，JDBC执行和我们人工模拟执行，时间是否相差太大，执行计划是否一样？
三查看相关对象信息
问题，我们的表统计信息如何？表上有几个索引，上面条件字段上是否有索引，索引是否需要重建？字段列上的数据分布如何？ 那么我们经历下面几个查询获得相关信息
--1 获得表大小
select segment_name,bytes/1024/1024 as size_mb from dba_segments where segment_name=upper('&tablename');
--2 获得表行数
select count(*) from table_name;
--3 获得表统计信息
select owner,table_name,num_rows,avg_row_len,last_analyzed
from dba_tables where table_name=upper('&tablename');
--4 获得表列信息
select column_name,data_type,num_distinct,low_value,high_value,density,num_nulls,histogram,last_analyzed
from dba_tab_cols where owner='PLPM' AND TABLE_NAME='&TABLE_NAME';
--5 获得表索引
SELECT INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,DESCEND
FROM DBA_IND_COLUMNS
WHERE INDEX_OWNER='PLPM' AND TABLE_OWNER='PLPM'
AND TABLE_NAME='&TABLE_NAME';
--6 哪些列被索引
SELECT CUSTNAME,COUNT(*)
FROM PLPM.CUST_INFO
GROUP BY CUSTNAME
HAVING COUNT(*) >1
ORDER BY 2 DESC;
--8 收集统计信息和直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS
(ownname => 'SCOTT',tabname => 'DEPT',estimate_percent => 100,
method_opt => 'for all columns size auto',
degree => 4, cascade=>TRUE);
发现 CUSTNAME 有8万是一样的，而测试人员就是使用该值。导致走CUSTNAME索引超慢，因为索引除了FAST扫描外都是单块读。读一次回表一次。所以第8步走全面收集该表重要字段的直方图。
疑问 为什么好好的PARE_ID索引不走，却走CUSTNAME索引呢？那么我们需要启动事件来调查
10046 SQL执行过程
10053 优化器执行过程 为什么选择这个执行计划
很显然我们选择10053事件。下面是启动事件的方法。
四 启动事件调研
ALTER SYSTEM|SESSION SET EVENTS
'[EVENTNUMBER|IMMEDIATE] TRAE NAME EVENTANME
[FOREVER] [,LEVEL LEVELNUMBER];'
--开启10053跟踪
ALTER SESSION SET EVENTS='10053 trace name context forever,level 12';
--执行需要跟踪的SQL语句：可以我们人工模拟方式
--查找trace文件路径
SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';
关闭10053事件：
ALTER SESSION SET EVENTS '10053 trace name context off';
调查其他回话：
SQL> EXEC SYS.DBMS_SYSTEM.SET_EV (22, 339, 10053,1, '');
其他回话执行
--关闭跟踪
SQL> EXEC SYS.DBMS_SYSTEM.SET_EV (22, 339, 10053,0, '');
11GR以上可以直接调研SQLID
SQL> execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'5s4ny8pxtdkyf', p_child_number=>0, p_component=>'Optimizer', p_file_id=>'SQL_TRACE_10053');
从TRC文件分析得知PARE_ID索引有问题，至于什么问题呢？我也不知道，不过我做了如下操作，
1 重建PARE_ID
2 刷新SQL，把该SQL语句从内存中刷出，以便生成新的执行计划。
select address,hash_value,executions,parse_calls from v$sql where sql_id='01wa3qb3vd2qm';
sys：
exec dbms_shared_pool.purge('0000001F2F280AF8,3350629075', 'c') -- address,hash_value,
最后问题解决了，从700毫秒降低到50毫秒！
第五 使用更简便的MONITOR工具
###查看超过4秒的慢查询
declare
v_report clob;
begin
select count(distinct sql_id) into n_sql_num from v\$sql_monitor where module='JDBC Thin Client';
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => i.sql_id,type => 'TEXT', report_level => 'ALL') AS report into v_Report FROM dual;
DBMS_OUTPUT.put_line(dbms_lob.substr(v_Report,32767));
end;
/
##sql_monitor 收集信息每秒刷新一次，SQL执行完保留一分钟
##提高提示 /*+ monitor */ /*+ no_monitor */
##一般监控300行的执行计划 隐含参数控制数量_sqlmon_max_planlines
##一般监控超过4秒的SQL _sqlmon_threshold控制时间
对于低于4秒的只有通过提示获得 monitor
var v_paertype varchar2(50);
var v_paperid varchar2(50);
var v_custname varchar2(50);
exec :v_paertype:='A';
....
select /*+ monitor */
*
FROM custinfo c
where c.PARE_TYPE=:v_papertype
and c.PARE_ID=:v_paperid
and c.CUST_NAME=:v_custname;
对于过了很久，只能从历史表获得信息
SELECT * FROM DBA_HIST_SQL_PLAN；
SELECT * FROM DBA_HIST_SQL_SUMMARY；
SELECT * FROM DBA_HIST_SQLBIND；
SELECT * FROM DBA_HIST_SQLSTAT；
select * from dba_hist_active_sess_history；
select * from DBA_HIST_SNAPSHOT
dba_hist_active_sess_history:
TM_DELTA_TIME | 在其时间间隔（以微秒计）TM_DELTA_CPU_TIME和 TM_DELTA_DB_TIME被累积 |
TM_DELTA_CPU_TIME | 此会话在过去TM_DELTA_TIME几微秒内花在CPU上的时间 |
TM_DELTA_DB_TIME | 此会话在过去TM_DELTA_TIME几微秒内在数据库调用中花费的时间 |
DELTA_TIME | 自上次采样或创建会话以来的时间间隔（以微秒为单位），累计接下来的五个统计信息 |
DELTA_READ_IO_REQUESTS | 此会话在过去DELTA_TIME几微秒内发出的读取I O请求数 |
DELTA_WRITE_IO_REQUESTS | 此会话在过去DELTA_TIME几微秒内发出的写入I O请求数 |
DELTA_READ_IO_BYTES | 此会话在过去DELTA_TIME几微秒内读取的I O字节数 |
DELTA_WRITE_IO_BYTES | 此会话在过去DELTA_TIME几微秒内写入的I / O字节数 |
DELTA_INTERCONNECT_IO_BYTES | 在过去DELTA_TIME几微秒内通过I / O互连发送的I / O字节数 |
TM_DELTA_TIME和DELTA_TIME属于间隔时间内统计其间的TM_DELTA_CPU_TIME等。
一个正确的执行计划该如何让其稳定，而不被其它情况给打乱呢？
下期请看
ODBA 技能五 执行计划稳定
