[20210621]plsql_code_type=native.txt

--//如果数据库系统设置打开plsql_code_type=native,据说能提高性能不好,缺省是INTERPRETED.
--//实际上PLSQL的代码执行效率并不高,特别是一些CPU密集型的操作,仅仅设置访问数据库存取的语句效率高一些.
--//如果我们编译包使用plsql_code_type=native,会出现什么情况呢?

http://orasql.org/2021/05/29/another-interesting-troubleshooting-case/

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

--//建立新的文件。