Mystery of _unnest_subquery=FALSE resolved

Continuing from where I left off in my previous post, we promptly referred this issue with the COBOL process SFPGLINT to Oracle as we had not done any customization. In the meanwhile we switched back the parameter "_unnest_subquery" to TRUE as an interim workaround. Oracle on their part started the investigation of the case with the usual suspicion around whether we are on a "supported version" of Oracle DB, PeopleTools, Weblogic etc...

Going down this path uncovered the horror that the production database (on version 11.2.0.2) was not up to date on the mandatory oracle patches for PeopleTools 8.52.We were missing patch numbers - 12704078, 13504791, 16065596 

What were these mandatory Oracle patches?
You can get them here ->[Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]
Based on your database version and PeopleTools version you need to be up to date and not have missed any of them.

How did Oracle find out what we missed? 
Oracle requested for the init.ora file and the output of this command (Need to login as SYS/SYSADM) - 

 $ORACLE_HOME/OPatch/opatch lsinventory > /tmp/opls.txt   

This text file will contain the oracle patch numbers installed in the database. You can then compare if you have all those mandatory patches (relevant to you) from the PeopleTools patch spreadsheet listed in this text file. If you can't find it, then it is highly likely you are missing it.

Eventually after these missing patches were applied in production we then switched back the database parameter flag _unnest_subquery to FALSE and ran the accounting entry generation process SFPGLINT and it worked fine. Thus the issue was resolved without having to do any further investigation.

That a SQL query would stop fetching rows with a database parameter set to False when the database is missing mandatory patches was beyond me. Murphy's law in full force. This issue highlighted the importance of not missing on mandatory patches, a lesson I won't forget soon.

Note: While applying the missing patches you we realized each patch has certain pre-requisite patch and then there is possibility of the mandatory patch mentioned being overridden by another recent patch which was not applicable to our DB version. If you end up in a similar situation you need to log a case with oracle requesting for a merge-patch for your DB version.

The curious case of _unnest_subquery=FALSE in Oracle PeopleSoft

At the customer site where I'm currently working I came across a very odd issue recently. The Student Financials "Generate Accounting Entries" COBOL process - SFPGLINT stopped working and failed to generate any rows and this continued for several days. The main SQL of the COBOL process failed to return any rows, despite data being present for the criteria specified in the SQL.

That it was happening only in production and not any of the non-production instances only added to the mystery surrounding this issue. As a last resort, I imported the production data into the non-production instance and the exact same SQL was able to produce rows. This led me to believe that there is some difference at the database settings that is causing this strange behavior.

Using this sql (Note: You can also get this from the init.ora file) - 

 select * from v$parameter;  

I compared and narrowed down to the differences between the two databases (PROD and non-PROD). One of the difference happened to be this mysterious parameter - _unnest_subquery . While it was TRUE in all non-production databases it was set to FALSE in Production.

At this point I had very little knowledge of what this parameter was and why it was set to FALSE. So I went ahead and altered my SQLTools session where I was replicating the issue, using the command -

 alter session set "_unnest_subquery" = FALSE ;  

After altering the session this command run, I re-ran the same SQL and to my surprise the SQL failed to return any rows. 

I knew how the problem can be fixed now (by modifying the initialization parameter _unnest_subquery to TRUE), but it was deeply disturbing to know that Oracle database would produce different results for the same SQL because of an initialization parameter.

A google search of this parameter revealed that this parameter was all too familiar for PeopleSoft customers elsewhere. This blog post gave me insights into what this parameter does. At my customer place, it turned out that a decision was taken to change this parameter "_unnest_subquery" to FALSE in Production (a few weeks prior to this COBOL process issue being noticed) in order to resolve other performance issues seen in online pages and batch programs, as explained in the blog post.

Continuing to explore more on the google search results revealed that this recommendation to set this parameter to FALSE for PeopleSoft came from none other than Oracle who ships the Oracle databases with the parameter set to TRUE as default. The recommendation is found in the Oracle support site [Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]. The recommendation is found for all database versions from 9i all the way to the more recent 12g (See below). 

Although this parameter should ideally have a performance impact, it still didn't explain as to why there is difference in the result rather than performance. So at this point we went ahead and logged a case with Oracle. I will continue on this topic in a later post as to where this leads to.