Adverse SQR performance as a result of the Oracle setting _unnest_subquery=FALSE

As I mentioned in an earlier post that the performance tuning recommendation from Oracle for PeopleSoft customers requires setting the init.ora parameter _unnest_subquery to FALSE.

While this is probably a panacea to many a performance issues seen in the system, it is not the case in some occasions. Just like an adverse reaction of a drug that is meant to cure one disease ends up you having another disease; in the case of my customer's PeopleSoft Campus Solution system it caused the custom attendance calculation process to see a sudden and huge spike in the time take to complete (from ~40 odd minutes to 7.5 hours).

Diagnosis gone wrong:

The issue was dealt by many a consultants and system admins over the course of a year without any tangible difference in performance and then I happened to join the team and thus all and sundry were eager to have their hands off this issue. And so I got to know that all cliched solutions were tried out by this time.

As I expected, the issue was misdiagnosed or rather no proper diagnosis was done. The very mention of performance issue and the guys went about lining up the usual suspects (cliched performance fixes) - 

• Gathering stats on the tables
• Flushing shared pool
• altering session parameters
• rebuilding indexes
• putting hints and doing trial and error

When nothing worked, perhaps they just clung on to hope that the problem would disappear just as it miraculously appeared out of nowhere.

What I did differently:

The first thing I did was to do a real-time monitoring of the SQL's as they were being executed by the SQR while the process took long time to run. I used the below SQL to do that - 

 SELECT S.username,   
     s.sid,   
     s.inst_id,   
     s.serial#,   
     s.osuser,   
     t.sql_id,   
     sql_text   
 FROM  v$sqltext_with_newlines t,   
     gv$session s   
 WHERE t.address = s.sql_address   
     AND t.hash_value = s.sql_hash_value   
     AND s.status = 'ACTIVE'   
     AND s.username <> 'SYSTEM'   
     AND osuser <> :1   
 ORDER BY s.sid,   
      t.piece   

From this I noticed a couple of SQL's that were always seen running and running long. Both SQL's had an "exists" clause and when I extracted the explain plan I found that the plan for both the SQL's were significantly costlier as shown below.
SQL1 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt C   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw C1   
           WHERE C1.emplid = :4   
              AND C1.crse_id = :5   
              AND C1.strm = :2   
              AND C1.class_nbr = C.class_nbr   
              AND C1.stdnt_enrl_status = 'E')   

SQL2 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw A1   
           WHERE A.institution = A1.institution   
              AND a.strm = a1.strm   
              AND a.class_nbr = a1.class_nbr   
              AND A1.emplid = :3   
              AND A1.crse_id = :4   
              AND A1.strm = :2   
              AND A1.class_nbr = A.class_nbr   
              AND A1.stdnt_enrl_status = 'E')   

Note: I did get a much better plan when the session parameter _unnest_subquery is TRUE. However there was no going back on that one. It had to remain FALSE. So the only option was to rewrite the SQL's to perform better without altering the program logic.

Now that it was clear that the plan was too costly I rewrote the SQL's to do a equi-join to get the row count rather than using exists clause as shown below. I was probably lucky that the SQL with an equi-join would suffice in this situation. A more straightforward and easier solution was to use nested begin-select statement within the SQR program, which I thought of using if equi-join is not an option.

With the revised SQL, the plan prepared by Oracle was way better than before.

SQL1 (after):

 SELECT Count(1)   
 FROM  ps_attend_tmplt C,   
     ps_stdnt_enrl_l_vw C1   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND C.institution = C1.institution   
     AND C.strm = C1.strm   
     AND C.class_nbr = C1.class_nbr   
     AND C1.emplid = :4   
     AND C1.crse_id = :5   
     AND C1.strm = :2   
     AND C1.class_nbr = C.class_nbr   
     AND C1.stdnt_enrl_status = 'E'   

SQL2 (After):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A,   
     ps_stdnt_enrl_l_vw A1   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND A.institution = A1.institution   
     AND A.strm = A1.strm   
     AND A.class_nbr = A1.class_nbr   
     AND A1.emplid = :3   
     AND A1.crse_id = :4   
     AND A1.strm = :2   
     AND A1.class_nbr = A.class_nbr   
     AND A1.stdnt_enrl_status = 'E'   

Thus I was able to bring back the run time performance from 7.5 hours+ to the usual 30 to 40 minutes. What did you do in a similar situation or is there a better way out? Leave a comment below. Thanks!!

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.

