Tips1_PL/SQL

1.

You issue an UPDATE command, and then a COMMIT.the following actions has happpend:

①. Data blocks are copied from the datafiles into the database buffer cache.

②. The log buffer is updated.

③. Data blocks in the cache are updated.

④. The log buffer is flushed to disk.

⑤. The changed blocks are flushed to disk.

Remember that everything happens in memory first, and that your server process writes to the log buffer before writing the database buffer cache. Then on commit, the log buffer is flushed to disk. The data blocks may not be written for some time.

■Instance Parameters for PL/SQL

2.PLSQL_CODE_TYPE, default INTERPRETED

When PL/SQL is compiled, the resulting code stored depends on the PLSQL_CODE_TYPE setting.

・INTERPRETED means that PL/SQL code is compiled only down to byte code.Then when invoked, this is interpreted.

・NATIVE, in conjunction with the parameter that follows, instructs Oracle to precompile the PL/SQL code to C code, and then to compile and link this with the C compiler and linker provided by your

server’s operating system. This may improve performance.

・・

Native compiled code is stored in an operating system file, 

Interpreted code is stored in the data dictionary.

・・※

In most cases, the default settings for all these instance parameters will be suitable.

On a production system, you may wish to consider setting the PLSQL_CODE_TYPE to NATIVE; this is in fact recommended for Oracle E-Business Suite running in a 10g database.

3.PLSQL_OPTIMIZE_LEVEL, default 2

・Level 2, the highest, enables the use of all the compiler’s optimization features; 

this gives the best run-time performancebut perhaps increases compilation time. 

・Lower settings (0 or 1) will result in quicker compilation but possibly slightly degraded execution. 

・・※If the production　system is doing a large amount of compilation (anonymous PL/SQL, for　example), then it might be necessary to change this.