《Pro Oracle SQL》CHAPTER2-2.3 The Library Cache

The Library Cache? 库缓存???? (page 54)
??? The first thing that must happen to every SQL statement you execute is that it must be parsed and
loaded into the library cache.? The library cache, as mentioned earlier, is the area within the shared pool
that holds previously parsed statements.? Parsing involves verifying the statement syntax, validating
objects being referred to, and confirming user privileges on the objects.? If those checks are passed, the
next step is for Oracle to see if that same statement has been executed previously.? If it has, then Oracle
will grab the stored information from the previous parse and reuse it.? This type of parse is called a soft
parse.? If the statement hasn’t previously been executed, then Oracle will do all the work to develop the
execution plan for the current statement and then store it in the cache for later reuse.? This type of parse is called a hard parse.
???? 你所执行的每条SQL语句，首先发生的事是它被解析然后加载到库缓存。如前所述，库缓存是在共享池中的一块区域，存放之前解析的语句。解析涉及到验证语句的语法，校验引用的的参数，以及确认用户对对象的权限。如果这些检查都通过了，下一步Oracle就会看是否之前执行过相同的语句。如果是，Oracle将从之前解析的存储信息中取出并重用它。这种类型的解析称之为“软解析”。如果语句之前没有执行过，Oracle将会对当前语句开发执行计划然后存储它必备重用。这种类型的解析称之为“硬解析”。
??? Hard parses require Oracle to do a lot more work than soft parses.? Every time a hard parse occurs,
Oracle must gather all the information it needs before it can actually execute the statement.? In order to
get the information it needs, Oracle will execute a bunch of queries against the data dictionary.? The
easiest way to see what Oracle does during a hard parse is to turn on extended SQL tracing, execute a
statement and then review the trace data.? Extended SQL tracing captures every action that occurs so not only will you see the statement you execute, but you’ll see every statement that Oracle must execute as well.? Since I haven’t covered the details of how tracing works and how to read a trace file, I’m not going to show the detailed trace data.? Instead, Table 2-1 provides the list of system tables that were queried during a hard parse of select * from employees where department_id = 60.
??? 硬解析相比软解析需要Oracle做更多的工作。每次硬解析发生，在Oracle实际执行语句之前它必须先收集所有必要的信息。为了获得它需要的信息，Oracle将针对数据字典进行一大串查询。查看Oracle在硬解析期间做了什么的最简单的方式是开启扩展的SQL tracing(追踪),执行一语句然后检查追踪的数据。扩展的SQL tracing捕获发生的每个动作，这样你不仅能看到你所执行的语句，还将看到Oracle必须执行的每条语句。由于我还没有深入讲解如何追踪以及如何解读追踪文件，我不打算展示详细的追踪文件。(而是直接给出结果)，表2-1提供在硬解析select * from employees where department_id = 60期间Oracle所查询的一列系统表。

Table 2-1. System Objects Queried During Hard Parse
Tables??????????? #Queries?? ?? Purpose???????????????????????????????????????????????????????????????????????? 目的
access$?????????? 1??????? ? ? ?? Permissions used by a dependent object against its parent依赖对象是否有其父对象的授权
ccol$?????????????????? 10???????? Constraint column-specific data????? ? ????????????????????????? 约束列数据
cdef$?????????????????? 3????????? Constraint-specific definition data???? ? ??????????????????????? 约束定义数据
col$???????????????????? 1????????? Table column-specific data???????????????????????????????????????? 表列数据
dependency$? ? ? ? 1????????? Interobject dependencies?????????????????????????????????????????? 对象依赖
hist_head$?????????? 12????????? Histogram header data ? ?????????????????????????????????????????? 柱状图头信息
histgrm$?????????????? 3????????? Histogram specifications???????????????????????????????????????????? 柱状图规范
icol$????????????? ? ? ?? 6????????? Index columns???????????????????????????????????????????????????????? 索引列
ind$, ind_stats$???? 1?????????? Indexes, index statistics????????????????????????????????????????? 索引列，索引统计
obj$????????????????????? 8????????? Objects???????????????????????????????????????????????????????????? ? ? 对象
objauth$??????????????? 2????????? Table authorizations????????????????????????????????????????????????? 表授权
seg$????????????????????? 7????????? Mapping of all database segments????????????????????????????? 映射所有数据库段
syn$????????????????????? 1????????? Synonyms???????????????????????????????????????????????????????????? 同义词
tab$, tab_stats$???? 1????????? Tables, table statistics???????????????????????????????????????????? 表，表统计
user$???????????????????? 2???????? User definitions????????????????????????????????????????????????????? 用户定义

??? In total, there were 59 queries against system objects executed during the hard parse.? The soft parse
of the same statement did not execute any queries against the system objects since all that work was
done during the initial hard parse.? The elapsed time for the hard parse was .060374 seconds while the
elapsed time for the soft was was .000095 seconds.? So, as you can see, soft parsing is a much more
desirable alternative to hard parsing.? Don’t ever fool yourself into thinking hard parsing doesn’t matter.
As you can see, it does!
??? 总体而言，在执行硬解析期间对系统对象执行了59次查询。相同语句的软解析不需要执行任何针对系统对象的查询，因为所有的工作都有最初的硬解析做了。硬解析耗时.060374秒而同比软解析耗时.000095 秒。这样你就能看出，软解析相比硬解析是非常可取的方案。不要自以为是的认为硬解析没什么。你将看到，它确实(很影响性能)。