OOW: Resolving Child Cursor Issues Resulting In Mutex Waits (Martin Klier)

PDF

When talking about parsing we’ve been talking about different parsing scenerarios as hard parse, soft parse, softer soft parse and no parse. What was very interesting in this presentation was the introduction of a new parse scenario which he called the harder soft parse. This scenario is caused by the new feature in 11g called Adaptive Cursor Sharing. The name harder soft parse comes from the idea that oracle has to some extra work. In addition to finding a query or hash match in the shared pool, Oracle will have to make a choice among available child cursors, this introduces a new bind peeking again.

The presentation then discussed through different wait events regarding mutexes. The very interesting part was the discussion on the JDBC pitfalls. When using PreparedStatement cursors, the jdbc code is used to set the bind values. For instance you might use the setNumber(2, 300) to set the second bind variable to the value 300. If you need to set a NULL value, you would use the setNull(2) function on the prepared statement.

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setNumber(1,300);
pstmt.setNull(2);

The problem with this is that the setNull maps to a varchar2 datatype and the setNumber maps to the number datatype. This will create the need for a new child cursor caused by the bind mismatch. When having many bind variables in one query, and possibly setting all these variables occationally to null, this could cause a magnitude of child cursors (actually 2 power of n). The solution to this is to use the setNull(2,java.sql.Types.INTEGER). THis causes the bind to map to a number and a bind match.

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setNumber(1,300);
pstmt.setNull(2,java.sql.Types.INTEGER);

A good advice is to check your system for cursor having many child cursors (very seldom there should be a need to have above 100 child cursors). You should also check how your applications handles bind variables.

Post a Comment

Your email is never published nor shared. Required fields are marked *