Database Software

Hello

We are planning oracle database update from 11.2 to 12c. For that I recently installed 12c on two node rac, for testing purpose, to determine what and how are changed in 12c. Also we created whole schema there, with all the objects and their dependencies, just a lot less data (development team has made clean start scripts for this, that do all the work and insert some data into tables for testing purposes). At the moment, our main concern is regarding mat. views - they just won't get refreshed in certain time (should be 10 seconds, but it takes 30-40 seconds). I also opened SR, and for now they (oracle team) have come to a decision that problem seems to be with dbms_job (default job that is declared for mat. views sync job), that won't start the job at next date.

At the moment, waiting update from oracle team.

Production system is still on 11.2

But another problems, high "log file sync" and "enq: IV - contention".

Database is not in archivelog mode, so no dataguard configured.

Log_buffer is 28888K. Redo_log file sizes are 50MB (before they were 100mb, but then I tried to decrease the size to 50mb, just like our 11.2 testing environment is, also I tried to make server parameter file in 12c as similar as possbile for our 11.2 testing environment). CPU count and memory is also the same. Only big difference at the moment is that, 12c is in virtual machine.

I have tried this (COMMIT_WAIT = NOWAIT and COMMIT_LOGGING = BATCH) for log file sync, but still no luck  https://antognini.ch/2012/04/commit_wait-and-commit_logging/.

Regarding "enq: IV - contention", I am aware of Doc ID 2028503.1, still this would not be the case, because I have _ges_server_processes value 2 on both nodes in cluster, so maximum value.

I am quite new on dba, so any help would be appreciated, of what and where to look next about possible causes..

I found query from here https://oraclefunda.wordpress.com/2009/10/30/query-to-find-top-5-wait-events-in-database/ to determine the top 5 wait events, and they both are included there.

FYI: Regarding dbms_job problem, I tried making another job (exactly similar to mat. view sync job), except, I changed "what", and replaced it with something simple like print one line, for example ",what      => 'sys.dbms_output.put_line(''Testjob6'');'"

So job that I declared, looked like this:

DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT

  ( job       => X

   ,what      => 'sys.dbms_output.put_line(''Testjob6'');'                              --Before was something like this ",what      => 'dbms_refresh.refresh(''"ETOIMIK_JAKKO"."AET_ISIKUD_ASJAD"'');'"

   ,next_date => to_date('09.01.2017 13:23:16','dd/mm/yyyy hh24:mi:ss')

   ,interval  => 'sysdate + 1/24/60/60 * 10 '

   ,no_parse  => FALSE

  );

  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char( x ));

COMMIT;

END;

/

But no luck, so I made similar job like above, but changed interval less than 10 seconds, like 5 seconds, and everything started working fine instantly. That's really strange (creating job, with interval greater than 10 seconds, changed nothing). So I better post this discovery to SR also.

Kind regards

Raul