Use of Oracle FLASH_CACHE

Hi All,

I need some help.

This is a question on Oracle FLASH CACHE usage.

Environment: Oracle 11G R2
FLASH CACHE: 400GB
Number of records in the src_big_table: 300million rows per day.

There is a big debate going within our DBAs at present about the FLASH CACHE usage. I need to know which one of the below INSERT logic/option will be using the FLASH CACHE so that data will be cached for dependant processes to use.

a) INSERT into trg_big_table SELECT * from src_big_table

b) INSERT into trg_big_table SELECT /*+ parallel(src,16) */ * from src_big_table src

c) INSERT /*+ parallel(trg_big_table,16) */ into trg_big_table SELECT /*+ parallel(src,16) */ * from src_big_table src

d) BULK LOAD - SELECT /*+ parallel(src,16) */ * from src_big_table src and INSERT /*+ parallel(trg_big_table,16) */ into trg_big_table

commit every 100000 records

e) BULK LOAD - SELECT * from src_big_table src and INSERT /*+ parallel(trg_big_table,16) */ into trg_big_table

commit every 100000 records

f) BULK LOAD - SELECT * from src_big_table src and INSERT into trg_big_table

commit every 100000 records

g) BULK LOAD - SELECT * from src_big_table src and INSERT /*+ APPEND_VALUES */ into trg_big_table

commit every 100000 records

Please let me know which option would use/record data in the FLASH CACHE.



Kind Regards,

I found answer for my question from Oracle. I am sharing the same, as this might help others.

HTH

Partitioning and Flash Cache
First, a quick test just to confirm that the ability to pin a segment into the flash cache can operate at the partition level. This is potentially important if you want to pin N, N-1 and N-2 partitions of the fact table in cache, but recycle the previous 17 partitions.

So here were have a simple table with three parititions:

SQL> select TABLE_NAME, PARTITION_NAME, FLASH_CACHE from user_tab_partitions;

TABLE_NAME PARTITION_NAME FLASH_C
------------------------------ ------------------------------ -------
ALL_FACTS YEAR_2009 DEFAULT
ALL_FACTS YEAR_2010 DEFAULT
ALL_FACTS YEAR_2011 DEFAULT

The following statement will pin just the YEAR_2011 partition in the flash cache:

SQL> alter table all_facts modify partition YEAR_2011 storage (flash_cache keep);

Table altered.

SQL> select TABLE_NAME, PARTITION_NAME, FLASH_CACHE from user_tab_partitions;

TABLE_NAME PARTITION_NAME FLASH_C
------------------------------ ------------------------------ -------
ALL_FACTS YEAR_2009 DEFAULT
ALL_FACTS YEAR_2010 DEFAULT
ALL_FACTS YEAR_2011 KEEP


Data Load and Flash Cache
When the main fact table current days partition is loaded using parallelised direct path insert/append, that will not load the flash cache with the new fact table blocks since the direct path mode of operation will bypass the buffer cache.

In order to get the fact table blocks into the flash cache so they have the chance of being used by subsequent aggregation jobs, in this scenario an initial step to populate the cache would be required. That could take the form of either:

- the first aggregation job, running standalone (for the current partition - N-1 and N-2 jobs could run at the same time) and without parallel query against the fact table current partition
- a "dummy" non parallel query against the fact table current partition which ran before the aggregation jobs ran

Since the current fact partition is expected to be up to 90GB, a non-PQ would take a fairly substantial (and perhaps unacceptable) amount of time to scan this partition. One way to reduce this substantially would be to subpartition the fact table and split the first aggregation job or "dummy" query into a number of non parallel query SQLs against each current sub-partition, and run them concurrently.

Once the blocks of the current (sub)partition(s) were loaded into cache, the (remaining) aggregation jobs could then run against the current days data. Note that they would need to access the partitions without parallel query in order to make use of the flash cache. There could be, for example, a separate aggregation job run without parallel query against each of the current sub partitions.

For the N-1 and N-2 partitions, processing could start straight away as they should still be in cache from the previous night (assuming they were pinned and they "fit"). Part of the processing would need to unpin the N-3 partition before the job starts to make room in the cache for the new partition.

Using this technique, it is important that partitions are pinned effectively, since the queries will be running without PQ against each partition (and if they had to go to disk that would potentially have a substantial impact).

Link: http://guyharrison.squarespace.com/b...ep-option.html

Cheers,