Chapter 8, Compiling and Tuning to Improve Performance

Question No.	Answer	Explanation
1	a and b	Interpreted compilation mode is preferred when a program unit is in development stage and involves SQL statement processing.
2	c and d	The Real Native compilation method removes the dependency on C compiler to generate DLL for the program unit. Instead, the native DLLs are stored in the database dictionary itself. As the DLLs are stored in the dictionary, they can be a part of the normal backup and recovery.
3	b	The PLSQL_OPTIMIZE_LEVEL value set to 3 strictly inlines all the subprograms at high priority.
4	b and c	The PLSQL_CODE_TYPE value specified during recompilation of a program overrides the current system or session settings. Its default value is INTERPRETED and it must be updated in the spfile instance after the database upgrade process. In real native mode, the libraries are stored in the SYSTEM tablespace.
5	a and c	Usage of BULK COLLECT can pull multi-row data from the database in a single attempt, thus reducing context switches. PLS_INTEGER is a preferred data type in arithmetic calculations.
6	d	Inlining of subprograms is only supported at optimization level greater than one.
7	b and c	The F_ADD local function would be called inline because its call has been marked inline using PRAGMA INLINE. It might also be considered for inlining as the PLSQL_OPTIMIZE_LEVEL value is 2.
8	b	The DLLs generated from the Real Native compilation are stored in the SYSTEM tablespace.
9	a and d	The NOT NULL data types add overhead to check every assignment for nullity. L_SUM must be declared as L_SUM NATURAL; Usage of appropriate data types to avoid implicit typecasting improves performance. L_ID must be declared as NUMBER.
10	a, c, and d	PRAGMA INLINE works for PLSQL_OPTIMIZE_LEVEL values 2 and 3. At level 1, the Oracle optimizer doesn't consider any subprogram for inlining. At level 3, all subprograms are strictly called for inline. However, inlining of a subprogram can be set off by specifying PRAGMA INLINE (<Function name>,'NO').