【Oracle 12c】In-Memory Database Cache内存数据库选项

NAME AVALUE SDESC _inmemory_check_prot_meta FALSE If true, marks SMU area read only to prevent stray writes _inmemory_private_journal_quota 100 quota for transaction in-memory private journals _inmemory_private_journal_sharedpool_quota 20 quota for transaction in-memory objects _inmemory_private_journal_numbkts 512 Number of priv jrnl ht bkts _inmemory_private_journal_numgran 128 Number of granules per HT node _inmemory_jscan 0 inmemory jscan enable _inmemory_pin_hist_mode 16 settings for IM pinned buffer history _inmemory_txn_checksum 0 checksum for SMUs and private journals _inmemory_buffer_waittime 100 wait interval for one SMU or IMCU to be freed _inmemory_cu_timeout 100 maximum wait time for one IMCU to be freed _inmemory_cudrop_timeout 1000 maximum wait time for IMCU to be freed during drop _inmemory_exclto_timeout 1000 maximum wait time to pin SMU for cleanout _inmemory_num_hash_latches 256 Maximum number of latches for IM buffers _inmemory_strdlxid_timeout 0 max time to determine straddling transactions _inmemory_incremental_repopulation FALSE If true, incremental repopulation of IMCU will be attempted _inmemory_lock_for_smucreate FALSE take object lock during smu creation _inmemory_auto_distribute TRUE If true, enable auto distribute _inmemory_autodist_2safe FALSE If true, enable auto distribute with 2safe _inmemory_distribute_timeout 300 If true, enable auto distribute with 2safe _inmemory_distribute_ondemand_timeout 300 On demand timeout for redistribute inmemory_size 0 size in bytes of in-memory area _inmemory_64k_percent 30 percentage of in-memory area for 64k pools _inmemory_min_ima_defersize 0 Defer in-memory area allocation beyond this size _inmemory_memprot TRUE enable or disable memory protection for in-memory _inmemory_analyzer_optimize_for 0 inmemory analyzer optimize for _inmemory_default_flags 8459 Default flags based on inmemory_clause_default _inmemory_default_new FALSE Force in-memory on new tables inmemory_clause_default

Default in-memory clause for new tables inmemory_force DEFAULT Force tables to be in-memory or not inmemory_query ENABLE Specifies whether in-memory queries are allowed _inmemory_query_scan TRUE In-memory scan enabled _inmemory_scan_override FALSE In-memory scan override _inmemory_scan_threshold_percent_noscan 50 In-memory scan threshold maximum percent dirty no scan _inmemory_small_segment_threshold 65536 In-memory small segment threshold (must be larger for in-memory) _inmemory_query_fetch_by_rowid FALSE In-memory fetch-by-rowid enabled _inmemory_pruning ON In-memory pruning _inmemory_enable_sys FALSE enable in-memory on system tablespace with sys user _inmemory_populate_fg FALSE populate in foreground _inmemory_pga_per_server 536870912 minimum pga needed per inmemory populate server inmemory_max_populate_servers 0 maximum inmemory populate servers _inmemory_servers_throttle_pgalim_percent 55 In-memory populate servers throttling pga limit percentage inmemory_trickle_repopulate_servers_percent 1 inmemory trickle repopulate servers percent _inmemory_populate_wait FALSE wait for population to complete _inmemory_populate_wait_max 600 maximum wait time in seconds for segment populate _inmemory_imco_cycle 120 IMCO cycle in seconds (sleep period) _inmemory_enable_population_verify 1 verify in-memory population _inmemory_log_level 1 in-memory log level _inmemory_fs_verify FALSE in-memory faststart verify _inmemory_force_fs FALSE in-memory faststart force _inmemory_force_fs_tbs SYSAUX in-memory faststart force tablespace _inmemory_force_fs_tbs_size 1073741824 in-memory faststart force tablespace size _inmemory_fs_raise_error FALSE in-memory faststart raise error _inmemory_fs_nodml FALSE in-memory faststart assumes no dmls while populating _inmemory_fs_enable FALSE in-memory faststart enable _inmemory_fs_enable_blk_lvl_inv TRUE in-memory faststart enable block level invalidation _inmemory_fs_blk_inv_blkcnt

