[20210621]plsql_code_type=native.txt

[20210621]plsql_code_type=native.txt

--//如果資料庫系統設定開啟plsql_code_type=native,據說能提高效能不好,預設是INTERPRETED.
--//實際上PLSQL的程式碼執行效率並不高,特別是一些CPU密集型的操作,僅僅設定訪問資料庫存取的語句效率高一些.
--//如果我們編譯包使用plsql_code_type=native,會出現什麼情況呢?



1.環境:

SCOTT@book> @ 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

SCOTT@book> show parameter plsql
NAME                     TYPE          VALUE
------------------------ ------------- -------------
plsql_ccflags            string
plsql_code_type          string        INTERPRETED
plsql_debug              boolean       FALSE
plsql_optimize_level     integer       2
plsql_v2_compatibility   boolean       FALSE
plsql_warnings           string        DISABLE:ALL

SCOTT@book> alter session set plsql_code_type=native ;
Session altered.

$ ls -l /dev/shm/
total 0

create or replace PROCEDURE testp2 (
  P_ORG_NUM IN number,
  p_result OUT varchar2,
  p_seq OUT number
) AS
BEGIN
  p_seq := P_ORG_NUM; p_result:='';
END;
/

create or replace package testp1
as
   procedure foo;
end;
/

create or replace package body testp1 as
  procedure foo is begin null; end;
end;
/


$ ls -l /dev/shm/
total 8
-rwx------ 1 oracle oinstall 432 2021-06-22 09:18:48 PESLD_book_1c38004_00dcf27d00000000
-rwx------ 1 oracle oinstall 528 2021-06-22 09:17:33 PESLD_book_1c38004_d87eef7c00000000

--//PESLD表示什麼呢? PESLD – Plsql code Execution Shared object manager native code Loader
--//這樣編譯的過程和包在/dev/shm產生了檔案PESLD_book_1c38004_00dcf27d00000000,PESLD_book_1c38004_00dcf27d00000000.

2.測試：
--//退出重新登入，執行：
SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       295       1571 23979                    DEDICATED 23980       21        192 alter system kill session '295,1571' immediate;

SCOTT@book> exec testp1.foo()
PL/SQL procedure successfully completed.

$ strace -f -e open -p 23980
Process 23980 attached - interrupt to quit
open("/dev/shm/PESLD_book_1c38004_00dcf27d00000000", O_RDONLY|O_NOFOLLOW) = 8

--//你可以發現會出現呼叫open /dev/shm/PESLD_book_1c38004_00dcf27d00000000
--//現在刪除檔案。

$ rm /dev/shm/PESLD_book_1c38004_00dcf27d00000000
/bin/rm: remove regular file `/dev/shm/PESLD_book_1c38004_00dcf27d00000000'? y

SCOTT@book> exec testp1.foo()
PL/SQL procedure successfully completed.
--//OK。沒有問題，因為時間控制程式碼已經ok，你可以發現第2次執行沒有呼叫open。
--//開啟新的session執行：

SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        30       3837 23998                    DEDICATED 23999       26        234 alter system kill session '30,3837' immediate;

SCOTT@book>  exec testp1.foo()
BEGIN testp1.foo(); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [pesldl01_Get_Object: shm_open failed: errno 2 errmsg No such fil], [/PESLD_book_1c38004_00dcf27d00000000], [], [], [], [], [], [], [], [], [], []

--//重新編譯。
SCOTT@book> alter session set plsql_code_type=native ;
Session altered.

SCOTT@book> ALTER PACKAGE SCOTT.TESTP1 COMPILE;
Package altered.

$ ls -l /dev/shm/
total 8
-rwx------ 1 oracle oinstall 432 2021-06-22 09:38:24 PESLD_book_1c38004_00dcf27d00000000
-rwx------ 1 oracle oinstall 528 2021-06-22 09:17:33 PESLD_book_1c38004_d87eef7c00000000

SCOTT@book>  exec testp1.foo()
PL/SQL procedure successfully completed.

3.連結作者說的情況我給繼續測試：
variable v_result varchar2;
variable v_seq  number ;

SCOTT@book> exec testp2(1,:v_result,:v_seq);
PL/SQL procedure successfully completed.

--//我估計應該不是引數plsql_code_type=native引起的情況。
--//開啟新會話重新編譯。
SCOTT@book> ALTER PACKAGE SCOTT.TESTP1 COMPILE;
Package altered.

$ rm -rf /dev/shm/PESLD_book_1c38004_*

variable v_result varchar2;
variable v_seq  number ;

SCOTT@book> exec testp2(1,:v_result,:v_seq);
BEGIN testp2(1,:v_result,:v_seq); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [pesldl01_Get_Object: shm_open failed: errno 2 errmsg No such fil], [/PESLD_book_1c38004_d87eef7c00000000], [], [], [], [], [], [], [], [], [], []

--//重新建立：
create or replace PROCEDURE testp2 (
  P_ORG_NUM IN number,
  p_result OUT varchar2,
  p_seq OUT number
) AS
BEGIN
  p_seq := P_ORG_NUM; p_result:='';
END;
/

SCOTT@book> exec testp2(1,:v_result,:v_seq);
PL/SQL procedure successfully completed.

4.如果安裝如上編譯的包，發生異常關閉呢，繼續測試？
$ ls -l /dev/shm/
total 4
-rwx------ 1 oracle oinstall 432 2021-06-22 09:53:47 PESLD_book_1c58004_f8ae607c00000000

SYS@book> shutdown abort ;
ORACLE instance shut down.

$ ls -l /dev/shm/
total 4
-rwx------ 1 oracle oinstall 432 2021-06-22 09:53:47 PESLD_book_1c58004_f8ae607c00000000

--//檔案還在。
SYS@book> startup
ORACLE instance started.

Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SCOTT@book> alter session set plsql_code_type=native ;
Session altered.

SCOTT@book> exec testp1.foo()
PL/SQL procedure successfully completed.

$ ls -l /dev/shm/
total 8
-rwx------ 1 oracle oinstall 432 2021-06-22 09:53:47 PESLD_book_1c58004_f8ae607c00000000
-rwx------ 1 oracle oinstall 432 2021-06-22 09:56:43 PESLD_book_1c78004_c841197c00000000

--//建立新的檔案。