Set values for bind parameters in a database view using application context – single round-trip context manipulation and querying

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

While writing an article on database session context for the OTech Magazine’s Spring issue, I tried out whether I can send a SQL statement from a database consumer such as the SOA Suite Database Adapter or an ADF BC ViewObject that passes in the values for some bind parameters that are not used in the SQL statement itself but instead in the definition of the database view that is being queried. This blog article shows that it can be done – as well as how to do it. I am not sure yet how to exactly put this approach to good use – but it seems that it can help with bringing complex SQL to be encapsulated in the database itself (in SQL and PL/SQL APIs) instead of in client applications.

image

This figure visualizes my intention: the SQL statement that my client application sends to the database not only contains the select * from <view> but also passes in the values of one or more bind parameters. These values are stored in a custom application context. When the query defined by the view is executed, it makes use of the values stored in the application context to return the data required by the application, without the application needing to know anything about the SQL that is executed and the underlying data model. In this article I will use bind parameters in the WHERE clause of the View definition. Note however that these values can just as easily be used in the FROM (Flashback), GROUP BY, ORDER BY and SELECT clauses.

Prepare the database

The preparation of the database in this example is pretty straightforward: in a database schema that has a table that contains airline records and the privileges to create packages, views and any [application]context, I create an application context and a PL/SQL package to manipulate the values in this context. Next I create a view that has a where clause that filters airlines based on an attribute in the application context.

image

 

In the next section I will execute a SQL query against this view. In a single SQL statement, I will push a value to the application context and have this value influence the query against the view.

Create the application context:

CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_CONTEXT_API;

Create the PL/SQL package:

CREATE OR REPLACE PACKAGE MY_CONTEXT_API AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2);

function getset_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2)
return varchar2;                    

END MY_CONTEXT_API;

 

and the body:

CREATE OR REPLACE PACKAGE BODY MY_CONTEXT_API AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2) AS
BEGIN
  DBMS_SESSION.set_context(‘MY_CONTEXT’, p_name, p_value);
END;

function getset_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2)
return varchar2
is
begin
  set_value (p_name  ,
                     p_value );
  return sys_context(‘MY_CONTEXT’, p_name);                    
end getset_value;

END MY_CONTEXT_API;

 

Create the View:

create or replace view airlines
as
select car.*
,      SYS_CONTEXT(‘MY_CONTEXT’, ‘AIRLINE’) context_airline
from   fli_airline_carriers car
where  car.iata_code = nvl (SYS_CONTEXT(‘MY_CONTEXT’, ‘AIRLINE’), car.iata_code)

 

Execute a query against the view

A simple SQL statement “select * from airlines” will simply return all airlines, because no value is set in the application context that is used to filter by.

By adding an inline view that we force the database to execute first (with the /*+ MATERIALIZE */ hint) we can have a “pre-query” statement executed just prior to the main query. In this “pre query” query, we can invoke a PL/SQL function, for example to pass values that the function can subsequently put in an application context:

with this_first as
( select /*+ materialize */ MY_CONTEXT_API.getset_value(‘AIRLINE’, ‘EK’) dummy from dual)
select ale.*
from   airlines ale cross join this_first

Executing this query:

image

 

With another airline (LH):

image

And with an unknown airline:

image

 

And with no airline filter at all:

image

Example with ADF BC View Object

This mechanism can easily be exploited in an ADF BC View Object.

In a new Fusion Web Application with a database connection to the schema used above, I create a new View Object:

SNAGHTML770abbf

and its SQL query:

image

Notice how this ViewObject has a single bind parameter – bind_airline – and a simple ‘select * from airlines’ as its core query.

When I run the ADF Model Tester, I can see the same effect of setting the bind parameter in the results of executing the query for the ViewObject as seen in the previous section.

For example:

image

and:

image

In each case, the (non) value entered for the bind parameter is used in the SQL statement that is passed to the database and there in turn into the application context MY_CONTEXT. In this way – the bind parameter influences the effective where clause of the query – even though the ViewObject definition does not even contain a WHERE clause.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

ADF Performance Monitor: Measuring Slow Network Time

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Recently I was implementing the ADF Performance Monitor at a customer site and doing a detailed performance analysis. This customer had severe (and less severe) performance problems but was unable to pinpoint the exact pain points. Frequently, end-users were complaining because they were experiencing first a very good performance (response times less than a second), and then suddenly experiencing a drop in performance for a certain period of time (an hour or so) with response times between five to twenty seconds.

In this particular case, at the hour overview of the ADF Performance Monitor (from 14:00 to 15:00) we can see the sudden start – and end of this drop in performance. In the top right section HTTP response times can be viewed over the selected time range. This graph makes visible when the load is high (and how high), how the response times are distributed over the categories (very slow, slow and normal) and when there are performance problems (more red and yellow colored parts of the bars).Visible is that from 14:10 to 14:40 the response times have increased significantly. This should be a trigger to drill down to this period in the monitor – for further analysis in order to find the root cause.

database_batchjob_prod

We can drill down from an hour to a 5 minute range (14:25 – 14:30) by clicking on the graph in the menu. Now we see an an overview of all individual HTTP request in this 5 minute range on the top graph – with time in WebLogic (blue) and time in database (yellow). We see many long response times between 5 and 30 seconds. We see that most is consumed in WebLogic but we still don’t know where the time is spent exactly. Also we can see from that the JVM (bottom graph) appears to be ‘happy'; the JVM can easily manage the load so the JVM is not the problem in this case:

database_batchjob_5minute_overview

 

