从上图中可以看到在2012年 oow期间12c的搜索趋势出现了一个小高潮，在2013年6月迎来了爆发点一路攀升，目前搜索量已不亚于”Oracle 11g”。

 

从地区上看 不管是12c还是11g，最感兴趣的地区 始终是印度 的卡纳塔克邦和安得拉邦 2个地区，班加罗尔市。

三哥三姐不愧为IT领跑者，对Oracle 12c的研究走到世界最前列！壮哉，我大印度IT产业！

 

 

 

 

 

美国本土的话主要集中在 加利福尼亚和 马塞诸塞 2个州。

在介绍12cR1的这个优化器特性之前，我们先来看如下的例子：

 

SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000;  

Table created.  

SQL> alter table sample add t2 number;

Table altered.

update sample set t2=dbms_random.value(1,999999);

900000 rows updated.

SQL> commit;
Commit complete.

SQL> create index ind_t1 on sample(t1) nologging tablespace users;
Index created.

SQL> create index ind_t2 on sample(t2) nologging tablespace users;
Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.

SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';

    BLOCKS   NUM_ROWS
---------- ----------
      9107     902319

SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';

CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
             1370        2004        900000 IND_T1
           899317        4148        900000 IND_T2

alter session set events '10046 trace name context forever,level 12';

set autotrace traceonly;

alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=true;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.68       8.56      12754    1810330          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.68       8.56      12754    1810330          0      899999

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID BATCHED SAMPLE (cr=1810330 pr=12754 pw=0 time=20413784 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63873 pr=4150 pw=0 time=4655140 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                     11388        0.00          1.70
  SQL*Net message from client                 60001        0.00          8.95
  db file parallel read                         197        0.00          0.00

 alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=false;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.70       8.82      12754    1810333          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.70       8.82      12754    1810333          0      899999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID SAMPLE (cr=1810333 pr=12754 pw=0 time=25464232 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63874 pr=4150 pw=0 time=4404956 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  db file sequential read                     12754        0.00          1.85
  SQL*Net message from client                 60001        0.00          8.95

 

 

我们看到了一个陌生的operation ” TABLE ACCESS BY INDEX ROWID BATCHED” 注意 这个Batched是 之前的版本没有的。

 

须知 TABLE ACCESS BY ROWID 这种常见操作是从 子数据集合(例如INDEX中)获得必要的ROWID， 以便在表上定位到对应的行fetch对应数据。 若该行不在Buffer Cache中，则该 Table Access by ROWID的数据集合需要等待必要的IO完成才能处理下一个ROWID。 在很多场景中IO延迟在这里成为重要的瓶颈， 由于不管是RANGE SCAN、FULL SCAN还是Access By Rowid默认均使用DB FILE SEQUENTIAL READ所以如果访问的数据恰巧不在内存里+ 它要Fetch大量的数据行则 往往其整体相应速度和逻辑读要多于全表扫描。

 

常见在以下三种场景中多需要Table Access by Rowid的数据源访问：

1. Index Range SCan
2. Bitmap index plan
3. Nested Loop Join

 

所以Oracle开发人员想到了要使用prefetch预读取数据源来提升性能，通过遍历ROWID以找出那些需要完成的IO操作并prefetch其数据源，将那些数据块预先读入。这里的实现上应当是通过buffer 驱动数据源哪里获得的ROWID，之后通过遍历这些 ROWID对应的的找到需要做物理读的数据块，并使用向量Io操作(例如上文中的db file parallel read)来prefetch这些数据块到buffer cache中，这样TABLE ACCESS By ROWID的访问就可以保证必要的块(主要是表块)均在buffer cache中。

使用此Batching Io特性可以有效减少IO延迟造成的性能损耗，但并不是任何场景都有效。由于实际能buffer的ROWID是有限的，而且是在不知道哪些ROWID对应需要IO哪些不需要的情况下全部都复制到buffer中，所以如果buffer的所有ROWID对应只需要少量的IO，则该IO Batching特性带来的性能改善将最小化。 亦或者遇到的ROWID对应的数据块全部在内存在 一点Io都不需要，则这种prefetch数据的行为有画蛇添足之嫌，反倒会徒增CPU时间片。

 

目前控制该特性的 优化器参数为_ optimizer_batch_table_access_by_rowid，该参数2个选项 TRUE /FALSE负责控制是否启用Table access by ROWID IO batching。

 

还可以通过 BATCH_TABLE_ACCESS_BY_ROWID和 NO_BATCH_TABLE_ACCESS_BY_ROWID 2个HINT来控制是否启用该特性， HINT的优先级高于参数optimizer_batch_table_access_by_rowid。不过目前在12.1.0.1.0上测试该HINT仍有一些问题。

 

 

 

SQL> select * from V$VERSION where rownum=1;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

  1* select name from v$SQL_HINT where name like '%BATCH%'

NAME
----------------------------------------------------------------
NLJ_BATCHING
NO_NLJ_BATCHING
BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID

SQL> alter session set "_optimizer_batch_table_access_by_rowid"=true;

Session altered.

SQL>   select /*+     index(sample ind_t2)  NO_BATCH_TABLE_ACCESS_BY_ROWID */ * from sample where t2 between 1 and 999997;

899999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3882332507

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   900K|    23M|   903K  (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE |   900K|    23M|   903K  (1)| 00:00:36 |
|*  2 |   INDEX RANGE SCAN                  | IND_T2 |   900K|       |  4155   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

 

【12c新特性】12cR1中新加入的Statistic

 

select  A.* from v$sysstat A  where A.name not in (select B.name from v$sysstat@db_11gR2 B);

 

STATISTIC#	NAME	CLASS	VALUE	STAT_ID	CON_ID
52	physical read partial requests	8	0	286702467	0
54	physical write requests optimized	8	0	2483607112	0
55	physical write request redirties	8	0	4146911311	0
56	physical write total bytes optimized	8	0	4085960041	0
57	physical write partial requests	8	0	1535615968	0
70	ka messages sent	32	0	4222258831	0
71	ka grants received	32	0	2310418695	0
81	consistent gets pin	8	248409	1168838199	0
82	consistent gets pin (fastpath)	8	240756	2910712465	0
83	consistent gets examination	8	46775	1966540185	0
84	consistent gets examination (fastpath)	8	45808	1990445227	0
86	fastpath consistent get quota limit	40	0	560973176	0
178	flashback securefile cache read optimizations for block new	8	0	955255216	0
179	flashback securefile direct read optimizations for block new	8	0	963322245	0
180	physical reads cache for securefile flashback block new	8	0	2429466467	0
181	physical reads direct for securefile flashback block new	8	0	3121545084	0
184	data warehousing scanned objects	8	0	247471814	0
185	data warehousing scanned chunks	8	0	3880771368	0
186	data warehousing scanned chunks – memory	8	0	1765983694	0
187	data warehousing scanned chunks – flash	8	0	3811273611	0
188	data warehousing scanned chunks – disk	8	0	1684884558	0
189	data warehousing evicted objects	8	0	1827708704	0
190	data warehousing evicted objects – cooling	8	0	1769197766	0
191	data warehousing evicted objects – replace	8	0	547725926	0
192	data warehousing cooling action	8	0	2905230597	0
200	Streaming Stall Reap	2	0	3489516369	0
201	Streaming No-Stall Reap	2	0	2378677367	0
210	redo writes (group 0)	2	164	2952991530	0
211	redo writes (group 1)	2	16	1083730459	0
212	redo writes (group 2)	2	0	2759403975	0
213	redo writes (group 3)	2	0	3475566097	0
214	redo writes (group 4)	2	0	1807859197	0
215	redo writes (group 5)	2	0	1792560815	0
216	redo writes (group 6)	2	0	1695728381	0
217	redo writes (group 7)	2	0	1074957749	0
218	redo writes adaptive all	2	180	3061077218	0
219	redo writes adaptive worker	2	180	3220418890	0
221	redo blocks written (group 0)	2	995	2520028696	0
222	redo blocks written (group 1)	2	301	3244346714	0
223	redo blocks written (group 2)	2	0	1273391004	0
224	redo blocks written (group 3)	2	0	1050845280	0
225	redo blocks written (group 4)	2	0	2795831152	0
226	redo blocks written (group 5)	2	0	615604096	0
227	redo blocks written (group 6)	2	0	764128333	0
228	redo blocks written (group 7)	2	0	435637049	0
229	redo write size count (   4KB)	2	145	4206847440	0
230	redo write size count (   8KB)	2	15	3604386338	0
231	redo write size count (  16KB)	2	11	1937637258	0
232	redo write size count (  32KB)	2	7	2689404784	0
233	redo write size count (  64KB)	2	0	3887142398	0
234	redo write size count ( 128KB)	2	2	2998280397	0
235	redo write size count ( 256KB)	2	0	2120393820	0
236	redo write size count ( 512KB)	2	0	3912524051	0
237	redo write size count (1024KB)	2	0	395882065	0
238	redo write size count (inf)	2	0	4145578355	0
251	redo synch time overhead (usec)	128	3142053	3961087021	0
252	redo synch time overhead count (  2ms)	128	35	1771370497	0
253	redo synch time overhead count (  8ms)	128	0	2324186582	0
254	redo synch time overhead count ( 32ms)	128	0	2882285036	0
255	redo synch time overhead count (128ms)	128	0	1234629759	0
256	redo synch time overhead count (inf)	128	3	2239006192	0
261	redo write info find	2	38	3584739253	0
262	redo write info find fail	2	0	553778103	0
267	gc cr blocks served with BPS	40	0	1600220233	0
275	gc current blocks served with BPS	40	0	1004484383	0
278	gc cr blocks received with BPS	40	0	3270643842	0
281	gc current blocks received with BPS	40	0	301773697	0
282	gc ka grants received	40	0	912334553	0
283	gc ka grant receive time	40	0	3746639269	0
289	gc cleanout saved	40	0	4119317321	0
290	gc cleanout applied	40	0	1976898865	0
291	gc cleanout no space	40	0	522936568	0
293	gc reader bypass waits	40	0	1120557156	0
298	gc force cr disk read	40	395	1058102273	0
307	AVM files created count	128	0	1887082337	0
308	AVM files deleted count	128	0	4223523824	0
309	AVM file bytes allocated	128	0	3731650962	0
310	AVM au bytes allocated	128	0	3441520794	0
311	AVM file bytes deleted	128	0	1514042146	0
312	AVM non-flash bytes requested	128	0	1829484955	0
313	AVM flash bytes requested	128	0	965137504	0
314	AVM bytes for file maps	128	0	2904743103	0
315	AVM bytes read from flash	128	0	4263147678	0
316	AVM bytes read from disk	128	0	2004986892	0
317	AVM count when 10% of buckets in pb	128	0	652947275	0
318	AVM count when 25% of buckets in pb	128	0	3588709547	0
319	AVM count when 50% of buckets in pb	128	0	2879014823	0
320	AVM count when 75% of buckets in pb	128	0	1964315023	0
321	AVM count when 90% of buckets in pb	128	0	226051874	0
322	AVM count – borrowed from other node	128	0	4037843577	0
323	AVM count – searched in pb	128	0	4000147916	0
324	AVM spare statistic 1	128	0	47653185	0
325	AVM spare statistic 2	128	0	3191674657	0
326	AVM spare statistic 3	128	0	2665872976	0
327	AVM spare statistic 4	128	0	2816010972	0
328	AVM spare statistic 5	128	0	4250363583	0
329	AVM spare statistic 6	128	0	3756487597	0
330	AVM spare statistic 7	128	0	2604881032	0
331	AVM spare statistic 8	128	0	176682480	0
345	storage index soft misses in bytes	8	0	2809906174	0
353	cell num smart IO sessions in rdbms block IO due to open fail	64	0	1611570469	0
363	cell num smartio automem buffer allocation attempts	64	0	145506540	0
364	cell num smartio automem buffer allocation failures	64	0	727055891	0
365	cell num smartio transient cell failures	64	0	2276204331	0
366	cell num smartio permanent cell failures	64	0	299072157	0
367	cell num bytes of IO reissued due to relocation	64	0	3754903472	0
388	recovery marker	2	0	2982845773	0
389	cvmap unavailable	2	0	3849353583	0
390	recieve buffer unavailable	2	0	3480097050	0
462	tracked transactions	128	0	4230695614	0
463	foreground propagated tracked transactions	128	0	2081753160	0
464	slave propagated tracked transactions	128	0	275867045	0
465	large tracked transactions	128	0	1755433832	0
466	very large tracked transactions	128	0	4033000846	0
467	fbda woken up	128	0	138331311	0
468	tracked rows	128	0	943642878	0
469	CLI Flush	128	73	670819718	0
470	CLI BG attempt Flush	128	73	2751550570	0
471	CLI Client Flush	128	0	2418073855	0
472	CLI Imm Wrt	128	0	47996927	0
473	CLI Buf Wrt	128	0	1466815534	0
474	CLI Thru Wrt	128	2	2721289668	0
475	CLI Prvtz Lob	128	0	1688196485	0
476	CLI SGA Alloc	128	32	2076026298	0
477	CLI BG ENQ	128	73	2537508108	0
478	CLI BG Fls done	128	2	1898500432	0
479	CLI Flstask create	128	73	4150293767	0
480	CLI bytes fls to table	128	1376	872375576	0
481	CLI bytes fls to ext	128	0	2251457522	0
482	Heatmap SegLevel – Write	128	0	2305866014	0
483	Heatmap SegLevel – Full Table Scan	128	0	3635715785	0
484	Heatmap SegLevel – IndexLookup	128	0	4088384827	0
485	Heatmap SegLevel – TableLookup	128	0	26595750	0
486	Heatmap SegLevel – Flush	128	0	3466367062	0
487	Heatmap SegLevel – Segments flushed	128	0	2885452372	0
504	KTFB alloc req	128	0	3506976771	0
505	KTFB alloc space (block)	128	0	254882839	0
506	KTFB alloc time (ms)	128	0	573758863	0
507	KTFB free req	128	25	1286187813	0
508	KTFB free space (block)	128	1528	1243401580	0
509	KTFB free time (ms)	128	266	408510199	0
510	KTFB apply req	128	16	2829590811	0
511	KTFB apply time (ms)	128	902	1827629900	0
512	KTFB commit req	128	9	2268695636	0
513	KTFB commit time (ms)	128	16659	3807444826	0
514	KTFB alloc myinst	128	0	637674164	0
515	KTFB alloc steal	128	0	3819194715	0
516	KTFB alloc search FFB	128	0	1572111054	0
522	Heatmap BlkLevel Tracked	128	0	417269865	0
523	Heatmap BlkLevel Not Tracked – Memory	128	0	3244920981	0
524	Heatmap BlkLevel Not Updated – Repeat	128	0	1235344528	0
525	Heatmap BlkLevel Flushed	128	0	3201601810	0
526	Heatmap BlkLevel Flushed to SYSAUX	128	0	153666168	0
527	Heatmap BlkLevel Flushed to BF	128	0	329477246	0
528	Heatmap BlkLevel Ranges Flushed	128	0	3869669302	0
529	Heatmap BlkLevel Ranges Skipped	128	0	120128078	0
530	Heatmap BlkLevel Flush Task Create	128	0	1236100146	0
531	Heatmap Blklevel Flush Task Count	128	0	1887039906	0
568	index compression (ADVANCED LOW) prefix change at block	128	0	1089998764	0
569	index compression (ADVANCED LOW) prefix no change at block	128	0	2879842113	0
570	index compression (ADVANCED LOW) blocks not compressed	128	0	3703793538	0
571	index compression (ADVANCED LOW) reorg avoid split	128	0	2501129012	0
573	index compression (ADVANCED HIGH) leaf block splits avoided	128	0	228768206	0
575	index compression (ADVANCED HIGH) leaf block 90_10 splits faile	128	0	3445701516	0
612	HSC OLTP Compression wide compressed row pieces	128	0	784760009	0
669	EHCC Used on ZFS Tablespace	128	0	2536989047	0
670	EHCC Used on Pillar Tablespace	128	0	3901974308	0
671	EHCC Conventional DMLs	128	0	547882683	0
672	EHCC Block Compressions	128	0	2852097326	0
673	EHCC Attempted Block Compressions	128	0	726324667	0
674	SecureFiles DBFS Link Operations	128	0	408804124	0
675	SecureFiles Move to DBFS Link	128	0	2159528439	0
676	SecureFiles Copy from DBFS Link	128	0	3313150606	0
677	SecureFiles Get DBFS Link Reference	128	0	3776855272	0
678	SecureFiles Put DBFS Link Reference	128	0	1020980477	0
679	SecureFiles Implicit Copy from DBFS Link	128	0	2864160252	0
680	SecureFiles DBFS Link streaming reads	128	0	2291010287	0
681	SecureFiles DBFS Link Overwrites	128	0	3546571658	0
682	index cmph ld, CU under-est	128	0	3487869306	0
683	index cmph ld, CU fit, add rows	128	0	3074245919	0
684	index cmph ld, CU fit	128	0	312995821	0
685	index cmph ld, CU over-est	128	0	3287792462	0
686	index cmph ld, retry in over-est	128	0	2794871331	0
687	index cmph ld, CU negative comp	128	0	747638515	0
688	index cmph ld, lf blks flushed	128	0	3933169485	0
689	index cmph ld, lf blks w/o CU	128	0	2058955770	0
690	index cmph ld, lf blks w/o unc r	128	0	1877031790	0
691	index cmph ld, lf blks w/ und CU	128	0	500852118	0
692	index cmph ld, rows compressed	128	0	2461980696	0
693	index cmph ld, rows uncompressed	128	0	1487477542	0
694	index cmph gencu, uncomp sentinals	128	0	3972713215	0
707	Number of NONE redactions	1	0	2910416594	0
708	Number of FULL redactions	1	0	4021003316	0
709	Number of PARTIAL redactions	1	0	2340397149	0
710	Number of FORMAT_PRESERVING redactions	1	0	2739332778	0
711	Number of RANDOM redactions	1	0	2308447938	0
712	Number of REGEXP redactions	1	0	3081010860	0
795	OLAP Paging Manager Cache Hit	64	0	249788237	0
796	OLAP Paging Manager Cache Miss	64	0	2631123639	0
797	OLAP Paging Manager New Page	64	0	1639856938	0
798	OLAP Paging Manager Cache Write	64	0	2077400790	0
799	OLAP Session Cache Hit	64	0	3766195924	0
800	OLAP Session Cache Miss	64	0	1569481295	0
801	OLAP Aggregate Function Calc	64	0	3109348342	0
802	OLAP Aggregate Function Precompute	64	0	352609299	0
803	OLAP Aggregate Function Logical NA	64	0	2269374713	0
804	OLAP Paging Manager Pool Size	64	0	3621573995	0
805	OLAP Import Rows Pushed	64	0	3846608240	0
806	OLAP Import Rows Loaded	64	0	2782483173	0
807	OLAP Row Source Rows Processed	64	0	1032576542	0
808	OLAP Engine Calls	64	0	4076583183	0
809	OLAP Temp Segments	64	0	3547622716	0
810	OLAP Temp Segment Read	64	0	1927042645	0
811	OLAP Perm LOB Read	64	0	2809117898	0
812	OLAP Paging Manager Cache Changed Page	64	0	2200669834	0
813	OLAP Fast Limit	64	0	283242358	0
814	OLAP GID Limit	64	0	1120107350	0
815	OLAP Unique Key Attribute Limit	64	0	3812252850	0
816	OLAP INHIER Limit	64	0	2844959843	0
817	OLAP Full Limit	64	0	2189109011	0
818	OLAP Custom Member Limit	64	0	3030144806	0
819	OLAP Row Id Limit	64	0	3437716459	0
820	OLAP Limit Time	64	0	2592657924	0
821	OLAP Row Load Time	64	0	953132701	0

【12c新特性】12c中新后台进程，主要包括但不局限于：

 

OFSD	Oracle File Server BG
RMON	rolling migration monitor
IPC0	IPC Service 0
BW36	db writer process 36
BW99	db writer process 99
TMON	Transport Monitor
RTTD	Redo Transport Test Driver
TPZ1	Test Process Z1
TPZ2	Test Process Z2
TPZ3	Test Process Z3
LREG	Listener Registration
AQPC	AQ Process Coord
FENC	IOServer fence monitor
VUBG	Volume Driver Umbilical Background
SCRB	ASM Scrubbing Master

 

 

可以看到这里LREG进程开始负责对Listener Registration监听器的注册：

Service registration enables the listener to determine whether a database service and its service handlers are available. A service handler is a dedicated server process or dispatcher that acts as a connection point to a database. During registration, the LREG process provides the listener with the instance name, database service names, and the type and addresses of service handlers. This information enables the listener to start a service handler when a client request arrives.

Figure 16-5 shows two databases, each on a separate host. The database environment is serviced by two listeners, each on a separate host. The LREG process running in each database instance communicates with both listeners to register the database.

 

截止目前12c的官方文档中的配图还有问题， 图示还是用PMON注册监听。

 

 

Reference:

E16655_01/E16655_01/server.121/e17633/dist_pro.htm#CHDIBHAD

12c中新加入的Enqueue Lock列表如下：

 

其中值得注意的 ，为CDB加入了不少enqueue

BC ==》 Container lock held while creating/dropping  a container

PB ==》 Enqueue used to synchronize PDB DDL operations

select A.* from ksqst_12cR1 A where  A.KSQSTTYP not in (select B.KSQSTTYP from ksqst_11gR2@MACDBN  B);

 

AC	Synchronizes partition id
AQ	kwsptGetOrMapDqPtn
AQ	kwsptGetOrMapQPtn
BA	subscriber access to bitmap
BC	Container lock held while creating a container
BC	Container lock held while dropping a container
BC	Group lock held while creating a contained file
BC	Group lock held while creating a container
BC	Group lock held while dropping a container group
BI	Enqueue held while a contained file is cleaned up or deleted
BI	Enqueue held while a contained file is created
BI	Enqueue held while a contained file is identified
BV	Enqueue held while a container group is rebuilding
BZ	Enqueue held while a contained file is resized
CB	Synchronizes accesses to the CBAC roles cached in KGL
CC	decrypting and caching column key
CP	Synchronization
FH	Serializes flush of ILM stats to disk
FO	Synchronizes various Oracle File system operations
IC	Gets a unique client ID
IF	File Close
IF	File Open
IP	Enqueue used to synchronize instance state changes for PDBs
KI	Synchronizes Cross-Instance Calls
MC	Serializes log creation/destruction with log flushes
MF	Serializes flushes for a SGA log in bkgnd
MF	Serializes flushes for a single SGA log – client
MF	Serializes flushes for a single SGA log – destroy
MF	Serializes flushes for a single SGA log – error earlier
MF	Serializes flushes for a single SGA log – space lack
MF	Serializes multiple processes in creating the swap space
OP	Synchronizing access to ols$profile when deleting unused profiles
OP	Synchronizing access to ols$user when inserting user entries
PA	lock held for during modify a privilege capture
PA	lock held for during reading privilege captur status
PB	Enqueue used to synchronize PDB DDL operations
PQ	kwslbFreShadowShrd:LB syncronization with Truncate
PQ	kwsptChkTrncLst:Truncate
PQ	kwsptLoadDqCache: Add DQ Partitions.
PQ	kwsptLoadDqCache:Drop DQ Partitions.
PQ	kwsptLoadQCache: Add Q Partitions.
PQ	kwsptLoadQCache:Drop Q Partitions.
PQ	kwsptMapDqPtn:Drop DQ Partitions in foreground
PQ	kwsptMapQPtn: Add Q Partitions in foreground
PY	Database RTA info access on AVM
PY	Instance RTA info access on AVM
RA	Flood control in RAC. Acquired in no-wait.
RQ	AQ indexed cached commit
RQ	AQ uncached commit WM update
RQ	AQ uncached dequeue
RQ	Cross process updating disk
RQ	Cross(export) – truncate subshard
RQ	Cross(import) – free shadow shard
RQ	Dequeue updating scn
RQ	Enqueue commit rac cached
RQ	Enqueue commit uncached
RQ	Free shadow – Cross(import) shard
RQ	Parallel cross(update scn) – truncate subshard
RQ	Truncate – Cross(export) subshard
RZ	Synchronizes access to the foreign log cache while a structure is being inserted
RZ	Synchronizes access to the foreign log cache while a structure is being removed
SG	Synchronize access to ols$groups when creating a group
SG	Synchronize access to ols$groups when zlllabGroupTreeAddGroup does a read
SG	Synchronizing access to ols$groups when alter group parent
SG	Synchronizing access to ols$groups when dropping a group
ZS	lock held while writing to/renaming/deleting spillover audit file

【12c新特性】12c中新增的V$动态视图：

select A.view_name from v$fixed_view_definition A where A.view_name not in (select B.view_name from v$fixed_view_definition@11gR2 B)
and view_name like ‘GV%’ order by 1;

 

GV$AQ_BACKGROUND_COORDINATOR
GV$AQ_BMAP_NONDUR_SUBSCRIBERS
GV$AQ_CROSS_INSTANCE_JOBS
GV$AQ_JOB_COORDINATOR
GV$AQ_MESSAGE_CACHE
GV$AQ_MSGBM
GV$AQ_NONDUR_REGISTRATIONS
GV$AQ_NONDUR_SUBSCRIBER
GV$AQ_NONDUR_SUBSCRIBER_LWM
GV$AQ_NOTIFICATION_CLIENTS
GV$AQ_SERVER_POOL
GV$AQ_SUBSCRIBER_LOAD
GV$ASM_ACFSREPL
GV$ASM_ACFSREPLTAG
GV$ASM_ACFSTAG
GV$ASM_ACFS_SEC_ADMIN
GV$ASM_ACFS_SEC_CMDRULE
GV$ASM_ACFS_SEC_REALM
GV$ASM_ACFS_SEC_REALM_FILTER
GV$ASM_ACFS_SEC_REALM_GROUP
GV$ASM_ACFS_SEC_REALM_USER
GV$ASM_ACFS_SEC_RULE
GV$ASM_ACFS_SEC_RULESET
GV$ASM_ACFS_SEC_RULESET_RULE
GV$ASM_AUDIT_CLEANUP_JOBS
GV$ASM_AUDIT_CLEAN_EVENTS
GV$ASM_AUDIT_CONFIG_PARAMS
GV$ASM_AUDIT_LAST_ARCH_TS
GV$ASM_ESTIMATE
GV$BACKUP_NONLOGGED
GV$BTS_STAT
GV$BT_SCAN_CACHE
GV$BT_SCAN_OBJ_TEMPS
GV$CACHE
GV$CACHE_LOCK
GV$CACHE_TRANSFER
GV$CELL_OFL_THREAD_HISTORY
GV$CHANNEL_WAITS
GV$CLIENT_SECRETS
GV$CLONEDFILE
GV$CONTAINERS
GV$CON_SYSSTAT
GV$CON_SYSTEM_EVENT
GV$CON_SYSTEM_WAIT_CLASS
GV$CON_SYS_TIME_MODEL
GV$COPY_NONLOGGED
GV$DEAD_CLEANUP
GV$DG_BROKER_CONFIG
GV$EDITIONABLE_TYPES
GV$ENCRYPTION_KEYS
GV$FALSE_PING
GV$FLASHFILESTAT
GV$GES_DEADLOCKS
GV$GES_DEADLOCK_SESSIONS
GV$GG_APPLY_COORDINATOR
GV$GG_APPLY_READER
GV$GG_APPLY_RECEIVER
GV$GG_APPLY_SERVER
GV$GOLDENGATE_CAPABILITIES
GV$GOLDENGATE_CAPTURE
GV$GOLDENGATE_MESSAGE_TRACKING
GV$GOLDENGATE_TABLE_STATS
GV$GOLDENGATE_TRANSACTION
GV$HEAT_MAP_SEGMENT
GV$INSTANCE_PING
GV$IOS_CLIENT
GV$IO_OUTLIER
GV$KERNEL_IO_OUTLIER
GV$KSFQP
GV$LGWRIO_OUTLIER
GV$MAPPED_SQL
GV$NONLOGGED_BLOCK
GV$OFSMOUNT
GV$OFS_STATS
GV$OPTIMIZER_PROCESSING_RATE
GV$PATCHES
GV$PDBS
GV$PDB_INCARNATION
GV$PING
GV$PX_PROCESS_TRACE
GV$REPLAY_CONTEXT
GV$REPLAY_CONTEXT_LOB
GV$REPLAY_CONTEXT_SEQUENCE
GV$REPLAY_CONTEXT_SYSDATE
GV$REPLAY_CONTEXT_SYSGUID
GV$REPLAY_CONTEXT_SYSTIMESTAMP
GV$RO_USER_ACCOUNT
GV$RT_ADDM_CONTROL
GV$SCHEDULER_INMEM_MDINFO
GV$SCHEDULER_INMEM_RTINFO
GV$SESSIONS_COUNT
GV$SQL_DIAG_REPOSITORY
GV$SQL_DIAG_REPOSITORY_REASON
GV$SQL_MONITOR_SESSTAT
GV$SQL_MONITOR_STATNAME
GV$SQL_REOPTIMIZATION_HINTS
GV$SYS_REPORT_REQUESTS
GV$SYS_REPORT_STATS
GV$TEMPUNDOSTAT
GV$TSDP_SUPPORTED_FEATURE
GV$UNIFIED_AUDIT_TRAIL
GV$XSTREAM_APPLY_COORDINATOR
GV$XSTREAM_APPLY_READER
GV$XSTREAM_APPLY_RECEIVER
GV$XSTREAM_APPLY_SERVER
GV$XSTREAM_CAPTURE
GV$XSTREAM_MESSAGE_TRACKING
GV$XSTREAM_TRANSACTION
GV$XS_SESSIONS
GV$XS_SESSION_NS_ATTRIBUTE
GV$XS_SESSION_ROLE

 

 

【12c新特性】12cR1 diff 11gR2 Hidden Parameters

 

_ILM_FILTER_TIME	0	Upper filter time for ILM block compression
_ILM_FILTER_TIME_LOWER	0	Lower filter time for ILM block compression
_ILM_POLICY_NAME	FALSE	User specified ILM policy name
__data_transfer_cache_size	0	Actual size of data transfer cache
_abort_on_mrp_crash	FALSE	abort database instance when MRP crashes
_ac_enable_dscn_in_rac	FALSE	Enable Dependent Commit SCN tracking
_adaptive_scalable_log_writer_disable_worker_threshold	90	Percentage of overlap across multiple outstanding writes
_adaptive_scalable_log_writer_enable_worker_threshold	200	Increase in redo generation rate as a percentage
_adaptive_window_consolidator_enabled	TRUE	enable/disable adaptive window consolidator PX plan
_add_nullable_column_with_default_optim	TRUE	Allows add of a nullable column with default optimization
_adg_buffer_wait_timeout	10	Active Dataguard buffer wait time in cs
_adg_instance_recovery	TRUE	enable ADG instance recovery
_advanced_index_compression_options	0	advanced index compression options
_advanced_index_compression_options_value	20	advanced index compression options2
_advanced_index_compression_trace	0	advanced index compression trace
_allow_file_1_offline_error_1245	FALSE	don’t signal ORA-1245 due to file 1 being offline
_alter_common_user_schema	TRUE	allow local user to create objects in common schema
_am_container_filesystem_ausize	4194304	allocation unit size for non-ASM containers
_am_max_containers	0	maximum number of containers
_am_max_groups	0	maximum number of containers
_am_max_seg_bytes	4000	maximum number of bytes per array segment
_am_timeouts_enabled	TRUE	enable timeouts
_am_trace_buffer_size	131072	size of per-process I/O trace buffer
_appqos_cdb_setting	0	QoSM CDB Performance Class Setting
_appqos_po_multiplier	1000	Multiplier for PC performance objective value
_aq_disable_x	FALSE	AQ – Disable new cross processes at an instance
_aq_dq_sessions	20	Deq session count
_aq_eq_sessions	10	Enq session count
_aq_init_shards	5	Minimum enqueue shards per queue at an instance
_aq_precrt_partitions	0	Precreate Partitions
_aq_pt_processes	1	Partition background processes
_aq_stop_backgrounds	FALSE	Stop all AQ background processes
_aq_subshard_Size	2000	Sub Shard Size
_aqsharded_cache_limit	0	Limit for cached enqueue/dequeue operations
_array_cdb_view_enabled	TRUE	array mode enabled for CDB views
_asm_access	auto	ASM File access mechanism
_asm_allow_unsafe_reconnect	TRUE	attempt unsafe reconnect to ASM
_asm_allowdegeneratemounts	TRUE	Allow force-mounts of DGs w/o proper quorum
_asm_dba_spcchk_thld	20000	ASM Disk Based Allocation Space Check Threshold
_asm_disable_ufg_dump	FALSE	disable terminated umbilicus diagnostic
_asm_disable_ufgmemberkill	FALSE	disable ufg member kill
_asm_diskerr_traces	2	Number of read/write errors per disk a process can trace
_asm_diskgroups2		disk groups to mount automatically set 2
_asm_diskgroups3		disk groups to mount automatically set 3
_asm_diskgroups4		disk groups to mount automatically set 4
_asm_enable_xrov	FALSE	Enable XROV capability
_asm_global_dump_level	267	System state dump level for ASM asserts
_asm_healthcheck_timeout	180	seconds until health check takes action
_asm_network_timeout	1	Keepalive timeout for ASM network connections
_asm_networks		ASM network subnet addresses
_asm_nodekill_escalate_time	180	secs until escalating to nodekill if fence incomplete
_asm_noevenread_diskgroups		List of disk groups having even read disabled
_asm_procs_trace_diskerr	5	Number of processes allowed to trace a disk failure
_asm_proxy_startwait	60	Maximum time to wait for ASM proxy connection
_asm_remote_client_timeout	300	timeout before killing disconnected remote clients
_asm_resyncCkpt	1024	number of extents to resync before flushing checkpoint
_asm_scrub_limit	AUTO	ASM disk scrubbing power
_asm_skip_diskval_check	FALSE	skip client side discovery for disk revalidate
_asm_trace_limit_timeout	30000	Time-out in milliseconds to reset the number of traces per disk and the number of processes allowed to trace
_autotask_test_name	N/A	Name of current Autotask Test (or test step)
_aux_dfc_keep_time	1440	auxiliary datafile copy keep time in minutes
_awr_cdbperf_threshold	21	Setting for AWR CDBPERF Threshold
_awr_mmon_deep_purge_all_expired	FALSE	Allows deep purge to purge AWR data for all expired snapshots
_awr_pdb_registration_enabled	FALSE	Parameter to enable/disable AWR PDB Registration
_awr_remote_target_dblink		AWR Remote Target DBLink for Flushing
_backup_ksfq_bufmem_max	2.68E+08	maximum amount of memory (in bytes) used for buffers for backup/restore
_backup_min_ct_unused_optim	2097152	mimimun size in bytes of change tracking to apply unused space optimuzation
_bct_mrp_timeout	600	CTWR MRP wait timeout (seconds), zero to wait forever
_bct_public_dba_buffer_dynresize	2	allow dynamic resizing of public dba buffers, zero to disable
_bct_public_dba_buffer_maxsize	0	max buffer size permitted for public dba buffers, in bytes
_bg_spawn_diag_opts	0	background processes spawn diagnostic options
_block_level_offload_high_lat_thresh	40000	High Latency Threshold for Block Level Offload operations
_bloom_filter_size	0	bloom filter vector size (in KB)
_bloom_predicate_offload	TRUE	enables or disables bloom filter predicate offload to cells
_bloom_rm_filter	FALSE	remove bloom predicate in favor of zonemap join pruning predicate
_bloom_sm_enabled	FALSE	enable bloom filter optimization using slave mapping
_broadcast_scn_mode	1	broadcast-on-commit scn mode
_cache_orl_during_open	ALL	cache online logs
_cdb_compatible	TRUE	CDB Compatible
_cdb_rac_affinity	TRUE	rac affinity for parallel cdb operations
_cell_materialize_all_expressions	FALSE	Force materialization of all offloadable expressions on the cells
_cell_materialize_virtual_columns	TRUE	enable offload of expressions underlying virtual columns to cells
_cell_object_expiration_hours	24	flashcache object expiration timeout
_cell_offload_complex_processing	TRUE	enable complex SQL processing offload to cells
_cell_offload_expressions	TRUE	enable offload of expressions to cells
_cell_offload_sys_context	TRUE	enable offload of SYS_CONTEXT evaluation to cells
_cgs_comm_readiness_check	1	CGS communication readiness check
_cgs_memberkill_from_rim_instance	FALSE	allow a RIM instance to issue a CSS member kill
_cgs_msg_batch_size	4096	CGS message batch size in bytes
_cgs_msg_batching	TRUE	CGS message batching
_cgs_ticket_sendback	50	CGS ticket active sendback percentage threshold
_check_pdbid_in_redo	FALSE	Enable checking of pluggable database ID in redo
_cleanout_shrcur_buffers	TRUE	if TRUE, cleanout shrcur buffers
_cli_cachebktalloc	100	Percentage of memory to allocate
_client_enable_auto_unregister	FALSE	enable automatic unregister after a send fails with timeout
_clone_one_pdb_recovery	FALSE	Recover ROOT and only one PDB in clone database
_cloud_name		gsm cloud name
_collect_tempundo_stats	TRUE	Collect Statistics v$tempundostat
_common_data_view_enabled	TRUE	common objects returned through dictionary views
_common_user_prefix	C##	Enforce restriction on a prefix of a Common User/Role/Profile name
_concurrency_chosen	10	what is the chosen value of concurrency
_controlfile_cell_flash_caching	3	Flash cache hint for control file accesses
_cpu_eff_thread_multiplier		CPU effective thread multiplier
_crash_domain_on_exception	0	allow domain to exit for exceptions in any thread
_create_stat_segment	0	create ilm statistics segment
_ctx_doc_policy_stems	FALSE	enable ctx_doc.policy_stems api
_cu_row_locking	0	CU row level locking
_cursor_reload_failure_threshold	0	Number of failed reloads before marking cursor unusable
_cvmap_buffers	5000	Number of change vector buffers for multi instance media recovery
_data_transfer_cache_bc_perc_x100	500	Percentange * 100 of buffer cache to transfer to data transfer cache
_data_transfer_cache_size	0	Size of data transfer cache
_data_warehousing_scan_buffers	TRUE	if TRUE, enable data warehousing scan buffers
_datapump_compressbas_buffer_size	0	specifies buffer size for BASIC compression algorithm
_datapump_metadata_buffer_size	131072	specifies buffer size for metadata file I/O
_datapump_tabledata_buffer_size	262144	specifies buffer size for table data file I/O
_db_block_cache_history_lru	FALSE	buffer header tracing for lru operations
_db_block_prefetch_wasted_threshold_perc	2	Allowed wasted percent threshold of prefetched size
_db_dump_from_disk_and_efc	0	dump contents from disk and efc
_db_dw_scan_adaptive_cooling	FALSE	if TRUE, enable adaptive DW scan cooling
_db_dw_scan_max_shadow_count	5	DW Scan adaptive cooling max shadow count
_db_dw_scan_obj_cooling_factor	500	DW Scan object cooling factor to cool all temperatures
_db_dw_scan_obj_cooling_interval	100	DW Scan object cooling interval in number of scans, seconds, or pct of cache size
_db_dw_scan_obj_cooling_policy	CACHE_SIZE	DW scan objtect cooling policy
_db_dw_scan_obj_warming_increment	1000	DW Scan object warming increment when an object is scanned
_db_flash_cache_max_latency	400	Flash cache maximum latency allowed in 10 milliseconds
_db_flash_cache_max_outstanding_writes	32	Flash cache maximum outstanding writes allowed
_db_flash_cache_max_read_retry	3	Flash cache max read retry
_db_flash_cache_max_slow_io	3	Flash cache maximum slow io allowed
_db_num_gsm	0	database number in gsm dbpool
_dbfs_modify_implicit_fetch	TRUE	DBFS Link allows implicit fetch on modify – only on SecureFiles
_dbg_scan	0	generic scan debug
_dbop_enabled	1	Any positive number enables automatic DBOP monitoring. 0 is disabled
_dbpool_name		gsm database pool name
_dd_validate_remote_locks	TRUE	GES deadlock detection validate remote locks
_deadlock_record_to_alert_log	TRUE	record resolved deadlocks to the alert log
_defer_sga_alloc_chunk_size	1.07E+09	Chunk size for defer sga allocation
_defer_sga_enabled	FALSE	Enable deferred shared memory allocation for SGA
_defer_sga_min_spsz_at_startup	5.37E+10	Minimum shared pool size at startup with deferred sga enabled
_defer_sga_min_total_defer_segs_sz	1.07E+11	Minimum total deferred segs size for defer sga allocation
_defer_sga_test_alloc_intv	0	SA** sleeps for N secs before allocating a deferred segment
_deferred_seg_in_seed	TRUE	Enable Deferred Segment Creation in Seed
_diag_test_seg_reinc_mode	FALSE	Sets trace segmentation to be in reincarnation mode
_diag_xm_enabled	FALSE	If TRUE, DIAG allows message exchanges across DB/ASM boundary
_disable_12cbigfile	FALSE	DIsable Storing ILM Statistics in 12cBigFiles
_disable_directory_link_check	FALSE	Disable directory link checking
_disable_flashback_recyclebin_opt	TRUE	Don’t use the Flashback Recyclebin optimization
_disable_rolling_patch	0	Disable Rolling Patch Feature
_dm_dmf_details_compatibility	12.1.0	set dm dmf details compatibility version
_dm_enable_legacy_dmf_output_types	FALSE	revert dmf output types to pre-12.1.0.1
_dnfs_rdma_enable	1	Enable dNFS RDMA transfers
_dnfs_rdma_max	1048576	Maximum size of dNFS RDMA transfer
_dnfs_rdma_min	8192	Minimum size of dNFS RDMA transfer
_drop_stat_segment	0	drop ilm statistics segment
_dump_10261_level	0	Dump level for event 10261, 1=>minimal dump 2=>top pga dump
_dump_scn_increment_stack		Dumps scn increment stack per session
_emon_pool_inc	1	increment in EMON slaves per pool type
_emon_pool_max	10	maximum number of EMON slaves per pool type
_emon_pool_min	1	minimum number of EMON slaves per pool type
_emon_send_timeout	10000	send timeout after which the client is unregistered
_emx_control	0	EM Express control (internal use only)
_emx_max_sessions	128	Maximum number of sessions in the EM Express cache
_emx_session_timeout	3600	Session timeout (sec) in the EM Express cache
_enable_12g_bft	TRUE	enable 12g bigfile tablespace
_enable_columnar_cache	0	Enable Columnar Flash Cache Rewrite
_enable_heatmap_internal	FALSE	heatmap related – to be used by oracle dev only
_enable_iee_stats	TRUE	enables IEE stats gathering
_enable_ilm_flush_stats	TRUE	Enable ILM Stats Flush
_enable_ilm_testflush_stats	FALSE	Enable Test ILM Stats Flush
_enable_offloaded_writes	FALSE	Enable offloaded writes for Unit Test
_enable_pluggable_database	FALSE	Enable Pluggable Database
_enable_securefile_flashback_opt	FALSE	Enable securefile flashback optimization
_enqueue_deadlock_detect_all_global_locks	FALSE	enable deadlock detection on all global enqueues
_enqueue_sync_retry_attempts	3	max number of times the bg process to retry synchronous enqueue open if it failed because master could not allocate memory
_enqueue_sync_sim_mem_error	FALSE	simulate master instance running out of memory when synchronously getting a remotely mastered enqueue
_external_scn_logging_threshold_seconds	86400	High delta SCN threshold in seconds
_external_scn_rejection_delta_threshold_minutes	0	external SCN rejection delta threshold in minutes
_external_scn_rejection_threshold_hours	24	Lag in hours between max allowed SCN and an external SCN
_fast_index_maintenance	TRUE	fast global index maintenance during PMOPs
_fast_psby_conversion	TRUE	Enable fast physical standby conversion
_flush_ilm_stats	0	flush ilm stats
_force_logging_in_upgrade	TRUE	force logging during upgrade mode
_force_sys_compress	TRUE	Sys compress
_full_diag_on_rim	FALSE	rim nodes have full DIA* function
_gc_affinity_acquire_time	TRUE	if TRUE, save the time we acquired an affinity lock
_gc_async_send	FALSE	send blocks asynchronously
_gc_fg_spin_time	0	foreground msgq spin time
_gc_object_queue_max_length	0	maximum length for an object queue
_gc_save_cleanout	TRUE	if TRUE, save cleanout to apply later
_gc_split_flush	FALSE	if TRUE, flush index split redo before rejecting bast
_gc_temp_affinity	FALSE	if TRUE, enable global temporary affinity
_gcr_cpu_min_free	10	minimum amount of free CPU to flag an anomaly
_gcr_enable_high_memory_kill	FALSE	if TRUE, GCR may kill foregrounds under high memory load
_gcr_enable_new_drm_check	FALSE	if FALSE, revert to old drm load metric
_gcr_enable_statistical_cpu_check	TRUE	if FALSE, revert to old cpu load metric
_gcr_high_memory_threshold	10	minimum amount of Memory process must consume to be kill target
_gcr_max_rt_procs		maximum number of RT DLM processes allowed by GCR
_gcr_mem_min_free	10	minimum amount of free memory to flag an anomaly
_gcs_disable_switch_role_with_writer	TRUE	if TRUE, disable switching to local role with a writer
_gcs_min_slaves	0	if non zero, it enables the minimum number of gcs slaves
_gcs_res_hash_buckets		number of gcs resource hash buckets to be allocated
_gcs_reserved_resources	400	allocate the number of reserved resources in reconfiguration
_gcs_reserved_shadows	400	allocate the number of reserved shadows in reconfiguration
_ges_default_lmds	*	default lmds for enqueue hashing
_ges_direct_free	FALSE	if TRUE, free each resource directly to the freelist
_ges_direct_free_res_type	CT	string of resource types(s) to directly free to the freelist
_ges_dump_open_locks	FALSE	if TRUE, dump open locks for the LCK process during shutdown
_ges_fggl	TRUE	DLM fg grant lock on/off
_ges_freeable_res_chunk_free	FALSE	if TRUE, free dynamic resource chunks which are freeable
_ges_freeable_res_chunk_free_interval	180	time interval for freeing freeable dynamic resource chunks
_ges_gather_res_reuse_stats	FALSE	if TRUE, gather resource reuse statistics
_ges_hash_groups	*	enqueue hash table groups
_ges_lmd_mapping	*	enqueue to lmd mapping
_ges_nres_divide	0	how to divide number of enqueue resources among hash tables
_ges_resource_memory_opt	4	enable different level of ges res memory optimization
_ges_server_processes	1	number of background global enqueue server processes
_grant_secure_role	FALSE	Disallow granting of SR to NSR
_gsm		GSM descriptions
_gsm_config_vers	0	version of gsm config
_gsm_cpu_thresh	75	CPU busy threshold
_gsm_drv_interval	30	metric derived values interval
_gsm_max_instances_per_db	8	maximum number of instances per database in gsm cloud
_gsm_max_num_regions	10	maximum number of regions in gsm cloud
_gsm_region_list		List of GSM Regions
_gsm_srlat_thresh	20	Single block read latency threshold
_gsm_thresh_respct	50	threshold resource percentage
_gsm_thresh_zone	10	threshold zone
_gwm_spare1		gsm spare 1
_gwm_spare2	0	gsm spare 2
_gwm_spare3	0	gsm spare 3
_hang_base_file_count	5	Number of trace files for the normal base trace file
_hang_base_file_space_limit	20000000	File space limit for current normal base trace file
_hang_bool_spare1	TRUE	Hang Management 1
_hang_cross_boundary_hang_detection_enabled	TRUE	Hang Management Cross Boundary detection
_hang_delay_resolution_for_libcache	TRUE	Hang Management delays hang resolution for library cache
_hang_hang_analyze_output_hang_chains	TRUE	if TRUE hang manager outputs hang analysis hang chains
_hang_int_spare2	FALSE	Hang Management 2
_hang_log_verified_hangs_to_alert	FALSE	Hang Management log verified hangs to alert log
_hang_lws_file_space_limit	20000000	File space limit for current long waiting session trace file
_hang_monitor_archiving_related_hang_interval	300	Time in seconds ignored hangs must persist after verification
_hang_resolution_allow_archiving_issue_termination	TRUE	Hang Management hang resolution allow archiving issue termination
_hang_terminate_session_replay_enabled	FALSE	Hang Management terminates sessions allowing replay
_hashops_prefetch_size	4	maximum no of rows whose relevant memory locations are prefetched
_heatmap_format_1block	FALSE	heatmap related – to be used by oracle dev only
_heatmap_min_maxsize	0	Internal testing only
_hm_xm_enabled	TRUE	If TRUE, DIA0 allows message exchanges across DB/ASM boundary
_ilm_mem_limit	10	percentage of the max shared pool heat-map can use – internal
_ilmflush_stat_limit	0	ILM flush statistics limit – Internal testing only
_ilmset_stat_limit	0	ILM set statistics limit – Internal testing only
_ilmstat_memlimit	10	Percentage of shared pool for use by ILM Statistics
_image_redo_gen_delay	0	Image redo generation delay in centi-seconds (direct write mode)
_imr_rr_holder_kill_time	300	IMR max time instance is allowed to hold RR lock in seconds
_index_load_buf_oltp_sacrifice_pct	10	index load buf oltp sacrifice pct
_index_load_buf_oltp_under_pct	85	index load buf and comp oltp under-estimation pct
_index_max_inc_trans_pct	20	max itl expand percentage soft limit during index insert
_io_internal_test	0	I/O internal testing parameter
_io_osd_param	1	OSD specific parameter
_io_outlier_threshold	500	Latency threshold for io_outlier table
_ka_allow_reenable	FALSE	reenability of kernel accelerator service after disable
_ka_compatibility_requirement	all	kernel accelerator compatibility operation requirement
_ka_doorbell	0	kernel accelerator doorbell mode
_ka_locks_per_sector	4	locks per sector in kernel accelerator
_ka_mode	0	kernel accelerator mode
_ka_msg_reap_count	40	maximum number of KA messages to receive and process per wait
_ka_pbatch_messages	TRUE	kernel accelerator perform pbatch messages
_kcfis_automem_level	1	Set auto memory management control for kcfis memory allocation
_kcfis_cell_passthru_dataonly	TRUE	Allow dataonly passthru for smart scan
_kcfis_celloflsrv_passthru_enabled	FALSE	Enable offload server usage for passthru operations
_kcfis_celloflsrv_usage_enabled	TRUE	Enable offload server usage for offload operations
_kcfis_qm_prioritize_sys_plan	TRUE	Prioritize Quaranitine Manager system plan
_kcfis_qm_user_plan_name		Quaranitine Manager user plan name
_kdizoltp_uncompsentinal_freq	16	kdizoltp uncomp sentinal frequency
_kdlf_read_flag	0	kdlf read flag
_kdli_descn_adj	FALSE	coalesce extents with deallocation scn adjustment
_kdli_mts_so	TRUE	use state objects in shared server for asyncIO pipelines
_kdli_ralc_length	10485760	lob length threshold to trigger rounded allocations
_kdli_ralc_rounding	1048576	rounding granularity for rounded allocations
_kdz_pred_nrows	32767	Number of rows to predicate at a time in kdzt
_kdz_proj_nrows	1024	Number of rows to project at a time in kdzt
_kecap_cache_size	10240	Workload Replay INTERNAL parameter used to set memory usage in  Application Replay
_kffmlk_hash_size	512	size of kffmlk_hash table
_kffmop_chunks	42	number of chunks of kffmop’s
_kgl_cap_hd_alo_stacks	FALSE	capture stacks for library cache handle allocation
_kjac_force_outcome_current_session	FALSE	if TRUE, enable to run force outcome on the current session
_kjdd_call_stack_dump_enabled	FALSE	Enables printing of short call stack with the WFG
_kjdd_wfg_dump_cntrl	0	To control the way Wait-For_Graph is dumped
_kjltmaxgt	1000	record latch requests that takes longer than this many us
_kjltmaxht	1000	record latch reqeust that are held longer than this many us
_kjlton	FALSE	track DLM latch usage on/off
_kqdsn_max_instance_bits	8	maximum bits used for instance value in sequence partition
_kqdsn_min_instance_bits	0	minimum bits used for instance value in sequence partition
_kqdsn_partition_ratio	30	ratio of instance to session bits in sequence partition
_kqr_optimistic_reads	FALSE	optimistic reading of row cache objects
_ksipc_libipc_path		over-ride default location of libipc
_ksipc_mode	0	ksipc mode
_ksipc_spare_param1	0	ksipc spare param 1
_ksipc_spare_param2		ksipc spare param 2
_ksipc_wait_flags	0	tune ksipcwait
_ksmlsaf	0	KSM log alloc and free
_ksv_slave_exit_timeout	120	slave exit timeout
_ksxp_skgxp_ant_options		SKGXP ANT options
_ksxp_skgxp_inets		limit SKGXP networks
_ktilmsc_exp	600	expiration time of ktilm segment cache (in second)
_ktspsrch_scchk	60	cleanout check time of space search cache
_ktspsrch_scexp	60	expiration time of space search cache
_ktst_rss_max	1000	maximum temp extents to be released across instance
_ktst_rss_min	100	minimum temp extents to be released across instance
_ktst_rss_retry	2	maximum retries of sort segment release
_kxdbio_ctx_init_count	32	initial count of KXDBIO state object
_kxdbio_disable_offload_opcode	0	KXDBIO Disable offload for the set opcodes.  Value is a Bitmap of    0x00000001 – disable cell to cell data copy offload    0x00000002 – disable disk scrubbing offload to cell    0x00000004 – disable offloaded writes to cell
_kxdbio_enable_ds_opcode	0	KXDBIO Enable Dumb storage simulation for the set opcodes.
_kxdbio_hca_loadavg_thresh	98	HCA loadavg threshold at which writes need to get offloaded
_kxdbio_ut_ctl	0	kxdbio unit test controls
_latch_wait_list_pri_sleep_secs	1	Time to sleep on latch wait list until getting priority
_ldr_tempseg_threshold	8388608	amount to buffer prior to allocating temp segment (extent sizing)
_lgwr_io_outlier	0	LGWR I/O outlier frequency
_lm_big_cluster_optimizations	FALSE	enable certain big cluster optimizations in drm
_lm_comm_channel	ksxp	GES communication channel type
_lm_comm_msgq_busywait	10	busy wait time in microsecond for msgq
_lm_comm_reap_count	1	message reap count for receive
_lm_comm_tkts_add_factor	10	Ticket allocation addition factor
_lm_comm_tkts_calc_period_length	5	Weighted average calculation interval length (second)
_lm_comm_tkts_max_periods	10	Max number of periods used in weighted avearage calculation
_lm_comm_tkts_min_decrease_wait	120	Time to wait before allowing an allocation decrease
_lm_comm_tkts_min_increase_wait	25	Time to wait before allowing an allocation increase
_lm_comm_tkts_mult_factor	200	Ticket allocation multiplication factor
_lm_comm_tkts_nullreq_threshold	25	Null request frequency threshold (percentage)
_lm_drmopt12	8	enable drm scan optimizations in 12
_lm_drmopt12_nolatch	TRUE	enable drm latching optimizations in 12
_lm_enqueue_blocker_dump_timeout_cnt	30	enqueue blocker dump timeout count
_lm_fdrm_stats	FALSE	gather full drm statistics
_lm_hashtable_bkt_high	5	High element threshold in hash table bucket
_lm_hashtable_bkt_low	3	Low element threshold in hash table bucket
_lm_hashtable_bkt_thr	70	Threshold for hash table resizing
_lm_hb_acceptable_hang_condition	default	list of acceptable hang conditions in heartbeat check
_lm_hb_enable_acl_check	TRUE	to enable the wait analysis with acceptable condition lists
_lm_hb_exponential_hang_time_factor	2	heartbeat exponential hang time multiplier
_lm_hb_maximum_hang_report_count	20	maximum heartbeat hang report count
_lm_idle_connection_kill_max_skips	1	GES idle connection max skip kill request
_lm_idle_connection_max_ignore_kill_count	2	GES maximum idle connection kill request ignore count
_lm_idle_connection_quorum_threshold	50	GES idle connection health quorum threshold
_lm_lms_opt_priority	TRUE	enable freeslot lms priority optimization
_lm_lms_priority_check_frequency	60000	frequency of LMS priority decisions in milliseconds
_lm_msg_pool_dump_threshold	20000	GES message pool dump threshold in terms of buffer count
_lm_num_bnft_stats_buckets	1	number of buckets in the benefit stats hash table
_lm_process_lock_q_scan_limit	100	limit on scanning process lock queue instead of resource convert lock queue
_lm_res_tm_hash_bucket	0	number of extra TM resource hash buckets
_lm_resend_open_convert_timeout	30	timeout in secs before resubmitting the open-convert
_lm_rm_slaves	1	if non zero, it enables remastering slaves
_lm_wait_pending_send_queue	TRUE	GES wait on pending send queue
_lock_ref_constraint_count	50	number of nowait attempts to lock referential constraint
_log_undo_df_info	FALSE	generate marker to log file# that belong to undo tablespace
_log_write_info_size	4096	Size of log write info array
_log_writer_worker_dlm_hearbeat_update_freq	5000	LGWR worker DLM health-monitoring heartbeat update frequency (ms)
_lthread_cleanup_intv_secs	5	interval for cleaning lightweight threads in secs
_lthread_clnup_hk_wait_secs	3	timeout after hard killing operation for lthread to exit
_lthread_clnup_pmon_softkill_wait_secs	1	wait timeout for PMON between soft kill and hard kill of lthreads
_lthread_clnup_spawner_sk_wait_secs	30	timeout for spawner between soft kill and hard kill of lthreads
_lthread_debug	FALSE	Enable Debugging mode for lightweight threads
_lthread_enabled	TRUE	Enable lightweight threads
_lthread_max_spawn_time_csecs	12000	maximum time interval a spawner will wait for a lthread to get ready
_lthread_spawn_check_intv_ms	10	time interval for a spawner to check for spawnee to get ready
_lthread_step_debugging	FALSE	Enable Step wise Debugging mode for lightweight threads
_max_clients_per_emon	256	maximum number of clients per emon
_max_data_transfer_cache_size	5.37E+08	Maximum size of data transfer cache
_max_defer_gran_xfer_atonce	10	Maximum deferred granules transferred by MMAN atonce
_max_incident_file_size		Maximum size (in KB, MB, GB, Blocks) of incident dump file
_max_kcnibr_ranges	1048576	Max number of nonlogged data block ranges
_max_log_write_io_parallelism	1	Maximum I/O parallelism within a log write (auto=0)
_max_log_write_parallelism	1	Maximum parallelism within a log write (auto=0)
_max_outstanding_log_writes	2	Maximum number of outstanding redo log writes
_max_queued_report_requests	300	Maximum number of report requests that can be queued in a list
_max_report_flushes_percycle	5	Max no of report requests that can be flushed per cycle
_max_string_size_bypass	0	controls error checking for the max_string_size parameter
_max_sys_next_extent	0	Dictionary managed SYSTEM tablespace maximum next extent size in MB (allowed range [16-4095], 0 if unlimited)
_memory_max_tgt_inc_cnt	0	counts the times checker increments memory target
_midtier_affinity_cluswait_prc_threshold	6	cluster wait precentage threshold to enter affinity
_min_time_between_psp0_diag_secs	300	minimum time between PSP0 diagnostic used for flow control
_modify_column_index_unusable	FALSE	allow ALTER TABLE MODIFY(column) to violate index key length limit
_mpmt_enabled_backgrounds	*	mpmt enabled backgrounds
_mpmt_fg_enabled	FALSE	MPMT mode foreground enabled
_mpmt_procs_per_osp	100	max procs per osp
_multi_transaction_optimization_enabled	TRUE	reduce SGA memory use during create of a partitioned table
_mv_add_log_placeholder	TRUE	add log placeholder
_mv_cleanup_orphaned_metadata	TRUE	cleanup orphaned materialized view metadata
_mv_complete_refresh_conventional	FALSE	use conventional INSERTs for MV complete refresh
_mv_deferred_no_log_age_val	TRUE	avoid build deferred MV log age validate
_mv_expression_extend_size	4096	MV expression extend size
_mv_refresh_insert_no_append	TRUE	materialized view refresh using insert no append
_nameservice_consistency_check	TRUE	NameService Consistency check switch
_net_timeout_latency	0	NET_TIMEOUT latency
_no_small_file	FALSE	Not to apply new extent scheme for small file temp spaces
_no_stale_joinback_rewrite	FALSE	No joinbacks if mv is stale
_nologging_kcnbuf_hash_buckets	1024	Number of nologging buffer hash buckets
_nologging_kcnbuf_hash_latches	256	Number of nologging buffer hash latches
_nologging_load_slotsz	1048576	Nologging standby: direct load buffer size
_nologging_sdcl_append_wait	100	Nologging standby append sdcl wait time
_nologging_sendbuf_ratio	99	Nologging standby: outstanding send buffer ratio
_nologging_txn_cmt_wait	1500	Nologging standby transaction commit wait time
_noncdb_to_pdb	FALSE	converting a non-cdb to a pdb
_numa_shift_enabled	TRUE	Enable NUMA shift
_numa_shift_value	0	user defined value for numa nodes shift
_number_group_memberships_per_cache_line	3	maximum number of group memberships per cache line
_odci_aggregate_save_space	FALSE	trade speed for space in user-defined aggregation
_olap_row_load_time_precision	DEFAULT	OLAP Row Load Time Precision
_old_extent_scheme	FALSE	Revert to old extent allocation
_oltp_comp_dbg_scan	0	oltp compression scan debug
_oltp_spill	FALSE	spill rows for oltp compression if loader pga limit is exceeded
_omni_enqueue_enable	1	Enable Omni Enqueue feature (0 = disable, 1 = enable on ASM (default), 2 = enable)
_online_ctas_diag	0	controls dumping diagnostic information for online ctas
_optimizer_adaptive_plan_control	0	internal controls for adaptive plans
_optimizer_adaptive_plans	TRUE	enable adaptive plans
_optimizer_ads_max_table_count	0	maximum number of tables in a join under ADS
_optimizer_ads_time_limit	0	maximum time limit (seconds) under ADS
_optimizer_ads_use_result_cache	TRUE	use result cache for ADS queries
_optimizer_ansi_join_lateral_enhance	TRUE	optimization of left/full ansi-joins and lateral views
_optimizer_ansi_rearchitecture	TRUE	re-architecture of ANSI left, right, and full outer joins
_optimizer_batch_table_access_by_rowid	TRUE	enable table access by ROWID IO batching
_optimizer_cluster_by_rowid	TRUE	enable/disable the cluster by rowid feature
_optimizer_cluster_by_rowid_control	3	internal control for cluster by rowid feature mode
_optimizer_cube_join_enabled	TRUE	enable cube join
_optimizer_dsdir_usage_control	126	controls optimizer usage of dynamic sampling directives
_optimizer_gather_feedback	TRUE	optimizer gather feedback
_optimizer_gather_stats_on_load	TRUE	enable/disable online statistics gathering
_optimizer_generate_transitive_pred	TRUE	optimizer generate transitive predicates
_optimizer_hybrid_fpwj_enabled	TRUE	enable hybrid full partition-wise join when TRUE
_optimizer_multi_table_outerjoin	TRUE	allows multiple tables on the left of outerjoin
_optimizer_null_accepting_semijoin	TRUE	enables null-accepting semijoin
_optimizer_partial_join_eval	TRUE	partial join evaluation parameter
_optimizer_performance_feedback	OFF	controls the performance feedback
_optimizer_proc_rate_level	BASIC	control the level of processing rates
_optimizer_proc_rate_source	DEFAULT	control the source of processing rates
_optimizer_strans_adaptive_pruning	TRUE	allow adaptive pruning of star transformation bitmap trees
_optimizer_unnest_scalar_sq	TRUE	enables unnesting of of scalar subquery
_optimizer_use_gtt_session_stats	TRUE	use GTT session private statistics
_optimizer_use_histograms	TRUE	enable/disable the usage of histograms by the optimizer
_oracle_script	FALSE	Running an Oracle-supplied script
_parallel_blackbox_sga	TRUE	true if blackbox will be allocated in SGA, false if PGA
_parallel_ctas_enabled	TRUE	enable/disable parallel CTAS operation
_parallel_fault_tolerance_threshold	3	total number of faults fault-tolerance will handle
_part_access_version_by_number	TRUE	use version numbers to access versioned objects for partitioning
_part_redef_global_index_update	TRUE	online partition redefinition update global indexes
_partition_advisor_srs_active	TRUE	enables sampling based partitioning validation
_partition_cdb_view_enabled	TRUE	partitioned cdb view evaluation enabled
_pdb_use_sequence_cache	TRUE	Use sequence cache in PDB mode
_pga_limit_check_wait_time	1000000	microseconds to wait for over limit confirmation
_pga_limit_dump_summary	FALSE	dump PGA summary when signalling ORA-4036
_pga_limit_interrupt_smaller	FALSE	whether to interrupt smaller eligible processes
_pga_limit_min_req_size	4194304	bytes of PGA usage below which process will not get ORA-4036
_pga_limit_simulated_physmem_size	0	bytes of physical memory to determine pga_aggregate_limit with
_pga_limit_target_perc	200	default percent of pga_aggregate_target for pga_aggregate_limit
_pga_limit_time_to_interrupt	2	seconds to wait until direct interrupt
_pga_limit_time_until_idle	15	seconds to wait before treating process as idle
_pga_limit_time_until_killed	30	seconds to wait before killing session over limit
_pga_limit_tracing	0	trace pga_aggregate_limit activity
_pga_limit_use_immediate_kill	TRUE	use immediate kill for sessions over limit
_pga_limit_watch_perc	50	percentage of limit to have processes watch
_pga_limit_watch_size	1.05E+08	bytes of PGA usage at which process will begin watching limit
_pin_time_statistics	FALSE	if TRUE collect statistics for how long a current pin is held
_ping_wait_for_log_force	TRUE	Wait for log force before block ping
_pluggable_database_debug	0	Debug flag for pluggable database related operations
_pred_push_cdb_view_enabled	TRUE	predicate pushdown enabled for CDB views
_prefered_standby		standby db_unique_name prefered for krb operations
_print_inmem_heatmap	0	print inmem ilm heatmap
_print_stat_segment	0	print ilm statistics segment
_px_adaptive_dist_method	CHOOSE	determines the behavior of adaptive distribution methods
_px_adaptive_dist_method_threshold	0	Buffering / decision threshold for adaptive distribution methods
_px_adaptive_offload_percentage	30	percentage for PQ adaptive offloading of granules
_px_back_to_parallel	OFF	allow going back to parallel after a serial operation
_px_cdb_view_enabled	TRUE	parallel cdb view evaluation enabled
_px_concurrent	TRUE	enables pq with concurrent execution of serial inputs
_px_cpu_autodop_enabled	TRUE	enables or disables auto dop cpu computation
_px_cpu_operator_bandwidth		CPU operator bandwidth in MB/sec for DOP computation
_px_cpu_process_bandwidth	200	CPU process bandwidth in MB/sec for DOP computation
_px_dp_array_size	32767	Max number of pq processes supported
_px_filter_parallelized	TRUE	enables or disables correlated filter parallelization
_px_filter_skew_handling	TRUE	enable correlated filter parallelization to handle skew
_px_groupby_pushdown	FORCE	perform group-by pushdown for parallel query
_px_hybrid_TSM_HWMB_load	TRUE	Enable Hybrid Temp Segment Merge/High Water Mark Brokered load method
_px_join_skew_handling	TRUE	enables skew handling for parallel joins
_px_join_skew_minfreq	30	sets minimum frequency(%) for skewed value for parallel joins
_px_join_skew_ratio	10	sets skew ratio for parallel joins
_px_load_balancing_policy	UNIFORM	parallel load balancing policy
_px_load_monitor_threshold	10000	threshold for pushing information to load slave workload monitor
_px_message_compression	TRUE	enable compression of control messages for parallel query
_px_monitor_load	FALSE	enable consumer load slave workload monitoring
_px_object_sampling	200	parallel query sampling for base objects (100000 = 100%)
_px_object_sampling_enabled	TRUE	use base object sampling when possible for range distribution
_px_onepass_slave_acquisition	TRUE	enable/disable one pass slave acquisition for parallel execution
_px_parallelize_expression	TRUE	enables or disables expression evaluation parallelization
_px_partial_rollup_pushdown	ADAPTIVE	perform partial rollup pushdown for parallel execution
_px_proactive_slave_alloc_threshold	8	parallel proactive slave allocation threshold/unit
_px_pwmr_enabled	TRUE	parallel partition wise match recognize enabled
_px_replication_enabled	TRUE	enables or disables replication of small table scans
_px_single_server_enabled	TRUE	allow single-slave dfo in parallel query
_px_tq_rowhvs	TRUE	turn on intra-row hash valueing sharing in TQ
_px_wif_dfo_declumping	CHOOSE	NDV-aware DFO clumping of multiple window sorts
_px_wif_extend_distribution_keys	TRUE	extend TQ data redistribution keys for window functions
_px_wif_min_ndv_per_slave	2	mininum NDV of TQ keys needed per slave for scalable WiF PX
_radm_enabled	TRUE	Data Redaction
_re_fast_sql_operator	all	enables fast boxable sql operator
_re_independent_expression_cache_size	20	defines max number of compiled cached expressions for iee
_re_num_complex_operator	1000	defines max number of compiled complex operator per ruleset-iee
_re_num_rowcache_load	2	defines max number of complex operators loaded with row cache
_re_result_cache_keysiz	20	defines max number key for result cache hash table
_re_result_cache_size	20	defines max number of cached elements for result cache
_read_mostly_enable_logon	FALSE	Read mostly instances enable non-privileged logons
_read_mostly_instance	FALSE	indicates this is a read_mostly instance
_read_mostly_instance_qa_control	0	internal parameter to control read mostly instance QA
_read_mostly_slave_timeout	20000	Time to wait on read mostly node when hub not available
_realfree_heap_pagesize	65536	hint for real-free page size in bytes
_realfree_pq_heap_pagesize	65536	hint for pq real-free page size in bytes
_redef_on_statement	FALSE	Use on-statement refresh in online redefinition
_redo_log_debug_config	0	Various configuration flags for debugging redo logs
_redo_log_record_life	168	Life time in hours for redo log table records
_region_name		gsm region name
_relocate_pdb	FALSE	Relocate PDB to another RAC instance after it is closed in the current instance
_remote_asm		remote ASM configuration
_remote_awr_enabled	FALSE	Enable/disable Remote AWR Mode
_remove_exf_component	TRUE	enable/disable removing of components EXF and RUL during upgrade
_report_capture_cycle_time	60	Time (in sec) between two cycles of report capture daemon
_report_capture_dbtime_percent_cutoff	50	100X Percent of system db time daemon is allowed over 10 cycles
_report_capture_recharge_window	10	No of report capture cycles after which db time is recharged
_report_capture_timeband_length	1	Length of time band (in hours) in the reports time bands table
_report_request_ageout_minutes	60	Time (in min) after which a report request is deleted from queue
_resource_includes_unlimited_tablespace	FALSE	Whether RESOURCE role includes UNLIMITED TABLESPACE privilege
_result_cache_deterministic_plsql	FALSE	result cache deterministic PLSQL functions
_resumable_critical_alert	0	raise critical alert for resumable failure
_rman_roundrobin_placement		Numa round robin placement for RMAN procs
_rond_test_mode	0	rac one node test mode
_rowlen_for_chaining_threshold	1000	maximum rowlen above which rows may be chained across blocks
_rowsets_cdb_view_enabled	TRUE	rowsets enabled for CDB views
_rowsets_enabled	TRUE	enable/disable rowsets
_rowsets_max_rows	200	maximum number of rows in a rowset
_rowsets_target_maxsize	524288	target size in bytes for space reserved in the frame for a rowset
_rtaddm_trigger_args		comma-separated list of numeric arguments for RT addm trigger
_rtaddm_trigger_enabled	TRUE	To enable or disable Real-Time ADDM automatic trigger
_scalar_type_lob_storage_threshold	4000	threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB
_securefile_log_num_latches	0	Maximum number of open descriptors for securefile log
_securefile_log_shared_pool_size	0	Size of securefile log buffer pool from SGA
_securefiles_breakreten_retry	5	segment retry before dishonoring retention
_securefiles_spcutl	FALSE	securefiles segment utl optimization
_serial_log_write_worker_io	FALSE	Serialize log write slave I/O
_set_container_service	DEFAULT	set container service
_sf_default_enabled	TRUE	enable 12g securefile default
_sga_alloc_slaves_term_timeout_secs	120	Termination timeout in secs for SA** slaves
_shrd_que_tm_processes	1	number of sharded queue Time Managers to start
_shrd_que_tm_statistics_duration	0	Shaded queue statistics collection window duration
_simulated_log_write_usecs	0	Simulated latency of log writes (usecs)
_skgxp_ant_options		SKGXP ANT options (oss)
_skip_acfs_checks	FALSE	Override checking if on an ACFS file system
_smm_max_size_static	92160	static maximum work area size in auto mode (serial)
_smm_px_max_size_static	230400	static maximum work area size in auto mode (global)
_snapshot_recovery_enabled	TRUE	enable/disable snapshot recovery
_sort_sync_min_spill_threshold	90	controls the mininum spill size for synchronized spill (in percent)
_sort_sync_min_spillsize	262144	controls the size of mininum run size for synchronized spill (in kb)
_sql_diag_repo_origin	all	duarations where sql diag repository are retained
_sql_diag_repo_retain		retain sql diag repository to cursor or not
_sql_hvshare_debug	1	control hash value sharing debug level
_sql_hvshare_threshold	0	threshold to control hash value sharing across operators
_sql_plan_directive_mgmt_control	3	controls internal SQL Plan Directive management activities
_sql_show_expreval	FALSE	show expression evalution as shared hash producer in plan
_stat_aggs_one_pass_algorithm	FALSE	enable one pass algorithm for variance-related functions
_suppress_identifiers_on_dupkey	FALSE	supress owner index name err msg
_switch_current_scan_scn	TRUE	switch current uses scan scn
_switchover_timeout	0	Switchover timeout in minutes
_sys_logon_delay	1	failed logon delay for sys
_target_log_write_size	0	Do log write if this many redo blocks in buffer (auto=0)
_target_log_write_size_timeout	1	How long LGWR will wait for redo to accumulate (csecs)
_temp_undo_disable_adg	FALSE	is temp undo disabled on ADG
_test_hm_extent_map	FALSE	heatmap related – to be used by oracle dev only
_test_param_7		test parameter 7 – big integer list
_test_param_8	20	test parameter 8 – cdb tests
_thread_state_change_timeout_pnp	1800	Thread state change timeout for PnP instance (in sec)
_time_based_rcv_ckpt_target	0	time-based incremental recovery checkpoint target in sec
_time_based_rcv_hdr_update_interval	0	time-based incremental recovery file header update interval in sec
_trace_ktfs	FALSE	Trace ILM Stats Tracking
_trace_ktfs_mem	FALSE	Debug memleak
_trace_temp	FALSE	Trace Tempspace Management
_track_metrics_memory	TRUE	Enable/disable Metrics Memory Tracking
_tsenc_obfuscate_key	BOTH	Encryption key obfuscation in memory
_twenty-eighth_spare_parameter	FALSE	twenty-eighth spare parameter – boolean
_twenty-fifth_spare_parameter		twenty-fifth spare parameter – string list
_twenty-first_spare_parameter		twenty-first spare parameter – string list
_twenty-fourth_spare_parameter		twenty-fourth spare parameter – string list
_twenty-second_spare_parameter		twenty-second spare parameter – string list
_twenty-seventh_spare_parameter	FALSE	twenty-seventh spare parameter – boolean
_twenty-sixth_spare_parameter	FALSE	twenty-sixth spare parameter – boolean
_twenty-third_spare_parameter		twenty-third spare parameter – string list
_txn_control_trace_buf_size	4096	size the in-memory buffer size of txn control
_unified_audit_flush_interval	3	Unified Audit SGA Queue Flush Interval
_unified_audit_flush_threshold	85	Unified Audit SGA Queue Flush Threshold
_unified_audit_policy_disabled	FALSE	Disable Default Unified Audit Policies on DB Create
_upddel_dba_hash_mask_bits	0	controls masking of lower order bits in DBA
_use_fips_mode	FALSE	Enable use of crypographic libraries in FIPS mode
_use_hidden_partitions	FALSE	use hidden partitions
_use_single_log_writer	ADAPTIVE	Use a single process for redo log writing
_utlmmig_table_stats_gathering	TRUE	enable/disable utlmmig table stats gathering at upgrade
_uts_first_segment_retain	TRUE	Should we retain the first trace segment
_uts_first_segment_size	0	Maximum size (in bytes) of first segments
_uts_trace_disk_threshold	0	Trace disk threshold parameter
_uts_trace_segment_size	0	Maximum size (in bytes) of a trace segment
_uts_trace_segments	5	Maximum number of trace segments
_validate_metric_groups	FALSE	Enable/disable SGA Metric Structure validation
_wcr_grv_cache_size	65535	Oracle internal: Set the replay cache size for WRR$_REPLAY_DATA.
_wcr_seq_cache_size	65535	Oracle internal: Set the replay cache size for WRR$_REPLAY_SEQ_DATA.
_widetab_comp_enabled	TRUE	wide table compression enabled
_xa_internal_retries	600	number of internal retries for xa transactions
_xds_max_child_cursors	100	Maximum number of XDS user-specific child cursors
_xs_cleanup_task	TRUE	Triton Session Cleanup
_xs_dispatcher_only	FALSE	XS dispatcher only mode
_zonemap_control	0	control different uses/algorithms related to zonemaps
_zonemap_staleness_tracking	1	control the staleness tracking of zonemaps via trigger
_zonemap_use_enabled	TRUE	enable the use of zonemaps for IO pruning
cell_offloadgroup_name		Set the offload group name
connection_brokers	((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))	connection brokers specification
db_big_table_cache_percent_target	0	Big table cache target size in percentage
db_index_compression_inheritance	NONE	options for table or tablespace level compression inheritance
dnfs_batch_size	4096	Max number of dNFS asynch I/O requests queued per session
enable_pluggable_database	TRUE	Enable Pluggable Database
heat_map	OFF	ILM Heatmap Tracking
max_string_size	STANDARD	controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
noncdb_compatible	FALSE	Non-CDB Compatible
optimizer_adaptive_features	TRUE	controls adaptive features
optimizer_adaptive_reporting_only	FALSE	use reporting-only mode for adaptive optimizations
parallel_degree_level	100	adjust the computed degree in percentage
parallel_fault_tolerance_enabled	FALSE	enables or disables fault-tolerance for parallel statement
pdb_file_name_convert		PDB file name convert patterns and strings for create cdb/pdb
pga_aggregate_limit	2.15E+09	limit of aggregate PGA memory consumed by the instance
spatial_vector_acceleration	FALSE	enable spatial vector acceleration
temp_undo_enabled	FALSE	is temporary undo enabled
threaded_execution	FALSE	Threaded Execution Mode
unified_audit_sga_queue_size	1048576	Size of Unified audit SGA Queue
use_dedicated_broker	FALSE	Use dedicated connection broker

【12c新特性】CBO Optimizer新隐藏参数，以下Optimizer优化器新特性列表，通过对比 12c R1 和 11gR2的10053 trace中有影响的优化器参数获得：

 

CLI_internal_cursor

PMO_altidx_rebuild

_adaptive_window_consolidator_enabled

_arch_comp_dbg_scan

_array_cdb_view_enabled

_bloom_filter_size

_bloom_predicate_offload

_bloom_rm_filter

_bloom_sm_enabled

_cell_materialize_all_expressions

_cell_materialize_virtual_columns

_cell_offload_complex_processing

_cell_offload_expressions

_cell_offload_sys_context

_common_data_view_enabled

_dbg_scan

_fast_index_maintenance

_hashops_prefetch_size

_long_varchar_allow_IOT

_multi_commit_global_index_maint

_oltp_comp_dbg_scan

_optimizer_adaptive_plans

_optimizer_ads_max_table_count

_optimizer_ads_time_limit

_optimizer_ads_use_result_cache

_optimizer_ansi_join_lateral_enhance

_optimizer_ansi_rearchitecture

_optimizer_batch_table_access_by_rowid

_optimizer_cluster_by_rowid

_optimizer_cluster_by_rowid_control

_optimizer_cube_join_enabled

_optimizer_dsdir_usage_control

_optimizer_gather_feedback

_optimizer_gather_stats_on_load

_optimizer_generate_transitive_pred

_optimizer_hybrid_fpwj_enabled

_optimizer_multi_table_outerjoin

_optimizer_null_accepting_semijoin

_optimizer_partial_join_eval

_optimizer_performance_feedback

_optimizer_proc_rate_level

_optimizer_proc_rate_source

_optimizer_strans_adaptive_pruning

_optimizer_unnest_scalar_sq

_optimizer_use_gtt_session_stats

_optimizer_use_histograms                        ==>10g已有

_parallel_ctas_enabled

_parallel_fault_tolerance_threshold

_partition_advisor_srs_active

_partition_cdb_view_enabled

_pred_push_cdb_view_enabled

_px_adaptive_dist_method

_px_adaptive_dist_method_threshold

_px_back_to_parallel

_px_cdb_view_enabled

_px_concurrent

_px_cpu_autodop_enabled

_px_cpu_process_bandwidth

_px_filter_parallelized

_px_filter_skew_handling

_px_groupby_pushdown

_px_hybrid_TSM_HWMB_load

_px_join_skew_handling

_px_join_skew_minfreq

_px_join_skew_ratio

_px_load_monitor_threshold

_px_loc_msg_cost

_px_monitor_load

_px_net_msg_cost

_px_object_sampling_enabled

_px_parallelize_expression

_px_partial_rollup_pushdown

_px_pwmr_enabled

_px_replication_enabled

_px_single_server_enabled

_px_tq_rowhvs

_px_wif_dfo_declumping

_px_wif_extend_distribution_keys

_rowsets_cdb_view_enabled

_rowsets_enabled

_rowsets_max_rows

_rowsets_target_maxsize

_smm_max_size_static

_smm_px_max_size_static

_sql_hvshare_threshold

_stat_aggs_one_pass_algorithm

_upddel_dba_hash_mask_bits

_use_hidden_partitions

_zonemap_control

_zonemap_use_enabled

ilm_access_tracking

ilm_dml_timestamp

ilm_filter

optimizer_adaptive_features

optimizer_adaptive_reporting_only

parallel_degree_level

parallel_execution_message_size

parallel_fault_tolerance_enabled

sqlstat_enabled

==============================================================================================》

_adaptive_window_consolidator_enabled	TRUE	enable/disable adaptive window consolidator PX plan
_arch_comp_dbg_scan	0	archive compression scan debug
_array_cdb_view_enabled	TRUE	array mode enabled for CDB views
_bloom_filter_size	0	bloom filter vector size (in KB)
_bloom_predicate_offload	TRUE	enables or disables bloom filter predicate offload to cells
_bloom_rm_filter	FALSE	remove bloom predicate in favor of zonemap join pruning predicate
_bloom_sm_enabled	FALSE	enable bloom filter optimization using slave mapping
_cell_materialize_all_expressions	FALSE	Force materialization of all offloadable expressions on the cells
_cell_materialize_virtual_columns	TRUE	enable offload of expressions underlying virtual columns to cells
_cell_offload_complex_processing	TRUE	enable complex SQL processing offload to cells
_cell_offload_expressions	TRUE	enable offload of expressions to cells
_cell_offload_sys_context	TRUE	enable offload of SYS_CONTEXT evaluation to cells
_common_data_view_enabled	TRUE	common objects returned through dictionary views
_dbg_scan	0	generic scan debug
_fast_index_maintenance	TRUE	fast global index maintenance during PMOPs
_hashops_prefetch_size	4	maximum no of rows whose relevant memory locations are prefetched
_oltp_comp_dbg_scan	0	oltp compression scan debug
_optimizer_adaptive_plans	TRUE	enable adaptive plans
_optimizer_ads_max_table_count	0	maximum number of tables in a join under ADS
_optimizer_ads_time_limit	0	maximum time limit (seconds) under ADS
_optimizer_ads_use_result_cache	TRUE	use result cache for ADS queries
_optimizer_ansi_join_lateral_enhance	TRUE	optimization of left/full ansi-joins and lateral views
_optimizer_ansi_rearchitecture	TRUE	re-architecture of ANSI left, right, and full outer joins
_optimizer_batch_table_access_by_rowid	TRUE	enable table access by ROWID IO batching
_optimizer_cluster_by_rowid	TRUE	enable/disable the cluster by rowid feature
_optimizer_cluster_by_rowid_control	3	internal control for cluster by rowid feature mode
_optimizer_cube_join_enabled	TRUE	enable cube join
_optimizer_dsdir_usage_control	126	controls optimizer usage of dynamic sampling directives
_optimizer_gather_feedback	TRUE	optimizer gather feedback
_optimizer_gather_stats_on_load	TRUE	enable/disable online statistics gathering
_optimizer_generate_transitive_pred	TRUE	optimizer generate transitive predicates
_optimizer_hybrid_fpwj_enabled	TRUE	enable hybrid full partition-wise join when TRUE
_optimizer_multi_table_outerjoin	TRUE	allows multiple tables on the left of outerjoin
_optimizer_null_accepting_semijoin	TRUE	enables null-accepting semijoin
_optimizer_partial_join_eval	TRUE	partial join evaluation parameter
_optimizer_performance_feedback	OFF	controls the performance feedback
_optimizer_proc_rate_level	BASIC	control the level of processing rates
_optimizer_proc_rate_source	DEFAULT	control the source of processing rates
_optimizer_strans_adaptive_pruning	TRUE	allow adaptive pruning of star transformation bitmap trees
_optimizer_unnest_scalar_sq	TRUE	enables unnesting of of scalar subquery
_optimizer_use_gtt_session_stats	TRUE	use GTT session private statistics
_optimizer_use_histograms	TRUE	enable/disable the usage of histograms by the optimizer
_parallel_ctas_enabled	TRUE	enable/disable parallel CTAS operation
_parallel_fault_tolerance_threshold	3	total number of faults fault-tolerance will handle
_partition_advisor_srs_active	TRUE	enables sampling based partitioning validation
_partition_cdb_view_enabled	TRUE	partitioned cdb view evaluation enabled
_pred_push_cdb_view_enabled	TRUE	predicate pushdown enabled for CDB views
_px_adaptive_dist_method	CHOOSE	determines the behavior of adaptive distribution methods
_px_adaptive_dist_method_threshold	0	Buffering / decision threshold for adaptive distribution methods
_px_back_to_parallel	OFF	allow going back to parallel after a serial operation
_px_cdb_view_enabled	TRUE	parallel cdb view evaluation enabled
_px_concurrent	TRUE	enables pq with concurrent execution of serial inputs
_px_cpu_autodop_enabled	TRUE	enables or disables auto dop cpu computation
_px_cpu_process_bandwidth	200	CPU process bandwidth in MB/sec for DOP computation
_px_filter_parallelized	TRUE	enables or disables correlated filter parallelization
_px_filter_skew_handling	TRUE	enable correlated filter parallelization to handle skew
_px_groupby_pushdown	FORCE	perform group-by pushdown for parallel query
_px_hybrid_TSM_HWMB_load	TRUE	Enable Hybrid Temp Segment Merge/High Water Mark Brokered load method
_px_join_skew_handling	TRUE	enables skew handling for parallel joins
_px_join_skew_minfreq	30	sets minimum frequency(%) for skewed value for parallel joins
_px_join_skew_ratio	10	sets skew ratio for parallel joins
_px_load_monitor_threshold	10000	threshold for pushing information to load slave workload monitor
_px_loc_msg_cost	1000	CPU cost to send a PX message via shared memory
_px_monitor_load	FALSE	enable consumer load slave workload monitoring
_px_net_msg_cost	10000	CPU cost to send a PX message over the internconnect
_px_object_sampling_enabled	TRUE	use base object sampling when possible for range distribution
_px_parallelize_expression	TRUE	enables or disables expression evaluation parallelization
_px_partial_rollup_pushdown	ADAPTIVE	perform partial rollup pushdown for parallel execution
_px_pwmr_enabled	TRUE	parallel partition wise match recognize enabled
_px_replication_enabled	TRUE	enables or disables replication of small table scans
_px_single_server_enabled	TRUE	allow single-slave dfo in parallel query
_px_tq_rowhvs	TRUE	turn on intra-row hash valueing sharing in TQ
_px_wif_dfo_declumping	CHOOSE	NDV-aware DFO clumping of multiple window sorts
_px_wif_extend_distribution_keys	TRUE	extend TQ data redistribution keys for window functions
_rowsets_cdb_view_enabled	TRUE	rowsets enabled for CDB views
_rowsets_enabled	TRUE	enable/disable rowsets
_rowsets_max_rows	200	maximum number of rows in a rowset
_rowsets_target_maxsize	524288	target size in bytes for space reserved in the frame for a rowset
_smm_max_size_static	92160	static maximum work area size in auto mode (serial)
_smm_px_max_size_static	230400	static maximum work area size in auto mode (global)
_sql_hvshare_threshold	0	threshold to control hash value sharing across operators
_stat_aggs_one_pass_algorithm	FALSE	enable one pass algorithm for variance-related functions
_upddel_dba_hash_mask_bits	0	controls masking of lower order bits in DBA
_use_hidden_partitions	FALSE	use hidden partitions
_zonemap_control	0	control different uses/algorithms related to zonemaps
_zonemap_use_enabled	TRUE	enable the use of zonemaps for IO pruning
optimizer_adaptive_features	TRUE	controls adaptive features
optimizer_adaptive_reporting_only	FALSE	use reporting-only mode for adaptive optimizations
parallel_degree_level	100	adjust the computed degree in percentage
parallel_execution_message_size	16384	message buffer size for parallel execution
parallel_fault_tolerance_enabled	FALSE	enables or disables fault-tolerance for parallel statement

PDB Pluggable Database是12c中扛鼎的一个新特性， 但是对于CDB中的PDB，默认启动CDB时不会将所有的PDB带起来，这样我们就需要手动alter pluggable database ALL OPEN;

 

 

例如：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup ;
ORACLE instance started.

Total System Global Area 1419685888 bytes
Fixed Size                  2288344 bytes
Variable Size             536872232 bytes
Database Buffers          872415232 bytes
Redo Buffers                8110080 bytes
Database mounted.
Database opened.
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 MACC                           MOUNTED

 

 

 

可以通过添加Trigger的形式来客制化startup时自动将PDB OPEN:

 

 

CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

Trigger created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup;
ORACLE instance started.

Total System Global Area 1419685888 bytes
Fixed Size                  2288344 bytes
Variable Size             536872232 bytes
Database Buffers          872415232 bytes
Redo Buffers                8110080 bytes
Database mounted.
Database opened.
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 MACC                           READ WRITE

NOTE: dependency between database MAC and diskgroup resource ora.DATADG.dg is established
alter pluggable database all open
Sun Jul 07 01:40:59 2013
This instance was first to open pluggable database MACC (container=3)
Opening pdb MACC (3) with no Resource Manager plan active
Pluggable database MACC opened read write
Completed: alter pluggable database all open
Starting background process CJQ0

 

 

使用SYS用户创建如下触发器即可：

 

 

conn / as sysdba

CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

_optimizer_use_histograms是一个CBO隐藏参数，目前所知该参数负责让CBO optimizer启用/禁用Histogram(This parameter enables/disables the usage of histograms by the optimizer.)

 

不过诡异的是这个参数在10gR2、12cR1中均存在，但是在11gR2中消失了。。。

 

11gR2:

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

SQL> alter session set “_optimizer_use_histograms”=false;
alter session set “_optimizer_use_histograms”=false
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

 

12cR1:

SQL> select * from v$version;

BANNER
——————————————————————————–
CON_ID
———-
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
0

PL/SQL Release 12.1.0.1.0 – Production
0

CORE 12.1.0.1.0 Production
0
BANNER
——————————————————————————–
CON_ID
———-
TNS for Linux: Version 12.1.0.1.0 – Production
0

NLSRTL Version 12.1.0.1.0 – Production
0
SQL> alter session set “_optimizer_use_histograms”=false;

Session altered.

 

 

 

10gR2:

 

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production

SQL> alter session set “_optimizer_use_histograms”=false;

Session altered.