Oracle constraints in XML data

Oracle introduced pretty cool XML support in 9i, it's even cooler in 10g and I don't understand why people don't use it more often. I keep seeing XML data stored in CLOB's all the time. Why not store it as proper XML, it's possible to index, query and even update individual XML elements, attributes or nodes. Fast, simple, easy, no need for full text indexes. Performance of xpath queries is pretty good if indexed correctly, 25 000 XML documents 10k each is still in the sub second range when hitting an index.

One thing that can be quite nice to have in the XML store is constraints to avoid duplicate data, indexes on XML data are pretty much plain standard pseudo-column indexes (or "functional indexes" as some refer to them as).
We just use the basic extract() or extractValue() functions in Oracles XML feature set.

Here's an example on how to to create unique constraints (indexes) on XML elements (or attributes):

SQL> create table x (a xmltype);

Table created.

SQL> insert into x values('<type><name>dog</name></type>');

1 row created.

SQL> insert into x values('<type><name>cat</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/name'));

Index created.

SQL> insert into x values('<type><name>cat</name></type>');
insert into x values('<type><name>cat</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x values('<type><name>fish</name></type>');

1 row created.

-- Lets try the constraint on an attribute.
-- attributes are handeled just like elements but need a @ sign prefix

SQL> drop index xui;

Index dropped.

SQL> truncate table x;

Table truncated.

SQL> insert into x(a) values('<type id="1"><name>sally</name></type>');

1 row created.

SQL> insert into x(a) values('<type id="2"><name>bob</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/@id'));

Index created.

SQL> insert into x(a) values('<type id="2"><name>carol</name></type>');
insert into x(a) values('<type id="2"><name>carol</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x(a) values('<type id="3"><name>carol</name></type>');

1 row created.

-- Ok, lets see if we can have duplicate names.

SQL> insert into x(a) values('<type id="4"><name>carol</name></type>');

1 row created.

SQL>

And some docs to read.

Viewing bind variable values in 10g

Oracle 10g introduced a couple of new nice views to help tune queries that use bind variables.
One cool view is v$sql_bind_capture, this view hold the latest captured value for each bind variable in queries that has been run.
First have a look in v$sql to find the SQL query you are looking for, join the sql_id to v$sql_bind_capture and to view the bind variable values for that query.
Example:

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='f8pavn1bvsj7t'
/

SQL_TEXT                                    BIND_NAME  BIND_STRIN
------------------------------------------- ---------- ----------
select con#,obj#,rcon#,enabled,nvl(defer,0) :1         9110
 from cdef$ where robj#=:1

I found a pretty bad example here, an Oracle internal dictionary query, but it should show the point.

Mixing Dell PowerEdge 1955 and 1855 blades

After looking around the Internet for information regarding mixing Dell PowerEdge 1855 and 1955 blades in one enclosure I found some varying "opinions" whether it works or not.

To get things right.
Yes, it is possible to mix and match any 1855 and 1955 blades in one single enclosure
However, there are two small requirements.

• The DRAC/MC needs firmware 1.3 or later (everyone should upgrade to 1.3, even if you don't have any 1955 blades).
  
• You need the digital KVM modules, they rock, get them!

The 1955 blades plug-in during operations just as any 1855 blades would, you get a sensor detect and they power on just fine.

Printout from the DRAC/MC:

[Server Module Power Consumption Table]
<Slot#> <Server Name>    <Blade Type> <Power State> <Current/Max Consumption>
 1       Server-1         PE1855       ON            300/300W
 2       Server-2         PE1855       ON            300/300W
 3       Server-3         PE1855       ON            300/300W
 4       Server-4         PE1855       ON            300/300W
 5       Server-5         PE1855       ON            300/300W
 6       Server-6         PE1855       ON            300/300W
 7       Server-7         PE1855       ON            300/300W
 8       Server-8         PE1955       ON            304/304W
 9       Server-9         PE1955       ON            304/304W
 10      Server-10        N/A          N/A           N/A

Cool!

Extended deployment

Ok, I admit it. I've sucked at blogging lately.
In my defence I have had a quite annoying cold that's been hanging on for the last two week, I *really* hate having a fever, I get cranky. I wrote half a blog post about using Oracle XE for reporting with materialized views but never finished it, will probably do that tomorrow or Monday.
Beyond that I just haven't had anything exciting to do, at work I've been working with Sybase, can't say that's very exciting. Especially not very exciting when one has to support a major investment bank which has a team of Sybase DBA's which seems to know even less about Sybase than I do (and trust me, I'm a Sybase noob).

One exciting thing I've been toying with is IBM Websphere Extended Deployment (XD). Everyone knows that Websphere isn't very exciting, the XD edition has got some pretty cool features though. And one has to love how simple IBM explains the huuuge cost savings it brings.

- "A client can buy a few as four medium sized mainframes and deploy a number of applications across these machines and achieve unprecedented utilization".

Ok, back up a bit here IBM. The concept of Websphere XD works pretty much all platforms. So why bring out the mainframes, I can't really see many clients needing four mainframes for a normal Websphere deployment.
Anyway, what XD brings is pretty much a resource manager and an object grid. Say you have a blade server with 10 blades, you want to deploy two applications which will be load balanced. Instead of telling Websphere to deploy each application on five servers you define metrics of what kind of response times you expect the applications to have, then Websphere will allocate as much resource as needed (or send angry emails requesting more servers). Say one application runs on 2 servers and the other application on the remaining 8, then every Friday everyone in the company needs to use the first application for a couple of hours. Websphere will see the extra utilization and assign a couple of more servers to this app, then when not used any more they will be returned to the second application. Neat!
Another thing you can use XD for is to distribute a large set of data, you can write a distributed application and requests will be sent to the server holding that data. It's way cheaper to buy 16 servers with 16Gb RAM each than to buy one server with 256Gb RAM, let each server hold a piece of the data in RAM in an object grid.
XD also brings some cool monitoring features and other crud. See the comparison of the different versions here.

Enough about Websphere, it's not that great. It's just a nice challange to work with. :-)

