During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - so when it doesn't know what value to store, a space is used.
In general such BINDs lead to improper NESTED LOOPs.
Every time I found such executions, they where accepted as defect by development.
I'm in the lucky position to have tuning pack licensed, so I can use real time sql monitoring. That's very handy as I can save the a SQL in question together with a lot of infos (client, machine, module, start-time, BINDs, ...) in a html file and save it for later analysis/forward.
As v$sql_monitor shows specific executions, and it has all the BINDs stored as well, I can use it now to find other suspicious statements. So this little statement gives me some infos where to look closer:
SELECT sql_id, name, '--' || value || '--' FROM (SELECT KEY, sql_id, xmltype.Createxml(binds_xml) confval FROM v$sql_monitor) v, XMLTABLE('/binds/bind' passing v.confval COLUMNS name VARCHAR2(25) path '@name', value VARCHAR2(4000) path '.' ) WHERE value = ' ' OR value IS NULL;with an output like this:
SQL_ID NAME VALUE ------------- ------------------------- ----- 63pw6bqt84793 :2 -- --
Please do NOT start with this statement when your application is "slow" - not even when it's Peoplesoft.