in-memory faststart CU invalidation threshold(blocks) _inmemory_fs_blk_inv_blk_percent 20 in-memory faststart CU invalidation threshold(blocks) _inmemory_enable_stat_alert FALSE dump in-memory stats in alert log file _inmemory_imcu_align TRUE Enforce 8M IMCU alignment _inmemory_max_populate_retry 3 IM populate maximum number of retry _inmemory_imcu_target_rows 1048576 IMCU target number of rows _inmemory_imcu_target_bytes 0 IMCU target size in bytes _inmemory_imcu_source_extents 0 number of source extents per IMCU _inmemory_imcu_source_blocks 0 number of source blocks per IMCU _inmemory_imcu_source_minbytes 1048576 number of minimum source bytes per IMCU _inmemory_imcu_populate_minbytes 5242880 minimum free space in IMA for populating IMCU _inmemory_imcu_source_analyze_bytes 134217728 number of source analyze bytes per IMCU _inmemory_imcu_target_maxrows 8388608 IMCU maximum target number of rows _inmemory_imcu_source_maxbytes 536870912 IMCU maximum source size in bytes _inmemory_max_queued_tasks 0 Maximum queued populating tasks on the auxiliary queue _inmemory_repopulate_threshold_rows

In-memory repopulate threshold number of modified rows _inmemory_repopulate_threshold_blocks

In-memory repopulate threshold number of modified blocks _inmemory_pct_inv_rows_invalidate_imcu 50 In-memory percentage invalid rows for IMCU invalidation _inmemory_pct_inv_blocks_invalidate_imcu 100 In-memory percentage invalid blocks for IMCU invalidation _inmemory_repopulate_threshold_mintime_factor 5 In-memory repopulate minimum interval (N*timetorepop) _inmemory_repopulate_threshold_mintime 0 In-memory repopulate minimum interval (millisec) _inmemory_repopulate_threshold_scans 0 In-memory repopulate threshold number of scans _inmemory_repopulate_priority_scale_factor 100 In-memory repopulate priority threshold scale factor _inmemory_repopulate_invalidate_rate_percent 100 In-memory repopulate invalidate rate percent _inmemory_repopulate_priority_threshold_row 20 In-memory repopulate priority threshold row _inmemory_repopulate_priority_threshold_block 40 In-memory repopulate priority threshold block _inmemory_repopulate_threshold_rows_percent 5 In-memory repopulate threshold rows invalid percentage _inmemory_repopulate_threshold_blocks_percent 10 In-memory repopulate threshold blocks invalid percentage _inmemory_repopulate_disable FALSE disable In-memory repopulate _inmemory_check_protect FALSE If true, marks in-memory area read only to prevent stray writes _inmemory_checksum FALSE If true, checksums in-memory area to detect stray writes _inmemory_validate_fetch FALSE If true, validate single-row fetch between in-memory and disk _inmemory_journal_row_logging FALSE If true, log the entire row into the in-memory journal _inmemory_journal_check 0 Depending on value does one of the DML verifications _inmemory_rows_check_interrupt 1000 Number of rows buffered before interrupt check _inmemory_dbg_scan 0 In-memory scan debugging _inmemory_segment_populate_verify 0 In-memory segment populate verification _inmemory_query_check 0 In-memory query checking _inmemory_test_verification 0 In-memory verification testing _inmemory_invalidate_cursors TRUE In-memory populate enable cursor invalidations _inmemory_prepopulate_fg 0 Force prepopulate of in-memory segment in foreground _inmemory_prepopulate TRUE Enable inmemory populate by IMCO _inmemory_trickle_repopulate TRUE Enable inmemory trickle repopulate _inmemory_trickle_repopulate_threshold_dirty_ratio 0 IMCO Trickle Repopulate threshold dirty ratio _inmemory_trickle_repopulate_min_interval 300 IMCO Trickle Repopulate Interval _inmemory_trickle_repopulate_fg 0 Trickle Repopulate in the Foreground _inmemory_force_non_engineered FALSE force non-engineered systems in-memory behavior on RAC _inmemory_suppress_vsga_ima FALSE Suppress inmemory area in v$sga optimizer_inmemory_aware TRUE optimizer in-memory columnar awareness _optimizer_inmemory_table_expansion TRUE optimizer in-memory awareness for table expansion _optimizer_inmemory_gen_pushable_preds TRUE optimizer generate pushable predicates for in-memory _optimizer_inmemory_autodop TRUE optimizer autoDOP costing for in-memory _optimizer_inmemory_access_path TRUE optimizer access path costing for in-memory _optimizer_inmemory_quotient 0 in-memory quotient (% of rows in in-memory format) _optimizer_inmemory_pruning_ratio_rows 100 in-memory pruning ratio for # rows (% of rows remaining after pruning) _parallel_inmemory_min_time_threshold AUTO threshold above which a plan is a candidate for parallelization for in-memory tables (in seconds) _parallel_inmemory_time_unit 1 unit of work used to derive the degree of parallelism for in-memory tables (in seconds) _optimizer_inmemory_bloom_filter TRUE controls serial bloom filter for in-memory tables _optimizer_inmemory_cluster_aware_dop TRUE Affinitize DOP for inmemory objects _optimizer_inmemory_minmax_pruning TRUE controls use of min/max pruning for costing in-memory tables

