Gathering stats for gc lost blocks

In a two node RAC gc cr block lost appeared to be second in the top 5 wait events list. But this was only happening in one of the nodes whereas other node has almost zero waiting on this event. Stats plotted with ADMon taken from statspack base tables

Waits
Wait Timesgc lost blocks diagnostics [ID 563566.1] describe what to look for to diagnose this wait event. Among them is stats related to dropped and fragmented packets. Looking at the two following could be observed, on the problem node

 netstat -s
Ip:
   650337997 total packets received
   145 with invalid addresses
   0 forwarded
   0 incoming packets discarded
   422623145 incoming packets delivered
   990924957 requests sent out
   1718163 fragments dropped after timeout
   271652117 reassemblies required
   43937411 packets reassembled ok
   4233888 packet reassembles failed
   42533293 fragments received ok
   259374063 fragments created

On the non-problem node

Ip:
   2896602613 total packets received
   4445 with invalid addresses
   0 forwarded
   0 incoming packets discarded
   1690637959 incoming packets delivered
   2164503370 requests sent out
   4433 outgoing packets dropped
   4 fragments dropped after timeout
   1430346149 reassemblies required
   224385940 packets reassembled ok
   4 packet reassembles failed
   239685318 fragments received ok
   52 fragments failed
   1386980139 fragments created

Solution for this is to increase the reassembly buffer space (ipfrag_low_thresh,ipfrag_high_thresh and ipfrag_time). But this didn't help either. As per above metalink note "In most cases, gc buffer lost has been attributed to (a) A missing OS patch (b) Bad network card (c) Bad cable (d) Bad switch (e) One of the network settings.".

Following shell script could be used to store the IP packet stats relating to the above wait event in a database table.

SQL>create table ipfrag(stat_time timestamp default systimestamp, fragments_dropped number, reassemblies_required number, reassembled_ok number, reassemble_failed number);

$ netstat -s | grep 'Ip' -A 12 | grep -E 'reassembl|dropped' | awk 'BEGIN {FS=" "} $2~/^fragments/ {dropped = $1} $2~/^reassemblies/ {res_required = $1}
$2~/^packets/ {res_ok = $1}  $3~/^reassembles/ {res_fail = $1; state=3} state == 3 {printf "%s%d,%d,%d,%d%s\n","insert into ipfrag (fragments_dropped
,reassemblies_required,reassembled_ok,reassemble_failed) values(",dropped,res_required,res_ok,res_fail,");"}' | sqlplus -s username/pw

Update on 06-06-2011
These servers are hosted in a hosting company's data centre and problem turned out to be a mismatch in a switch setting. Once fixed (speed and duplex was set to auto/auto in this case) and gc cr block lost waits went away on the problem node as well. Graphs taken with ADMon after the fix.

Performance Overhead with FBDA 11.1 vs 11.2

Blog came about as a result of evaluating alternatives to a trigger base audit mechanism. Few simple test cases were run against databases on 11.1 and 11.2 with FBDA enabled and disabled. Test consists of inserting 100k rows into a table (which doesn't have any indexes and only two columns), then updating and deleting one row, updating and deleting 9999 rows then creating an unique index and running a update and delete loop. A commit is issued soon after the DML which stresses the fbda process and also makes the undo segments eligible for re-write once fbda has done with archiving. Flashback archive was created on a separate tablespace and the storage system is a file system (RHEL 5, ext3) not ASM and both 11.2 and 11.1 databases resided in the same machine.

On 11.2 (PSU 11.2.0.2.2)
Even though it was said in FBDA documentations that insert statements does not generate any records in the flashback archive, inserts were slow on tables with FBDA enabled than on table without FBDA. strace on the fbda process showed burst of activity when inserts were going on, though no records are written clearly some work is being carried out which adds some overhead.
However the individual DML statements (outside the loops) that were run (with FBDA enabled) had elapsed time that were close to DML statements run without FBDA.Time shown is inclusive of execute time and commit timeIt seem if the fbda is able to keep up with the modification the overhead is less but whenever there's burst of activity it is possible to exhaust the fbda process to add considerable overhead.

On 11.1 (PSU 11.1.0.7.7)
It was difficult to get the test case working on 11.1 at times with PL/SQL terminating with the following

SQL>  begin
  2      for i in 1 .. 100000
  3      loop
  4      insert into x values(i,i||'abcdefg');
  5      commit;
  6      end loop;
  7      end;
  8      /

 begin
*
ERROR at line 1:
ORA-55616: Transaction table needs Flashback Archiver processing
ORA-06512: at line 4

Explanation for this error says

*Cause: Too many transaction table slots were being taken by transactions on tracked tables.
*Action: Wait for some amount of time before doing tracked transactions.

Which indicates it is possible to exhaust the slots in the tracker table and worryingly this will put an end to further DML on that table until more slots are available. Overhead was far greater than in 11.2However the key difference between 11.2 and 11.1 came when the elapsed time on individual DMLs were compared. Although execute time was low and similar to elapsed times of 11.2 or even without FBDA, commit time was far greater than in 11.2It could be said that 11.2 has some improvements over 11.1 when it comes to FBDA but still careful consideration must be given to the performance overhead introduced by FBDA.

From metalink
Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666
Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]

