Cloud Platform

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Soaring Through the Clouds – Part 8: Oracle JET Application on top of MCS and ACC

Bob Rhubart-OracleMay 24 2016 — edited Aug 8 2016

The final installment in the Soaring Through the Clouds series, this article examines the last link in the chain of ten cloud services: a rich user interface in a desktop browser or on a mobile device, implemented using the Oracle JET rich client web development toolkit.

By Lucas Jellema

Oracle JET application on top of MCS and ACC

In this article, we reach the end of the chain—the apex of the integrated flow across ten cloud services. A rich user interface in a desktop browser or on a mobile device, implemented using the Oracle JET rich client web development toolkit. This application runs on the Application Container Cloud as a Node.js application and makes use of the developer-friendly APIs exposed from MCS, as discussed in the previous installment in this series.

Figure 1

The JET application provides insight into the artist proposals that have been recorded after being initiated on Twitter, approved on PCS and enriched on SOA CS. The screenshot below shows the overview page in the application.

Figure 2

The list of approved artist proposals retrieved from MCS can be sorted by number of votes or registration date. Multiple visualizations are available for the number of votes per artist; using the buttons at the bottom, we can toggle between horizontal and vertical (and stacked or unstacked) bar charts as well as a pie chart.

A user can drill down to learn about a particular artist’s details, such as biography and discography:

**
**

Figure 3

All data shown on the details page are retrieved from MCS (and indirectly from SOA CS and DBaaS). The image shown for the artist has been selected by the human approver in the PCS workflow. The tweet button allows the user to add his or her vote for the proposed artist by tweeting in the required format, as shown in Figure 4:

Figure 4

The Backend Node.js Application

The Oracle JET web application is a rich client application that runs in the browser. When the application is accessed from a browser, all required (static) files – HTML, CSS, JavaScript – are retrieved by the browser from a web server and executed in the browser. We use a simple Node.js application to serve the client side JET application sources to the browser.

Additionally, the Node.js application is a simple proxy between the client application in the browser and the remote MCS APIs, as shown in the next figure. In order to smoothly deal with the CORS headers (Cross-Origin Resource Sharing) and handle the mobile backend identification and the authorization for the MCS APIs, it is convenient to use the server side Node.js application for the interaction with MCS on behalf of the rich client JET application.

Figure 5

The first step in creating the rich web application is the use of the npm module express generator to generate the application scaffold. The index.js file is configured next to handle HTTP requests for static files in the public directory (where the rich client application’s resources reside) and API requests for the REST APIs on MCS. The entire index.js application is shown in the code snippet below:

var express = require('express');

var app = express();

var request = require('request');

app.use(express.static('public'));

app.use('/mobile/*', function (req, res) {

var url = process.env.MCS_URL + req.originalUrl;

req.pipe(request({

url: url,

headers: { 'oracle-mobile-backend-id': process.env.MCS\_BACKEND\_ID },

auth: { user: process.env.MCS\_USER, pass: process.env.MCS\_PWD }

})).pipe(res);

});

var PORT = process.env.PORT || 3000;

app.listen(PORT);

The Express framework is used for specifying the URLs to be served by this simple application. The contents of the directory public are exposed through app.use(express.static('public'));. This directory contains the JET application resources that are to be downloaded to the browser.