In-Memory Database Cache


  IM                           in-memory ((null))
    IM_transaction             IM transaction layer ((null))
      IM_Txn_PJ                IM Txn Private Journal (ktmpj)
      IM_Txn_SJ                IM Txn Shared Journal (ktmsj)
      IM_Txn_JS                IM Txn Journal Scan (ktmjs)
      IM_Txn_Conc              IM Txn Concurrency (ktmc)
      IM_Txn_Blk               IM Txn Block (ktmb)
      IM_Txn_Read              IM Txn Read (ktmr)
    IM_space                   IM space layer ((null))
    IM_data                    IM data layer (kdm)
      IM_populate              IM populating (kdml)
      IM_background            IM background (kdmr)
      IM_scan                  IM scans ((null))
      IM_journal               IM journal ((null))
      IM_dump                  IM dump ((null))
      IM_FS                    IM faststart ((null))
      IM_optimizer             IM optimizer (kdmo)
	  
	  
	  
	  
	  alter session set events 'trace[IM_scan] disk=medium';
	  
	  
	  
	  SQL> alter system set inmemory_size=2g scope=spfile;

System altered.

SQL> shutdown immediate;


[oracle@mlab2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 3 23:18:18 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size                  2928008 bytes
Variable Size             402653816 bytes
Database Buffers          117440512 bytes
Redo Buffers               13848576 bytes
In-Memory Area           2147483648 bytes
Database mounted.
Database opened.

    alter session set events 'trace[IM_scan] disk=medium';
      select count(*) from mac_imm1;
	SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/diag/rdbms/c12r1/C12R1/trace/C12R1_ora_16700.trc




kdmsirs(): map with 13 extents
Ext: 0, dba: 0x18000db, len: 5, skp: 0
Ext: 1, dba: 0x18000e0, len: 8, skp: 0
Ext: 2, dba: 0x18000e9, len: 7, skp: 0
Ext: 3, dba: 0x18000f0, len: 8, skp: 0
Ext: 4, dba: 0x18000f9, len: 7, skp: 0
Ext: 5, dba: 0x1800100, len: 8, skp: 0
Ext: 6, dba: 0x1800109, len: 7, skp: 0
Ext: 7, dba: 0x1800110, len: 8, skp: 0
Ext: 8, dba: 0x1800119, len: 7, skp: 0
Ext: 9, dba: 0x1800120, len: 8, skp: 0
Ext: 10, dba: 0x1800129, len: 7, skp: 0
Ext: 11, dba: 0x1800130, len: 8, skp: 0
Ext: 12, dba: 0x1800139, len: 1, skp: 0 
kdmsStartEndDBA(): scan start: 0x18000db end: 0x18000df rdba[0]: 0x18000db size[0]: 5, nblks: 5  extno: 0, skip: 0
kdmsGetIMCU(): In arguments: nblks 5: start_addr 25166043, end_addr 25166047
kdmsGetIMCU(): Mem addr: baffffd0: mem len: 1048576: Start dba: 0x18000db 25166043: len: 5 smu: fbf44770 td: 0x7f76515361a8
kdmsGetIMCU(): range: 0 lrid dba: 0x0 slot: 0 hrid dba: 0x0 slot: 0
kdmsGetIMCU(): found imcu 0xbaffffd0 25166043 25166047 1
kdst_fetch_imc(): imcu get 0x18000db
kdmsTransGet(): got ktmrds for dba 0x18000db, td : 0x7f76515361a8
kdmsFindEndDba: ext in imcu is 0, and on disk 0
IMCU extents
extent 0: 25166043 0x18000db + 5
extent 1: 25166048 0x18000e0 + 8
extent 2: 25166057 0x18000e9 + 7
extent 3: 25166064 0x18000f0 + 8
extent 4: 25166073 0x18000f9 + 7
extent 5: 25166080 0x1800100 + 8
extent 6: 25166089 0x1800109 + 7
extent 7: 25166096 0x1800110 + 8
extent 8: 25166105 0x1800119 + 7
extent 9: 25166112 0x1800120 + 8
extent 10: 25166121 0x1800129 + 7
extent 11: 25166128 0x1800130 + 8
extent 12: 25166137 0x1800139 + 1 
Disk extents
extent 0: 25166043 0x18000db + 5 (skip = 0)
extent 1: 25166048 0x18000e0 + 8 (skip = 0)
extent 2: 25166057 0x18000e9 + 7 (skip = 0)
extent 3: 25166064 0x18000f0 + 8 (skip = 0)
extent 4: 25166073 0x18000f9 + 7 (skip = 0)
extent 5: 25166080 0x1800100 + 8 (skip = 0)
extent 6: 25166089 0x1800109 + 7 (skip = 0)
extent 7: 25166096 0x1800110 + 8 (skip = 0)
extent 8: 25166105 0x1800119 + 7 (skip = 0)
extent 9: 25166112 0x1800120 + 8 (skip = 0)
extent 10: 25166121 0x1800129 + 7 (skip = 0)
extent 11: 25166128 0x1800130 + 8 (skip = 0)
extent 12: 25166137 0x1800139 + 1 (skip = 0)
kdmsFindEndDba(): begin: 25166043, end dba: 25166137     done ext in imcu 12 on disk 12
min of imcu end 25166138 0x180013a disk end 25166138 0x180013a discont 0
kdst_fetch_imc(): done: 1, empty: 0, ftch: 0
kdst_fetch_imc(): fetch: 0, invalid: 0
kdmsRepopulate(): repop 0 nblks 89 0 nrows 2342 0 cnt 5
kdstf00100010001101kmP(): create vv 18000db 95
kdmsCreateIMCUValidVector: Scan range (25166043, 25166137)
Extent map passed into kdzd layer:
Extent 0: (25166043, 25166047)
Extent 1: (25166048, 25166055)
Extent 2: (25166057, 25166063)
Extent 3: (25166064, 25166071)
Extent 4: (25166073, 25166079)
Extent 5: (25166080, 25166087)
Extent 6: (25166089, 25166095)
Extent 7: (25166096, 25166103)
Extent 8: (25166105, 25166111)
Extent 9: (25166112, 25166119)
Extent 10: (25166121, 25166127)
Extent 11: (25166128, 25166135)
Extent 12: (25166137, 25166137)
kdzd_dump_validvec:
IMCU: 1
Num slots: 2342 set: 2342 rounded to 8: 2368 bytes: 296
Flag if all rows valid: 1
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.
ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.ff.3f.
3 final 0
kdzd_dump_validvec: End Dumping Valid Vector
**************** ktmrDS Dump  *****************
pdb=0, tsn=4, rdba=25166043, objn=91999, objd=91999, typ=1, gflg=0 flg=0
loadscn=scn: 0x0000.001af80b
invalid blkcnt=0, fetch blkcnt=0
env [0x7f765152af6c]: (scn: 0x0000.001af830   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.001af82c  flg: 0x00000660)invalid rowcnt=0,
 fetch rowcnt=0
kdst_fetch_imc(): release 0x18000db from td: 0x7f76515361a8
kdmsGetJournalRows(): done with IMC fetch; journal rows -1
kdmsEnd(): imcuctx 0x7f7651688b78 release 0x18000db from td: 0x7f76515361a8