The next step is to drill down to so called ‘ADF request call stacks’ in this time range. An ADF request call stack of the ADF Performance Monitor gives visibility into which ADF method caused other methods to execute, organized by the sequence of their execution. A complete breakdown of the HTTP request is shown by actions in the ADF framework (lifecycle phases, model and ADF BC executions, ApplicationModule pooling, ViewObject query executions,  time to fetch database rows into the ADF app, e.g.), with elapsed times and a view of what happened when. Call stacks are a very useful help to understand specific situations, it shows bottlenecks and where we can avoid bad ADF practices and write more efficient ADF code. The parts of the ADF Request that consume a lot of time are highlighted and indicated with an alert signal.

Slow_fetch_time

After investigating many ADF request callstacks, we we able to detect that the time that was needed to fetch database records by ADF ViewObjects was extremely long. For example, the time shown in this callstack (see image above) to fetch only 173 records into the ViewRowCache of this ViewObject took more than 5,3 seconds and later in the same HTTP request 6,2 seconds (!). And that while the ViewObject query time – method executeQueryForCollection() – took only around 50 milliseconds. Normally, ViewObjects should be able to fetch this number of records in easily only a few milliseconds. ADF always executes this in the following order: first the ViewObject query is executed by the executeQueryForCollection() method. Then, when this method is completed, the database rows of this ViewObject are being fetched. This action and how long this takes is shown in the callstack as ‘Fetching, creating rows for <viewobject instance name>, fetched <xxx rows> ‘.

When the time to fetch rows takes long – and the number of rows fetched database rows is still relatively low – this is a very strong hint that during this request the network between the production WebLogic server and the database seemed to be the bottleneck. This can be the case when they are very far from each other and/or the internet connection is very slow. Or that the network is already very busy.

We knew there was a firewall between the database and the Weblogic server that definitely did not help the communication between these two. But, it turned out that the firewall was not the main problem. It was the periodically database batchjobs (unfortunately executed by the operation team during daytime) that were taking very very many network resources. See the following screenshot from the database Enterprise Manager with metrics from exactly the same time period:

em_networktime

We can clearly see in this picture too that from 14:10 to 14:40 there is many network traffic – caused by a costly database job that was executed by the operation team on that particular moment. The solution is to not execute these batchjobs anymore during daytime :). Now end-users do not have to suffer anymore from these jobs that could be executed in the night as well.

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

ADF in Action – slides and demonstration from OUGF 2014

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

During this week’s OUGF 2014 Harmony conference in Haltia, Finland I presented on Oracle’s premier application development framework: ADF.

ADF is the strategic application development framework for Oracle, a core element in Fusion Middleware. ADF can be used to create large scale, enterprise applications. It can be used to create entirely license free applications. It can be used to create cloud applications and also applications that can be used from all kinds of mobile devices.
This presentation provides an overview of ADF: what it consists of, what it can be used for, what it is better not used for, what does it take to (learn how to) develop ADF applications is an important question that is addressed. The presentation features several demonstrations of the both the first HelloEmployees application as well as a number of advanced ADF mechanisms.

If you have not recently – or ever at all – seen ADF in action, and application development does take place in your organization, this session is a valuable quick introduction and overview.


Here are the slides that visualize the demonstration that I did during this session.

They show in many small steps (that take approximately 20 minutes) how to create the application that when complete looks like this:image

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

ADF for the Enterprise and beyond – 3-day conference for senior developers and application architects

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

SNAGHTML5d1bacOn May 21st, 22nd and 23rd – AMIS and Oracle join forces for a three day event around enterprise application development with Oracle Fusion Middleware. The event targets senior (ADF) developers and application architects. It addresses many of the themes currently or shortly relevant to any organization: multi device UI, mobility, security, agile & automated software engineering, performance & scalability, user experience, web & mobile oriented architecture and cloud. It will discuss and demonstrate Oracle’s vision and the upcoming generation of products.

This event takes place in The Netherlands (Nieuwegein, 35 minutes from Schiphol Airport). If you are in the Western European region and active in the area of Fusion Middleware, we suggest you seriously consider attending – as this is a fairly rare opportunity to hear from and interact with Oracle product managers (up close and personal) and several seasoned speakers as well as an audience composed of your peers, sharing their experiences and insights. The event is small scale and set up for easy interaction between all participants. Registration can be done here – the conference fee for the full three days (including lunches, drinks and snacks) is set at 750 euros.

Enterprise to mobility event AMISSpeakers at the conference are Chris Muir and Frank Nimphius (both from Oracle ADF Product Management)

joined by: Willem de Pater (Security Solution Architect Oracle), Steven Davelaar (Oracle A-Team) and Lancy Silveira (Oracle UX team), Wilfred van der Deijl (AMIS Associate and Oracle ACE Director), Luc Bors (AMIS, Oracle ACE), Lucas Jellema (AMIS, Oracle ACE Director), Frank Houweling (AMIS), Paul Swiggers (AMIS), Aino Andriessen (AMIS).

Continue reading

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

Troubleshooting ADF ViewObject Range Paging Issues

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page

ADF BC ViewObjects provide a very valuable mechanism to page through large data sets so that a user can navigate to a specific page in the results. Range Paging fetches and caches only the current page of rows in the ViewObject row cache (at the cost of another query execution) to retrieve each page of data. Range paging is very performing when your ViewObject has access to (hundreds of) thousands of database rows, and if you want to avoid JVM memory overconsumption. In my current project I ran into two severe performance problems that I will discuss in this blog.  Continue reading

Share this on .. Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Email this to someoneShare on TumblrBuffer this page