Archive

Archive for February, 2009

SQL: SELECTing a random subset of data

February 6, 2009 2 comments

Show me some data, Baby

Have a table with a lot of data in and want to work with a random subset of that data? The answer is the Sample Clause of the SELECT statement. The syntax is often described as something like the following:

SELECT *
FROM my_table SAMPLE (percentage_of_rows);

For example:

SQL> CREATE TABLE ANDY.objs AS SELECT * FROM all_objects;

Table created.

SQL> SELECT COUNT(*) FROM ANDY.objs;

COUNT(*)
———-
71277

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7107

"Some data" is not always the same "some data"

The above is demonstrably returning a random 10% of data from the ANDY.objs table. Great. Very useful. However, an (arguably) interesting thing happens if we run the exact same SELECT statement again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7020

And again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7209

We get a different number of rows each time. It’s still close enough to 10% of the data, you might say. Maybe so but it does highlight an interesting point: The Sample Clause does not bring back x% of rows (as is often thought) but rather gives each row an x% chance of being returned.

From the Oracle Documentation:
"This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

The moral of this tale

Use the Sample Clause to work with a random subset of data but don’t rely on it to bring back exactly the expected number of rows. Complete details on the Sample Clause can be found here and more examples can be found here.

Advertisements
Categories: SQL

Apex: Speeding things up with Oracle Web Cache compression

February 6, 2009 1 comment

Apex pages on a diet

Duncan Mein last year wrote about enabling content compression using Oracle Web Cache to speed up use of both Apex apps and Oracle Discoverer.

Enabling compression in this way is a risk-free, quick win and there’s really no reason not to do it.

Feeling the burn

To enable compression for your Apex apps (including Application Builder itself), do the following:

  1. Access the URL http://your.server.com:9400/ (this is the default port for accessing Web Cache administration).
  2. Click "Caching, Personalization, and Compression Rules".
  3. Under "For all sites" select the last radio button and click "Insert Below".
  4. Add a rule for Get and Get with Querystring of type regular expression.
  5. Add the regular expression of: /pls/apex/.*$
    NOTE: You need to replace /pls/apex/ with the DAD (Database Access Descriptor) that you use to access Apex.
  6. You will be able to check that these settings have taken effect by taking a look at the HTTP response headers that your browser receives when you access one of your Apex pages.

    The above screenshot is from a tool called Live HTTP Headers available for Firefox. It’s an extremely useful tool, showing you exactly what is being set inĀ  both the Request and Response headers. It can be installed from here. If you start playing with Live HTTP Headers, you may notice that in every request your browser sends to the server, it includes the following:

    This indicates to the server that your browser does indeed support gzip compression (which is what you have just configured within Oracle Web Cache). If your browser did not send the Accept-Encoding header as above, then the server would not send back compressed content but rather just plain old uncompressed content which would still be displayed correctly.

    You can see more general information about HTTP Request headers here and about HTTP Response headers here.

Taking the pills

The configuration that you performed above via Oracle Web Cache Administration is just a front-end for modification to the file webcache.xml found in:

<Oracle Middle Tier Home>\webcache\webcache.xml

As such, it is possible to perform these steps via script. This may be useful if, for example, you are trying to create a silent method for installation or configuration of your application. If you are using Windows, you can use the use the batch script and accompanying text file below to perform this configuration entirely via the command prompt (you could work this into a larger installation / configuration script depending upon your requirement).

enable_web_cache_compression.bat – You will need to modify the first line of this file to point to your Oracle Middle Tier Home.

new_webcache_entry.txt – You will only need to modify this file if your DAD is something other than /pls/apex/

Once you have saved these files to any directory and made the changes to these files as mentioned above you can just run enable_web_cache_compression from a Command Prompt to perform the relevant configuration.

DISCLAIMER: You should only use these files as a guide to one possible way for configuring Web Cache to use compression. You should only consider using them once you have looked at their contents and understand what they do. And, as always, you should have a backup of everything before you start modifying configuration files.

Categories: ApEx

How to stop your Apex pages being cached

February 1, 2009 6 comments


Why Browser Caching is Good

Browser caching is often a very useful thing. Accessing a web site you visit often, it can greatly speed things up if much of the contents can be read from your local browser’s cache rather than having to be re-fetched from the server.

Why Browser Caching is Bad

Sometimes, however, caching is not what you want. For example, you may have data which is displayed in the course of a user using your application which you would not want to be cached because you would not want that potentially sensitive information to be stored locally or accessible to other users (simply clicking Back in a browser will often display a cached version of the page). In the scenario where you have logged into an application, viewed some sensitive information, clicked Log Out but left the Browser window open, another user who sits down at the same computer may well be able to click "Back" in their browser to look at what you were just looking at (even though they will not be able to interact with the application in any way because they are not logged in).

How to Stop Caching

There are meta-tags (e.g. <meta http-equiv="Pragma" content="no-cache"/>) which you can specify in the HTML of your web pages whose raison d’etre would seem to be to prevent caching. However, as this Microsft article indicates, these, in fact, have no effect in Internet Explorer 6 (or 7 or 8). One alternative solution that this article suggests is to use SSL / HTTPS. This would indeed work but what if you don’t want to use HTTPS? The other choice is to set HTTP headers to instruct the browser not to cache.

How to Set HTTP Headers to Stop Caching

If using OHS (Oracle HTTP Server, i.e. Apache) you can modify your httpd.conf file to set such headers for only certain URLs so that, for example, your Apex pages are never cached. You would need to add something similar to the following to your httpd.conf file (found in %ORACLE_HOME%\Apache\Apache\conf\httpd.conf):

<LocationMatch "/pls/apex/f">
<IfModule mod_headers.c>
Header set Cache-Control "max-age=0, no-cache, no-store, must-revalidate"
Header set Pragma "no-cache"
Header set Expires "Wed, 11 Jan 1984 05:00:00 GMT"
</IfModule>
</LocationMatch>

You would need to modify the "/pls/apex/f" value if your DAD is something other than the default of "/pls/apex". The above entry will have the effect of preventing caching for all of your Apex pages accessed via this DAD (including those pages associated with Application Builder). You can add in this text pretty much anywhere in your httpd.conf file as long as it is after the call to "AddModule mod_headers.c", Once you have made the change you will need to restart Apache (by running, for example, %ORACLE_HOME%\opmn\bin\opmnctl restartproc ias-component=HTTP_Server from a Command Prompt).

This works in Internet Explorer 6, 7 and 8 as well as in Firefox.

Categories: ApEx