log file switch (archiving needed)

I was running a test in Oracle 11.2 and the SQL below, which usually takes around 1 minute, seemed to be running forever:

SQL> begin
  2   for a in 1..8 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

I looked up the session's SID in V$SESSION to see if there was a row with wait_time set to zero. This means that the session is currently waiting on the event concerned. I saw a wait event I had not seen before:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2596

SQL>

To double check the diagnosis, I waited for 10 seconds:

SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL>

... and when I looked again, the seconds_in_wait had increased by 10 seconds too, proving that the session was spending all its time waiting on this event:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2606

SQL>

In most situations, you would have to clear some space from the archive area to allow the log file switch to proceed. However, the test I was running did not need the database to be in ARCHIVELOG mode so I closed the database and mounted it:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             318767656 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL>

However, when I tried to put the database in NOARCHIVELOG mode, I saw an error which I had not seen before - 2 new things in 1 day!

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback
database is enabled

SQL>

Well, I was doing some flahsback testing about a week ago but that is finished for now so I turned it off:

SQL> alter database flashback off
  2  /

Database altered.

SQL>

Then I was able to put the database in NOARCHIVELOG mode and get on with my original test, which I hope to report in a future post:

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL>

V$SESSION_WAIT SECONDS_IN_WAIT Column

This was tested on Oracle 11.2.0.1.0. I created a user in the red session below and used it to login to the database:

 

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL>

 

While this session was waiting, I logged in to the green session below and looked in V$SESSION_WAIT to see what it was waiting for:

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

  5  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client                642

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

 5  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client                652

 

SQL>

 

In the 10 second delay between the first query and the second, the value in the SECONDS_IN_WAIT column went up by 10, showing that the session was actively waiting on this event.

 

In Oracle 10, the information from V$SESSION_WAIT was included in V$SESSION so I could have used the following query instead:

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client               2036

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

------------------------------ ---------------

SQL*Net message from client               2046

 

SQL>