Posts tonen met het label bc. Alle posts tonen
Posts tonen met het label bc. Alle posts tonen

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:
  1. 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");
      }
  2. Perform the necessary actions in your application for which you want to have the trace.
  3. You can now stop the trace by executing the following statement: alter session set sql_trace=false
    or just perform action 4.
  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). 
  5. 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 
  6. 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.
  7. 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.

F

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

dinsdag 28 mei 2013

ADF BC: JBO-25014: Another user has changed the row with primary key

Challenge

You receive the error mentioned in the title, but you are quite sure this is not the case and nothing has changed in the database due to triggers or pl/sql-code.

Context

Jdeveloper: 11gR1

Solution

There are already quite a few blogs on this error within ADF.  They all talk about the fact that something has changed in the database, without BC knowing about it.  This can be done through another user, a batch-script, a trigger or any other pl/sql-code.
But what if you are 100% sure this is not the case, then read on.

There is also another reason why this happen: the comparison of the different attributes didn't go well.  Although the documentation clearly state that the oracle.jbo.domain-classes should solve this issue, we still have found ourselves multiple times in this situation and mostly due to Date-attributes.
There are 2 ways to handle this:
  1. You can identify an attribute in your entity as a “Change Indicator”.  Once you have identified such an attribute in your entity, the BC-code will no longer compare all attributes, instead it will only compare those with the “Change Indicator” activated.

    To set this indicator on an attribute, just open the editor of the attribute to see the “Change Indicator” property.
  2. You can remove the attribute that is causing the problem from the comparison.  This is easier said than done, because you need to know which attribute is causing the problem.

    To find this out, just activate the JBO-diagnostic logging, you can do this by adding the following “-Djbo.debugoutput=console” to the Java-options of your run-configuration.
    Now run the application again and simulate the problem.  You should find something like this:
    <EntityImpl><compare> [508] Entity compare failed for attribute HireDate
    <EntityImpl><compare> [509] Original value :19-06-1987
    <EntityImpl><compare> [510] Target value :19-06-1987
    
    Now you know it is the HireDate-Attribute.  Now add the following code to the Impl-class of your entity:
    @Override
    protected boolean compare(SparseArray sparseArray) {
       // Removing the HIREDATE attribute from the array
       if (sparseArray != null && !sparseArray.isEmpty()) {
           for (int i=0; i<sparseArray.length(); i++) {
               Object value = sparseArray.get(i);
               if (value != null) {
                   if (i == HIREDATE) sparseArray.clear(i);
               }
           }
       }
       // Calling the standard compare method
       return super.compare(sparseArray);
    }
    
    You can do this for as many attributes as you need, just add them to the 3th if-statement.

maandag 6 augustus 2012

ADF: Programmatical views made simple

Challenge

How do I build a simple programmatical view object in ADF BC? I want to know what action to perform in which method and what the order of execution is of these different methods.

Context

JDeveloper version : 11.1.1.5.0 OS: Windows 7

Solution

We are going to build a programmatical view object based on static values. These are the different steps:

Create the Programmatical View

Launch the "New View Object" wizard
    Fill in the name of the view object and select the "Rows populated programmatically, not based on a query" data source type
    Create the attributes that you need.  Here we only created the primary key (Id) and the name (CountryName) attributes.
    Make sure that you always indicate at least one attribute as "Key Attribute" and also make sure that you put the "Updatable" property to "Always"
    Make sure you create the View Object Class, creating the View Row Class can be handy.  I'm using the View Row class in my example, so I need to generate this one also.

Create the necessary java code to override standard behavior

Here is the complete java code of the AllCountriesViewImpl class:
package model.hr.vo;

import java.sql.ResultSet;

import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Mon Aug 06 17:47:14 CEST 2012
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class AllCountriesViewImpl extends ViewObjectImpl {
    /**
     * This is the default constructor (do not remove).
     */
    public AllCountriesViewImpl() {
    }
    
    // A simple structure to hold the different countries
    private String[] allCountries;
    

    /**
     * executeQueryForCollection - overridden for custom java data source support.
     */
    protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams) {
        // Initialize the list of countries
        // This can be a call to anything returning your list of countries
        allCountries = new String[5];
        allCountries[0] = "Belgium";
        allCountries[1] = "The Netherlands";
        allCountries[2] = "Germany";
        allCountries[3] = "France";
        // Set the initial position
        setFetchPos(qc, 0);
        // Call the original method implementation
        super.executeQueryForCollection(qc, params, noUserParams);
    }

    /**
     * createRowFromResultSet - overridden for custom java data source support.
     */
    protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
        // Get the current fetch position
        int pos = getFetchPos(qc);
        // Create a new row
        AllCountriesViewRowImpl newRow = (AllCountriesViewRowImpl)createNewRowForCollection(qc);
        // Set the necessary fields 
        // This is why you need the RowImpl implementation
        newRow.setId(pos);
        newRow.setCountryName(allCountries[pos]);
        // Change the fetch position
        setFetchPos(qc, pos + 1);
        // Return the newly created row
        return newRow;
    }

    /**
     * hasNextForCollection - overridden for custom java data source support.
     */
    protected boolean hasNextForCollection(Object qc) {
        // Determine wether there are still countries to fetch from the array
        return getFetchPos(qc) < allCountries.length;
    }


    /**
     * getQueryHitCount - overridden for custom java data source support.
     */
    public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
        long value = super.getQueryHitCount(viewRowSet);
        return value;
    }
    
    /**
     * Method to set the new fetch position
     * @param rowset
     * @param pos
     * @writer Filip Huysmans
     */
    private void setFetchPos(Object rowset, int pos) {
        if (pos == allCountries.length) {
            setFetchCompleteForCollection(rowset, true);
        }
        setUserDataForCollection(rowset, new Integer(pos));
    }

    /**
     * Method to get the current fetch position
     * @param rowset
     * @return
     * @writer Filip Huysmans
     */
    private int getFetchPos(Object rowset) {
        int value = ((Integer)getUserDataForCollection(rowset)).intValue();
        return value;
    }

}

The order in which these methods are executed, is:
  1. executeQueryForCollection: this is start of the query. Here you can do your initilizations and the fetching of the values that you need.
  2. createRowFromResultSet: this method will actually create a new row. This method is called for each iteration of the records you have.
  3. hasNextForCollection: this method determines whether there are still rows to be created. If it returns true, the createRowFromResultSet-method will be executed again. If it returns false, the creation of the records will end.
Now you can use this view object to create a table or use it as a LOV for an attribute.

Conclusion

With this simple example you should be able to start working with programmatical views.  This example only focusses on showing information.  There are circumstances where more complicated operations are needed.
I hope this was helpfull for you.

 

References