bash globbing and dot-files

Found a nifty little feature in bash.

Globbing is expanding file pattterns, like when you typ "ls -l file*" in bash it is not ls that does the file matching and filtering. bash will glob ("expand") the file list file* and ls will get all the files as arguments.
Now, per default bash doesn't glob dot-files. If I do "ls *" I will not get .bashrc and .bash_profile etc. Luckily it is easy to change this behavior. Set the bash option dotglob to enabled and it works!
Example:

[hlinden@spinner testdir]$ ls -Al
total 0
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 .dotfile1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 .dotfile2
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file2
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file3
[hlinden@spinner testdir]$ echo *
file1 file2 file3
[hlinden@spinner testdir]$ shopt -s dotglob
[hlinden@spinner testdir]$ echo *
.dotfile1 .dotfile2 file1 file2 file3
[hlinden@spinner testdir]$

Just put shopt -s dotglob in your .bashrc file or in a global /etc/profile.d file.
Another quite nice globbing feature is to have case insensitive globbing.
Check this out:

[hlinden@spinner testdir]$ shopt -s nocaseglob
[hlinden@spinner testdir]$ ls -l F*1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file1
[hlinden@spinner testdir]$

Flashback a user or schema in Oracle

Since 10g we've had the quite cool feature 'flashback', or rather we've had it since waay back. But now Oracle gave us an easy way to use it. We have the recyclebin as a new feature though. Cool but sometimes a bit confusing.

Flashback can be done at two levels, the whole database or for a single object. Why on earth didn't Oracle include a "flashback schema" feature. I would guess most users use flashback when doing testing, like schema upgrades and things like that. And if the test fails you want to restore the tables to before the scripts ran (ok workspaces comes to mind here but that's another post).
I took some time and wrote a script to flashback all tables and dependant objects from the recyclebin and to flashback existing tables to a previous version.
Since flashback require row movement to be enabled the script checks if it needs to enable that for the tables and will do so if needed.

-- Ok, let's create a little mess we can clear up.
SQL> drop table i;

Table dropped.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

SQL> update a set d=sysdate;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 21-JAN-07 11.29.53.000000 PM
         2 21-JAN-07 11.29.53.000000 PM

-- Commited and all! We sure screwed that up

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
T                              TABLE
D                              TABLE
BIN$J5ZNZK2dxdvgQKjAKF9ZFQ==$0 TABLE

4 rows selected.

-- Lets run the script to generate our flashback script
-- The script will prompt you for the number of minutes you want to go back
SQL> @flashback_user
How far back do you want to flashback (in minutes)?
Enter value for minute: 8
         8
testuser

Spooling flashback_user_testuser.sql

alter table T enable row movement;
flashback table A to timestamp sysdate - interval '8' minute;
flashback table T to timestamp sysdate - interval '8' minute;
flashback table D to timestamp sysdate - interval '8' minute;
flashback table I to before drop;

SQL> @flashback_user_testuser

Table altered.


Flashback complete.


Flashback complete.


Flashback complete.


