User friendly / supported monitoring of concurrent processes

Yes, I know everyone else is having a great time at OOW, but some of us are back in the real world still.

I've asked a question on OTN (under EBS General Discussion) Best way to execute / monitor long running custom conc request with slave.

Can anyone help me with suggestions for an EBS-supported API (11.5.10 on Solaris 10 / Oracle 9iR2) that would enable the professional user who launched a (PL/SQL) concurrent process to monitor its progress over several hours from his/her application UI? To add to the fun, the process is going to spawn some slaves to make use of all the spare CPUs / cores / threads we have lying around.

As a developer, I would normally start with the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure (and I'll build that in anyhow) - but in this case I'm struggling to find any documentation or ML notes. to point me at something that would actually appear on the apps UI.

Answers here - or better still, on the OTN thread. Thanks in advance!

H-Store - a new architectural era, or just a toy?

Philip Howard's commentary Merchant relational databases: over-engineered and out-of-date? supports the idea that perhaps general purpose relational databases should now be treated as "legacy". He references a paper The End of an Architectural Era (It's time for a rewrite) by Michael Stonebraker and others from MIT.

My first thought was that RDBMS developers such as Oracle have seen off previous architectural challengers - most notably object oriented databases (OODBMS) - in the past 25-30 years. What makes Stonebraker's H-Store any different?

First, a quick summary of the paper:

• RDBMSs were designed 30 years ago - since then memory and cpu have become faster, cheaper and bigger, changing the balance against magnetic (disc) storage
• increasingly they are failing to meet today's complex challenges
• niche solutions have overtaken "general purpose" RDBMS in many areas (eg data appliances for business intelligence; specialist text search engines; etc)
• and now even OLTP, the "core competence" of the RDBMS, is no longer safe; a new approach (such as H-Store) can easily beat traditional RDBMS by cutting out non-functional architectural features (eg: redo logs stored on disc) and achieving the same goals (ACID transactions) in another way.

The paper claims that H-Store can beat a traditional RDBMS at TPC-C style benchmarks; an early version runs up to 80 times faster than "a very popular RDBMS" which itself underwent several days of tuning by a "professional DBA".

H-Store's secret sauce is that it is (in effect) single threaded. It makes the assumptions that all transactions are very fast; then it executes each transaction in turn. This gets rid of the need for complex read-consistency models. Other optimisations include keeping undo in memory (because transactions are short and sharp) and discarding it at the end of the transaction.

Well, go off and read the paper for the details, but here's what I think.

On the negative side:

• As a comparative benchmark, this fails through insufficient disclosure. For a paper that passes as academic, there is remarkably little detail on what they actually did.
• Stonebraker assumes that "most" OLTP systems can be represented by a hierarchical model - what he calls a "constrained tree application" (CTA). As a result these applications are relatively easy to partition over a shared-nothing architecture. I wonder whether this is really the case. Parts of your application may be like that, or they may be like that for some periods (during the online day, for example). But even OLTP applications need to manage longer transactions, complex reporting, and updates to the "read only" tables. In his example, he assumes (section 5.0) that the Items table is read only, so it doesn't break his tree and it can easily be replicated. But we know that new items will be added; others will be re-priced, re-categorised, phased out. Can that be handled without interrupting a 24/7 H-Store style application?
• He also seems to assume that there is only one axis of partitioning - in his case, the warehouse. But over time, the main focus of interest changes. An order is taken at a shop; it is ordered from a warehouse; it is delivered to the customer. Different "CTAs" at each stage. How does the H-Store morph its representation through the course of the information lifecycle?

On the positive side, though, this represents a call to action for the traditional vendors.

• Any performance specialist knows that the best way to tune something is to stop doing it. If we really can change the rules of the game, we can avoid all that expensive "insurance". We're used to making calculated design tradeoffs for performance; this could be just another one of those.
• I suspect that the RDBMS vendors will (more or less rapidly) steal any really good ideas. Stonebraker states that "no [RDBMS] system has had a complete redesign since its
  inception". But, like the proverbial axe, RDBMS internals have been refreshed, a piece at a time. Oracle's database kernel has had at least two major rehashes in its lifetime. It's well within Oracle's or IBM's capability to incorporate the more realistic ideas from this paper, and to find a way to blend them with current state of the art.
