12c vs 11g x$messages

ACMS initialization ACMS
ADR PDB Auto Purge Task MMON
ADR Space Management Statistics Flush MMON
ARCH initialization ARC*
ASH Progressive Flusher (KEWA) MMON
ASM Audittrail cleanup MMON
AWR PDB Auto Flush Task MMON
AWR PDB Auto Purge Task MMON
AWR Raw Metrics Capture GEN1
Action-Based process Test GEN0
Archiver disconnect ARCH
Auxilary ipc finish gen0 action GEN0
Auxilary ipc init gen0 action GEN0
Auxilary ipc intr gen0 action GEN0
Auxilary ipc message gen0 action GEN0
Auxilary ipc timedout gen0 action GEN0
BA container GEN0 action GEN0
CLI AutoPartition MMON
CLI Create All Slave Tasks GEN0
CLI Create One Slave Task GEN0
Check for async in-memory job messages CJQ0
Cleanup of unpinned KGL handles MMON
Column-Level Statistics flush MMON
DBRM ADG in-memory state refresh DBRM
DBWO timeout kcbifc DBW0
DBWR write buffers DBW*|BW*
DDE Periodic Dump Scheduler MMON
DMON do critical instance eval and registration DMON
DSKM fini DSKM
DSKM init DSKM
DSKM procures HCA loadavg and computes offloaded write thresh DSKM
DTrace based Kernel IO  Outlier  Processing GEN0
Direct NFSv4 RENEW lease operation GEN0
Execute on-demand tuning task (KESTS) MMON
Free java patching locks LCK0
GEN0 Master Check GEN0
Get java patching locks LCK0
Hang Manager parameters GEN0
ILM check MMON
ILM cleanup MMON
ILM row access flush MMON
ILM segment access flush MMON
IMCO ADO action IMCO
IMCO FastStart Defer Write Scheduler IMCO
IMCO Trickle Repopulate IMCO
IMCO action IMCO
IMCO cycle action IMCO
IMCO global dictionary action IMCO
IMCO worker action IMCO
Inactive Account Time Job GEN0
Index usage tracking statistics flush MMON
Initiate KSBCITST TEST
KEWR SlavePool Test MMON Main MMON
KJBFP PBR logfile scan LMFC
KJBFP PBR recovery LMFC
KJBFP PBR writer main LMFC
KJBFP increment PRI LMFC
KJBFP pbr logFile CLose *
KJFM update process heartbeat LCK*|DIA*|LGWR|CKPT|DBRM|IPC0
KQLM interrupt action LCK1
KQLM invalidation instance lock operation LCK1
KSB GEN1 init GEN1
KSGL initialize service IPC0
KSGL mount in IPC0 IPC0
KSGL node exit IPC0
KSGL node join IPC0
KSGL notify IPC0 IPC0
KSGL timeout IPC0
KSIPC Grp Refresh action IPC0
KSIPC MGA Segment Check IPC0
KSIPC finish action IPC0
KSIPC initialize server IPC0
KSIPC interrupt action IPC0
KSIPC msg action IPC0
KSIPC reconfig action IPC0
KSIPC shutdown action IPC0
KSIPC timeout action IPC0
KSM SGA slaves spawn GEN0
KSRMA RMA OP IPC0
KSRMA Recovery Log Allocation IPC0
KSRMA mount IPC0
KSU GUID MAC Address update GEN0
LGWR flush workers LGWR
LGWR initialization LGWR
MMON request to purge LTXID history table MMON
Monitor initialization TMON
Monitor wakeup TMON
Multi procs per DTP UTMU
Network Server forced NSS*
Network Server shutdown NSS*
PDB SGA init GEN0
PDB close abort GEN0
PMON notify IPC0 of process failure IPC0
PQ: Adjust Slave Pool MMON
Payload action to BG RMON
Process new DBs that join ASM locally DIA*
RMON BG Driver RMON
RMON Init Action RMON
RTTD initialization RTTD
Real-Time ADDM Trigger MMON
Redo writer quiesce IMC on standby LGWR
Refresh active service cache MMON
Report Capture Daemon MMON
Report Capture Test (KERPI) MMON
SGA deferred allocated granules Initialization MMAN
SGA deferred allocated granules move MMAN
SMON_SCN_TIME Copy to PDBs MMON
SQL Memory Management Calculation DBRM
Spawn processes on behalf of someone else GEN0
Standby media recovery info cleanup LGWR
Suspended session cleanup GEN0
Sweep PL/SQL incidents MMON
Switchover/PDB relocate message channel subscribe LGWR
TPZ initialization TPZ*
Test Driver wakeup RTTD
Test Process wakeup TPZ*
Timeout interrupt action RBAL
Triton Session Cleanup MMON
UMF Auto Task Pool Queue Server MMON
UMF Auto Task Pool Scheduler MMON
UTS Async Dump GEN0
Volume Resource Action GEN0
Wait event outlier detection GEN0
XStream timeout action GEN0
acquire enq during pdbopen by HARIM DBW0
action for buddy instance RMS0
action to cleanup buddy instance context RMS0
check for KJCI cross-instance requests *
cleaning up workload information for optimizer MMON
clear the dependent scn DBRM
dblink logon table cleanup MMON
enter / exit graph test specified wait *
event nfy timeout action GEN0
event outlier dump info. GEN0
extend quarantine area GEN0
flushing workload information for optimizer MMON
free PX memory chunks in background PXMN
get/release open thread enqueue DBW*|BW*
init function for LCK1 *
initiate block repair GEN0
kcb DW object cooling GEN0
kcbz background redodump GEN0
kcbz update TSE bh CKPT
kill client GEN0
ksim cache line update LCK0
ksim instance group membership notifier *
kxfp remote slave spawn recv function PXMN
light-weight checks for optimizer statistics advisor MMON
mira CKPT channel CKPT
mount/dismount all db files DBW*|BW*
pdb event stats action GEN0
periodic PDB tasks GEN0
pmon dtp init PMON|CLMN
prespawn clean check GEN0
prespawn init check GEN0
prespawn timeout check GEN0
register to node local process group RBAL
shutdown RMON process RMON
sync PDB DBW0
threshold reloading MMON
unit test DBW0

