Strange behavior - recursive sql

Subject: Strange behavior - recursive sql Platform: Oracle 9.2.0.6.0 on Solaris 9

In assisting a developer trying to track down a performance issue, I set up an 'on logon' trigger to start a 10046 trace, then fed the trace file thru tkprof. An analysis of the results solved our problem, but raised a few questions.

What we found in looking at the 'before' trace was that most of the time was spent on this statement:

UPDATE "NMM"."POSTING_DETAIL" set "PSTDTL_PSTSUM_TS" = null where "PSTDTL_PSTSUM_TS" = :1

The explain plan showed a full table scan, with 200,170 waits on db file scattered read and another 93,195 waits on db file sequential read.

Two things were VERY strange about this. First, tkprof showed the parsing user id to be SYS, with a recursive depth of 2. We don't understand what we could have done to cause this kind of statement to show up as recursive SQL. The developer scanned the procedure to confirm that he is not issuing this statement.

Second, the column PSTDTL_PSTSUM_TS that this statement is setting to null has a NOT NULL constraint! Am I losing my mind?

While we couldn't figure out what was going on, we decided to try throwing an index on the column referenced by the WHERE clause and re-ran the test. This time, the procedure ran much faster. However, when we looked to see the performance of the problem query described above, we found that it had disappeared completely. There was no such query shown in the tkprof report.

Can anyone explain what I'm seeing in these traces? Received on Mon Mar 20 2006 - 09:40:17 CST