Huysmans Industry Three Thousand: januari 2014

vrijdag 3 januari 2014

ADF: Putting IE in a certain document mode

Problem

In our ADF application we used some javascript to put the focus on the first field of an af:query component. This seems to work great when running directly from JDeveloper, but doesn't if the application is deployed on a separate WLS.

Solution

First we thought it is the javascript itself, but this was not possible since we do not change the code.

One of our JSF experts, Rudy De Busscher, figured out that the compatibility mode of IE changed between both deployments. How or why, remains still a mystery.

So, now we need to force IE to keep his IE8 compatibility mode. To do this, you need to put a meta tag in the header of your page. This is easy enough to do in ADF, in the af:document tag, add a facet with this tag in, like this:

<af:document id="d1" title="Test Javascript">
   <f:facet name="metaContainer">
      <f:verbatim>
         <meta http-equiv="x-ua-compatible" content="IE=8"/>
      </f:verbatim>
   </f:facet>
....

The problem with this solution is that the meta-tag will be put after your stylesheet tags, which will result in the fact that this meta-tag is not taken into account.

To solve this problem, you need to perform a small operation. Since this seems to be a hack, I'm not sure it will work in all ADF versions. I tested it with ADF 11.1.1.3.0.

Make your code look like the following:

<f:view>
   <f:verbatim>
      <head><meta http-equiv=x-uq-compatible" content="IE=8"/></head>
   </f:verbatim>
   <af:document id="d1" title="Test Javascript">
   ...

To check whether IE is taken this into account, use the F12 key to open the Developer Tools, in the top menu you will find the Document Mode. This will indicate in which mode you are operating. Also have a look at the header of the document, the meta-tag of http-equiv should be after the title, but proceeding the stylesheet tags.

Have Fun.

donderdag 2 januari 2014

ADF BC: Find out what is really happening in the database

Problem

While there are multiple ways (see previous blog) within ADF BC to see what is being generated and executed in the database, perhaps you are not satisfied with the information provided nor the overhead of the other loggings which are blocking your clear view on the matter.

Or perhaps you are missing information like how many times a query is executed, how long it took, how many fetches were used, ...

Solution

Use the logging of the database to find the required information. You can enable your sessions or even the entire database to activate logging that can be used to give you a clear overview on what is being asked from the database.

To activate this logging you need to perform the following actions:

Activate the logging in your session
Execute the following statement : alter session set sql_trace=true
You can execute this command in your application module impl class by overriding the afterConnect() method. I'm also using this method to set some optimizer hints for my queries, like optimizer_index_cost_adj and optimizer_index_caching.
Example:

@Override
protected void afterConnect() {
super.afterConnect();
executeCommand("alter session set sql_trace=true");
}

Perform the necessary actions in your application for which you want to have the trace.
You can now stop the trace by executing the following statement: alter session set sql_trace=false
or just perform action 4.
At this point the database will have generated a very detailed, not easy to read and understand trace file. This file can be found under the user dump directory, which is defined by the database parameter user_dump_dest (show parameter user).
Now we need to parse this file to a more readable file by executing the following statement:
tkprof <sid>_ora_<pidid>.trc <result_file> sort=fchela
Since it is very hard to know what process id you need, I just always took the last one :-)
I added the sorting criteria to see the statements first with the biggest elapsed time. Information on all parameters for tkprof can be found here.
Example : tkprof orcl_ora_2463.trc test.txt sort=fchela
Now you have a file that is readable, namely test.txt. This file will give an overview of all statements being executed by your sessions and at the end an overview. Let's have a look at output for 1 statement.
Only take the statements into account where the parsing user is not SYS. The latter is used for database internal queries and do not adhere to the same tuning rules as your custom queries.

Here is an example output for a very simple query:

Let's have a look at the important bits of information:

As 3th part of information you see the query like it has been asked at the db. Here you see whether correct bind variables are used. The only drawback of this technique is that this query is not linked directly to a certain view object. It is up to you to determine which view object has generated this query.
The count column of the Parse row, should be 1 or as low as possible. This indicates how many times the db needed to parse this particular statement. If you are not using bind variables, you will see that for each value a new statement overview is given and that the parse count will still be 1.
The count column of the Execute row, should represent the number of times this query should be executed. If you would have expected a lower number then the one appearing here, then BC has executed this query more often. To solve this problem, you will need to look at the page definition files to determine when and how often the query is executed or even in your custom code when you asked to execute query on an iterator.
Normally the cpu time of the execute row, should be very low. If this is not the case, then it indicates that the query itself is very complex and has a lot of hierarchies of tables. Or it could be because it is a DML-statement.
Now comes the most important row, the Fetch-row. The fetch count number indicates how many times the application has fetched rows for this query. If the execute count is 1 and the fetch count is 10, this means that ADF BC has executed the query once, but needed to go 10 times to the db to get all data. To resolve this problem, just change the 'in Batches of'-tuning parameter of your view object. Look at the following blog for some tuning guides.

