使用ALTER SYSTEM运行OS命令

Comments

2 responses to “使用ALTER SYSTEM运行OS命令”

1. 

   maclean

   August 29, 2010

   Package Body and specification can be compiled in different mode in Oracle 10G
   PURPOSE
   ——-

   Explanation of enhancement in Oracle 10G related to Native Compilation.

   Explanation
   ———

   Existing Behaviour in 9i
   ==========================
   When you compile package body and specification in different settings(NATIVE,INTERPRETED)
   you get PLS-723: package body cannot be compiled native if its spec was
   compiled non-native
   OR
   PLS-724: package body cannot be compiled to bytecode if its spec was native

   Description:
   Before 10G, if a package specification is compiled for native execution,
   the corresponding body should also be compiled using the same setting.

   Enhancement in 10G
   =====================
   Package body and spec are no longer required to be compiled in the same
   setting. For example, package body can be compiled native when package
   spec was compiled interpreted, or vice versa.

   The following example shows the problem:

   create or replace package native_check
   is
   procedure native_proc1;
   end;
   /

   alter session set plsql_compiler_flags=’NATIVE’;
   /* Following settings as per requirement */
   alter system set plsql_native_library_dir=’—–‘;
   alter system set plsql_native_c_compiler=’—–‘;
   alter system set plsql_native_linker=’—–‘;
   alter system set plsql_native_make_file_name=’—–‘;
   alter system set plsql_native_make_utility==’—–‘;
   alter system set plsql_native_library_subdir_count=0;

   create or replace package body native_check
   is
   procedure native_proc1 is
   begin
   dbms_output.put_line(‘Its native_proc1 procedure inside the native_check package’);
   end;
   end;
   /

   Error in 9i
   ============
   LINE/COL ERROR
   ——– —————————————————————–
   0/0 PL/SQL: Compilation unit analysis terminated
   1/1 PLS-00723: package body cannot be compiled native if its spec was
   compiled non-native

   In 10G
   ========
   Gets compiled successfully.

   Reply
