The problem is in Oracle 10G Databases, there is a problem with SQL Text Address. If Oracle memory does not show the SQL Address then PAO does not "see" the same and thus the SQL statement executions are not seen by PAO.
To elaborate further,
When running the trigger on 11G or 9i, the sql text address from the session changes accordingly to current running sql, but it is not happening in 10.2.0.3 / 10.2.0.4
Means,
When running trigger, if we will run the following query:
Select PREV_SQL_ADDR, SQL_ADDRESS from v$session where SADDR ='0700000062510408'; where SADDR is session address in memory,
we will see that for Oracle 10G Databases, the trigger sql address did not appear under PREV_SQL_ADDR nor under SQL_ADDRESS (current running).
Â
Seems like incorrect oracle behavior specific to Oracle 10G databases
Â
Dev team did not observe such a behaviour in Oracle 11G databases. Our recommendation is to try the SQL and Trigger test from a Oracle 11G database to see if the problem can be reproduced.