New Result Caching Functionality
Oracle Database 11g introduces several new caching features that let you utilize memory more
efficiently, which results in faster query processing. There are actually two types of caching
features: the server result cache that caches SQL query results as well as PL//SQL function
results in the SGA and the OCI consistent client cache (client cache) that lets you cache query
results on the client. The client cache is especially useful when you’re using large-scale stateless
web applications driven by frameworks such as PHP. We’ll review the two types of caching
features in the following sections, starting with the server result cache.
Oracle 11G引入了新的缓存机制，可以更加有效的去利用内存，它可以使查询处理变得更快，主要有两种类型的
缓存机制：
1、服务器端的缓存：在SGA(系统全局区)的SQL查询缓存和PL/SQL函数缓存
2、客户端的缓存：将允许在客户端进行结果集的缓存，当使用一些框架语言，例如PHP，进行大数据量的
交互，客户端的缓存变得非常有效。下面是两种类型的缓存方面的信息介绍，首先介绍下服务器端的缓存
Using the Server Result Cache to Enhance Performanc
The server result cache is a new concept of Oracle Database 11g that enables the database to
cache SQL query and PL/SQL function results in memory. The database serves the results for
frequently executed SQL queries and PL/SQL functions straight from the cache instead of
reexecuting the actual query or function all over again. You can imagine the dramatic savings
in resource usage (for example, IO) as well as the improved response times when you use cached
results. Both logical as well as physical IO waits are virtually eliminated since the database fetches
the necessary results from memory. The actual server result cache contains a SQL query result
cache and a PL/SQL function result cache, both of which share an identical infrastructure.
服务器端的缓存是Oracle 11G的新特性，他允许数据库在内存中进行SQL查询和PL/SQL函数的结果集的缓存，
Oracle对频繁执行的SQL查询和PL/SQL的函数结果集直接进行缓存而不再重新的执行SQL查询和函数。当使用
缓存的时候，可以发现一些资源使用降低了，例如IO，它也提高查询的响应时间。事实上，当从内存中读取
结果集的时候，逻辑读和物理读都减少了，实际上，服务器端的缓存包括SQL语句查询和PL/SQL查询结果集
的缓存。它们两者共享底层的缓存设施-即使用相同的缓存空间区域（SGA的一部分）。
All database sessions can share the cached results of a query, as long as they share execution
plans even partially. Oracle’s internal tests show that the server result cache leads to gains
as high as a 200 percent improvement in performance for workloads that are read-intensive.
所有的数据库会话都可以共享查询的缓存结果集，即使它们的执行计划是不同的，Oracle的内部测试表明
，在大批量读的情况下，使用服务器端的缓存可以使性能有两倍以上的提高。
In Oracle Database 11g, there is a new SGA component called result cache, which is actually
part of the shared pool in the SGA. By default, the server result cache uses a small part of
the shared pool, even when you don’t explicitly allocate memory for the cache. The default
maximum size depends on the size of the SGA as well as the memory management method
you’re using. However, to set the size for the result cache memory size, you use the result_
cache_max_size initialization parameter. If you’re manually managing the shared pool, make
sure you increase the shared pool size when you increase the result cache size, since the result
cache draws its memory from the shared pool. Once you set the result cache memory size, the
automatic shared memory management infrastructure will automatically manage the memory
you allocate for the server-side result cache.
在Oracle 11g中，引入了一个新的SGA组件-结果集缓存，它实际上是SGA中共享池的一部分，默认的缓存
大小依赖于SGA的大小，也就是当前使用的内存管理的方式，可以使用参数 result_cache_max_size设置
缓存区的大小，当进行手动管理共享池的时候，当增加缓存区大小的时候，必须确保同时增加共享池的
大小，当你设置了缓存区的大小，ASSM(自动内存管理)将自动的管理分配在服务器端的缓存的内存大小。
The new PL/SQL package DBMS_RESULT_CACHE provides various procedures to administer the
result cache feature, including monitoring and managing the cache. The V$RESULT_CACHE_*
views let you determine the success of a cached SQL query or a PL/SQL function by determining
the cache-hit success of the query or function.
PL/SQL包DBMS_RESULT_CACHE提供了各种用于管理缓存结果集和过程，包括监控和管理缓存，动态
视图V$RESULT_CACHE_*允许查询缓存的SQL语句和PL/SQL函数查看它们的缓存命中率。
In the following sections, you’ll learn how the two components of the server result cache—
the SQL query result cache and the PL/SQL result cache—work. You’ll also learn how to manage
the two types of caches, which together make up the server result cache.
下面介绍SQL查询结果集和PL/SQL结果集的缓存是如何工作的，你可以了解如何去管理上述两种类型的缓存
他们共同组成了服务器端的缓存
SQL Query Result Cache
SQL查询结果集的缓存
You can now cache the results of frequently executed SQL query results in the SQL query result
cache. It doesn’t take a rocket scientist to figure out that extracting results from the cache
takes far less time than actually running the SQL query. You’ll see significant database-wide
performance improvements when you use the SQL query result cache.
现在你可以在SQL结果集缓存中，对经常执行的SQL进行查询结果缓存，可能从结果中查询数据，不会有非常
巨大的性能提升，但是最起码，比执行SQL语句的过程中花费的时间少。当你使用SQL结果集缓存的使用，你
将会发现数据库的性能有一个显著的提升。
Query result caching is ideal in the following circumstances:
1、 The query processes a large number of rows to yield just a handful of rows or even a
single row.
2、 The database executes the query frequently, with little or no changes in the data itself.
Thus, although technically speaking you can apply the SQL query result cache to any kind
of workload, data warehousing applications are the most common beneficiaries of the cache
查询结果缓存应用在下面的情况下是最理想的：
1、查询处理了大量的数据行，但是只返回少量的行或者只返回一行
2、一个SQL在数据库中频繁的执行，在这个过程中，数据本身未发生变化，或者发生了很小的变化，
从技术上来说，你可以将结果集缓存应用到任何场景下，数据仓库使用缓存可能会取得更好的效果。
注意：In an Oracle RAC environment, each of the nodes has its own result cache that can’t
be used by the other instances in the RAC.
在Oracle的集群环境下，每一个节点都有自己的缓存信息，各个节点之间的缓存不能共享。
The database automatically invalidates the cached results of a SQL query when there is a
change in the data that’s part of the query or a change in any of the objects that are part of the
query.
当查询关联的对象或者关联的数据发生了变化，数据库自动就会把之前的SQL语句的查询结果集置为无效。
管理SQL结果集的缓存
Three new initialization parameters—result_cache_mode, result_cache_max_size, and
result_cache_max_result—are crucial in managing the server-side result cache. The initialization
parameter result_cache_mode determines whether and under what circumstance query
result caching will apply. The result_cache_max_size initialization parameter determines the
size of the result cache memory allocation. We’ll talk about the three result cache–related
initialization parameters in the following discussion.
You can control when the database uses the SQL query
使用result_cache_mode, result_cache_max_size,result_cache_max_result这三个初始化参数管理数据
库服务器端的缓存，result_cache_mode决定是否使用和在什么情况下使用缓存。result_cache_max_size决定
了内存分配给缓存区域的大小。
You can control when the database uses the SQL query result cache by setting the result_
cache_mode initialization parameter. If you turn caching on, a ResultCache operator is added to
the execution plan of the cached SQL query. The setting of the result_cache_mode parameter
determines when the optimizer will add the ResultCache operator to a query’s execution plan.
Here are the three possible values for the parameter。
你可以使用result_cache_mode这个初始化参数来设置是否使用SQL语句查询缓存。如果开启了缓存，
那么缓存操作符会加入到缓存的SQL语句的执行计划中，设置这个参数可以决定在什么时候，将缓存操作
符加入到执行计划中，下面是这个参数的三种可能取值情况：
1、The default value of this parameter is manual, which means the result cache operator will
be added (results cached) only if you use the new result_cache hint in the SQL query.
2、If you set the value to auto, the cost optimizer will determine when it should cache the
query results, based on factors such as the frequency of execution, the cost of execution,
and how frequently the database objects that are part of the query are changing.
3、If you set the value to force, the database caches the results of all SQL statements, as
long as it’s valid to cache the result. That is, the database will cache the results of all SQL
statements where it’s possible to do so.
1、默认的参数值为manual（手动），这意味着，只有在SQL语句中使用result_cache这个hint关键字，Oracle
才会使用查询缓存。
2、如果设置为auto（自动），查询的优化器将会决定在什么时候使用缓存信息，包含下面这些因素：SQL语句
执行的频繁度，执行的代价，查询关联的对象是否经常发生改变。
3、如果设置为force（强制），数据库会对所以的查询结果集进行缓存，只要缓存的结果是有效的，
下面是使用alter system 语句进行缓存参数的修改。
SQL> alter system set result_cache_mode = force
Note that the force option means the database will cache the results of all SQL statements.
By setting the result caching mode to manual, as shown here, you disable automatic query
caching by the database:
注意:设置为force意味着Oracle对所有的SQL语句查询结果集进行缓存，当设置为manual，将禁用
缓存功能。
SQL> alter system set result_cache_mode = manual;
Once automatic query caching is disabled, you must use the result_cache hint in your
queries to enforce query result caching.
当自动缓存被禁用，那么必须在查询的语句中使用hint来强制使用结果集缓存
Note You can add the result_cache hint to SQL queries, subqueries, and inline views.
注意：可以为SQL语句，子查询和内嵌视图添加result_cache hint.
The result_cache_mode parameter helps you enable the query result cache at the database
level. You can enable just session-level caching by setting the parameter using the alter session
statement. You can override the setting of the result_cache_mode initialization parameter by
specifying the new optimizer hints result_cache and no_result_cache in order to turn SQL
query caching on and off. The hints will override both the auto and force settings of the
result_cache_mode parameter.
result_cache_mode这个参数帮助你在数据库的层级上使用查询缓存，也可以通过设置在，session级别
使用缓存，你可以使用no_result_cache和result_cache两个查询暗示来覆盖默认设置的result_cache_mode
参数值，使查询缓存的功能打开或者关闭，这个索引也会auto和force这两个参数.
If you set the result_cache_mode parameter to manual, you must then specify the result_
cache hint in a query so the database can cache the query results. The result_cache hint tells
the database to cache the current query’s results and to use those results for future executions
of the query or query fragment. If you set the result_cache_mode parameter to auto or force, on
the other hand, the database will try to cache the results of all the queries. If you don’t want
to cache the results of any query under these circumstances, you must specify the no_result_
cache hint in the query. The no_result_cache hint, like the result_cache hint, overrides the
value set for the result_cache_mode parameter.
如果将缓存模式设置为munual(手动),你必须在SQL语句中指明使用查询缓存,那么数据库将可以缓存查询
结果集,result_cache hint(缓存暗示)告诉数据库缓存当前的查询结果集,并且在未来,对当前语句或者当前查询
语句的一部分语句,使用查询结果集缓存,当设置该参数为force或者auto,另一方面,Oracle会尽力对所以的查询
语句进行结果集的缓存,如果你在这种情况下,不想进行结果结的缓存,那么你必须指明no_result_cache这个hint,
no_result_cache hint和result_cache hint类似,都会覆盖默认的result_cache_mode的值.
下面是如何在SQL查询语句中使用result_cache hint这个参数
SQL> select /*+ result_cache */
avg(income), region
from employees
group by region;
Although the result_cache_mode initialization parameter determines whether the database
caches the query results, the result_cache_max_size parameter determines the maximum
amount of the SGA that the database can allocate to the result cache. Note that the result cache
size you set by using this parameter applies to both components of the server-side result cache—
the SQL query result cache as well as the PL/SQL result cache.
尽管result_cache_mode这个初始化参数决定了数据库是否使用查询缓存，但是result_cache_max_size
这个参数决定了数据库可以在SGA中给查询缓存开辟的最大空间