Why I use the Resource Manager on apex.oracle.com

A few weeks ago, I blogged about the use of the Oracle Database Resource Manager and apex.oracle.com, and how it is essential to keep this service up and running.

Well, today, I stumbled across 10 active database sessions, all from the same user, all running an anonymous PL/SQL block issued within SQL Commands on apex.oracle.com. All 10 sessions had been running for hundreds of seconds and all 10 were in the APEX_LOW resource consumer group. By using the built-in-to-APEX Utilities -> Database Monitor -> Sessions reports, I was able to determine exactly what was executing in these sessions.

What's wrong with this user's code?

1 2 3 4 5 6 7 8 9 10 11

DECLARE i NUMBER(3):=1; BEGIN    WHILE (i<5) LOOP       IF( mod(i,2)=0)  THEN                DBMS_OUTPUT.PUT_LINE(i);       END IF;    END LOOP;   END;

I am NOT suggesting you run this on apex.oracle.com nor your own instance of Application Express.

Application Express 3.2.1 patch set applied to apex.oracle.com

This past weekend, the forthcoming Application Express 3.2.1 patch set was applied to apex.oracle.com (and apex.oraclecorp.com, if you're an Oracle employee and user of the internal instance). The APEX 3.2.1 Patch Set Note lists the bugs fixed in this patch set.

In addition to the bugs fixed in this patch set, a few interesting points:

1. The online help (the popup window of the brows-able documentation) is available in Japanese.
2. AnyChart AnyGantt Flash Gantt and AnyChart AnyMap files are included in this patch set. Even though these aren't directly integrated into the Application Express declarative environment, they are included in the license of Application Express and available for your use within an Application Express application.
3. An updated version of FCKEditor is included, fixing a number of bugs. However, one user has already reported a change in behavior, with the default behavior of the Enter key resulting in a '<p>' versus the previous '<br />'.
4. This version of Application Express, 3.2.1.00.10, is the identical version that will be bundled with Oracle Database 11gR2.

For those customers running Oracle Database 11gR1 11.1.0.7, you'll need to pay special attention to the note about applying the APEX 3.2.1 patch set.

As with all past Application Express patch sets, the Application Express 3.2.1 patch set will be available for download on Oracle MetaLink. The full distribution of APEX 3.2.1 will be available for download on OTN. These should be available within one day.

Neues Application Express Buch - auf Deutsch!

Im November 2009 soll das neue Buch "Oracle APEX und XE in der Praxis" von Dietmar Aust, Denes Kubicek und Jens-Christian Pokolm herauskommen. Ich kenne Dietmar schon seit den Anfangstagen der Oracle XE Datenbank, wo er innerhalb kurzer Zeit ein Experte für diese wurde. Und Denes Kubicek ist ein bekannter Experte für Oracle Application Express, welcher letztes Jahr mit dem "Oracle APEX Developer of the Year" ausgezeichnet wurde.

Ich freue mich schon auf das Buch (und natürlich auch auf eine signierte Ausgabe des Buches!).

--------

Coming in November 2009 is a new book from Dietmar Aust, Denes Kubicek and Jens-Christian Pokolm, entitled "Oracle APEX und XE in der Praxis". I have known Dietmar since the early days of Oracle Database XE, where he quickly became a subject matter expert. And Denes Kubicek is a recognized expert of Oracle Application Express and was last year's Oracle APEX Developer of the Year.

I look forward to this book (and getting a signed copy of this book!).

Forthcoming book about Application Express

Today, Packt Publishing announced their forthcoming book, "Oracle Application Express 3.2 – The Essentials and More", authored by my good friend Arie Geller and Matthew Lyon. The expected availability is January 2010.

Congratulations, Arie. The end is in sight!

Application Express & YubiKey

Roger Cohen from APEXtras was kind enough to introduce me to two-factor authentication using YubiKey and Application Express.

Now - I'll be honest, I had never heard of YubiKey before, but I am familiar with two-factor authentication. I am a satisfied user of KeePass, and I am able to maintain both a password and a separate key file (in my case, on a Flash Drive) for access to my encrypted KeePass passwords database. I need both my password and the keyfile to access the passwords database. I lose one or both and I can't get in.

Roger gives a very good description of YubiKey here, what it's good for and why you would want to consider it. But more importantly, Roger has a working demonstration of APEX and YubiKey authentication live on apex.oracle.com. Granted, you'll need a YubiKey for this demonstration to work. Lastly, the folks from APEXtras were kind enough to post an explanation of the logic and all of the source code for the custom authentication for this solution.

Oracle Database Resource Manager and Oracle Application Express

At ODTUG Kaleidoscope this year, I gave a presentation on using the Oracle Database Resource Manager with Oracle Application Express. The Oracle Database Resource Manager enables an administrator to control the allocation of hardware resources within an Oracle database - something that an OS-based process scheduler cannot accomplish. The actual PowerPoint presentation is here.

I often talk about the scalability of Oracle Application Express on apex.oracle.com, which runs on fairly modest hardware. However, I fully believe that this database and server would eventually crumble if it were not for our use of the Oracle Database Resource Manager. Unlike a database instance which is running a handful of tuned applications, apex.oracle.com is a free-for-all. As long as you have a workspace, you can run whatever SQL you like from SQL Commands (I'm not recommending this), or, you could inadvertently write some pretty inefficient SQL or PL/SQL in your application. Without any controls in place, a user could easily and quickly monopolize the CPU resources on apex.oracle.com. And for a site that gets between 3.5 million and 6 million page views per week, page view requests could quickly back up and overwhelm the database server.

So how do we manage this chaos? Via the Oracle Database Resource Manager. Rather than explain the syntax of the Resource Manager, I think it's much simpler to convey the plan that is in place right now on apex.oracle.com and then comment on it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131

-- apex.oracle.com resource plan   -- Section 1 begin   dbms_resource_manager.clear_pending_area();   dbms_resource_manager.create_pending_area();     dbms_resource_manager.create_consumer_group(       consumer_group => 'APEX_HIGH',       comment        => 'All APEX sessions start in this group.');   dbms_resource_manager.create_consumer_group(       consumer_group => 'APEX_MEDIUM',       comment        => 'APEX sessions are switched to this group after 10 seconds.');        dbms_resource_manager.create_consumer_group(       consumer_group => 'APEX_LOW',       comment        => 'Any sessions in this group have been executing for more than 120 seconds');         dbms_resource_manager.submit_pending_area();   end; /     -- Section 2 begin     dbms_resource_manager_privs.grant_switch_consumer_group (         grantee_name => 'APEX_PUBLIC_USER',         consumer_group => 'APEX_HIGH',         grant_option => FALSE );     dbms_resource_manager_privs.grant_switch_consumer_group (         grantee_name => 'APEX_PUBLIC_USER',         consumer_group => 'APEX_MEDIUM',         grant_option => FALSE );     dbms_resource_manager_privs.grant_switch_consumer_group (         grantee_name => 'APEX_PUBLIC_USER',         consumer_group => 'APEX_LOW',         grant_option => FALSE ); end; /   begin    -- Section 3   dbms_resource_manager.clear_pending_area();   dbms_resource_manager.create_pending_area();   dbms_resource_manager.create_plan( plan => 'APEX_ORACLE_COM_PLAN', comment => 'APEX Plan');             -- Plan Directives Section        dbms_resource_manager.create_plan_directive(       plan => 'APEX_ORACLE_COM_PLAN',       group_or_subplan => 'APEX_HIGH',       comment => 'All APEX sessions start in this group.',       mgmt_p1 => 70,       switch_group => 'APEX_MEDIUM',       switch_time => 10,       switch_for_call => TRUE,       switch_estimate => FALSE );     dbms_resource_manager.create_plan_directive(       plan => 'APEX_ORACLE_COM_PLAN',       group_or_subplan => 'APEX_MEDIUM',       comment => 'APEX sessions are switched to this group after 10 seconds.',       mgmt_p1 => 8,       switch_group => 'APEX_LOW',       switch_time => 120,       switch_for_call => TRUE,       switch_estimate => FALSE );             dbms_resource_manager.create_plan_directive(       plan=> 'APEX_ORACLE_COM_PLAN',       group_or_subplan => 'APEX_LOW',       comment => 'Any sessions in this group have been executing for more than 120 seconds',       mgmt_p1 => 2,       switch_group => 'CANCEL_SQL',       switch_time => 1800,       switch_for_call => TRUE,       switch_estimate => FALSE );           -- Section 4   dbms_resource_manager.create_plan_directive(       plan=> 'APEX_ORACLE_COM_PLAN',       group_or_subplan => 'OTHER_GROUPS',       comment => 'The mandatory group',       mgmt_p1 =>10);         dbms_resource_manager.create_plan_directive(       plan=> 'APEX_ORACLE_COM_PLAN',       group_or_subplan => 'ORA$AUTOTASK_SUB_PLAN',       comment => 'Sub plan for maintenance activity',       mgmt_p1 => 10 );         -- Section 5   dbms_resource_manager.set_initial_consumer_group(       user => 'APEX_PUBLIC_USER',       consumer_group => 'APEX_HIGH');     dbms_resource_manager.submit_pending_area();   end; /       -- Section 6 begin   dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');   dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW',       attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN'); end; /     -- Section 7 alter system set resource_manager_plan = 'APEX_ORACLE_COM_PLAN' scope=both;

• Section 1 - Create three consumer groups with the names APEX_HIGH, APEX_MEDIUM and APEX_LOW. These group names are arbitrary. A consumer group is simply a collection of users.


• Section 2 - Remember that the Oracle HTTP Server and mod_plsql connect to the database as user APEX_PUBLIC_USER (if you're using the embedded PL/SQL Gateway, this is user ANONYMOUS). From a database perspective, all it sees are a bunch of database sessions, connecting as database user APEX_PUBLIC_USER. So in Section 2, we are granting the ability to switch among these consumer groups to database user APEX_PUBLIC_USER.


• Section 3 - Create the plan directives which controls how much of the machine resources is dedicated to a particular consumer group, and how long they are allowed to remain in this group before switching to another group. So if a session is in the APEX_HIGH group, the APEX_HIGH group is entitled to 70% of the CPU resources. If the top-level SQL call is executing for more than 10 "on-CPU" seconds, then they get switched to the APEX_MEDIUM group. The APEX_MEDIUM group gets only 8% of the available CPU resources, for up to 120 seconds. After 120 seconds, the session gets switched to the APEX_LOW group. The APEX_LOW group has only 2% of the CPU resources available. After 1800 seconds (which is a *long* time), if the top-level SQL call is still executing, then it will be canceled. The session will not be killed, but the top-level SQL call will be canceled.


• Section 4 - Directives are created for two other pre-defined and built-in groups, namely, OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN. The ORA$AUTOTASK_SUB_PLAN is used for all of the automated maintenance tasks of the database (e.g., statistics collection). OTHER_GROUPS is used for any other session not already a part of a group in the active Resource Manager plan. This would be the group for anyone connecting directly to the database, background database jobs not running as APEX_PUBLIC_USER, etc.


• Section 5 - Make APEX_HIGH, our newly created consumer group, the default consumer group for user APEX_PUBLIC_USER. Then, submit the pending area for validation.


• Section 6 - The Oracle database (at least for 11gR1 and 11gR2) ships with a predefined scheduler plan for maintenance tasks. Modify the resource plan in effect during these maintenance windows. If we don't, then the DEFAULT_MAINTENANCE_PLAN would be in effect and not our newly created Resource Manager Plan APEX_ORACLE_COM_PLAN. And what that means is we'd be back to a free-for-all during this maintenance window, with anyone being able to monopolize the server.


• Section 7 - Change the settings of the database to use our newly created plan.

There are tools available in Enterprise Manager to create and monitor resource plans, so you don't have to commit the syntax of all of these PL/SQL packages to memory. Also, there are a large number of database views that can be used to monitor the Resource Manager, including:

• V$RSRC_PLAN – Currently active resource plan


• V$RSRC_CONSUMER_GROUP – Cumulative amount of CPU stats


• V$RSRC_PLAN_HISTORY – History of resource plan, when enabled, disabled or modified


• V$RSRC_CONS_GROUP_HISTORY – History of consumer group statistics


• V$RSRCMGRMETRIC – Information about resources consumed and wait times per consumer group


• V$RSRCMGRMETRIC_HISTORY – History of Resource Manager metrics

I granted privileges on these SYS-owned views to another database user that was mapped to an APEX workspace (actually, my workspace on apex.oracle.com). Then, I was able to easily build an APEX application using Interactive Reports on top of these views and monitor how well we were doing.

The Oracle Database Resource Manager is only available as a part of the Oracle Database Enterprise Edition. It is not available with XE, Standard Edition One, or Standard Edition of the Oracle Database. But for those customers that are already using Oracle Application Express on the Oracle Database Enterprise Edition, and they're attempting to consolidate a number of groups and applications onto a single instance, the Oracle Database Resource Manager can be easily used to prevent any one user or application from monopolizing the server.