Optimized Shared Memory & MEMORY_MAX_TARGET in Solaris 11.2 + Oracle 12c

Starting with 12c, Oracle Database uses the Optimized Shared Memory (OSM) model of Oracle Solaris on Oracle Solaris 10 1/13 or later and Oracle Solaris 11 SRU 7.5 or later systems to implement Automatic Memory Management.

OSM allows dynamic resizing of System Global Area (SGA) without restarting the instance. It does not use the oradism utility and swap disk space. OSM is NUMA-optimized.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             905972952 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1200M
SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1200M

Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             601             601        NONE

oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:39:35 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m         24   0x0        --rw-r-----   oracle oinstall   14680064
m         23   0x0        --rw-r-----   oracle oinstall   33554432
m         22   0x0        --rw-r-----   oracle oinstall 1123966976
m         21   0x0        --rw-r-----   oracle oinstall   83886080
m         20   0x0        --rw-r-----   oracle oinstall    4194304

If the column ALLOC shows an integer, it specifies that OSM is in use. If the column ALLOC shows a hyphen, it specifies that OSM is not in use.

oracle@solaris:~$ ipcs -im
IPC status from <running system> as of Tuesday, October  7, 2014 08:38:28 PM SGT
T         ID      KEY        MODE        OWNER    GROUP ISMATTCH
Shared Memory:
m         25   0x6a4bcd4  --rw-r-----   oracle oinstall       46
m         24   0x0        --rw-r-----   oracle oinstall       46
m         23   0x0        --rw-r-----   oracle oinstall       46
m         22   0x0        --rw-r-----   oracle oinstall       46
m         21   0x0        --rw-r-----   oracle oinstall       46
m         20   0x0        --rw-r-----   oracle oinstall       46

SQL> select (14680064+33554432+1123966976+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1123966976+83886080+4194304)/1024/1024
---------------------------------------------------------
                                               1201.89844

SQL> alter system set memory_max_target=1232M;
alter system set memory_max_target=1232M
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

                                          
SQL> alter system set memory_max_target=1232M scope=spfile;

System altered.

SQL> startup
ORACLE instance started.

Total System Global Area 1291845632 bytes
Fixed Size                  3003272 bytes
Variable Size             939527288 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes
Database mounted.

All SGA segments were allocated at startup
**********************************************************************
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Available system pagesizes:
  4K, 2048K
Supported system pagesize(s):
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               2               2        NONE
     2048K       Configured             617             617        NONE
**********************************************************************

oracle@solaris:~$ ipcs -dm
IPC status from <running system> as of Tuesday, October  7, 2014 08:53:02 PM SGT
T         ID      KEY        MODE        OWNER    GROUP      ALLOC
Shared Memory:
m  268435459   0x6a4bcd4  --rw-r-----   oracle oinstall           -
m  268435458   0x0        --rw-r-----   oracle oinstall   14680064
m  268435457   0x0        --rw-r-----   oracle oinstall   33554432
m  268435456   0x0        --rw-r-----   oracle oinstall 1157496832
m  251658303   0x0        --rw-r-----   oracle oinstall   83886080
m  251658302   0x0        --rw-r-----   oracle oinstall    4194304

SQL> select (14680064+33554432+1157496832+83886080+4194304)/1024/1024 from dual;

(14680064+33554432+1157496832+83886080+4194304)/1024/1024
---------------------------------------------------------
                                                 1233.875

                                                
SQL> alter system set memory_target=1232M;

System altered.

What are these values added in spfile after issued “create spfile from memory”

