I compared the Database parameters with PROD DB running in XXDFFGPRDDB and I found the following differences. If we have
same resources and file system configuration in PROD and QA, and the
issue only happens in QA, then we should change the parameters in QA to match
those in PROD.
Please get PM approval for changing the following parameters
in QA to match those in PROD.
PROD DB in
============================
streams_pool_size
= 512M
pga_aggregate_limit
= 12G
filesystemio_options
= "ASYNCH"
undo_retention
= 40000
The following hidden parameters
are only in PROD:
_disable_streams_pool_auto_tuning=
TRUE
ß this is a workaround for
unpublished Bug 24560906 for EXPDP And IMPDP Slow Performance.
_optimizer_gather_stats_on_load=
FALSE
ß changed was made on Dec 13
11:42:08 2016. this is a workaround for Bug 19695624 - ORA-600 [qctfrc :
bfc] reported during online statistics gathering (Doc ID 19695624.8)
_smu_debug_mode
=
33554432
ß changed was made on Jan 20
08:31:26 2017 . this is a workaround for Bug 5387030 - Automatic tuning
of undo_retention causes unusual extra space allocation (Doc ID
5387030.8). The default value of _smu_debug_mode is 0.
“_smu_debug_mode
= 33554432”
causes the v$undostat.tuned_undoretention to be calculated as
the
maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
QA DB in XXDFFGQADB
============================
streams_pool_size
= 192M
pga_aggregate_limit
= 24G
filesystemio_options
= "SETALL"
undo_retention
= 10800
I reviewed Things To Consider For Setting
filesystemio_options And disk_asynch_io (Doc ID 1987437.1) and found the
following information:
The parameter filesystemio_options controls
whether asynchronous and/or direct I/O is attempted for Oracle files available
through a file system. The parameter has no effect on disk accesses that
bypass the OS file system layer—such disk access always uses direct I/O.
Raw files, files on ASM, files on the Veritas file system when accessed using
Oracle Disk Manager (ODM), and files accessed using Direct NFS (dNFS) all
bypass the file system layer, so this parameter is ignored in all such cases.
The following settings are available for this parameter:
Synchronous I/O
|
Asynchronous I/O
|
|
Buffered I/O
|
none
|
asynch
|
Direct I/O
|
directIO
|
setall
|
i would to thank you for the efforts you have made in writing this article oracle training in chennai
ReplyDeleteGe set your career towards Oracle with the best Oracle training in Chennai with Infycle Technologies. Infycle offers the best software courses such as Big Data, Java, Python, Hadoop, etc in a complete hands-on training tutored by professionals in the field. Apart from the training, the mock interviews for the placement will be given, so that, they can face the interviews with full confidence. While completing the mock interview, the candidates will be placed in the top MNC's with a great salary package. For all this, call 7502633633 and make this happen for your happy life. Best Oracle Training Institute in Chennai
ReplyDeleteInfycle Technologies offers the
ReplyDeletebest Data Science training in Chennai and is widely known for its excellence in giving the best Data Science Certification course in Chennai. Providing quality software programming training with 100% placement & to build a solid career for everTechnologiesy young professional in the software industry is the ultimate aim of Infycle Technologies. Apart from all, the students love the 100% practical training, which is the specialty of Infycle . To proceed with your career with a solid base, reach Infycle Technologies through 7502633633.