【Oracle 12c】In-Memory Database Cache内存数据库选项
程序员文章站
2022-05-19 07:55:45
...
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
| 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 |
test
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
Related posts:
- 【Oracle Database 12c新特性】In-Memory Option
- #ORACLE 12c in-memory#
- Know More about Oracle PGA Memory
- #揭秘Oracle 12c in-memory option#
- EVENT 10235:"check memory manager internal structures"
- ora-4031 and "obj stat memory" component in Shared Pool
- How GoldenGate process consumes memory
- Large Memory Footprints on AIX
- 【Oracle Database 12c新特性】Database Statistics 描述
- 那些在11gR2中可能惹祸的新特性，一张列表帮助你摆脱升级11gR2带来的烦恼
原文地址：【Oracle 12c】In-Memory Database Cache内存数据库选项, 感谢原作者分享。