Performance Engineering Interview Questions #9

Following are the Performance Engineering Interview Questions Set #9.

Q. 41 What is Library Cache in Oracle DB?

Ans: The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code.

This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. The library cache also contains private SQL areas in a shared server architecture.

When a SQL statement is executed, the database reuses previously executed code. If a parsed representation of an SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as a hard parse or a library cache miss.

Q. 42 What is Data Dictionary Cache in Oracle DB?

Ans: The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.

Q. 43 What is Server Result Cache?

Ans: The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks. The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.

Q. 44 What do SQL Query Result Cache and PL/SQL Function Result Cache store?

Ans: The SQL query result cache stores the results of queries and query fragments and the PL/SQL function result cache stores function result sets.

Q. 45 What is a Reserved Pool in Oracle DB?

Ans: The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.