Flashback complete.

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
D                              TABLE
T                              TABLE
I                              TABLE

4 rows selected.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

2 rows selected.
-- And we are back in business.

Download my script here: http://halisway.hifichoice.com/flashback_user.sql.

Read more about flashback here.

Balancing SGA in Oracle 9i

Now with 10g having sga_target for automatically managing your SGA memory size some people get lazy and just forget about SGA. But most of us still have 9i databases to maintain.

Time for a quick overview on how to reduce disk i/o with the help of db_buffers.

I've got a Sun v480 with 4Gb RAM running one instance, it's not under a lot of load but could do with some tuning.
Lets look how our current SGA settings looks:

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
sga_max_size                         big integer 2283246744

SQL> select * from v$sgastat order by pool,bytes;

POOL        NAME                            BYTES
----------- -------------------------- ----------
java pool   free memory                  67108864
shared pool trigger source                    152
shared pool fixed allocation callback         496
shared pool trigger defini                    576
shared pool trigger inform                    920
shared pool PLS non-lib hp                   2088
shared pool joxs heap init                   4240
shared pool KQR S SO                         5416
shared pool table definiti                  18984
shared pool PX subheap                      28096
shared pool session heap                    29560
shared pool KGK heap                        33368
shared pool DG Broker heap                  39200
shared pool MTTR advisory                  388024
shared pool errors                         390856
shared pool sessions                       905840
shared pool message pool freequeue         940944
shared pool sim memory hea                1014808
shared pool KSXR receive buffers          1034000
shared pool FileIdentificatonBlock        1791824
shared pool parameters                    1827072
shared pool PL/SQL DIANA                  1960192
shared pool 1M buffer                     2098176
shared pool Checkpoint queue              2622720
shared pool KQR M PO                      2885104
shared pool dictionary cache              3229952
shared pool KQR L PO                      3372488
shared pool event statistics per sess     3762720
shared pool KQR L SO                      5260312
shared pool KGLS heap                     5424464
shared pool KQR M SO                      6135256
shared pool FileOpenBlock                11813536
shared pool PL/SQL MPCODE                22044808
shared pool miscellaneous                25966760
shared pool library cache                66948552
shared pool sql area                    166511368
shared pool free memory                 248709688
            fixed_sga                      734360
            log_buffer                     787456
            buffer_cache               1073741824

40 rows selected.

Ok, we can see that we do have quite some free memory
in the shared pool, what should we do with that?
from looking at iostat and wait statistics I've noticed that the disk
subsystem (a simple Sun 3310 in this case) is getting a bit hammered sometimes.
Lets see how the db cache is doing.

SQL> select size_factor, size_for_estimate, estd_physical_read_factor
  2    from v$db_cache_advice order by size_factor;

SIZE_FACTOR SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR
----------- ----------------- -------------------------
      .0938                96                 4344.0852
      .1875               192                  163.0333
      .2813               288                    23.782
       .375               384                   22.5502
      .4688               480                   20.6444
      .5625               576                   10.4806
      .6563               672                    1.4957
        .75               768                    1.0948
      .8438               864                    1.0367
      .9375               960                    1.0102
          1              1024                         1
     1.0313              1056                     .9999
      1.125              1152                      .813
     1.2188              1248                     .7185
     1.3125              1344                     .5829
     1.4063              1440                      .536 <- Lets aim for this
        1.5              1536                     .5196
     1.5938              1632                     .5139
     1.6875              1728                     .5079
     1.7813              1824                     .5055
      1.875              1920                     .5043

21 rows selected.

Right, from looking in the v$db_chace_advice view we can determine that a few hundred Mb more of cache would be a good idea. There is quite a lot of free memory at the OS level, so we can probably increase the sga_max_size with 250Mb as well.

-- Reduce the shared pool with 150Mb.
SQL> alter system set shared_pool=379584512 scope=spfile

System altered.

-- Add max size with 250Mb
SQL> alter system set sga_max_size=2545390744 scope=spfile;

System altered.

-- Add the 250Mb we added + the 150Mb we reduced from the shared pool.
SQL> alter system set db_cache_size=1493172224 scope=spfile;

System altered.

-- Restart Oracle
SQL> shutdown immediate
...
SQL> startup

That should do the trick, lets see what the server thinks about things in a couple a weeks time or so.

Don't forget:
Please have more than one look at the statistics before you do anything, the free memory in the shared pool could be free today but needed all other days. Take a few snapshots over time and analyze the results before doing any changes. And whatever you do, don't over allocate SGA.
The last thing you want is the SGA to be swapped to disk or the system swapping out other things.