Trimming spaces and adding spaces on a BI Publisher report

Quite often in BI Publisher, there is a need to trim unnecessary spaces and add spaces on occasions. 

Think of printing an address. PS_ADDRESSES table has so many fields but when printing the address, some of the fields may not necessarily have values, leaving the address formatting  messed up with a lot of white spaces. This is what I'm talking about - 

The RTF template shown above generates the output as shown here - 

Removing unwanted space
In order to solve this issue, I created one BI Publisher field "mail address" with the code written as shown below.

As you can see now, all the extra white space for fields that didn't have a value is now gone (I'll ignore the border issue for now). Though this fixed the extra space issue, it introduced a new problem. I had given a deliberate space between CITY and POSTAL field in the formula but was ignored by BI Publisher in the output. 

Adding Space
The way you can add a space is quite simple, enclose the space within quotes and embed it between the BI publisher tag as shown below -

 <?if:C.ADDRESS1 != ' '?><?C.ADDRESS1?><?end if?>  
 <?if:C.ADDRESS2 != ' '?><?C.ADDRESS2?><?end if?>  
 <?if:C.ADDRESS3 != ' '?><?C.ADDRESS3?><?end if?>  
 <?if:C.ADDRESS4 != ' '?><?C.ADDRESS4?><?end if?>   
 <?C.CITY?><?' '>?<?C.POSTAL?>  

This gives the proper output I was looking for - 

Printing Labels in PeopleSoft across down using BI Publisher (formerly XML Publisher)

The Problem:
Recently I was tasked to build the report in PeopleSoft to generate the list of external examiners (yes, it is obviously an education institute) which will eventually be printed on to commercially available Labels. The labels would be pasted on to the question paper stack assigned to the designated exam hall for distribution.
As the de-facto choice of technology to accomplish something of this nature I went with XML Publisher a.k.a BI Publisher. What I thought was a simple report eventually turned out to be anything but simple to print the data across and then down....

The difficulty:
My Data source was a PS Query which had already done the necessary sorting with the query criteria. 
In BI Publisher, the data within XML tags are printed one below the other using a For-loop in the form of a table. This in itself presented a challenge as the data would get printed vertically down in a single column. But I wanted multiple column output. Without thinking too much my mind immediately jumped into the idea of breaking the page into 4 columns which is an option in MS Word. When I printed the sample data I ended up with an output like this - 

1   4   7   10

2   5   8   11

3   6   9   12

What I was actually looking to accomplish was to print the data in an "across down" manner. The same way we write things on a piece of paper (left to right, then down),

1    2    3    4 

5    6    7    8 

9   10   11   12

The next best option:

As I had developed cross tab/pivot reports previously, which is primarily used when the number of columns and rows are dynamic. In my situation, I had a fixed number of rows which had to be printed in a columnar fashion. After messing with the PS Query a bit and the RTF template, I then played around with the H tag (horizontal-break-table) of BI Publisher cross tab reports and got the output I wanted albeit with a catch. Each box (or row) of data elements got printed on separate pages. Unfortunately the horizontal-break-table causes a page-break and not a line-break and this works only with PDF output.

The solution:

At this point I had tried so many things that I had given up on BI Publisher and a tad disappointed at how much difficult this was proving to be as opposed to a simple MS Word mail merge. And just when I was about to settle for the closest solution I got using the 4 column output I discussed above, I found this wonderful blog from Vetriselvan where he had shown how to accomplish the output I was trying to get.

My sample template appears this way -

and the sample output is just the way I wanted - 

There is also a caveat with this solution as well. The 4 embedded tables grow vertically down printing 1 data element and skipping the other 3 in a round robin fashion, thus making it appear to print across and then down. As long as the data is even, this works fine. However if any of the data element is larger than the rest, the rows will be of uneven size and the output would look like this (Element-28 occupies more height than the rest causing the alignment to go haywire) -

This is not a big issue as most of the commercial labels have pre-defined height and width and the tables embedded in the RTF template have to adhere to those size restrictions. This coupled with choosing the right font and font-size addresses any such issues. 

Feel free to drop a comment if you have a question or accomplished using a different approach.

Missing or invalid version of SQL library PSORA64 (200, 0) error with PT8.54

Error Message: Missing or invalid version of SQL library PSORA64 (200, 0)
I received this error while trying to open the application designer for PeopleTools 8.54.

