11gOCP 1z0-052 :2013-09-14 repeated parsing activity

11gOCP 1z0-052 :2013-09-14 repeated parsing activity

 

 

正确答案：D

 

SQL语句的执行过程:

  1、客户端输入sql语句update t_gyj set name='gyj1' where id=1;

 

  2、sql语句通过网络到达实例

 

  3、server process接收sql语句，以下是执行一个sql的大致步骤（获取数据(buffer cache-->PGA)-->返数据给用户这一步只有SELECT语句中有，其它DML中没有）

 

sql-->sql_hash-->shared pool解析(消耗CPU)-->生成执行计划-->执行sql(buffer计算数据:逻辑读或物理读I/O)-->获取数据(buffer cache-->PGA)-->返数据给用户

  

当这条sql发出来时，Oracle要做些什么呢？我们先来看一张图（来自DSI405的Library cache）

 

 

 

在整个SGA中最复杂的就是sharedpool，而shared pool中最复杂就是library cache，这里对它的机制不做详细讨论，简单讲讲sql在里面的运作流程。

 

首先sql（update t_gyjset name='gyj1' where id=1）的每个字符当然包括空格转化成ASCII码后，再拿这一堆ASCII码通过HASH函数生成一个sql_hash值，Oracle拿着这个sql_hash值去描扫HASH Buckets（看上面的图，这个幅画的不太好，只画了0号的HASH BUCKETS）,假如刚好sql_hash值＝0，那么Oracle就延着0号HASHBuckets去搜索ObjectHandle链,在这个Object Handle上存有sql的文本，如果和我们的update t_gyj set name='gyj1' where id=1一模一样对上，那就说明这条sql已被缓存在共享池了，这个过程就是软解析。当然再往下我就不说了，再说下去很复杂了父子游标，最后执行计划是被存放在堆6中。

 

　　　好，那么如果通过上面的方式在Object Handle链没搜索到这条sql的文本，那说明sql不在共享池中，这个时侯就要做硬解析（过程大要做语法，语义，权限，查询视图展开、划分小的查询块、sql等价转换、代价估算、最后生成执行计划），这个代价会有点高，如果有大量的硬解析那会消耗CPU和占用共享池。

 

　　 如果这条SQL直接能在共享池的Library cache中能找到，那就是软解析。

     如果想对软解析进一步优化，那就做软软解析（具体这里不深入了）

 

所有这些PARSE（解析）都与共享池有关。

 共享池（SHARED POOL）

A、库缓存

B、数据字典缓存

C、PL/SQL区

D、SQL查询和PL/SQL函数结果缓存

 

 

答案A，不正确，与访问路径无关（是不是走索引、全表扫描、物化视图等）

答案B，不正确，SGA不能自动控制下面五个池了（BUFFER CACHE/SHARED POOL/JAVA POOL/LARGEPOOL/STREAMS POOL），因类自动共享内存关闭了。

 

答案C，不正解，PGA与软软解析相关，但软软解只是缓存一个游标，占用内存非常……

答案E，不正解，解析不发生在BUFFER CAHCE中，BUFFERCACHE中一般是修改和访问数据―――执行sql(buffer计算数据:逻辑读或物理读I/O)-->获取数据(buffer cache-->PGA)。

 

 共享池参考官方文档的CONCEPTS:

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.

Figure 14-9 Shared Pool

 

 

 

Description of "Figure 14-9 Shared Pool"

 

This section includes the following topics:

Library Cache

Data Dictionary Cache

Server Result Cache

Reserved Pool

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 andexecution 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.

The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements.

The database performs the following steps:

Checks the shared pool to see if a shared SQL area exists for a syntactically and semantically identical statement:

If an identical statement exists, then the database uses the shared SQL area for the execution of the subsequent new instances of the statement, thereby reducing memory consumption.

If an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool. A statement with the same syntax but different semantics uses a child cursor.

In either case, the private SQL area for the user points to the shared SQL area that contains the statement and execution plan.

Allocates a private SQL area on behalf of the session

The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

Figure 14-10 shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their own PGAs. In a shared server, this copy is in the UGA, which in the large pool or in the shared pool when no large pool exists.

Figure 14-10 Private SQL Areas and Shared SQL Area