18、解析1_2（硬解析、共享sql、统计信息影响）
硬解析
清空shared pool：
SQL> alter system flush shared_pool;
System altered.
感知硬解析的存在
模拟一个硬解析，trace文件具体看递归SQL，以及需要访问的一些字典表
查询会话sid、serial#：
SQL> select sid from v$mystat where rownum = 1;
SID
----------
926
SQL> select sid,serial# from v$session where sid = 926;
SID SERIAL#
---------- ----------
926 547
跟踪会话，执行一个SQL：
SQL> exec dbms_monitor.session_trace_enable(926,547);
PL/SQL procedure successfully completed.
SQL> select count(*) from t2;
COUNT(*)
----------
86317
SQL> exec dbms_monitor.session_trace_disable(926,547);
PL/SQL procedure successfully completed.
查看跟踪文件：
[oracle@db11g ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@db11g trace]$ ll -t |more
total 2832
-rw-r----- 1 oracle oinstall 59004 Feb 7 01:03 orcl_mmon_1482.trc
-rw-r----- 1 oracle oinstall 6226 Feb 7 01:03 orcl_mmon_1482.trm
-rw-r----- 1 oracle oinstall 46869 Feb 7 01:02 orcl_dbrm_1466.trc
-rw-r----- 1 oracle oinstall 2448 Feb 7 01:02 orcl_dbrm_1466.trm
-rw-r----- 1 oracle oinstall 23680 Feb 7 00:56 orcl_ora_11804.trc -- 跟踪文件
-rw-r----- 1 oracle oinstall 357 Feb 7 00:56 orcl_ora_11804.trm
-rw-r----- 1 oracle oinstall 61820 Feb 7 00:53 orcl_ora_9550.trc
-rw-r----- 1 oracle oinstall 1099 Feb 7 00:53 orcl_ora_9550.trm
-rw-r----- 1 oracle oinstall 1148 Feb 6 22:24 orcl_m001_11309.trc
-rw-r----- 1 oracle oinstall 61 Feb 6 22:24 orcl_m001_11309.trm
-rw-r----- 1 oracle oinstall 67833 Feb 6 22:00 alert_orcl.log
-rw-r----- 1 oracle oinstall 2612 Feb 6 22:00 orcl_j003_11202.trc
-rw-r----- 1 oracle oinstall 118 Feb 6 22:00 orcl_j003_11202.trm
-rw-r----- 1 oracle oinstall 954 Feb 6 22:00 orcl_vkrm_11190.trc
-rw-r----- 1 oracle oinstall 60 Feb 6 22:00 orcl_vkrm_11190.trm
-rw-r----- 1 oracle oinstall 970 Feb 6 22:00 orcl_cjq0_1543.trc
-rw-r----- 1 oracle oinstall 70 Feb 6 22:00 orcl_cjq0_1543.trm
-rw-r----- 1 oracle oinstall 919 Feb 6 19:00 orcl_j000_10592.trc
-rw-r----- 1 oracle oinstall 69 Feb 6 19:00 orcl_j000_10592.trm
-rw-r----- 1 oracle oinstall 856 Feb 6 18:27 orcl_lgwr_1474.trc
-rw-r----- 1 oracle oinstall 59 Feb 6 18:27 orcl_lgwr_1474.trm
-rw-r----- 1 oracle oinstall 917 Feb 5 19:00 orcl_j000_5126.trc
-rw-r----- 1 oracle oinstall 77 Feb 5 19:00 orcl_j000_5126.trm
-rw-r----- 1 oracle oinstall 1146 Feb 5 09:21 orcl_m001_3218.trc
-rw-r----- 1 oracle oinstall 60 Feb 5 09:21 orcl_m001_3218.trm
-rw-r----- 1 oracle oinstall 915 Feb 5 08:51 orcl_j003_1551.trc
-rw-r----- 1 oracle oinstall 68 Feb 5 08:51 orcl_j003_1551.trm
-rw-r----- 1 oracle oinstall 2703 Feb 5 08:51 orcl_ora_1513.trc
-rw-r----- 1 oracle oinstall 169 Feb 5 08:51 orcl_ora_1513.trm
-rw-r----- 1 oracle oinstall 1347 Feb 5 08:51 orcl_p000_1515.trc
-rw-r----- 1 oracle oinstall 70 Feb 5 08:51 orcl_p000_1515.trm
-rw-r----- 1 oracle oinstall 1346 Feb 5 08:51 orcl_p001_1517.trc
-rw-r----- 1 oracle oinstall 70 Feb 5 08:51 orcl_p001_1517.trm
-rw-r----- 1 oracle oinstall 830 Feb 5 08:51 orcl_mman_1470.trc
-rw-r----- 1 oracle oinstall 59 Feb 5 08:51 orcl_mman_1470.trm
跟踪文件内容：
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11804.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: db11g
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 11804, image: oracle@db11g (TNS V1-V3)
*** 2017-02-07 00:56:39.426
*** SESSION ID:(926.547) 2017-02-07 00:56:39.426
*** CLIENT ID:() 2017-02-07 00:56:39.426
*** SERVICE NAME:(SYS$USERS) 2017-02-07 00:56:39.426
*** MODULE NAME:(SQL*Plus) 2017-02-07 00:56:39.426
*** ACTION NAME:() 2017-02-07 00:56:39.426
=====================
PARSING IN CURSOR #140124406532016 len=56 dep=0 uid=85 oct=47 lid=85 tim=1486400199425802 hv=3031425791 ad='be5b2460' sqlid='54thaa6uazsrz'
BEGIN dbms_monitor.session_trace_enable(926,547); END;
END OF STMT
EXEC #140124406532016:c=5999,e=7109,p=0,cr=57,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1486400199425787
WAIT #140124406532016: nam='SQL*Net message to client' ela= 17 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1486400199426750
*** 2017-02-07 00:56:44.813
WAIT #140124406532016: nam='SQL*Net message from client' ela= 5386567 driver id=1650815232 #bytes=1 p3=0 obj#=-1
tim=1486400204813396
CLOSE #140124406532016:c=0,e=37,dep=0,type=0,tim=1486400204813620
=====================
PARSING IN CURSOR #140124406530424 len=202 dep=1 uid=0 oct=3 lid=0 tim=1486400204814849 hv=3819099649 ad='be810bc8' sqlid='3nkd3g3ju5ph1' -- SQL语句的编号
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #140124406530424:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2853959010,tim=1486400204814838
FETCH #140124406530424:c=1000,e=269,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2853959010,tim=1486400204815239
CLOSE #140124406530424:c=0,e=19,dep=1,type=3,tim=1486400204815401
=====================
PARSING IN CURSOR #140124406510000 len=493 dep=1 uid=0 oct=3 lid=0 tim=1486400204815587 hv=2584065658 ad='be122018' sqlid='1gu8t96d0bdmu'
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
EXEC #140124406510000:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2035254952,tim=1486400204815576
FETCH #140124406510000:c=0,e=71,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2035254952,tim=1486400204815798
CLOSE #140124406510000:c=0,e=18,dep=1,type=3,tim=1486400204815878
=====================
......
......
......
=====================
PARSING IN CURSOR #140124406532016 len=23 dep=0 uid=85 oct=3 lid=85 tim=1486400204825809 hv=1020534364 ad='be80d258' sqlid='94dwfa8yd87kw'
select count(*) from t2
END OF STMT -- mis=1：表示在library cache里面没有找到要执行的SQL的SQL文本以及对应的执行计划
PARSE #140124406532016:c=9998,e=12087,p=1,cr=41,cu=0,mis=1,r=0,dep=0,og=1,plh=3321871023,tim=1486400204825802 -- 解析
EXEC #140124406532016:c=1000,e=315,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3321871023,tim=1486400204826344 -- 执行
-- 为了执行SQL：select count(*) from t2，产生的相关等待：
WAIT #140124406532016: nam='SQL*Net message to client' ela= 14 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1486400204826479
WAIT #140124406532016: nam='direct path read' ela= 220 file number=4 first dba=1859 block cnt=13 obj#=87632 tim=1486400204827533
WAIT #140124406532016: nam='direct path read' ela= 72 file number=4 first dba=1873 block cnt=15 obj#=87632 tim=1486400204827672
WAIT #140124406532016: nam='direct path read' ela= 214 file number=4 first dba=1889 block cnt=15 obj#=87632 tim=1486400204828157
WAIT #140124406532016: nam='direct path read' ela= 73 file number=4 first dba=1905 block cnt=15 obj#=87632 tim=1486400204828685
WAIT #140124406532016: nam='direct path read' ela= 373 file number=4 first dba=2049 block cnt=15 obj#=87632 tim=1486400204830428
WAIT #140124406532016: nam='direct path read' ela= 121 file number=4 first dba=2065 block cnt=15 obj#=87632 tim=1486400204831008
WAIT #140124406532016: nam='direct path read' ela= 31 file number=4 first dba=2081 block cnt=15 obj#=87632 tim=1486400204831194
WAIT #140124406532016: nam='direct path read' ela= 75 file number=4 first dba=2097 block cnt=15 obj#=87632 tim=1486400204831383
WAIT #140124406532016: nam='direct path read' ela= 30 file number=4 first dba=2178 block cnt=14 obj#=87632 tim=1486400204831540
......
......
......
WAIT #140124406532016: nam='direct path read' ela= 22 file number=4 first dba=3296 block cnt=13 obj#=87632 tim=1486400204841469
FETCH #140124406532016:c=14997,e=15099,p=1233,cr=1235,cu=0,mis=0,r=1,dep=0,og=1,plh=3321871023,tim=1486400204841631
STAT #140124406532016 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1235 pr=1233 pw=0 time=15117 us)'
STAT #140124406532016 id=2 cnt=86317 pid=1 pos=1 obj=87632 op='TABLE ACCESS FULL T2 (cr=1235 pr=1233 pw=0 time=263085 us cost=344 size=0 card=86317)' -- 执行计划（全表扫描）
WAIT #140124406532016: nam='SQL*Net message from client' ela= 388 driver id=1650815232 #bytes=1 p3=0 obj#=87632 tim=1486400204842101
FETCH #140124406532016:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3321871023,tim=1486400204842137
WAIT #140124406532016: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=87632 tim=1486400204842164
*** 2017-02-07 00:56:54.977
WAIT #140124406532016: nam='SQL*Net message from client' ela= 10135506 driver id=1650815232 #bytes=1 p3=0 obj#=87632 tim=1486400214977682
CLOSE #140124406532016:c=0,e=12,dep=0,type=0,tim=1486400214977766
=====================
PARSING IN CURSOR #140124406532016 len=57 dep=0 uid=85 oct=47 lid=85 tim=1486400214978686 hv=4102909378 ad='be82f2c8' sqlid='akapgavu8uvf2'
BEGIN dbms_monitor.session_trace_disable(926,547); END;
END OF STMTPARSE #140124406532016:c=1000,e=822,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1486400214978683
EXEC #140124406532016:c=0,e=221,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1486400214979048
有几个特点：
1、为了执行一个SQL，oracle又而外的执行了另外一堆的SQL，这些SQL访问的都是：tab$，字典表
2、硬解析的存在：mis = 1
3、有递归SQL：dep > 0，访问的是字典信息
dep（深度）
图示：
查询和解析相关的一些统计信息（会话sid=20）：
select b.NAME,a.VALUE from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and a.sid=20 and lower(b.name) like 'parse%';
花费 = CPU + 等待
parse count (total)：总共解析的次数
parse count (hard)：硬解析的次数
parse count (failures)：解析失败的次数
感知library cache
select * from v$librarycache;
SQL AREA：里面存放着SQL以及SQL的执行计划
GETS：表示在library cache里面找的次数
GETHITS：表示找的时候命中的次数
GETHITRATIO：命中率（软解析所占的百分比）
library cache里面的每一个SQL，在v$sql里面都有
查询SQL、SQL_ID
select * from v$sql where sql_text like 'select count(*) from t2%';
一个SQL文本对应一个SQL_ID
child_number：表示一个SQL对应的多个执行计划的child_number编号
要唯一确定一个执行计划，需要SQL_ID和child_number编号
v$sql里面还有sql执行的统计信息
library cache里面的执行计划在v$sql_plan里面
select * from v$sql_plan
查询使用U1用户执行的，产生全表扫描的SQL对应的执行计划：
select * from v$sql_plan where operation='TABLE ACCESS' and options='FULL' and object_owner='U1';
查询SQL对应的执行计划：
select * from table(dbms_xplan.display_cursor('4z7bfnaawkq32',1));
---- 4z7bfnaawkq32：SQL的SQL_ID
SQL> select count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 344 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 86309 | 344 (1)| 00:00:05 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
36 recursive calls -- 递归SQL的数量
0 db block gets
1258 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
查询session cursor的相关参数：
select b.NAME,a.VALUE from v$sesstat a,v$statname b
where a.STATISTIC#=b.STATISTIC# and a.sid=20
and lower(b.name) like 'session cursor%';
session cursor cache hits：表示发生软软解析的时候，它就会增加
session cursor cache count：表示在每个用户连接上的PGA里面，至少可以缓存私有SQL区与library cache建立的连接的个数
查询session cursor cache count参数：
SQL> show parameter session
sql共享
只进行一次硬解析
sql共享的条件：
1、编码不规范
2、常量SQL
3、绑定变量：绑定变量对SQL执行计划是智能的影响
字面量查询：
select count(*) from t2 where object_id = 17100;
select count(*) from t2 where object_id = 17101;
select * from v$sql where sql_text like 'select count(*) from t2 where object_id=1710%';
使用绑定变量查询：
SQL> variable x number;
exec :x := 18100;
SELECT object_name FROM t2 WHERE object_id =:x;
SQL>
PL/SQL procedure successfully completed.
SQL> variable x number;
exec :x := 18101;
SELECT object_name FROM t2 WHERE object_id =:x;
SQL>
PL/SQL procedure successfully completed.
select * from v$sql where sql_text like 'SELECT object_name FROM t2 WHERE object_id =:x%';
查询含有绑定变量的SQL：
select * from v$sql_bind_capture where sql_id='8a50x8p595rsy';
可能有时候sql没有实现共享，sql没有实现共享的原因：
select * from v$sql_shared_cursor where sql_id='8a50x8p595rsy';
auth_check_mismatch：N和Y没有匹配（可能就是说，同一个SQL有相同的SQL_ID，但是是不同的用户执行的，它们的执行计划就不一样；除此以外，可能还有一些别的情况导致执行计划的不一样）
DDL对v$sql的影响
SQL> show user
USER is "U1"
SQL> desc t2
Name Null? Type
------------------------------------------------------------ -------- -------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> alter table t2 modify object_type varchar(30);
Table altered.
select * from v$sql a
where sql_text like 'SELECT object_name FROM t2 WHERE object_id =:x%'
and a.PARSING_SCHEMA_NAME='U1';
OBJECT_STATUS：INVALID_UNAUTH（无效的）
LAST_LOAD_TIME：2017-02-07/07:00:42
然后再次执行SQL
SQL> variable x number;
exec :x := 17100;
SELECT object_name FROM t2 WHERE object_id =:x;
SQL>
PL/SQL procedure successfully completed.
select * from v$sql a
where sql_text like 'SELECT object_name FROM t2 WHERE object_id =:x%'
and a.PARSING_SCHEMA_NAME='U1';
OBJECT_STATUS：VALID（有效的）
LAST_LOAD_TIME：2017-02-07/07:10:34（重新做了一次LOAD，一次LOAD就是一次硬解析）
收集统计信息对v$sql的影响
这里有一个参数：no_invalidate（类型是布尔值类型）
no_invalidate = true（1）的时候，再重新收集了统计信息之后，不会让SQL的执行计划失效
no_invalidate = false（0）的时候，再重新收集了统计信息之后，SQL的执行计划会马上失效
SQL> exec dbms_stats.gather_table_stats('U1','T2',no_invalidate=>false);
PL/SQL procedure successfully completed.
select * from v$sql a
where sql_text like 'SELECT object_name FROM t2 WHERE object_id =:x%'
and a.PARSING_SCHEMA_NAME='U1';
OBJECT_STATUS 变成 INVALID_UNAUTH（无效的）了
SQL> variable x number;
exec :x := 17100;
SELECT object_name FROM t2 WHERE object_id =:x;
SQL>
PL/SQL procedure successfully completed.
select * from v$sql a
where sql_text
like 'SELECT object_name FROM t2 WHERE object_id =:x%'
and a.PARSING_SCHEMA_NAME='U1';
OBJECT_STATUS 又成 VALID（有效的）的了
x$ksmsp ksmsp视图
ksmsp里面的每一行代表着shared_pool里面的每一个chunk（大块）
创建一个名为：e$ksmsp的临时表：
SQL> create global temporary table e$ksmsp on commit preserve rows as
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom,count(ksmchcom) chunk,
decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
sum(ksmchsiz) sumfrom x$ksmsp group by ksmchcom,ksmchcls) a
where 1=0
group by a.ksmchcom;
把当前shared pool里面的信息插入到e$ksmsp临时表里：
SQL> insert into e$ksmsp
select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom,count(ksmchcom) chunk,
decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
sum(ksmchsiz) sum
from x$ksmsp group by ksmchcom,ksmchcls) a
group by a.ksmchcom;
然后执行一个脚本：
[root@db11g ~]# ./test.sh u1/u1 1000 -- 用户u1登录，执行1000次
脚本内容：
查询SQL：
select * from v$sql where sql_text like 'select count(*)%t2%where%';
再执行一个脚本：
SQL> select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk-b.chunk) c_diff,(a.sum-b.sum) s_diff
from
(select a.ksmchcom,
sum(a.chunk) chunk,
sum(a.recr ) recr,
sum(a.freeabl) freeabl,
sum(a.sum) sum
from (select ksmchcom,count(ksmchcom) chunk,
decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
sum(ksmchsiz) sum
from x$ksmsp
group by ksmchcom,ksmchcls) a
group by a.ksmchcom) a,e$ksmsp b
where a.ksmchcom=b.ksmchcom and (a.chunk-b.chunk) <> 0;
C_DIFF：表示chunk的增减数量
S_DIFF：表示free的增减数量
这里表现出在硬解析里面，可怕的事情是：free的空间在减小，而free的chunk还在增加
隐含参数
1、shared_pool_reserved_size
SQL> show parameter shared_pool_reserved_size
SQL> show parameter shared
shared_pool_reserved_size 默认是 shared_pool_size的5%的大小
2、_shared_pool_reserved_min_alloc
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%';
v$shared_pool_reserved：
select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size from v$shared_pool_reserved;