Root Cause: 
By now you would have come across the news that PT8.54 is a 64 bit application. Like me, if you forgot to install the Oracle 64 bit client and tried to access PT8.54 with the existing 32 bit Oracle client you will receive this particular error message. 

Remedy:
Download the Oracle 64 bit client from Oracle site (link here) and install it. Once installed you should not get this error anymore. Do not forget to update the tnsnames.ora file for the 64 bit client.

If you still receive the error, check the PATH environment variable value of your PC. Very unlikely, but it could be possible that the ORA_HOME of the 64 bit client was somehow not appended to the PATH value during the installation. You can manually add it to resolve the issue.

Note: Although Oracle 12c 64 bit client which is the latest version available I could not install it (Installation was stuck at the skip update step despite trying several things). So I ended up choosing the older version i.e 11gR2 client which worked without any hitch.

Implementing Captcha on PeopleSoft login page using JCaptcha - Part-2

Continuing from JCaptcha Part-1 we move on to Step-3 of the JCaptcha implementation on the PeopleSoft login page.

You will have to repeat the steps henceforth for each domain (in case of multiple domains)

Step-3:

• Before customizing the files signin.html, text.properties and error.properties, make a backup for restoration purposes. The files can be found here -
  

   <%PS_HOME%>webserv\<Domain>\applications\peoplesoft\PORTAL.war\WEB-INF\psftdocs\<Domain>  
  

  Open the signin.html file in a text editor and add the following code after the <HEAD> tag
  

   <!-- Added by V.Rao to prevent captcha image caching --><META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">  
  

• In the JavaScript section add the Ajax code below
  

   <!-- added by V.Rao [Begin] Ajax function to refresh the captcha image -->  
   <script>  
   function chitra()  
   {  
   var xmlhttp;  
   if (window.XMLHttpRequest)  
    {// code for IE7+, Firefox, Chrome, Opera, Safari  
    xmlhttp=new XMLHttpRequest();  
    }  
   else  
    {// code for IE6, IE5  
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  
    }  
   xmlhttp.onreadystatechange=function()  
    {  
     if (xmlhttp.readyState==4 && xmlhttp.status==200)  
     {  
     document.getElementById("sampige").src="/chitra_durga.jpg?r="+new Date().getTime();  
     }  
    }  
   xmlhttp.open("GET","/chitra_durga.jpg",true);  
   xmlhttp.send();  
   }  
   </script>  
   <!-- added by V.Rao [End] Ajax function to refresh the captcha image -->  
  

• After this, search for the form tag element and modify/replace the action attribute to call the custom Captcha validation JSP as opposed to the PeopleSoft delivered psp servlet as shown below
  

   <!-- Commented by V.Rao <form action="?cmd=login&languageCd=<%=languageCd%>" method="post" id="login" name="login" autocomplete="off" onSubmit="signin(document.login)"> -->  
   <!-- Added by V.Rao --><form aaction="<%=psCtxPath%><%=psHome%>/Rao99.jsp" method="post" id="login" name="login" autocomplete="off" onSubmit="<%=psCtxPath%><%=psHome%>/Rao99.jsp">   
  

• The next piece of customization on the signin html file is to bring in the captcha elements i.e captcha image, refresh icon to refresh the captcha image and a text box to capture the verification code entered by the user. The sample code from my signin page is shown below. Place this code below the "pwd" element.
  

   <!-- Code added by V.Rao [Begin] Captcha elements-->  
   <tr>  
   <td height="35"><img src="/chitra_durga.jpg" alt="<%=20000%>" id='sampige' width="175" height="65" />&nbsp;</td>  
   <td height="35" align='middle'>  
    <img src="<%=psCtxPath%><%=psHome%>/images/PT_REFRESH_ICN.gif" alt="Refresh" width="16" height="16" onclick="chitra()" />  
    &nbsp;</td>  
   <td height="35"><input type = "text" name="jcaptcha" placeholder="<%=20000%>" class="pslogineditbox" tabindex="4"></td>  
   </tr>  
   <!-- Code added by V.Rao [End] -->  
  

  You would have noticed that I have used scriptlet with the number 20000 in a couple of tags. This is the message number that serves as the alternate text for the captcha image as well as the default message shown in the text box provided for the user to key in the captcha verification code. This message text is defined in the text.properties file. The Ajax function chitra() that is triggered upon clicking the refresh icon in turn triggers the Java servlet "chitra_durga.jpg". The servlet generates and streams a fresh captcha image to the client browser.
