[20180912]PLSLQ與繫結變數.txt

[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;