2. 

   maclean

   August 29, 2010

   PL/SQL Native Compilation in Oracle9i

   PURPOSE
   ——-

   The purpose of this bulletin is to describe native compilation of
   PL/SQL program units, including configuration and limitations.

   SCOPE & APPLICATION
   ——————-

   This bulletin is intended to consolidate information available from
   a number of other sources as well as correct some errors in current
   versions of the official Oracle9i documentation. This bulletin also
   covers some rather subtle points that are not discussed explicitly
   in the documentation. It does not provide a tutorial on PL/SQL in
   general; an understanding of the PL/SQL language is assumed.

   NATIVE COMPILATION OF PL/SQL STORED PROGRAM UNITS IN ORACLE9i
   ————————————————————-

   Introduction
   ————

   In versions prior to Oracle9i, all PL/SQL stored program units are
   compiled to P-code. The resulting P-code is stored in the database
   and interpreted at runtime. While this results in a very portable
   solution, it does have some performance consequences; interpreted
   languages are usually slower than natively compiled languages.

   As a performance enhancement, Oracle9i provides the capability to
   natively compile PL/SQL code into shared libraries. These shared
   libraries are then loaded and executed whenever the corresponding
   PL/SQL program unit is invoked at runtime.

   Native compilation of PL/SQL, once it has been properly configured,
   is transparent to both the user compiling a PL/SQL program unit and
   to the end user invoking that same PL/SQL program unit.

   Oracle9i generates C code corresponding to the PL/SQL code and then
   compiles and links the resulting C code into shared libraries using
   the compiler and linker for that platform. Therefore, a C compiler
   must be installed on the same host for native compilation to work.

   The next section discusses the initialization parameters that are
   used to configure PL/SQL native compilation. Subsequent sections
   discuss other considerations when using native compilation.

   Initialization Parameters Related to PL/SQL Native Compilation
   ————————————————————–

   plsql_compiler_flags

   Determines whether PL/SQL code is compiled native or interpreted
   and determines whether debug information is included.

   This parameter is settable at both the system and session levels.

   Possible values are: ‘INTERPRETED’ – compile in interpreted mode
   ‘NATIVE’ – compile in native mode
   ‘DEBUG’ – include debug information
   ‘NON_DEBUG’ – no debug information

   Defaults are: ‘INTERPRETED’,’NON_DEBUG’

   Examples:

   alter session set plsql_compiler_flags=’NATIVE’;
   alter session set plsql_compiler_flags=’INTERPRETED’,’DEBUG’;

   Disallowed combinations:

   ‘NATIVE’,’INTERPRETED’ – for obvious reasons
   ‘DEBUG’,’NON_DEBUG’ – for obvious reasons
   ‘NATIVE’,’DEBUG’ – debugging of natively compiled PL/SQL
   is not yet supported

   This parameter affects ONLY those PL/SQL program units explicitly
   compiled AFTER the parameter is set.

   plsql_native_make_file_name

   Determines the full path to the makefile used to create the shared
   libraries that contain the natively compiled PL/SQL code.

   This parameter is settable only at the system level.

   This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.

   This parameter should be set to the full pathname of the makefile
   provided with Oracle9i for this purpose. The provided makefile is
   called ‘spnc_makefile.mk’ and resides in $ORACLE_HOME/plsql.

   Since $ORACLE_HOME can vary from one installation to another, this
   parameter does not have any default; it must be set explicitly.

   Examples:

   alter system set plsql_native_make_file_name=
   ‘/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk’;

   plsql_native_make_utility

   Determines the full path to the make utility used to process the
   makefile specified via plsql_native_make_file_name.

   This parameter is settable only at the system level.

   This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.

   This parameter should be set to the full pathname of the ‘make’
   utility on your machine. If the PATH environment variable is set
   appropriately, you may find that providing just the name of the
   make utility will work; however, it is highly recommended that
   the full pathname be provided to avoid potential problems.

   Since the location of the make utility can vary from one machine
   to another, this parameter does not have any default; it must be
   set explicitly.

   Examples:

   alter system set plsql_native_make_utility=’/usr/ccs/bin/make’;

   plsql_native_library_dir

   Determines the directory name used to store the shared libraries
   that contain the natively compiled PL/SQL code.

   This parameter is settable only at the system level.

   This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.

   This parameter should be set to the full pathname of the directory
   where the shared libraries are to be stored. The ‘oracle’ user must
   have write permissions on this directory or native compilation will
   fail. Other users’ access to the directory should be restricted; in
   particular, no user other than ‘oracle’ or ‘root’ should be allowed
   to delete files from this directory.

   The directory is assumed to have already been created. Oracle will
   not create the directory for you.

   There is no default value; this parameter must be set explicitly.

   Examples:

   alter system set plsql_native_library_dir=
   ‘/usr/app/oracle/product/9.0.1/plsql_libs’;

   plsql_native_c_compiler

   This parameter should NOT be set with an alter system command,
   in init.ora or the stored parameter file (SPFILE). The provided
   makefile should be modified to include the appropriate default
   location for the C compiler.

   Example (spnc_makefile.mk):

   # Specify C Compiler
   #
   CC=/opt/SUNWspro/bin/cc

   plsql_native_linker

   This parameter should NOT be set with an alter system command,
   in init.ora or in the stored parameter file (SPFILE). The provided
   makefile should be modified to include the appropriate default
   location for the linker.

   Examples (spnc_makefile.mk):

   # Specify Linker
   #
   LD=/usr/ccs/bin/ld

   plsql_native_library_subdir_count

   Determines the number of subdirectories to be created in the directory
   specified by the plsql_native_library_dir parameter.

   Rationale:

   Performance of certain file operations can be adversely affected if
   the number of files in a directory grows very large. In cases where
   many PL/SQL program units are natively compiled, each corresponding
   to a shared library in plsql_native_library_dir, the number of files
   in this directory can become unacceptably large.

   By setting this parameter to some value N, you are asking that Oracle
   distribute the generated shared libraries across the N subdirectories
   of plsql_native_library_dir. The names of these subdirectories are of
   the form ‘dn’ where n is the zero-based directory number.

   Setting this parameter is recommended if the number of PL/SQL program
   units to be natively compiled exceeds 10,000.

   This parameter is settable only at the system level.

   This parameter is optional; the default value is 0 (zero) so all shared
   libraries are by default stored directly in plsql_native_library_dir.

   A Typical PL/SQL Native Compilation Session
   ——————————————-

   $ sqlplus system/manager

   SQL> alter system set plsql_native_make_file_name=
   ‘/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk’;

   System altered.

   SQL> alter system set plsql_native_make_utility=’/usr/ccs/bin/make’;

   System altered.

   SQL> alter system set plsql_native_library_dir=
   ‘/usr/app/oracle/product/9.0.1/plsql_libs’;

   System altered.

   NOTE: The above parameters could also be set via the init.ora file. And if
   a stored parameter file (SPFILE) exists for this instance, the above
   commands will also persist these changes to the SPFILE, eliminating
   the need to reaccomplish these steps when the instance is restarted.

   NOTE: If there is some doubt about the current settings for the parameters,
   they can be viewed via the ‘show parameters plsql’ command in sqlplus
   or by querying the v$parameter table directly.

   SQL> connect scott/tiger

   Connected.

   SQL> alter session set plsql_compiler_flags=’NATIVE’;

   Session altered.

   SQL> create or replace procedure nativetest as
   eno number;
   begin
   select empno into eno from emp
   where rownum select object_name, param_name, param_value
   from user_stored_settings
   where object_name = ‘NATIVETEST’;

   OBJECT_NAME PARAM_NAME PARAM_VALUE
   ———– ——————— —————-
   NATIVETEST plsql_compiler_flags NATIVE,NON_DEBUG

   To run the natively compiled procedure:

   SQL> execute nativetest;

   PL/SQL procedure successfully completed.

   On Solaris, one can verify that the procedure is being executed natively by
   using ‘pmap’ to view the libraries loaded by the oracle executable.

   Find the name of the shared library corresponding to the PL/SQL procedure:

   SQL> !ls /usr/app/oracle/product/9.0.1/plsql_libs

   NATIVETEST__SCOTT__0.so

   Note that the shared library name (in this case, on Solaris) is constructed
   from the procedure, function or package name and the schema name.

   Find the process id of the current sqlplus process:

   SQL> !ps

   PID TTY TIME CMD
   593 pts/10 0:00 ksh
   11695 pts/10 0:00 sqlplus

   Find the process id of the oracle process: (Note: this assumes you connected
   via bequeath; otherwise the oracle process won’t be a child of sqlplus)

   SQL> !ps -ef | grep 11695

   langor 12080 11695 0 14:28:56 pts/10 0:00 grep 11695
   langor 11698 11695 0 14:13:22 ? 0:01 oracleO9iProd …
   langor 11695 593 0 14:13:22 pts/10 0:00 sqlplus scott/tiger

   Use pmap to see all of the memory segments (including shared libraries) that
   are currently attached to this process. You will see NATIVETEST__SCOTT__0.so
   listed among the libraries displayed by the pmap utility.

   SQL> !/usr/proc/bin/pmap 11698 | more

   /* partial listing of output */

   EF44C000 8K read/write/exec [ anon ]
   EF460000 56K read/exec /usr/lib/libresolv.so.2
   EF47C000 8K read/write/exec /usr/lib/libresolv.so.2
   EF47E000 16K read/write/exec [ anon ]
   EF490000 40K read/exec /usr/ucblib/libucb.so.1
   EF4A8000 8K read/write/exec /usr/ucblib/libucb.so.1
   EF4B0000 8K read/exec /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so
   EF4C0000 8K read/write/exec /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so

   NOTE: It may be possible to perform a similar test on other platforms besides
   Solaris, but the tools required will likely differ. See your platform
   documentation for how to do this on your operating system platform.

   Dependencies, Invalidation and Revalidation
   ——————————————-

   Dependencies between database objects are handled in the same manner as in
   previous Oracle versions. If an object upon which some natively compiled
   PL/SQL program unit depends changes, the PL/SQL module is invalidated. The
   next time the same PL/SQL program unit is executed, an attempt is made to
   revalidate (recompile) the module. Any time a module is recompiled as part
   of revalidation, it is compiled using its stored setting (i.e. the setting
   in force the last time the module was explicitly compiled and appearing in
   the USER/ALL/DBA_STORED_SETTINGS data dictionary views).

   The stored settings are ONLY used when recompiling as part of revalidation.
   Any time a PL/SQL module is explicitly compiled via ‘create or replace’ or
   ‘alter … compile’, the current setting for the session is used.

   Although natively compiled PL/SQL program units are obviously dependent on
   their implementation shared libraries, the database is unable to track such
   dependencies (since the libraries, which reside on the file system, can be
   manipulated directly via OS commands). For example, attempting to execute a
   PL/SQL program unit whose shared library has been deleted results in error.

   $ rm /usr/app/oracle/product/9.0.1/plsql_libs/NATIVETEST__SCOTT__0.so

   $ sqlplus scott/tiger

   SQL> execute nativetest;

   *
   ERROR at line 1:
   ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): ld.so.1:
   oracleO9iProd: fatal: /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so:
   open failed: No such file or directory

   Since the removal of the library is undetectable to Oracle until the module
   is executed, the program unit is not marked INVALID; therefore, there is no
   implicit revalidation (and recompilation). To recreate the missing library,
   you must explicitly recompile it or recreate it from source.

   SQL> alter session set plsql_compiler_flags=’NATIVE’;

   Session altered.

   SQL> alter procedure nativetest compile;

   Procedure altered.

   SQL> exit

   $ ls /usr/app/oracle/product/9.0.1/plsql_libs

   NATIVETEST__SCOTT__0.so

   On a related note, shared libararies corresponding to a PL/SQL program unit
   are not automatically deleted when the program unit is dropped; these files
   must be manually removed when they are no longer needed.

   Reply