大表缓存

最新推荐文章于 2018-04-10 00:00:00 发布

ctfmxd8196

最新推荐文章于 2018-04-10 00:00:00 发布

阅读量70

点赞数

大表缓存

Starting with Oracle Database 12c Release 1 (12.1.0.2), the big table cache enables serial queries and parallel queries to use the buffer cache. The big table cache facilitates efficient caching for large tables in data warehousing environments, even if these tables do not fully fit in the buffer cache. Table scans can use the big table cache in the following scenarios:
从12.1.0.2 开始，可以使用使用将大表缓存到buffer cache 当中，大表缓存首要目的是在数据库仓库环境下增加效率的，即使这个表非常的大不能完全缓存到buffer cache 当中，在下面的情况下可以使用到大表缓存。

? Parallel queries
并行查询两个条件
DB_BIG_TABLE_CACHE_PERCENT_TARGET 非0 默认值为0
PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE
默认值为MANUAL

In single-instance and Oracle Real Application Clusters (Oracle RAC) databases,

parallel queries can use the big table cache when the

DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a

non-zero value, and PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE.

? Serial queries
串行查询的时候
只要设置下面的参数就可以了。
DB_BIG_TABLE_CACHE_PERCENT_TARGET 非0 默认值为0

In a single-instance configuration only, serial queries can use the big table cache

when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is

set to a non-zero value.

Automatic big table caching enables parallel queries and serial queries to use the

buffer cache, which enhances the in-memory query capabilities of Oracle Database.

Automatic big table caching is designed primarily to enhance performance for data

warehouse workloads, but it also improves performance in mixed workloads. 首要目的是增加数据仓库的性能，也能改善复杂负载（我的理解是数据库仓库和oltp混合模式的负载）下的性能。

When a non-zero value is specified for the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter, the value indicates the

percentage of the buffer cache to reserve for the big table cache. The largest value that can be specified with the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter is 90, which reserves 10% of the buffer cache for usage besides table scans.

The default value of this parameter is 0. Therefore, automatic big table caching is not enabled by default. When automatic big table caching is not enabled, queries might run using the least recently used (LRU) mechanism for cached reads, or might decide to use direct reads for the table scan.
如果没有使用big table cache的情况下，则会使用lru链进行缓存读或者是决定使用直接读进行表扫描。

If a large table is about the size of the combined size of the big table cache of all instances, the table will be partitioned and cached or mostly cached on all instances.

With in-memory parallel query, this could eliminate most disk reads for queries on the table, or the database could intelligently read from disk only for the portion of the table that does not fit in the big table cache. If the big table cache cannot cache all the tables to be scanned, only the most frequently accessed tables will be cached, and the rest will be read via direct read automatically.

Use these guidelines when setting the parameter:
使用场景。

? If you do not enable automatic degree of parallelism (DOP) in your Oracle RAC

environment, do not set this parameter because the big table cache is not used in

that situation.

? When setting this parameter, consider the workload mix: how much of the

workload is for OLTP; insert, update, and random access; and how much of the

workload involves table scans. Because data warehouse workloads often perform

large table scans, you may consider giving the big table cache section a higher

percentage of buffer cache space for data warehouses.

? This parameter can be dynamically changed if the workload changes. The change

could take some time to reach the target (depending on the current workload)

because buffer cache memory might be actively used at that time.