Hi Experts,
I am trying to tune Oracle 12c Non-cdb database on Linux, referring a similar 12c(CDB) Database hosted on RHEL6 server which yields good and expected results when we run our application.
We are not getting the expected throughput, we are running performance test ... as part of our application testing using JMETER
=> Redo log size is 2 GB each with 9 groups - does this make any difference in performance, any suggestions in this regards.
=> Base tables when queried for tablespace DDL's returns invalid/corrupted objs
=>
grep Huge /proc/meminfo
AnonHugePages: 8192 kB
HugePages_Total: 13312
HugePages_Free: 510
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
DB server performing well which is used as reference,
OS - RHEL6
RAM : 62Gb
SWAP : 80GB
Current DB server:
OS - RHEL7
RAM : 62 GB
SWAP : 15GB
xxxxxxxxxxxxx.__data_transfer_cache_size=0
xxxxxxxxxxxxx.__db_cache_size=21474836480
xxxxxxxxxxxxx.__java_pool_size=268435456
xxxxxxxxxxxxx.__large_pool_size=201326592
xxxxxxxxxxxxx.__oracle_base='/ora00/app/oracle'#ORACLE_BASE set from environment
xxxxxxxxxxxxx.__pga_aggregate_target=6777995264
xxxxxxxxxxxxx.__sga_target=26843545600
xxxxxxxxxxxxx.__shared_io_pool_size=536870912
xxxxxxxxxxxxx.__shared_pool_size=4294967296
xxxxxxxxxxxxx.__streams_pool_size=0
*._immediate_commit_propagation=FALSE
*._optim_peek_user_binds=FALSE
*._with_subquery='INLINE'
*.commit_logging='BATCH'
*.commit_wait='NOWAIT'
*.compatible='12.1.0.2.0'
*.control_files='/OracleDBUndo/xxxxxxxxxxxxx_12c/control01.ctl','/OracleDBData02/xxxx/control02.ctl mweb_12c/control02.ctl'
*.cursor_sharing='EXACT' OORLY DESIGNED
*.db_block_size=8192
*.db_file_multiblock_read_count=64
*.db_files=500
*.db_name='xxxxxxxxxxxxx'
*.db_writer_processes=8
*.diagnostic_dest='/ora00/xxxxxxxxxxxxx'
*.disk_asynch_io=TRUE# added for log file sync
*.event='44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024'
*.filesystemio_options=setall #directio
*.job_queue_processes=20
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=153.71.9.157)(PORT=1 521)))'
*.lock_sga=TRUE
*.log_archive_dest='/OracleDBArcRedo/xxxxxxxxxxxxx'
*.log_checkpoint_interval=8388608
*.log_checkpoint_timeout=1800
*.open_cursors=1000
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=25
*.parallel_degree_policy='AUTO'
*.parallel_max_servers=200
*.parallel_min_time_threshold='AUTO'
*.parallel_servers_target=100
*.pga_aggregate_target=6427m
*.plsql_code_type='native'
*.plsql_optimize_level=2
*.processes=1280
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=500
*.sga_max_size=26843545600
*.sga_target=26843545600
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS2'
*.use_large_pages='ONLY'
The above parameters are from a well performing performance DB server taken as reference(VM).