These were for me the most important pieces of information to be able to tune my ADF application.

Have Fun.

ADF BC: Adapting the where-clause of a view criteria

Problem

You have build a working ADF BC application. After some tuning tests, it seems that your application can use some tuning. So you, or someone above you, decides to call an expert DBA or SQL tuning specialist.

Result : you need to change the way the where-clauses are created.
If you are lucky, it is just a where-clause you have written.
If you are less lucky, it is a where-clause created by a view criteria, in this case you can not change it. You can only activate or deactivate some properties.

In our case this wasn't enough. The ADF BC framework generated clauses like
UPPER(LAST_NAME) LIKE UPPER(:LastName_bvar || '%')

Solution

The first thing you need to do is to remove the upper-statement. This can easily been done by deselecting the 'Ignore Case' property in the edit view criteria screen.

So now you are getting the following clause
LAST_NAME LIKE (:LastName_bvar || '%')
While this seems fine for you, your tuning specialist still isn't happy. He wants to get rid of the wildcard.

To accomplish this requirement you will need to get more creative.
Add the following code to your ViewImpl class or your base ViewImpl class:

    /**
     * Source : http://tompeez.wordpress.com/2011/08/21/extending-viewcriteria-to-use-sql-contains-4/
     * Adapting the creation of the where-clause to remove the '%' after the bind variables of the like statements
     * At the same time we add the '%' wildcart at the end of the variable value.
     * We will do this for all bind variables with custom property LIKE_CLAUSE=CUSTOM
     * This method gets called for all bind variables
     * @param viewCriteriaItem
     * @return
     * @author Filip Huysmans
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem) {
        ArrayList<ViewCriteriaItemValue> lArrayList = viewCriteriaItem.getValues();
        if (lArrayList != null) {
            ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
            if (itemValue.getIsBindVar()) {
                Variable lBindVariable = itemValue.getBindVariable();
                // check for the special LIKE_CLAUSE in the used bind variable
                Object obj2 = lBindVariable.getProperty("LIKE_CLAUSE");
                String likeClause = (obj2 != null ? obj2.toString() : "null");
                if (likeClause != null && !likeClause.isEmpty() &&
                    !"null".equals(likeClause)) {
                    if (viewCriteriaItem.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
                        // normal query execution
                        return getLikeClauseForDatabaseUse(viewCriteriaItem,
                                                           likeClause);
                    } else {
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";
                    }
                } else {
                    // no special treatment for all other CriteriaItems
                    return super.getCriteriaItemClause(viewCriteriaItem);
                }
            }
        }
        // fallback call
        return super.getCriteriaItemClause(viewCriteriaItem);
    }
 
    protected String getLikeClauseForDatabaseUse(ViewCriteriaItem aVCI,
                                                 String typeLikeClause) {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
        String whereClause = "1=1";
        if (itemValue.getIsBindVar()) {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null) {
                // Adding the wildcard to the bind variable and putting the bind variable in upper-case
                varVal = (objVarVal.toString().toUpperCase() + "%");
                ensureVariableManager().setVariableValue(lBindVariable,
                                                         varVal);
            } else {
                // No value specified => return no where clause
                return null;
            }
 
            String bindVarName = lBindVariable.getName();
            if ("UPPER".equals(typeLikeClause))
                whereClause =
                        "UPPER(" + aVCI.getColumnName() + ") like :" + bindVarName +
                        " ";
            if ("CUSTOM".equals(typeLikeClause))
                whereClause =
                        aVCI.getColumnName() + " like :" + bindVarName + " ";
        }
        return whereClause;
    }

As mentioned in the java doc of this code block, you need to add a custom property to the bind variables for which you want this to take effect. Add a custom property with the name 'LIKE_CLAUSE' and a value of 'UPPER' or 'CUSTOM'.

From this situation you can add any functionality you want to make the where-clause exact what your tuning specialist is looking for.

Have Fun.

F