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) -
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;
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.
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.
No comments:
Post a Comment