052 试题 14 - shared pool
题目
14. These are points that describe the contents of different memory components:
1: Descriptive information or metadata about schema objects that are queried by using SQL statements
2: The run-time area for data manipulation language (DML) or data definition language (DDL) statements
3: Results of SQL queries and PL/SQL functions
4: Executable forms of SQL cursors, PL/SQL programs, and Java classes
5: The information necessary to reconstruct changes made to the database by a transaction
Which of these will be stored in the Shared Pool if the necessary configurations are done?
A.1 and 2
B.2 and 5
C.1, 3, and 4
D.3, 4, and 5
E.1, 2, 3, and 4
参考答案 C
解析
题目意思问的是，哪些存储在shared pool中。
shared pool包括库cache和数据字典cache。SQL语句的语法、语义检查是在shared pool中处理的。所以1 是正确的。
查询的result cache是缓存在shared pool中的。所以3 是正确的
库cache中的共享sql区域中存放着PLSQL program unit等等。所以4 正确。
参考文档：
https://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT1226
Shared Pool
The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.
The shared pool is divided into several subcomponents, the most important of which are shown in Figure 14-9.
Description of "Figure 14-9 Shared Pool"
This section includes the following topics:
Library Cache
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. In a shared server architecture, the library cache also contains private SQL areas.
When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a 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.
Shared SQL Areas
The database represents each SQL statement that it runs in the following SQL areas:
-
Shared SQL area
The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement.
-
Private SQL area
Each session issuing a SQL statement has a private SQL area in its PGA (see "Private SQL Area"). Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.