• They can also learn from the approach MySQL has taken of supporting multiple storage engines - horses for courses. Oracle already merges XML, relational and OLAP data stores; building in a high performance OLTP kernel to address specific classes of OLTP application is not at all inconceivable. Although it will lead to all sorts of information lifecycle difficulties, we are already used to migrating data from OLTP to OLAP; with good tool support it should be possible to work round the constraints that allow H-Store to dispense with so much that we normally take for granted.

I may revisit this paper to tease out other issues - for example Stonebraker rants against SQL (perhaps he's never got over Ingres being forced by the market to provide SQL rather than the more academically respectable Quel). H-Store uses C++, and may move to Ruby; the implication is that applications will be object-oriented, making row-by-row navigations (like so many J2EE apps, and suspiciously like COBOL/Codasyl) rather than being set-oriented.

Let's watch this space.

SYS_CONTEXT versus V$ views for getting session information

A thread on Oracle-L today sidetracked into the use of V$ views for getting hold of session information such as username, SID and module. The poster had a logon trigger that was supposed to record these items for each logon from a particular client.

I don't approve of granting access to V$ views willy nilly; best practice is always to grant the minimum privileges necessary to achieve an objective.

Another poster raised the issue of performance. In the past, SYS_CONTEXT was considered slower than direct access to the views.

So here is a test to compare the two:

---

set echo off feedback off
set timing on

set termout off

variable v_loops number;
exec :v_loops := 1000000;

set termout on

prompt Testing sys_context

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    for i in 1..l_loopcount loop
 dbms_application_info.read_module(l_module, l_action);
 l_user := sys_context('userenv', 'session_user');
 l_sid := sys_context('userenv', 'sessionid');
    end loop;
end;
/


prompt Testing mystat

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    -- note this only gets one of the three pieces of information
    for i in 1..l_loopcount loop
 select sid
 into   l_sid
 from   v$mystat
 where rownum = 1;
    end loop;
end;
/

---

And here are the results:

C:\sql>sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 22:23:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> @sys_context_test
Testing sys_context
Elapsed: 00:00:07.31
Testing mystat
Elapsed: 00:00:38.57
SQL>

I suspect that in the past SYS_CONTEXT issued recursive SQL under the covers (just as the SYSDATE pl/sql functions used to, and the USER function and the 11g assignment from a sequence still do).

Now I assume SYS_CONTEXT gets its information directly.

Schema Version Control

An outbreak of synchronicity has produced a volley of blogs and questions about database version control in the last few days.

• Tom Kyte adds his own twist to this story at Coding Horror.
• JAson Hinds asked about database release management on one of the OTN forums.
• And Syed Jaffar Hussein (the Human Fly) launched a thread on Oracle-L on the subject.

I seen so many projects take code control seriously but have no real idea how to control a database definition (let alone how to control changes to multiple instances of development, test and live data, both in house and on client sites).

Code control is a walk in the park compared to "schema control" (where the delta , like an ALTER TABLE needs to be developed alongside the new version of the database object creation script).

And "data control" adds even more challenges:

• can you upgrade the data in place? or do you need to create a replacement object?
• Is there enough space?
• Do you need to suspend user access? How long will it take?
• what are the chances of reversing the upgrade if something goes wrong?
• Will you even know if something goes wrong?
• Who decides go forward, go back or muddle through?

A complex application version upgrade should be treated with as much respect as a data migration project. There should be a strategy (sure, it may be a standard strategy). Use standard tools / scripts. The more the process is repeatable - and the more it is repeated - the better. Make sure that all likely error conditions (missing log files, out of DB storage, non-writeable working directories, no listener) are tested for - test the tests!

By the way, the "process" includes all the operating procedures. Make sure that upgrades aren't nannied through by developers. Script the whole thing; have the production DBAs try it out the procedures as early as possible in test to make sure that they understand what's supposed to be happening (and how to know if it worked OK; the best thing is if the script just says:

IT ALL WORKED OK

or

SOMETHING WENT WRONG IN STEP 13 - SEE LOGFILE xxxyyy13

By the time the schema-and-data upgrade is executed for real, nothing - not even the unexpected - should be unexpected.

Got an issue with that?

Here's a little summary of issue management systems I found in the Register for use with (agile) software products. It struck a chord with me - not least because I am right now working with a project team of around 30 trying to manage and organise a range of issues on an ever changing series of spreadsheets, using email as the repository.

I could add the example issue tracking system that comes with Oracle Apex - has anyone tried that?

Other wikis with issue management templates / recipes are available - including PmWiki PITS.

Buffered LOB_APPEND

Jens Ulrik asked to see the code for a buffered LOB_APPEND mentioned in an earlier post LOB_APPEND eats resources....

Here it is as part of a package. Apologies for the rush job at the time; inelegant, but it was worth it...

    gi_chunk_length integer := 0;
    gv_chunk varchar2(32767);

    -- add a character value to the CLOB being built up; catch null values
  -- NOTE - final flush is done elsewhere
  --        and we assume max(length(av_value))  < ki_maxchunksize

    PROCEDURE lob_append (at_doc_clob IN OUT NOCOPY CLOB, av_value IN VARCHAR2)
    IS
     li_value_length integer;
    li_new_chunk_length integer;
    ki_maxchunksize constant integer := 32000;
    BEGIN
        -- prt_common_pkg.print_xml_string(av_value);
        IF av_value IS NOT NULL THEN
        li_value_length := length(av_value);
    li_new_chunk_length := gi_chunk_length + li_value_length;
        
    if li_new_chunk_length >= ki_maxchunksize 
    then
      -- pad out the chunk as much as possible
      gv_chunk := gv_chunk || substr(av_value,1,ki_maxchunksize - gi_chunk_length);
      -- write out
      DBMS_LOB.append (at_doc_clob, gv_chunk);
      -- initialise remainder into next chunk
      gi_chunk_length := li_new_chunk_length - ki_maxchunksize;
      gv_chunk := substr(av_value,li_value_length +1 - gi_chunk_length);
    else
      gv_chunk := gv_chunk || av_value;
      gi_chunk_length := li_new_chunk_length;
    end if;
        ELSE
            prt_common_pkg.LOG ('lob_append null ignored',
                                prt_common_pkg.klog_ret
                               );
        END IF;
    END;

The final flush of the last chunk of a LOB is called like this:

    
    IF gi_chunk_length > 0 THEN
       -- flush out the last chunk of the CLOB
     dbms_lob.append(at_doc_clob, gv_chunk);
     gi_chunk_length := 0;
     gv_chunk := null;
    END IF;

John Backus - father of FORTRAN - RIP

As well as being the father of the first high level language suitable for numerical work - and of Algol 60 which can be taken as the root of most of today's widely used languages like C, C++ and Java - John Backus lives on as coauthor of Backus Naur Form (BNF) which we still use in the Oracle docs - for example:

relational_table ::= CREATE [GLOBAL TEMPORARY] TABLE [schema.]table
                       [(relational_properties)] 
                       [ON COMMIT {DELETE | PRESERVE} ROWS]
                       physical_properties
                       table_properties;

Unique IDs for multi-master replication - Sequence or SYS_GUID?

Oracle-L is proving to be a good source of inspiration at the moment. Oracle ACE Syed Jaffar Hussain asked the question Is it possible to share a database sequence between multiple databases?

Using remote sequences
A couple of the replies took the question a bit too literally, and said yes, you can define a sequence on one database, and use it from another:

Select SequenceOwner.RemoteSeqName.NextVal@DBLinkName From dual

This works - and it can be made 'location transparent' by creating a local synonym for the remote sequence. But it is asymmetric (one database has to own the sequence) and it introduces a point of failure. If database 1 owns the sequence, database 2 can only insert rows if database 1 is actually available. If you could guarantee availability, why would you bother with replication?

Using a sequence local to each database
Several replies (including mine) suggested using carefully defined sequences which will deliver discrete values on two or more master databases. There are two basic patterns:

Partitioned: Suggested by several posters, the number space is divided up in blocks:

on db 1: create sequence myseq start with 1000000 max 1999999 
on db 2: create sequence myseq start with 2000000 max 2999999

A variant on this is to use positive numbers for database 1, and negative numbers for database 2.

Interleaved: The more popular option is to use the old trick of assigning odd numbers to database 1, and even numbers to database 2:

on db 1: create sequence myseq start with 1 increment by 2
on db 2: create sequence myseq start with 2 increment by 2

This mechanism is much easier to manage (essentially, it doesn't need any further management). It is also easy to extend to 3, 4, 27 or 127 masters - just set the "start with" to the database number, and "increment by" to the maximum anticipated number of databases required.

A third option was also proposed by Mark D Powell: use SYS_GUID(). That has some disadvantages:

• SYS_GUID() is bigger (16 bytes on 9iR2) than a NUMBER (eg a 13 digit integer needs around 8 bytes). Obviously the extra space follows through to indexes, foreign keys etc.


• Another is simply that it is a RAW, which has some possibly undesirable implications; for example several common tools (including SQL*Developer 1.0) can't directly display RAW values; you have to explicitly select RAWTOHEX(id_column).

On the other hand, SYS_GUID can be defined as the default value for a column - unlike NEXTVAL which is normally set in a trigger or directly as part of an insert into/select from statement. Worse, it is very common to see a separate SELECT seq.NEXTVAL FROM DUAL for every ID generated. I've never investigated the relative performance of SYS_GUID() against getting a sequence number - anyone else like to share that? That may well be the most important consideration of all.

So I think I'll be coming back to this subject in future.

Update - later the same day:

I've quickly timed a million iterations of select sys_guid() from dual, and a million iterations of select sequence.nextval from dual (Oracle XE 10.2.0.1, HP dv1665 Centrino Duo running Windows XP)

    select sys_guid()  into variable from dual: 89 seconds
    select seq.nextval into variable from dual: 40 seconds

For good measure, I've added a variant - one million calls of sys_guid() without a select:

    variable := sys_guid()                    : 95 seconds 

One of the potential advantages of sys_guid() I had anticipated is that it could be called directly from PL/SQL - but it looks like the implementation is less efficient than it might be; tracing shows that the PL/SQL function recursively selects SYS_GUID() from dual.

So I think I'll stick to traditional and more convenient sequence.nextval for now.

Chin chin!

The more the meta - 'Intentional Software' meets reality?

This quite long Technology Review article catches up with Charles Simonyi - Microsoft's former Chief Architect and the alleged inventor of Hungarian variable naming convention - who will be rocketing up to the International Space Station in April.

In his day job, he has been developing a 'new' concept now known as 'intentional software'; for every problem domain, developers will create generic tools which users can employ to "guide the software's future evolution".

Code generators, CASE tools and IDEs could all be seen as primitive examples of what he's on about. They also point to the possible fly in the ointment. As you approach the problem boundaries, any preconceived tool inevitably becomes increasingly inefficient; and the abstraction often makes it difficult or impossible for the user/developer to interfere productively. This is the law of leaky abstractions.

That's not to say that domain-specific generators are a bad thing per se; the template driven generator underlying Constellar Hub was a key component in its success, allowing us to quickly improve and extend the range of objects it could generate. When the tool runs out of steam, code developers can simply jump out of the tool and modify the generated code (sadly, that seems to be the permanent situation for most J2EE - more code and less declarative than any 1980s 4GL) or use a different abstraction.

But as far as Simonyi's work goes, you can count me in with the sceptics. Changing the representation of a problem (from textual code to a graphical parse tree, for example) does not in itself make the problem easier to solve. I saw my first 'visual' coding system in about 1981 - on a VT100 at that - and it was both brilliant and (without a mouse) completely **** useless at the same time. Still, it will be fun watching him throw all his money at it...

Ciao!

Using global context variables

I recently stumbled on another 'new' feature - though in fact it was introduced way back in Oracle 9i. A questioner on the OTN database forum wanted to know how to invalidate cached package state derived from a table that might be updated from another session.

My first suggestion was to implement a time-to-live cache - treat the data as stale if it has been cached for longer than X seconds (using DBMS_UTILITY.get_time to record the load time). All sessions manage their own private caches; a cache can be inconsistent for up to X seconds (if the underlying data is updated immediately after the cache is loaded).

Vetaran OTN poster Jens Petersen came up with what could be a much neater solution, which is to use a global context. You can define your own namespace for use with the standard SYS_CONTEXT function, and arrange to get/set attribute name/value pairs - on a per-session or instance-global basis. Like a DIRECTORY, a CONTEXT isn't owned by a specific schema.

Creating the context

To be able to create a context the user must be granted CREATE ANY CONTEXT privilege.

create or replace context my_context
using testuser.my_context_pkg
accessed globally -- including this makes attribute values visible to all sessions
/

A package is linked to the context; this is the only interface that can be used to set attribute values in the context. This coupling is a little inelegant, but it does mean that only users with EXECUTE permission on the package will be able to set values.

create or replace package my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
);
end my_context_pkg;
/

create or replace package body my_context_pkg
as
procedure set_context
(av_attribute in VARCHAR2
,av_value in VARCHAR2
)
is
begin
DBMS_SESSION.SET_CONTEXT
( namespace=>'MY_CONTEXT'
, attribute=>av_attribute
, value=>av_value
);
end set_context;

end my_context_pkg;
/

Any API you like to define is supported; this example is the simplest, allowing a completely unconstrained setting of attributes pairs. Here's how you define and use an attribute:

my_context_pkg.set_context('Attribute Name', 'Attribute Value');

Now, what about that original problem - how to notify active sessions that data in their package state might be out of date?

Well, there are two approaches: replace the cache directly with (one or more) CONTEXTs, or construct a dirty cache notification scheme:

Replace cache with CONTEXT

Simply maintain the cache as attribute/values in a CONTEXT. This could be appropriate if the cache was relatively simple (eg a set of lookup codes and descriptions). The PL/SQL caching can be completely discarded; instead use SYS_CONTEXT to get the descriptions, and set up triggers on the lookup table that use MY_CONTEXT_PKG to update the context. The only minor snag is that the context values need to be set (at least once) on instance startup; this could be a scheduled job, or be incorporated into a logon trigger.

Dirty cache notification

Define a 'cache update sequence' context attribute; let's suppose it uses MY_CONTEXT with an attribute name of 'cacheseq'.

When the package initialises its cache, it should read and retain the latest value for 'cacheseq' (treating null as 0). Later, before using values from the cache, it should re-read the latest value for 'cacheseq'. If the numeric value is greater than the retained value, the cache must be dirty. Applications that update the underlying tables (and so invalidate any caches) must increment 'cacheseq' - normally by using a statement level trigger on insert/update/delete.

my_context_pkg.set_context
( 'cacheseq'
, nvl(to_number(SYS_CONTEXT('MY_CONTEXT','cacheseq')),0)+1
);

Updating a CONTEXT attribute is not transactional, and almost certainly not read consistent; if an update is rolled back, we may find our cache being refreshed unnecessarily. If two updates to the table are made concurrently, we might see 'cacheseq' increase by one only. But our dependent sessions should all see that there has been some update activity.

Performance

Before jumping in and using CONTEXTs, I wanted to be sure that the performance wouldn't be a limiting factor. I didn't want to find SYS_CONTEXT calls producing a hidden SELECT ... FROM DUAL as we all remember for SYSDATE, USER etc in the past. So here's a quick test:

declare
li_cached_integer integer := 0;
begin
for i in 1..100000 loop
li_cached_integer := nvl(to_number(sys_context('MY_CONTEXT','cacheseq')),0);
my_context_pkg.set_context('cacheseq',to_char(li_cached_integer+1));
end loop;
end;

Fetching the value 100,000 times (including char to number conversion) takes around 1 second; saving the modified value 100,000 times takes an additional 2 seconds (on an Intel Pentium 4 3.4Gb dual core running XP SP2 and Oracle XE).

Exporting Context Definitions

IMP/EXP won't import/export these objects, and I presume datapump won't either; so the easiest thing to do is create a SQL script using DBMS_METADATA package:

select DBMS_METADATA.GET_DDL(object_type, object_name)
from DBA_OBJECTS WHERE OBJECT_TYPE='CONTEXT';

Don't specify a schema in the call to GET_DDL, if you do, you get:

ORA-31604: invalid NAME parameter "SCHEMA" for object type CONTEXT in function SET_FILTER


Dictionary Views

There are some dictionary views that can be useful:

ALL_CONTEXT	lists contexts that have been activated (ie a value has been set since startup)
DBA_OBJECTS	(where object_type = 'CONTEXT')
V$CONTEXT	lists attributes set in the current session (appears to ignore globally accessible attributes)

I'm planning to come back to this topic once I've had a longer play with multiple sessions setting and reading locally and globally accessible context variables.

The real cost of denormalisation

Perhaps the most common performance problem I used to come across in my Oracle consultancy days was inappropriate, excessive or just plain dumb denormalisation. Any number of inexperienced (or wrongly experienced) developers would proudly show me how they had 'optimised' their database design because joins are expensive, aren't they.

Ironically, this design decision would often be the direct cause of my visit...

Anyway, while browsing the always entertaining Oracle-L mail list yesterday, I tripped over Jared Still's Normalize for Performance paper - given at Hotsos 2006 a few weeks ago. He's explained some of the typical problems (and also ways around some of them with materialised views). I'll enjoy going through the examples next time I've got a couple of free hours...