oracle如何判定统计信息陈旧

本文原始地址:http://www.askmaclean.com/?p=18742

注意自动收集统计信息是从10g开始的,10g以前版本默认不自动收集统计信息。

对于自动收集统计信息而言需要知道统计信息是否陈旧stale ,判定陈旧的标准是对应的表上的数据修改超过10%(删除或插入或更新10%或以上数据行)。 这里oracle是如何知道修改超过10%的?

 

SGA的shared pool存有SQL的statistics情况,对应的有SQL处理的行数,SMON进程定期将这些信息刷到表SYS.MON_MODS$基表中(参考拙作: http://www.askmaclean.com/archives/smon-flush-dml-statistics-mon-mods.html):SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$),
同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中,并清空原MON_MODS$中的数据。
MON_MODS_ALL$作为dba_tab_modifications视图的数据来源,起到辅助统计信息收集的作用,详见拙作<Does GATHER_STATS_JOB gather all objects’ stats every time?>

这样基于之前的统计信息中的表的行数(dba_tables.num_rows),对比 MON_MODS_ALL$(dba_tab_modifications)中的update、delete、insert、truncate信息就可以知道该表从上一次收集统计信息到现在做了多少百分比的修改,若该百分比超过10%则判定为stale陈旧,否则为不陈旧。陈旧的统计信息会在自动收集统计信息时再次被收集。

 

 

DOP degree of parallelism的设计算法 ​​​​

直击oracle内核代码算法,对于11g以后的自动Parallelism算法一般只能用10053 trace来研究其算法。这里通过直接查看oracle源码设计文档,我们可以得到DOP degree of parallelism的设计算法

 

8358fa4fgy1fcr7ivo81lj20zq0kijwk

12CR2 vs 11gR2 新增optimizer 参数列表

_optimizer_adaptive_plan_control 0
_optimizer_adaptive_plans_continuous FALSE
_optimizer_adaptive_plans_iterative FALSE
_optimizer_adaptive_random_seed 0
_optimizer_ads_for_pq FALSE
_optimizer_ads_result_cache_life 3600
_optimizer_ads_spd_cache_owner_limit 64
_optimizer_ads_use_partial_results TRUE
_optimizer_ads_use_spd_cache TRUE
_optimizer_aggr_groupby_elim TRUE
_optimizer_ansi_join_lateral_enhance TRUE
_optimizer_ansi_rearchitecture TRUE
_optimizer_band_join_aware TRUE
_optimizer_batch_table_access_by_rowid TRUE
_optimizer_bushy_cost_factor 100
_optimizer_bushy_fact_dim_ratio 20
_optimizer_bushy_fact_min_size 100000
_optimizer_bushy_join off
_optimizer_cbqt_or_expansion ON
_optimizer_cluster_by_rowid TRUE
_optimizer_cluster_by_rowid_batch_size 100
_optimizer_cluster_by_rowid_batched TRUE
_optimizer_cluster_by_rowid_control 129
_optimizer_control_shard_qry_processing 65534
_optimizer_cube_join_enabled TRUE
_optimizer_db_blocks_buffers 0
_optimizer_dsdir_usage_control 0
_optimizer_eliminate_subquery TRUE
_optimizer_enable_plsql_stats TRUE
_optimizer_enhanced_join_elimination TRUE
_optimizer_gather_feedback TRUE
_optimizer_gather_stats_on_load TRUE
_optimizer_generate_ptf_implied_preds TRUE
_optimizer_generate_transitive_pred TRUE
_optimizer_hll_entry 4096
_optimizer_hybrid_fpwj_enabled TRUE
_optimizer_inmemory_access_path TRUE
_optimizer_inmemory_autodop TRUE
_optimizer_inmemory_bloom_filter TRUE
_optimizer_inmemory_capture_stored_stats TRUE
_optimizer_inmemory_cluster_aware_dop TRUE
_optimizer_inmemory_gen_pushable_preds TRUE
_optimizer_inmemory_minmax_pruning TRUE
_optimizer_inmemory_pruning_ratio_rows 100
_optimizer_inmemory_quotient 0
_optimizer_inmemory_table_expansion TRUE
_optimizer_inmemory_use_stored_stats AUTO
_optimizer_interleave_or_expansion TRUE
_optimizer_key_vector_aggr_factor 75
_optimizer_key_vector_pruning_enabled TRUE
_optimizer_multi_table_outerjoin TRUE
_optimizer_multicol_join_elimination TRUE
_optimizer_nlj_hj_adaptive_join TRUE
_optimizer_null_accepting_semijoin TRUE
_optimizer_partial_join_eval TRUE
_optimizer_performance_feedback OFF
_optimizer_proc_rate_level BASIC
_optimizer_proc_rate_source DEFAULT
_optimizer_reduce_groupby_key TRUE
_optimizer_strans_adaptive_pruning TRUE
_optimizer_synopsis_min_size 2
_optimizer_undo_cost_change 12.2.0.1
_optimizer_union_all_gsets TRUE
_optimizer_unnest_scalar_sq TRUE
_optimizer_use_feedback_for_join FALSE
_optimizer_use_gtt_session_stats TRUE
_optimizer_use_histograms TRUE
_optimizer_use_table_scanrate HADOOP_ONLY
_optimizer_use_xt_rowid TRUE
_optimizer_vector_base_dim_fact_factor 200
_optimizer_vector_cost_adj 100
_optimizer_vector_fact_dim_ratio 10
_optimizer_vector_min_fact_rows 10000000
_optimizer_vector_transformation TRUE
optimizer_adaptive_plans TRUE
optimizer_adaptive_reporting_only FALSE
optimizer_adaptive_statistics FALSE
optimizer_features_enable 12.2.0.1
optimizer_inmemory_aware TRUE

诗檀学院 11g ocp 051 052 053 全套题库中英文对照详解下载

【诗檀学院】Oracle 11g OCP-051考试题库中英文对照详解

【诗檀学院】Oracle 11g OCP-052考试题库中英文对照详解

【诗檀学院】Oracle 11g OCP-053考试题库中英文对照详解

prm-dul data unloader tool manual user guide 0.4

prm-dul data unloader tool manual user guide 0.4

 

http://zcdn.askmaclean.com/prm-dul%20data%20unloader%20tool%20manual%20user%20guide%200.4.pdf

【诗檀学院】11g OCP 考试题库分析

【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解051  http://t.askmaclean.com/thread-4946-1-1.html

【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解 http://t.askmaclean.com/thread-4909-1-1.html

【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解052 http://t.askmaclean.com/thread-4944-1-1.html

 

诗檀IT学院 全套oracle 11g ocm学习材料新年大礼包

新的一年新气象,附上诗檀IT学院 全套oracle 11g ocm学习材料新年大礼包。

所有学习材料

下载地址:https://pan.baidu.com/s/1o7OmfJ8

下载地址:https://pan.baidu.com/s/1o7OmfJ8

下载地址:https://pan.baidu.com/s/1o7OmfJ8

内容包括三个部分:

1、ocm学习所需要的安装介质材料

 

qq%e5%9b%be%e7%89%8720170104121207

 

2、诗檀学院定制 ocm学习Vbox虚拟机模板ova文件,可直接导入vbox作为学习虚拟机使用

密码统一为:dbdao123

密码统一为:dbdao123

密码统一为:dbdao123

qq%e5%9b%be%e7%89%8720170104121329

 

3、诗檀学院编写的11g ocm教材全套

qq%e5%9b%be%e7%89%8720170104121230

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569