DBA 2.0 – how options are really not optional or: "the infinite database"

After Web 2.0, Enterprise 2.0 and Wife 2.0, Oracle introduces the term DBA 2.0. It seems to refer to the DBA of the very near future. The one who can rely on a formidable pal hiding inside the database and taking on many of the boring, repeating tasks as well as advising on some of the tougher challenges when it comes to managing the database. Manageability is one of the focus areas for Oracle in the marketing strategy for the database as well as in the development budget allocation. Lots of time and energy are invested in making the database more manageable and striking the optimal balance between automated management and human decision making.

DBA 2.0 is perhaps also the guy about whom Mark Townsend,Vice President Product Management for Server Technologies, said: "to really screw a system up, you have to be a DBA!". To which he added:....
"flashback helps to recover from most of the human errors so easily made, often without any one even noticing what happened.

Manageability is one of several focus areas for the database. Others include

– High Availability
– the Oracle Warehouse initiative (ensure that storage and processing power are balanced)
– Security

 

Associated with these focus areas are Options – specific pieces of functionality that make all the difference. In the past, Oracle marketed the Enterprise Edition and the Standard Edition without too much specific attention to specific options. That has been changed: Oracle’s strategy with the database is focused on Options. That is where marketing is focused, that is where most of the development budgets is spent. Options is where the main competitive edge, key differentiators are for the Oracle Database. That is where Oracle has more revenue than Microsoft does on its entire Database division. Note: Oracle’s marketshare (49%) of relational database market is bigger than Microsoft, IBM, Sybase and Teradata added together.

Mark categorically did not go into 11gR2 in his talk. The beta for this release has not yet started – beta participants are being recruited during the conference. No major announcements about its functionality are to be expected during this conference (with the possible exception of the BIG one). Editions – one of the features slated for 11gR2 that should make hot patching a reality – is definitely not going to be a topic.

11.1.0.7 is shipping – first patch set for 11gR1

Small announcement: the first patch set for the 11gR1 database is shipping: 11.1.0.7, currently only on Linux. Other platforms will follow in the next weeks and months. One new feature: Graphical Explain Plan Monitor. It gives a real time (for long running queries) graphical overview of query execution – each step of the explain plan is illustrated live with bar-charts indicating the relative cost.

To find it: in Database Control,click on performance tab, click on SQL Monitoring. Graphically monitor historic and current SQL statements. It is already part of Grid Control. Note: this feature is part of standard edition.

(a pretty unclear picture of this tool:

DBA 2.0 - how options are really not optional or: "the infinite database" treetargetiterator0011

)

The infinite database

Compression is a big topic – and also was last year. Oracle’s advanced compression – pretty expensive extra cost option – will save on storage (obviously) and the floor space and energy costs associated with storage and will improve performance. Well, at least for queries that do full table scans or at least a lot of IO. DML will have a small (less than 3%) performance impact, as data has to be compressed before being stored.

The story in short: Advanced Compression: less IO, higher CPU load (but most systems are IO bound!. Up to max 3% DML overhead, Storagw 3x, query (table scans) 2.5 faster. (2-4 times compression).

Advancing on this, Mark/Oracle envision (near) infinite databases: with even more advanced compression techniques, historical data can continue to be part of the (on line) database and still take up minimal (and cheaper, slow disk) storage. Their retrieval – hardly ever required and currently usually impossible because of off-line storage – will take longer than accessing current data. However, the data is still available and can be accessed. The maintenance effort and costs to offload non-current data will be dramatically reduced. As Lewis points out, some intelligent partitioning is required for this.

Partitioning is another very important topic when it comes to manageability. Oracle 11g introduced many more ways to partition tables – for example the Reference Partition option where child-data is partitioned along with the partioning scheme set up for its master (line items are partitioned with the order they belong to).

Upgrading the database

Oracle 9i is now on extended support: support rates go up(1% per year); customers should go to 11gR1 (but could go to 10gR2). Mark explained how SQL Performance Analyzer can be used to compare execution plans from 9i, 10g and 11g databases. It can help with the migration – together with the parameter optimizer_features_enable – by allowing the 11g database to run as 10g, creating the 10g ‘style’  execution plans as the baseline for query execution in 11g and only gradually introducing – when they have been assessed and deemed correct – real 11g plans. For migration from 9i – both to 10g or 11g – the SQL Performance Analyzer can help too. Very briefly: SQL Trace – to pull SQL statements from 9i, import in 11g, turn into SQL Tuning Set. Compare with 11g behavior. Now also : 9i plans into 11g, turn into SQL tuning set, export to 10g; execute in 10g and send results back to 11g. 11g will analyze difference between 9i/10g and 11g.

More information about the various ways to upgrade on OTN: http://www.oracle.com/technology/products/database/oracle11g/upgrade/index.html .

APEX and SQL Developer

Somewhat surprising to me, Mark spent quite some time on APEX and SQL Developer. Tools that are still growing in importance and visibility. One somewhat controversial new feature in APEX is its capability to convert Forms into APEX modules. Mark started by pointing out that Oracle continues to support Forms and Reports for many many years (at least until 2013). However, for organizations that have lost the knowledge about the application and the skills to understand the inner workings of the Forms and still need to move forward, APEX can help by extracting part of the Form definition – its database interaction (blocks, items), its overall structure and some of its layout and maybe some of its trigger logic as well (I could not get a clear view on that) – into the APEX repository. APEX uses the Forms 10g (was that available with 9i as well) XML representation of the FMB.

The controversy was in that Mark introduced Forms from the historical perspective of ‘enterprise level’ applications and then quickly linked it to APEX, while of course Oracle’s official line is: Professional large scale development: ADF. APEX for small scale, departmental, opportunistic and short lived applications. Offering a conversion – note the wording: it is not migration, it is more like design capture; – from what is (was) a technology plaform for enterprise level, mission critical application to a stack that Oracle states is not intended for that type of application is likely to cause some confusion. Especially when it is clear that various factions within Oracle have differing agendas on this. Mark conceded that Oracle is not using APEX for any of the Applications products it develops and that is indeed not intended for professional development of mission critical ap
plications. 

New in
SQL Developer are its modeling capabilities. With the recent acquisition of IKAN, Oracle bought functionality for conceptual (ER) and logical (Database Design) modeling, including reverse engineering from DDL scripts (from many different sources including non-Oracle databases) and table to entity retrofit. Some utilities (Table to View wizard?). Import capabilities from among others Oracle Designer and Warehouse Builder.

At the present time, all models are stored in a file based repository. Over time Mark stated: the model and metadata will be added to the database dictionary[!].

I find it ironic that in 2008 Oracle is proud to present functionality that it already had back in 1998 in the form of Oracle Designer. Functionality that is still around though in hibernation mode. And that it now acquired from an outside company to add into SQL Developer. I like the functionality, it is good to have it. But from a distance it looks somewhat ridiculous.

Note: it is not yet clear whether this modeling capability is to be a free or an extra cost option for SQL Developer.

Also see

OOW 2007: Mark Townsend explains “Storage is not cheap” – report on Mark’s presentation from last year

Lewis Cunningham, who was in the same session, wrote a pretty extensive report on it; see: http://it.toolbox.com/blogs/oracle-guide/oow-2008-ace-director-day-and-oracle-in-the-cloud-27305 .