*.__data_transfer_cache_size=0
orclcdb.__data_transfer_cache_size=0
*.__db_cache_size=208M
orclcdb.__db_cache_size=285212672
*.__java_pool_size=16M
orclcdb.__java_pool_size=16777216
*.__large_pool_size=144M
orclcdb.__large_pool_size=33554432
*.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment
*.__pga_aggregate_target=480M
orclcdb.__pga_aggregate_target=503316480
*.__sga_target=720M
orclcdb.__sga_target=754974720
*.__shared_io_pool_size=0
orclcdb.__shared_io_pool_size=16777216
*.__shared_pool_size=224M
orclcdb.__shared_pool_size=251658240
*.__streams_pool_size=0
orclcdb.__streams_pool_size=0
*._adaptive_window_consolidator_enabled=TRUE
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._bloom_serial_filter='ON'
*._complex_view_merging=TRUE
*._compression_compatibility='12.1.0.2.0'
*._connect_by_use_union_all='TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._diag_adr_trace_dest='/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace'
*._dimension_skip_null=TRUE
*._distinct_agg_optimization_gsets='CHOOSE'
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._gby_vector_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_adaptive_plans=TRUE
*._optimizer_aggr_groupby_elim=TRUE
*._optimizer_ansi_join_lateral_enhance=TRUE
*._optimizer_ansi_rearchitecture=TRUE
*._optimizer_batch_table_access_by_rowid=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_cluster_by_rowid=TRUE
*._optimizer_cluster_by_rowid_batched=TRUE
*._optimizer_cluster_by_rowid_control=129
*._optimizer_coalesce_subqueries=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_connect_by_elim_dups=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_cube_join_enabled=TRUE
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_dsdir_usage_control=126
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enable_table_lookup_by_nl=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=192
*._optimizer_false_filter_pred_pullup=TRUE
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_full_outer_join_to_outer=TRUE
*._optimizer_gather_feedback=TRUE
*._optimizer_gather_stats_on_load=TRUE
*._optimizer_group_by_placement=TRUE
*._optimizer_hybrid_fpwj_enabled=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_inmemory_access_path=TRUE
*._optimizer_inmemory_autodop=TRUE
*._optimizer_inmemory_bloom_filter=TRUE
*._optimizer_inmemory_cluster_aware_dop=TRUE
*._optimizer_inmemory_gen_pushable_preds=TRUE
*._optimizer_inmemory_minmax_pruning=TRUE
*._optimizer_inmemory_table_expansion=TRUE
*._optimizer_interleave_jppd=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_factorization=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_multi_table_outerjoin=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_nlj_hj_adaptive_join=TRUE
*._optimizer_null_accepting_semijoin=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_join_to_inner=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_partial_join_eval=TRUE
*._optimizer_proc_rate_level='BASIC'
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_reduce_groupby_key=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_strans_adaptive_pruning=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_undo_cost_change='12.1.0.2'
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_unnest_scalar_sq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._optimizer_use_gtt_session_stats=TRUE
*._optimizer_use_histograms=TRUE
*._optimizer_vector_transformation=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_adaptive_dist_method='CHOOSE'
*._px_concurrent=TRUE
*._px_cpu_autodop_enabled=TRUE
*._px_external_table_default_stats=TRUE
*._px_filter_parallelized=TRUE
*._px_filter_skew_handling=TRUE
*._px_groupby_pushdown='FORCE'
*._px_join_skew_handling=TRUE
*._px_minus_intersect=TRUE
*._px_object_sampling_enabled=TRUE
*._px_parallelize_expression=TRUE
*._px_partial_rollup_pushdown='ADAPTIVE'
*._px_partition_scan_enabled=TRUE
*._px_pwg_enabled=TRUE
*._px_replication_enabled=TRUE
*._px_scalable_invdist=TRUE
*._px_single_server_enabled=TRUE
*._px_ual_serial_input=TRUE
*._px_wif_dfo_declumping='CHOOSE'
*._px_wif_extend_distribution_keys=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._sqltune_category_parsed='DEFAULT'# parsed sqltune_category
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest='/u01/app/oracle/admin/orclcdb/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='/u01/app/oracle/oradata/ORCLCDB/controlfile/o1_mf_b2qcr7mn_.ctl','/u01/app/oracle/fast_recovery_area/ORCLCDB/controlfile/o1_mf_b2qcr8oj_.ctl'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/cdump'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='orclcdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclcdbXDB)'
*.enable_pluggable_database=TRUE
*.inmemory_size=112M
*.local_listener='LISTENER_ORCLCDB'
*.log_buffer=12936K# log buffer update
*.memory_max_target=1200M
*.memory_target=1200M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=3M
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'

Enable the In-Memory column store (IM column store) in database

SQL> select banner from v$version where banner like '%Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select * from v$sgainfo where name='In-Memory Area Size';

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
In-Memory Area Size                       0 No           0

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set inmemory_size=50M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-64353: in-memory area size cannot be less than 100MB

oracle@solaris:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ grep inmemory initorclcdb.ora
*.inmemory_size=100M

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  3003176 bytes
Variable Size             905972952 bytes
Database Buffers          218103808 bytes
Redo Buffers               13770752 bytes
In-Memory Area            117440512 bytes
Database mounted.
Database opened.

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 112M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

SQL> conn donghua/password@pdb1
Connected.
SQL> select * from v$sgainfo where name='In-Memory Area Size';

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
In-Memory Area Size               117440512 No           0

SQL> select * from v$inmemory_area;

POOL       ALLOC_BYTES USED_BYTES POPULATE_STATUS     CON_ID
---------- ----------- ---------- --------------- ----------
1MB POOL      82837504          0 DONE                     3
64KB POOL     16777216          0 DONE                     3

SQL> select sum(alloc_bytes)/1024/1024 from v$inmemory_area;

SUM(ALLOC_BYTES)/1024/1024
--------------------------
                        95

Use “pdb_save_or_discard_state” clause to instruct the database to save or discard the open mode of the PDB when the CDB restarts.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SQL>  alter pluggable database pdb1 open;

Pluggable database altered.

SQL>  alter pluggable database pdb1 save state;

Pluggable database altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1061158912 bytes
Fixed Size                  3011736 bytes
Variable Size             708840296 bytes
Database Buffers          343932928 bytes
Redo Buffers                5373952 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE

SQL> col con_name for a10
SQL> col state for a20

SQL> select con_name,state from dba_pdb_saved_states;

CON_NAME   STATE
---------- --------------------
PDB1       OPEN

Fix “Abnormal Program Termination” OUI error during installation Oracle 10.2.0.1 on Windows 2008

Error:

Starter DB state :true
Adding args :15
-scratchPath
C:\Users\ADMINI~1\AppData\Local\Temp\1\OraInstall2014-10-03_02-10-47PM
-sourceLoc
D:\Oracle\10201_database_win32\database\install\../stage/products.xml-sourceType network –timestamp 2014-10-03_02-10-47PM
-debug –oneclick –nosplash –nowelcome –nocleanUpOnExit -exitOnBack
Exception java.lang.NullPointerException occurred..
java.lang.NullPointerException
        at oracle.sysman.oii.oiix.OiixPathOps.concatPath(OiixPathOps.java:553)
        at oracle.sysman.oii.oiic.OiicBaseApp.setAdditionalProperties(OiicBaseApp.java:374)
        at oracle.sysman.oii.oiic.OiicInstaller.processCommandLine(OiicInstaller.java:669)
        at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:893)
        at oracle.sysman.oio.oioc.OiocOneClickInstaller.runInstaller(OiocOneClickInstaller.java:1016)
        at oracle.sysman.oio.oioc.OiocOneClickInstaller.startRun(OiocOneClickInstaller.java:1108)
        at oracle.sysman.oio.oioc.OiocOneClickDB.nextClicked(OiocOneClickDB.java:1084)
        at oracle.sysman.oio.oioc.OiocOneClickInstaller.main(OiocOneClickInstaller.java:1318)

Fix: