BugDB Reporting

Sorry to do this on a public blog, but this is a posting which is only relevant to Oracle employees.


It seems a lot of teams are looking to create an analysis application on top of the BugDB, to perform their own analysis of the BugDB data for their own product. There is a right way and a wrong way to get at this data. The wrong slow way to get at this data is to build a report on top of a query across the database link to the BugDB and query this data each and every page view in your application. The more efficient way to get at this data is to create a local table in your schema and then refresh from the BugDB into this local table, on demand. This results in much less load on the BugDB database itself, and is also a much faster application for your end users.

I actually cooked up something for someone else in Server Technologies. It will create all the necessary database objects to refresh from the BugDB and it also includes an Interactive Report on top of the primary BugDB table RPTHEAD. Anyone is welcome to use this application as a starting point. It’s also hooked up with SSO authentication, so if you import this into your workspace on the internal instance of Application Express at http://apex.oraclecorp.com, you’ll be ready to go with internal single sign-on. All you need to do is:

1. Download and unzip the application export BugReports_installer.sql from: http://general.us.oracle.com/~jkallman/bugreports/BugReports.zip
2. Create a database link to the BugDB named ‘BUGDB.WORLD’ in your schema. https://bug.oraclecorp.com/help/ConnectToBugHelp.html#dblink
3. Import and install the application export BugReports_installer.sql into your workspace (presumably on apex.oraclecorp.com).
4. After installation, you’ll be prompted to Install Supporting Objects. You should simply click Next > through the wizard. Prior to this step, the database link BUGDB.WORLD must exist.

That’s all there is to it! Run the application, navigate to the Administration tab, choose your product and timeframe, click Refresh, and then click the Bugs tab to view the Interactive Report on top of your local data.

It may seem like a simple application, but with the Interactive Report on top of the BugDB RPTHEAD, each end user can create a variety of customized reports and save them. You can read about Interactive Reports here: http://www.oracle.com/technology/obe/apex/apexusr31/apexusr31.htm

Who says Application Express can't scale?

Over a year ago, I wrote about who is using Oracle Application Express on http://apex.oracle.com. At the time, I included some weekly statistics.

During our team meetings every week, I report on the overall usage of http://apex.oracle.com. I just wanted to report on some astounding numbers:

Total Page Views Distinct Applications Distinct Users
---------------- --------------------- --------------
       6225566                  3268           2778


Total Workspaces
----------------
         11643


Total Applications
------------------
           39849


Workspaces Approved
-------------------
              316

That's 6,225,566 page views in the past week, and this is still running on the same Dell PowerEdge 1950.

Now granted - the ability to sustain this type of load throughout the week deserves some credit to Kris Rice, who spent a fair amount of time analyzing Application Express on apex.oracle.com and identifying some major problem areas in performance. As well, he brought in some experts to perform OS tuning. The recent purge on apex.oracle.com removed close to 10,000 workspaces, schemas, tablespaces and datafiles. Lastly, a couple months ago, I implemented a Resource Manager plan which prevents someone from monopolizing the entire server. I'll be presenting a detailed discussion of Resource Manager and apex.oracle.com at ODTUG Kaleidoscope 2009.

A large number of these page views are from the bots of Search Engines, especially since ProMED Mail is run on apex.oracle.com. But a page view and execution of the engine is a page view and execution of the engine. All in all, that's a large load on a relatively cheap piece of hardware.

OraTweet - Micro-blogging with Oracle Application Express

About a year ago, Carl Backstrom was telling me how he was helping another gentleman in Oracle, Noel Portugal from the CRM On Demand team, with a micro-blogging site developed with Oracle Application Express. Carl game me a demonstration of OraTweet, tried to explain Twitter, tweets, and micro-blogging. I just didn't get it, at the time.

Today, micro-blogging appears to be all the rage. Dwight Howard from the Orlando Magic basketball team is on Twitter. CNBC's Fast Money television show is on Twitter. I am even on Twitter, but I really don't use it, as you can tell.

Noel's application, OraTweet, has been in use within Oracle for the past year. As Noel states on the oratweet.com site: "Teams around Oracle are finding ways to communicate with their teams while reducing email overload. The results have been amazing. Global team members are connecting and sharing information. Users have started to develop stand alone clients to consume OraTweet’s timeline. We have also integrated the OraTweet timeline to Oracle Connect, our own internal social network. Overall information is flowing faster in a lightweight format."

Noel Portugal has developed an excellent Web site which explains OraTweet, lets you submit feedback, and provides a link to download the entire source code for OraTweet: http://oratweet.com/