Code Listing 1: Checking big table cache statistics (at first setup)
select * from v$bt_scan_cache; BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID —————————————— ——————————————— ———————————— ———————————————— ——————————————— —————— .210158085 40 0 0 1000 0 select * from v$bt_scan_obj_temps; no rows selected
两个动态性能试图显示如何缓存全表扫描的工作方式，v$bt_scan_cache显示了全表扫描的概述。
列的含义如下：
BT_CACHE_ALLOC. Even though John allocated 40 percent to the big table cache, not all of that cache might be required. As the demand for buffers in the big table cache rises due to incoming big tables, more space will be allocated. This column shows the ratio of the buffer cache used by the big table cache right now to the overall size of the buffer cache; in our example, it’s 0.210158085, or about 21 percent, because there is no big table in the buffer cache yet.
BT_CACHE_TARGET. This column shows the target allocation percentage, 40 percent, as set by John earlier.
OBJECT_COUNT. This column shows how many objects are in the big table cache. Because John just set up this cache, there are no objects yet; hence, this shows 0.
MEMORY_BUF_ALLOC. This column shows how many buffers are allocated to the objects in the big table cache right now. Again, because John just set up the big table cache, this shows 0.
MIN_CACHED_TEMP. As John explained earlier, temperature is a new way to designate the usefulness of an object (for example, a table) in the big table cache. The more often a table is accessed, the higher its temperature and, hence, the more beneficial it is for the table to be in this cache. This column shows the minimum temperature of objects that will be considered for this cache. It shows 1,000; therefore, objects with temperatures below 1,000 will not be considered.
CON_ID. This column shows the container ID for a database in a multitenant environment.
The second view, V$BT_SCAN_OBJ_TEMPS, shows the details of the big table cache. The V$BT_SCAN_OBJ_TEMPS view includes the following columns:
TS#. This column shows the tablespace number an object resides in. You can join this view with the TS$ table to get the tablespace name.
DATAOBJ#. This column shows the data object number of the object. You can join this view with DBA_OBJECTS to get the object name.
SIZE_IN_BLKS. This column shows the number of blocks of this object that were considered for the big table cache in this database instance.
TEMPERATURE. This column shows the temperature of the object.
POLICY. This column shows how the object was cached: either partially or in its entirety.
CACHED_IN_MEM. This column shows how many blocks of this object are in the big table cache.
CON_ID. This column shows the container ID for a database in a multitenant environment.
约翰指出，v$bt_scan_obj_temps视图没有返回任何行。这并不奇怪，他补充说，因为他只是设置了大表的缓存，但并没有表已被缓存。为了证明这个新的缓存特性，约翰从数据库中选择一个大的表名为T1。首先，他用以下SQL查询，查询下该表多少块由该表使用：
select blocks from user_tables where table_name = 'T1'; BLOCKS ——————————————— 335952
T1表有 335,952 数据块—这个表相当大，执行一个全表扫描:
执行全表扫描后, 这时两个动态视图显示的数据和前期的不一样了. 我们Code Listing 2. 试图 V$BT_SCAN_CACHE 的 BT_CACHE_ALLOC 列现在显示0.400012911, 表明大约有 40 % 的数据缓存在 buffer cache 中.
Code Listing 2: Checking big table cache statistics (after full table scan operation)