Test Case

set timing on
--create table x (a number, b varchar2(1000)); -- for without FBDA
create table x (a number, b varchar2(1000)) flashback archive fbdarchive; -- for with FBDA

begin
  for i in 1 .. 100000
  loop
    insert into x values(i,i||'abcdefg');
    commit;
   end loop;
end;
/
update x set b = 'aa' where a = 10;
commit;
delete from x where a = 10;
commit;
update x set b = 'bbbb' where a < 10001;
commit;
delete from x where a < 10001;
commit;

create unique index aidx on x(a) compute statistics nologging;

begin
  for i in 1 .. 100000
  loop
    update x set b = 'abcdef' where a = i;
    commit;
   end loop;
end;
/

begin
  for i in 1 .. 100000
  loop
    delete from x where a = i;
    commit;
   end loop;
end;
/

Flashback Data Archive 11.1 vs 11.2

Some of the DDL statements that weren't allowed on tables that had FDBA enabled in 11.1 are now allowed on 11.2. Section below gives a summary of the changes.

From 11.1 documentation
DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or sub partition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement

On 11.1.0.7 (11.1.0.7.3 PSU)

SQL> create table x (a number, b varchar2(1000)) flashback archive fbdarchive;

Table created.

SQL> alter table x rename to y;
alter table x rename to y
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> truncate table x;
truncate table x
               *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

From 11.2 documentation
DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:
ALTER TABLE statement that does any of the following:
Adds, drops, renames, or modifies a column
Adds, drops, or renames a constraint
Drops or truncates a partition or sub partition operation
TRUNCATE TABLE statement
RENAME statement that renames a table

Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:
ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

ALTER TABLE statement that moves or exchanges a partition or sub partition operation

DROP TABLE statement

On 11.2 (11.2.0.2)

SQL> create table x (a number, b varchar2(1000)) flashback archive auditarchive;

Table created.

SQL> alter table x rename to y;

Table altered.

SQL> truncate table y;

Table truncated.

SQL> drop table y;
drop table y
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

More bugs on 11.1 vs 11.2 Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666

Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]

New On Java 7

Following are some of the things new on Java 7. Taken from various java documentations.

Catching More Than One Type of Exception with One Exception Handler

In Java SE 7 and later, a single catch block can handle more than one type of exception. This feature can reduce code duplication and lessen the temptation to catch an overly broad exception.

In the catch clause, specify the types of exceptions that block can handle, and separate each exception type with a vertical bar (|):http://www.blogger.com/img/blank.gif

catch (IOException|SQLException ex) {
logger.log(ex);
throw ex;
}

Note: If a catch block handles more than one exception type, then the catch parameter is implicitly final. In this example, the catch parameter ex is final and therefore you cannot assign any values to it within the catch block.


Using Non-Reifiable Parameters with Varargs Methods

Generics in varargs
The compiler issues a warning "warning: [unchecked] unchecked generic array creation" when generics are passed as varargs. This warning could be suppressed with @SafeVarArgs annotation on the method, which would ensure that the method wouldn't perform any unsafe operation.

public static void main(String[] args) {
        compute(new HashMap(), 
new TreeMap());
       }

@SafeVarargs
    static void compute(Map... args) {
        }
}

Using Strings in switch Statements
A switch works with the byte, short, char, and int primitive data types. It also works with enumerated types (discussed in Enum Types), the String class, and a few special classes that wrap certain primitive types: Character, Byte, Short, and Integer (discussed in Numbers and Strings).

In Java SE 7 and later, you can use a String object in the switch statement's expression.

public static int getMonthNumber(String month) {

int monthNumber = 0;

if (month == null) { return monthNumber; }

switch (month.toLowerCase()) {
case "january":    monthNumber =  1; break;
case "february":   monthNumber =  2; break;
case "march":      monthNumber =  3; break;
case "april":      monthNumber =  4; break;
case "may":        monthNumber =  5; break;
case "june":       monthNumber =  6; break;
case "july":       monthNumber =  7; break;
case "august":     monthNumber =  8; break;
case "september":  monthNumber =  9; break;
case "october":    monthNumber = 10; break;
case "november":   monthNumber = 11; break;
case "december":   monthNumber = 12; break;
default:           monthNumber =  0; break;
}

return monthNumber;
}

The String in the switch expression is compared with the expressions associated with each case label as if the String.equals method were being used.

Diamond syntax
Until Java 7, generic instances were created with

Map<Integer, String> map = new HashMap<Integer, String>();

In Java 7 only refernce requires type information and actual type could be without type information.

Map<Integer, String> map = new HashMap<>();

Binary literal and underscore in numeric literals
Binary values could be defined as

int number = 0b1100110; // decimal 102

Lengthy numeric values can be separated using underscores

long longnum = 28_06_86L;
long verylongnumber = 2334_7656_4503_3844L;

Automatic Resource Management
With the new Java 7 try block syntax, resources declared while defining the try block will automatically close when the control comes out of the try block.

try (BufferedReader reader = new BufferedReader(new FileReader(fileName))) {
while ((line = reader.readLine()) != null) {
         //do some work
       }
    } catch (IOException ex) {
 }