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

【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中新后台进程

【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注册监听。

12c pmon LREG

 

 

Reference:

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

【12c新特性】12c中新加入的Enqueue Lock

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$动态视图

【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新特性】多LGWR进程SCALABLE LGWR “_use_single_log_writer”

SCALABLE LGWR是12cR1中引入的一个令人激动的特性, 这是由于在OLTP环境中LGWR写日志往往成为系统的主要性能瓶颈, 如果LGWR进程能像DBWR(DBW0~DBWn)那样多进程写出redo到LOGFILE那么就可能大幅释放OLTP的并发能力,增长Transcation系统的单位时间事务处理能力。

 

在12cR1 中真正用SCALABLE LGWR实现了这个目的, 也可以俗称为多LGWR进程。

 

 

         select * from opt_12cR1 where name like '%log%'
_use_single_log_writer	ADAPTIVE	Use a single process for redo log writing
_max_outstanding_log_writes	2	Maximum number of outstanding redo log writes

 

SCALABLE LGWR主要受到隐藏参数_use_single_log_writer的控制,  该参数默认值为ADAPTIVE 。

 

该参数主要有三个可选值 true, false, adaptive, 默认值为ADAPTIVE。

  • 对于ADAPTIVE 和False 如果CPU个数大于一个则会有多个lg0n进程
  • 对于true 则不会生成多个lg0n进程,而如同12.1之前那样仅有单个LGWR

 

 

 

SQL> show parameter _use_single_log_writer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_use_single_log_writer               string      ADAPTIVE

www.askmaclean.com

[oracle@maclean1 ~]$ ps -ef|grep lg
grid      4344     1  0 08:07 ?        00:00:00 asm_lgwr_+ASM1
oracle   12628     1  0 08:48 ?        00:00:00 ora_lgwr_MAC_1
oracle   12636     1  0 08:48 ?        00:00:00 ora_lg00_MAC_1
oracle   12640     1  0 08:48 ?        00:00:00 ora_lg01_MAC_1
oracle   13206  7447  0 08:51 pts/2    00:00:00 grep lg

 

 

可以使用 10468 level 2 来trace adaptive scalable LGWR

 

 

[oracle@maclean1 trace]$ oerr ora 10468 
10468, 00000, "log writer debug module"
// *Document: NO
// *Cause:
// *Action: Set this event to the appropriate level for log writer debugging.
//

alter system set events '10468 trace name context  forever,level 2';

LGWR TRACE:
kcrfw_slave_adaptive_updatemode: time=426523948079110 scalable slave=1 arbiter=3 group0=10678 all=12733 delay=100 rw=98774 single=1973
48 scalable_nopipe=197548 scalable_pipe=108651 scalable=180439
kcrfw_slave_adaptive_savewritecounts: time=426523954275133 group0=10695 all=12752
kcrfw_slave_adaptive_savewritecounts: time=426523954662537 group0=10696 all=12753

CKPT TRACE:

*** 2013-07-07 10:52:21.528
kcrfw_slave_adaptive_saveredorate: time=426523941528521 curr=16649627696 prev=16635613056 rate=14014640 avg=14307212

*** 2013-07-07 10:52:24.553
kcrfw_slave_adaptive_saveredorate: time=426523944553556 curr=16664120996 prev=16649627696 rate=14493300 avg=14318490

 

 

 

实际测试可以发现 仅在redo 生成率非常高的环境中SCALABLE LGWR 对于redo写出的吞吐量有所帮助,进而提高OLTP环境的TPS。

_use_single_log_writer  = adaptive  2个LG slave进程:

 

Per Second Per Transaction Per Exec Per Call
DB Time(s): 2.8 0.0 0.00 0.33
DB CPU(s): 2.6 0.0 0.00 0.31
Redo size (bytes): 8,180,730.6 545.6
Logical read (blocks): 46,382.1 3.1
Block changes: 60,219.5 4.0

 

Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
LGWR 1M 0.14 .004M 4.3G 29.80 16.16M 1785 79.10

 

_use_single_log_writer  = true  使用single lgwr

 

Per Second Per Transaction Per Exec Per Call
DB Time(s): 2.8 0.0 0.00 0.34
DB CPU(s): 2.6 0.0 0.00 0.32
Redo size (bytes): 8,155,843.5 545.0
Logical read (blocks): 46,550.1 3.1
Block changes: 60,036.7 4.0

 

Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
LGWR 1M 0.13 .003M 4.8G 25.49 16.141M 1611 95.97

 

相关AWR附件:

_use_single_log_writer = adaptive

_use_single_log_writer = true

 

 

LGWR Scalability 的正面积极意义:

12c通过并发辅助进程以及优化的log file写算法有效改善 多CPU环境中由LGWR引起的等待瓶颈,释放LGWR性能。

一般来说这种性能改善在中小型的数据库实例中并不明显,实际上它们主要是为了那些64个CPU或更多CPU可用的数据库实例。但有性能测试报告显示在最少8个CPU的情况下对性能也有改善。

在之前的版本中,单一的LGWR处理所有的redo strands收集redo记录并将其写出到redo logfile中。在Oracle Database 12c中,LGWR开始并协调多个辅助helper进程,并行地完成以前LGWR一个人做的工作。

 

  • LGWR进程变成了多个LGnn形式的helper进程的协调指挥者,并负责保证这一堆并发进程所做的工作仍满足正确的LGWR顺序
  • LGnn进程负责读取一个或多个redo strands,负责实际写出到log file以及post前台进程

 

 

限制

在Oracle database 12c中,当使用SYNC同步redo传输方式传输redo到standby database时, 不支持使用上述的并行写SCALABLE LGWR,讲返回到串行写的老路子上。 但是Parallel LGWR/SCALABLE LGWR是支持ASYNC异步redo 传输的。

 

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

【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    0×00000001 – disable cell to cell data copy offload    0×00000002 – disable disk scrubbing offload to cell    0×00000004 – 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优化器新特性列表

【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

【12c新特性】12c中如何自动启动PDB Pluggable Database

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;
/