[20150803]无法通过sql_id找到sql语句3.txt
--前一阵子，在做优化时遇到1个无法通过sql_id找到sql语句的情况：
http://blog.itpub.net/267265/viewspace-1749265/
--就是因为共享池太小，执行次数少，没到取样时间，已经从共享池清除。
--今天自己google，想想看看还有那种情况会出现呢？如果1条语句执行错误，会记录sql_id,当时查询v$sql视图应该也不能找到。
--自己直接那生产系统看看：
1.建立测试环境：
--看看生产系统的情况：
SYS@xxxxxx:1521/dbcn> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT COUNT (*), sql_id
FROM V$ACTIVE_SESSION_HISTORY
WHERE event = 'SQL*Net break/reset to client'
GROUP BY sql_id having count(*)>=9
ORDER BY 1 DESC;
COUNT(*) SQL_ID
---------- -------------
369 cm0qrtprj32qv
9 c21vhszr9gbdq
SYSTEM@xxxxxx:1521/dbcn> @dpc cm0qrtprj32qv ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID: cm0qrtprj32qv cannot be found
--无法发现sql语句。
SYS@192.168.99.106:1521/dbcn> select * from v$sql where sql_id='cm0qrtprj32qv';
no rows selected
--查询x$kglob视图：
$ cat shp4.sql
SELECT DECODE (kglhdadr,
kglhdpar, '父游标句柄地址',
'子游标句柄地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,20),
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20
FROM x$kglob
WHERE kglobt03 = '&1';
SYS@192.168.99.106:1521/dbcn> @sharepool/shp4 cm0qrtprj32qv
old 14: WHERE kglobt03 = '&1'
new 14: WHERE kglobt03 = 'cm0qrtprj32qv'
TEXT KGLHDADR KGLHDPAR SUBSTR(KGLNAOBJ,1,20) KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16 N20
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ----------
子游标句柄地址 000000130BCC8228 0000001309E41A28 SELECT PBD_FHGT,PBD_ 00 00 0 0 4091 4091 4091
子游标句柄地址 000000130570E978 0000001309E41A28 SELECT PBD_FHGT,PBD_ 00 00 0 0 4091 4091 4091
父游标句柄地址 0000001309E41A28 0000001309E41A28 SELECT PBD_FHGT,PBD_ 0000001307FF9158 00 4976 0 0 4976 4976
--
SYS@192.168.99.106:1521/dbcn> SELECT distinct kglnaobj c100 from x$kglob where kglobt03 = 'cm0qrtprj32qv';
C100
----------------------------------------------------------------------------------------------------
SELECT PBD_FHGT,PBD_FWGT,PBD_FITL,PBD_FUNL,PBD_FCHR,PBD_FPTC,PBD_FFCE,PBH_FHGT,PBH_FWGT,PBH_FITL,PBH
_FUNL,PBH_FCHR,PBH_FPTC,PBH_FFCE,PBL_FHGT,PBL_FWGT,PBL_FITL,PBL_FUNL,PBL_FCHR,PBL_FPTC,PBL_FFCE,PBT_
CMNT FROM SYSTEM.PBCATTBL WHERE PBT_OWNR = :"SYS_B_0" AND PBT_TNAM = :"SYS_B_1"
--这个是pb的程序，而这个表再我们的系统不存在。
SYS@192.168.99.106:1521/dbcn> @desc SYSTEM.PBCATTBL
ERROR:
ORA-04043: object SYSTEM.PBCATTBL does not exist
--导致每次程序访问是报错。
select trunc(sample_time,'hh24'),sql_id,count(*) from V$ACTIVE_SESSION_HISTORY where sql_id='cm0qrtprj32qv' and sample_time>=trunc(sysdate)+8/24
group by trunc(sample_time,'hh24'),sql_id
order by trunc(sample_time,'hh24') desc ,sql_id;
TRUNC(SAMPLE_TIME,' SQL_ID COUNT(*)
------------------- ------------- ----------
2015-08-03 10:00:00 cm0qrtprj32qv 28
2015-08-03 09:00:00 cm0qrtprj32qv 94
2015-08-03 08:00:00 cm0qrtprj32qv 60
--当前我执行的时间是10:23,看看9-10点，这个错误就浪费了94秒时间。可以发现执行实在太频繁。
--接着看看另外1条语句：
SYS@xxxxxx:1521/dbcn> SELECT distinct kglnaobj c100 from x$kglob where kglobt03 = 'c21vhszr9gbdq';
C100
----------------------------------------------------------------------------------------------------
Select csz2 From gy_xtcs where xtxh =:"SYS_B_0" And csmc =:"SYS_B_1"
SYS@xxxxxx:1521/dbcn> @desc portal_his.GY_XTCS
Name Null? Type
------ -------- ----------------------------
CSMC NOT NULL VARCHAR2(40)
CSZ VARCHAR2(200)
MRZ VARCHAR2(100)
BZ VARCHAR2(80)
--根本没有xtxh字段。
--真不知道这种大量无法执行的命令对性能能产生多大的影响。不过都可以通过x$kglob查询到。
