Bound Values in OA framework - I

A web bean in OAF(UIX) consists of

• Indexed Children
• Named Children
• Attributes.

Attributes can be specified at design time in which case it will be a simple name/value pair and value of that attribute will be static. Attribute value can also be determined during run time. If the attribute value has to be determined runtime it must be bound to a data source. When OAF(UIX) is building/rendering these UI nodes it establishes a context called rendering context. And to acheive this runtime behavior we get the data source from this rendering context.

Lets convert the above statement in English Language to Java language

RenderingContext - Interface representing the context when OAF(UIX) builds(renders) the UI nodes.
BoundValue - Is a simple interface with a single method getValue(RenderingContext context). It is left to the implementation of getValue() method to achieve the run time behavior based on rendering context object.

So OAWebBean (MutableUINode to be precise) have a method to set attribute value.

public void setAttributeValue(AttributeKey attrKey,Object value)

Value can be any implementation of BoundValue interface if it must be determined at runtime.

And at run time, when OAF(UIX) uses getAttributeValue(RenderingContext context, AttributeKey key), a typical code might look like

if ((value instanceof BoundValue))
{
return ((BoundValue)value).getValue(context);
}

Generally the parameters that determine the run time value will be passed to the constructor of BoundValue implementing object. For example DataBoundValue is a implementation of BoundValue which reads value based on DataObject in the context. DataObject is very simple interface to extract arbitarily structured data.

So one of the construtor of that Class is

public DataBoundValue(
Object select
)


where the parameter, select, is used to get the DataObject from RenderingContext.

And getValue() method implementation will be simple

return context.getCurrentDataObject().selectValue(context, select);

Bind Peeking

We have this really dandy performance monitoring tool which breaks down database activity (wait events) time wise, module wise, user wise etc. I am usually concerned with Top SQL tab which shows most non performing SQL (Top SQL !! misnomer I guess). So out of no where for few days one SQL got this top most spot and disappeared automagically. I decided to sleuth a little bit about this mysterious SQL.


The suspect

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = :B1


The Investigation


IC_LOTS_MST is Oracle Process Manufacturing table which stores lot information for the item. ITEM_ID and LOT_ID forms combinational primary key and there exists a unique index, IC_LOTS_MST_PK, on these columns. LOT_ID is unique but for the fact that there can be non lot controlled item for which LOT_ID = 0. So we also have non-unique index IC_LOTS_MST_I2 on LOT_ID.


select lot_dist, count(*)
from (select decode (lot_id,0,'ZERO','NONZERO') lot_dist
from ic_lots_mst)
group by lot_dist

LOT_DIST    COUNT
---------------------
ZERO       14,432
NONZERO  2,31,886

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 0


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 13 K 1734
TABLE ACCESS FULL GMI.IC_LOTS_MST 13 K 604 K 1734


If I pass 0 as literal, CBO performs a FTS on IC_LOTS_MST. Make sense


SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 12345

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 4
TABLE ACCESS BY INDEX ROWID GMI.IC_LOTS_MST 1 45 4
INDEX RANGE SCAN GMI.IC_LOTS_MST_I2 1 3


If I pass a non zero as literal, CBO picks a index. Make more sense.

The Findings
When we bounced database, the query in question was executed with a value 0. CBO peeked into the value first time, created a plan with FTS and continued using this. Until we bounced the database again and this time a non zero value was bound.

CUBE function and Pivot Table in SQL

I am currently working on a new OA component, pivot table. To mimic the pivot table in Microsoft Excel, I decided to design the functionality in Java rather than using Oracle's powerful CUBE functionality. I can definitely blog about the reasons behind this decision later, but I am overwhelmed by the power of using CUBE and decided to ramble something about it.

1. Its syntax is very simple
2. Its pretty fast

Illustration:

select e.dept_no, e.job_type, e.salary
from emp_table e

DEPT_NO   JOB_TYPE               SALARY
---------------------------------------
10                Manager         10,000
10                Clerk            3,000
10                Clerk            4,000
20                Manager         12,000
20                Clerk            3,000
 20                Clerk            5,000
20                Foreman          1,000

Say we want to see a tabular information of sum of salary for department wise and again group on it based on job type

select e.dept_no, e.job_type, sum(e.salary)
 from emp_table e
group by cube(e.dept_no, e.job_type)

DEPT_NO   JOB_TYPE   SALARY
 ---------------------------------------
          Manager         22,000
          Clerk           15,000
          Foreman          1,000
10        Manager         10,000
10        Clerk            7,000
 10        Foreman              0
10                        17,000
20        Manager         12,000
 20        Clerk            8,000
  20        Foreman          1,000
 20                        21,000
                          38,000

Now we can use the famous pivoting technique in SQL to get the same information like Excel.

select nvl(job_type, 'Grand Total') JOB_TYPEX
       decode(dept_no,10,max(s),0) DEPT_10,
       decode(dept_no,20,max(s),0) DEPT_20,
       decode(nvl(dept_no,-999),-999,max(s),0) ALL_DEPT
from (select e.dept_no, e.job_type, sum(e.salary) s
from emp_table e
group by cube(e.dept_no, e.job_type))
group by job_type
order by job_type

JOB_TYPEX    DEPT_10   DEPT_20  ALL_DEPT
-----------------------------------------
Clerk         7,000    8,000     15,000
Foreman           0    1,000      1,000
Manager      10,000   12,000     22,000  
Grand Total  17,000   21,000     38,000

But the problem is that, we must know all departments to pivot it.

set serveroutput on by default

DBMS_OUTPUT.PUT_LINE is unavoidable tool for an Oracle developer to debug issues. We must issue a sqlplus comman "SET SERVEROUT ON" to see the output from the above function in our console. By default it is off and we must issue the command explicitly to turn it on. Here is a small trick to turn it on by default.

1. Open login.sql/glogin.sql from SQL_PATH. (Search or grep for that file in the directory you have installed sqlplus.)
2. Add SET SERVEROUT ON command to that file.

Thats it. glogin.sql/login.sql will be executed every time you open a new sqlplus session.

Clearing Profile Cache

Its a royal pain in the **** to bounce the apache everytime you change a profile value, especially during OA page development. Well here is an easy way to clear the profile cache.

1. Login to APPS
2. Choose "Functional Administrator" responsibility
3. Navigate Core Services > Caching Framework > Cache Components
4. Search for %PROFILE%
5. Select "PROFILE_OPTION_CACHE" and "PROFILE_OPTION_CACHE_VALUES".
6. Press Clear Cache button.