[20180912]PLSLQ与绑定变量.txt

--//链接提到的性能问题,链接http://www.itpub.net/thread-2105061-1-1.html
--//我自己也没有很好的定位问题,我自己的定位对方的存储有点问题,单块读10ms,不管怎样感觉有点慢.当然里面一些sql也有问题.
--//顺便问一下同行,当然聊另外的问题,可以发现里面的一些sql语句特别长,实际上如下:

sql_id=cj6s4qvnpdrsf

begin
UPDATE bed_rec
   SET ward_code = '205', room_no = '02', dept_code = '132',
       bed_approved_type = '', bed_sex_type = '', bed_class = '',
       bed_status = '1', bed_type = '', bed_label = '01'
 WHERE local_bed_code = '05201'
   AND hospital_no    = '46600265-7';
.....

UPDATE bed_rec
   SET ward_code = '2298', room_no = '01', dept_code = '1351',
       bed_approved_type = '', bed_sex_type = '', bed_class = '',
       bed_status = '1', bed_type = '', bed_label = '018'
 WHERE local_bed_code = '613018'
   AND hospital_no    = ' 46600265-7';
end;

--//我想测试看看如果cursor_sharing=force的情况,这样的语句会替换吗?
--//自己测试看看.

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table deptx as select * from dept where 1=2;
Table created.

2.建立测试脚本:
--//cat ba.sql
begin
Insert into DEPTx (DEPTNO, DNAME, LOC) Values (10, 'ACCOUNTING', 'NEW YORK');
Insert into DEPTx (DEPTNO, DNAME, LOC) Values (20, 'RESEARCH', 'DALLAS');
Insert into DEPTx (DEPTNO, DNAME, LOC) Values (30, 'SALES', 'CHICAGO');
Insert into DEPTx (DEPTNO, DNAME, LOC) Values (40, 'OPERATIONS', 'DALLAS');
COMMIT;
end;
/
    
SCOTT@test01p> alter system  set cursor_sharing=force scope=memory;
System altered.    

--//退出再执行:
SCOTT@test01p> show parameter cursor_sharing
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ----------
cursor_sharing                       string               FORCE

SCOTT@test01p> @ d:\temp\ba.sql
PL/SQL procedure successfully completed.

SCOTT@test01p> @spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
       152         11 3376:6408                DEDICATED 7932                      22          6 alter system kill session '152,11' immediate;

SCOTT@test01p> select sql_text  c80 from V$OPEN_CURSOR where sid=152 and upper(sql_text) like '%DEPT%';
C80
--------------------------------------------------------------------------------
INSERT INTO DEPTX (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH
INSERT INTO DEPTX (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTI
INSERT INTO DEPTX (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIO
INSERT INTO DEPTX (DEPTNO, DNAME, LOC) VALUES (30, 'SALES',

SCOTT@test01p> select sql_text c120 from v$sql where sql_id='9w348rtwgr3v1';
C120
------------------------------------------------------------------------------------------------------------------------
begin Insert into DEPTx (DEPTNO, DNAME, LOC) Values (10, 'ACCOUNTING', 'NEW YORK'); Insert into DEPTx (DEPTNO, DNAME, LO
C) Values (20, 'RESEARCH', 'DALLAS'); Insert into DEPTx (DEPTNO, DNAME, LOC) Values (30, 'SALES', 'CHICAGO'); Insert int
o DEPTx (DEPTNO, DNAME, LOC) Values (40, 'OPERATIONS', 'DALLAS'); COMMIT; end;

--//可以发现里面的值没有替换,包括执行的sql语句.都无法通过参数cursor_sharing=force的情况下,使用绑定变量.
--//看来开发不能这样写代码...^_^.

SYS@test> @ sharepool/shp4 9w348rtwgr3v1 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16     N20 KGLNAHSH   KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ------- ---------- ------------- ----------
子游标句柄地址 000007FF26A880A8 000007FF26A84A18 begin Insert into DEPTx (DEPTNO, DNAME,           0          0          0 000007FF25528548 000007FF23084038       4032      12400       3399     19831   19831 2029752161 9w348rtwgr3v1          0
父游标句柄地址 000007FF26A84A18 000007FF26A84A18 begin Insert into DEPTx (DEPTNO, DNAME,           0          0          0 000007FF25548768 00                     4072          0          0      4072    4072 2029752161 9w348rtwgr3v1      65535

--//shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, '父游标句柄地址',
               '子游标句柄地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
       KGLHDLMD,
       KGLHDPMD,
       kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
       kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
       kglnahsh,
       kglobt03 ,
       kglobt09  
  FROM x$kglob
 WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;