The line starting with app.use('/mobile/*' handles all requests from the JET application to the MCS APIs: each request is forwarded to MCS with the required mobile backend identification and authentication details, and each response from MCS is returned to the browser unchanged, by the command pipe(res).

Development of the Oracle JET application

The Oracle JET application consists of a number of custom HTML, CSS and JavaScript resources, supported by the Oracle JET libraries and frameworks such as jQuery, Knockout and RequireJS. The JET application resides in the /public directory. All resources under that directory are served by Node.js upon browser requests. You can inspect and download these sources at https://github.com/lucasjellema/aced-cloud-demo-ofmw2016-valencia/tree/master/ACC/frontend/public .

The file index.html is the bootstrap resource in what is basically a single page application. This file includes the custom JET module drill – implemented through drill.html (the view) and drill.js (the supporting viewModel). This module largely consists of a single JET component—moj.ModuleAnimations.switcher (see documentation: http://www.oracle.com/webfolder/technetwork/jet/uiComponents-ojModule-platformAnimation.html ) -- that implements a simple pattern of switching between an overview page and a details page. The next figure shows the first half of the structure of the application.

Figure 6

The overview is implemented with the custom acts module in files acts.html and acts.js. Acts.html uses the listview component ( http://www.oracle.com/webfolder/technetwork/jet/uiComponents-listView-collectionListView.html ) to present all proposed acts that are returned from the backend REST API in a JSON data structure.

The acts module is extended with the charts module. This module shows a bar chart (horizontal or vertical, stacked or unstacked) or a pie chart based on the number of votes for each of the artists. These charts are created using the Data Visualization components Bar Chart ( http://www.oracle.com/webfolder/technetwork/jet/uiComponents-barChart-default.html ) and Pie Chart ( http://www.oracle.com/webfolder/technetwork/jet/uiComponents-pieChart-default.html ). This chart module is defined in chart.html supported by the viewModel in chart.js. The data underlying the chart is the sortedActs collection in the acts viewModel that is passed to the chart viewModel in acts.html.

Figure 7 also shows the details state of the drill module, based on the details module that is implemented by details.html and details.js.

Figure 7

The details module uses a number of JET components to present content in an appealing way: Button, Panel ( http://www.oracle.com/webfolder/technetwork/jet/uiComponents-panel-panelcolors.html ) and Filmstrip ( http://www.oracle.com/webfolder/technetwork/jet/uiComponents-filmStrip-filmStripNavArrows.html ).

Both acts.js and details.js call out to the Node.js backend to fetch JSON data sets at the /mobile/… URL. Requests at this URL are routed to MCS as visualized in the next image. These MCS APIs return JSON documents with summary information about all proposed acts or all details about a single act. These APIs have been introduced in the previous installment in this series.

Figure 8

This picture also shows how acts.html embeds a UI widget from Twitter, using the URL https://twitter.com/hashtag/ofmaces, to show the most recent tweets with hashtag #ofmaces.

Deploying and running the JET application on ACC

Deploying the application can be done through the ACC Cloud Service Console, as described in installment five of this series. Another option is deployment through the API that ACC exposes for that. By making an HTTP POST call to the ACC REST API, which submits a form that contains the contents of the zipped application archive, the JET application can be rolled out in a fully automated fashion.

The sources for the JET application are available in GitHub: https://github.com/lucasjellema/aced-cloud-demo-ofmw2016-valencia/tree/master/ACC/frontend . The gulpfile.js file contains the Node.js program code that makes the HTTP calls to the ACC API to create the application and programmatically deploy the zip file:

var form = new FormData();

form.append('name', name);
form.append('runtime', 'node');
form.append('subscription', 'Hourly');
form.append('manifest', fs.createReadStream('manifest.json'));
form.append('deployment', fs.createReadStream('deployment.json'));
form.append('archive', fs.createReadStream('frontend.zip'));
formData.submit({

method: method,

protocol: 'https:',

host: 'apaas.us2.oraclecloud.com',

path: '/paas/service/apaas/api/v1.1/apps/myDomain',

auth: 'admin:password',

headers: { 'X-ID-TENANT-NAME': 'myDomain'}

});

Note how the function _rjs() in gulp.js packages the JavaScript sources in the application into one compact and obfuscated file (optimized.js) that can far more efficiently be downloaded by the client than all the individual JavaScript files.

When the deployment is complete, a number of environment variables need to be set, in order to configure the application for the MCS instance with which it interacts. These values are passed to the Node.js application context by the Application Container Cloud framework and can be accessed from within the application. This means that we do not need to hard code references to end points and other configuration properties into our applications. Figure 9 shows the Service Console’s Deployments page with details for the application deployment, including these environment variables.

Figure 9

More details on how to get started with Oracle JET applications on the Application Container Cloud service for Node.js are in this article: https://technology.amis.nl/2016/02/07/deploying-an-oracle-jet-application-to-application-container-cloud-and-running-on-node-js/ .

Conclusion

Even a rich-client web application like Oracle JET that runs as a single page application in the browser needs to be served to the browser. A lightweight and very scalable option to serve these applications is use of the Node.js platform (e.g., in the Oracle Application Container Cloud (ACC)). In addition to simply serving all static resources, this Node.js backend application can provide some useful facilities, such as proxying requests to external services and handling identification and authorization details as well as HTTP headers like the ones to deal with CORS. This article showed how a JET application can be packaged and deployed as a Node.js application on ACC. This application can easily access the APIs that have been exposed on Mobile Cloud Service for consumption in mobile and web apps—in this case, to access artist [proposal] data.

The JET application itself provides a rich UI to end users, with a number of attractive features. Using the JET UI components, it is quite straightforward to create these rich UI elements. Once the application structure is created – using modules, views and viewModels and with the appropriate data structures and API calls – further refining and embellishing the application is fairly easy and productive. Development and testing can be done locally in quick development iterations, and automated deployment to ACC makes the next incarnation of the application available to the world.

About the Author

Oracle ACE Director Lucas Jellema is solution architect and CTO at AMIS, based in the Netherlands. An Oracle, Java, and SOA specialist, he works as a consultant, architect, and instructor in diverse areas such as Database & SQL, Java/Java EE, SOA/BPM and PaaS and SaaS Cloud Solutions. The running theme through most of his activities is the transfer of knowledge and enthusiasm (and live demos). Lucas is a well-known speaker at Oracle OpenWorld, JavaOne and various Oracle User Group conferences around the world. His articles have appeared in OTN, OTech and the AMIS Technology Weblog, and he is the author of Oracle SOA Suite 11g Handbook (2014, McGraw Hill) and the Oracle SOA Suite 12c Handbook (2015, McGraw Hill).

NOTE: This article represents the expertise, findings, and opinion of the author. It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.

Jonathan Lewis

I'm puzzled that the no_merge hint is not working for you - perhaps it's (yet another) side effect of how Oracle treats ANSI syntax.

Step 1: put the no_merge where you've got the MATERIALIZE in your WITH TEMP_TAB example - this should put the factored subquery inline and then optimize it exactly the way you want. (There have been cases in the past, though, where "manually inlining" a factored subquery produces a different plan from writing the subquery as a factored subquery and letting the optimizer decide to put it inline).

Can you try it both ways - simply hinting no_merge (and INLINE, if it doesn't move, which it should), and then copy the bracketed query in into place with the alias temp_tab, and tell us if you get two different plans.

Regards

Jonathan Lewis

On second thoughts - the no_merge() hint is about complex view merging, and this is an example of "simple view merging".

But it can't be stopped either by setting _simple_view_merging to false, or by hinting NO_QUERY_TRANSFORMATION

Regards

Jonathan Lewis

David Berger

Hello Jonathan

I tried the followings:

1.)

WITH TEMP_TAB
AS (SELECT /*+ NO_MERGE */ 
           n_finanzprodnummer
         , fpr_finanzprodnummer
         , ...
      FROM .....
   )
SELECT COUNT(*)
  FROM TEMP_TAB
WHERE n_finanzprodnummer = fpr_finanzprodnummer 
;

Result is the same bad plan -> Here happens a Filter-Push down again.

2.)

SELECT COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer 
;

Result is the same bad plan...

Jonathan Lewis

Reading through a 10053 trace, I found that the "problem" feature was "simple filter predicate pushdown".

So I set parameter "_optimizer_filter_pushdown" to false and (at least in my simple join example) bypassed your problem. It still needed the no_merge() hint to "surround" the inline view where you didn't want the predicate to go. Rather than doing "alter session" to set the parameter, you could use the hint: /*+ opt_param('_optimizer_filter_pushdown','false') */ but this is not one of the parameters listed as working with the hint, and the hint won't get reflected into the outline.

Regards

Jonathan Lewis

1 person found this helpful

Randolf Geist

Jonathan,

I think the "_optimizer_filter_pushdown" parameter is pretty new and was only added in 11.2.0.2 or 11.2.0.3, I can't remember off the top of my head. The potential problem is that it prevents any filter pushdowns I think, so if there are multiple query blocks where some should push filters and some don't I'm not sure this would be feasible with that parameter.

To the OP: I think one commonly used technique to prevent a filter pushdown is adding the ROWNUM pseudo-column (or a similar analytic function ROW_NUMBER() OVER (ORDER BY NULL)) to the query block where the filter shouldn't be pushed into.

Of course, adding ROWNUM adds its own overhead to the query (there will be an additional COUNT operator) and can have other side effects, some of them rather nasty when dealing with Parallel Execution.

Apart from that it usually makes sense to filter the data as early as possible, so possibly the filter pushdown itself is not the problem but as a side effect of the pushdown you get a less efficient execution plan, so it might make sense to understand what possibly goes wrong with the plan when the filter is pushed.

Randolf

1 person found this helpful

David Berger

Hello Jonathan

Super! Your solution works!

SELECT /*+ opt_param('_optimizer_filter_pushdown','false') */
       COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you and regards,

David

David Berger

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

SELECT COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
          WHERE ...
            AND ROWNUM >= 0

       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you very much!

-> In this case the solution is the preventing of using the filter push-down. Reason: There are Views in the joins which have DB-links and the cost is not estimated good because of the remote objects.

This query is very complicated.

But anyway, I have learnt something again! Thank You all!

Regards, David

Randolf Geist

DavidBerger wrote:

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

Hi David,

actually you don't need to reference ROWNUM in the WHERE clause, it should be sufficient to add ROWNUM to the list of projected columns. This should minimize the overhead - of course you might need to add then another layer of projection if you don't want to have that ROWNUM information shown in the result set.

Testing a query's performance by using COUNT(*) can be quite misleading, by the way, since the optimizer tries to be clever and avoid any work that's unnecessary. In your particular case it might not make a difference, but in general I would use a different technique, either SET AUTOTRACE TRACEONLY STATISTICS in SQL*Plus or wrapping the original query like this:

SELECT /*+ NO_MERGE(x) */ * FROM (

original query goes here

) x

WHERE ROWNUM > 1

;

The SQL*Plus approach needs to fetch all data to the client but doesn't spend any time in displaying/formatting it, the latter approach discards all data on the database server already, but needs to run the full projection logic due to the NO_MERGE hint. For very large data sets with many rows the FILTER operator that discards all rows on the server side can consume significant CPU time, but for smaller result sets this gives a pretty good and realistic indication of the actual runtime.

Randolf

Randolf Geist

DavidBerger wrote:

Hello Randolf

Yes. You are right, in this case the optimizer hint operates on the whole query.

I tried your solution and it works too!

One other thing: When using ROWNUM in current releases you don't need to add an explicit NO_MERGE hint as the query block is marked as non-mergeable due to the ROWNUM anyway, but it probably makes sense to use an explicit NO_MERGE hint nevertheless to make the intention clear and add another comment to the SQL stating why this all was done.

Randolf

David Berger

Hello Randolf

Ok. Thank you very much for your suggestions! They are really good!

Jonathan Lewis

DavidBerger wrote:

Hello Jonathan

Super! Your solution works!

SELECT /*+ opt_param('_optimizer_filter_pushdown','false') */
       COUNT(*)
  FROM (SELECT /*+ NO_MERGE */
               n_finanzprodnummer
             , fpr_finanzprodnummer
              , ...
          FROM .....
       )
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;

Thank you and regards,
David

David,

As Randolf suggests, though, I shouldn't really have mentioned it - but I got a bit curious and decided to see if I could find a method when I realised that the no_merge wasn't supposed to work.

As Randolf also indicates - there's bound to be a way of getting the data faster while having the predicate operating earlier - but there's probably an optimizer "glitch" that's causing a change of plan because an early appearance of that predicate reduces the cardinality of some join to a point where a change of join order or method looks good. ANSI SQL is much harder to unpick when this happens, though, so the rownum trick is a reasonable compromise between complexity and efficiency.

Regards

Jonathan Lewis

David Berger

Thank You Jonathan very much!

Jonathan Lewis

Randolf Geist wrote:

Jonathan,

Randolf

Randolf,

I just did a quick check on lists of parameters, and you're right; this one appeared very recently - 11.2.0.3

And you're also right that the OP shouldn't really make use of it.

Regards

Jonathan Lewis

1 - 13

Added on May 24 2016

#legacy-documents

0 comments

1,504 views

Cloud Platform

Soaring Through the Clouds – Part 8: Oracle JET Application on top of MCS and ACC

By Lucas Jellema

Table of Contents

The Backend Node.js Application

Development of the Oracle JET application

Deploying and running the JET application on ACC

Conclusion

About the Author

Comments

Post Details