• The last piece of customization on the signin html file is to modify the submit button action. Search for the input tag with type "Submit" and modify the code as given here
  

   <!-- Commented by V.Rao <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="submitAction(document.login)"></td> -->  
   <!-- Added by V.Rao --> <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="<%=psCtxPath%><%=psHome%>/Rao99.jsp"></td>  
  

• Open the text.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20000=Enter the verification code appearing in the image  
  

• I also need to display a custom error message if the captcha validation fails. To do this open the error.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20001=Verification code is required.  
   20002=Invalid verification code. Please try again.  
   20003=Verification failed.  
  

Step-4
In the HTML for the captcha elements, I had used the PeopleSoft delivered refresh icon - PT_REFRESH_ICN. Unfortunately this image is not available by default on the images folder in the web server. It is fetched from the database and cached in the webserver when a PeopleSoft page references the image. Therefore I decided to always have it available on the webserver. I did that by copying the refresh icon to the image folder located at 

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<domain>\images  

Step-5
Create the JSP file that will intercept the login request and do the validation of the captcha entered by the user. The JSP does one of two things, forwards the login request to the psp servlet if captcha validation is successful or else re-load the login page with the custom captcha error message shown (error code = 20001). The code in my JSP file i.e Rao99.jsp is listed below.

 /**  
  * @author Venkateshwara Rao  
  * @Date  7-Sep-2014  
  */  
 <%@page import="com.octo.captcha.module.servlet.image.SimpleImageCaptchaServlet" %>  
 <%   
   String userCaptchaResponse = request.getParameter("jcaptcha");  
   userCaptchaResponse = userCaptchaResponse.trim();   
   Boolean captchapassed = false;  
   if( userCaptchaResponse.equals("") || userCaptchaResponse == null )  
   {  
 %>  
    <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20001" >  
    </jsp:forward>  
 <%  
   }  
   else  
   {    // Captcha text is not blank or null  
        try  
        {   
             captchapassed = SimpleImageCaptchaServlet.validateResponse(request, userCaptchaResponse);  
        }  
        catch(Exception e)  
        {  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20003" >  
     </jsp:forward>  
 <%  
        }  
        System.out.println("captcha passed = " + captchapassed ); /* Logging to weblogic console for debugging purpose */  
        if(captchapassed)  
        {  // On captcha validation success redirect to the psp servlet with login command  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login" >  
     </jsp:forward>  
 <%  
        }  
        else  
        {// On captcha validation failure redirect to the signon page with error code set to custom# - 20001  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20002" >  
     </jsp:forward>  
 <%  
         }  
   }  
 %>  

The JSP file should be placed at this location on the web server

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<Domain>  

All the modifications done above require a webserver bounce with cache clear before we can proceed to test.

Results
After the webserver bounce, access the PeopleSoft login page and you will see the login page with the Captcha image and the text below the password field  as shown below

Click on the refresh icon to receive a new captcha text/challenge as shown below

If the captcha verification code entered by the user is incorrect the login page is shown again with the custom captcha specific error message (this is the message I added to the error.properties file) as shown below

If the user managed to enter the correct captcha validation code but the PeopleSoft login credentials were incorrect then the login page is displayed back with the usual login id password incorrect message with a fresh captcha challenge as shown below

If the user manages to enter all the three parameters correctly then the servlet forwarding brings them to the homepage as shown below

The captcha validation success is also confirmed in the weblogic logs (with the system.out message I wrote in the JSP code) as shown below

After-thought 

Although it looks simple enough in retrospect, it took me a while to piece together this solution and many a things learnt along the way (things to do and not to do). If only Oracle would incorporate this feature there wouldn't be the need to put in this customized solution. At the end of the day, what I achieved here is more of a workaround than anything because the most logical place for the captcha validation call is within the psp/psc servlet code which validates the PeopleSoft user id and password. This is something customers cannot modify.

Working on this, took me back to my Java/J2EE developer days and made me realize App Designer is such a wonderful tool that simplifies so much of the development and code deployment aspects. With all the talk of JDeveloper replacing PeopleTools/AppDesigner makes me feel we will be going back to cavemen times. It is unlikely however powerful JDeveloper is, for it to replace the Application Designer's simplicity and ease of use.

Note: This solution/source code provided in this post is by no means a production ready one. It was a proof of concept done by me and you will have to make necessary changes at your end before deploying.