Wednesday, September 27

Rotating tables (and sybase)

Got a question on IRC today about the equivalent of MySQLs MAX_ROW setting on a table. The problem at hand was more specific. The table should always contain N number of rows, if a new row was inserted, the oldest should be deleted. I wrote this quick example to provide a possible solution.
CREATE TABLE top10data (
id NUMBER(2) NOT NULL,
name VARCHAR2(20) NOT NULL
);

CREATE VIEW top10 AS
SELECT id,name FROM top10data;

INSERT INTO top10data VALUES(1,'one');
INSERT INTO top10data VALUES(2,'two');
INSERT INTO top10data VALUES(3,'three');
INSERT INTO top10data VALUES(4,'four');
INSERT INTO top10data VALUES(5,'five');
INSERT INTO top10data VALUES(6,'six');
INSERT INTO top10data VALUES(7,'seven');
INSERT INTO top10data VALUES(8,'eight');
INSERT INTO top10data VALUES(9,'nine');
INSERT INTO top10data VALUES(10,'ten');
COMMIT;

-- trigger on the view, catch the insert and
-- do it on the actuall table

CREATE OR REPLACE TRIGGER top10_trigger
INSTEAD OF INSERT ON top10 FOR EACH ROW
BEGIN
UPDATE top10data SET id=id+1;
DELETE FROM top10data WHERE id>10;
INSERT INTO top10data VALUES(1,:new.name);
END top10_trigger;
/

-- insert example, note that we insert on the view
SQL> select * from top10 order by id;

ID NAME
---------- --------------------
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten

10 rows selected.

SQL> insert into top10(name) values('New one');

1 row created.

SQL> select * from top10 order by id;

ID NAME
---------- --------------------
1 New one
2 one
3 two
4 three
5 four
6 five
7 six
8 seven
9 eight
10 nine

10 rows selected.
This solution may have some concurrency problems if it is heavly populated. To get around that problem you could use a sequence and increment the ID from the trigger and delete based on rank() of the numbers.

On another issue
I've been working a bit with Sybase lately.
To say the least, I do not like it.
  • The way it handles users, permissions and the "database owner" thing. I don't like it.
  • Data space and log space, whats this about. Isn't it just better to define logs and a number of "data storage thingies" (why don't we call them tablespaces) for anyone to use. Why pre-assign space and devices to a database (schema'ish) . I don't like it.
  • The structure of transaction logging and checkpointing. It just feels ancient. I don't like it.
  • If you do a database dump on Solaris/SPARC and try to load it on a Linux/x86 machine you need to do a data conversion process thingie. Whats that about, sure big endian vs. small endian when using raw devices. But a dump should be universal. Sybase should forget about always working with plain raw devices (disk, tape etc) and allow people to actually make use of the file system provided. Besides if you want "endian less" raw devices just use a cds volume under vxvm. Transportable tablespaces anyone? I don't like it.
  • The basic footprint is tiny compared to Oracle. The installation takes about 10 minutes and the sybase server uses about 40Mb of memory when you first start it up. So for a basic testing / dev database it is quite nice. I do like it.


And I've been messing around a bit with DB 2 as well. I'm not sure I like it yet but it is miles better than Sybase. I'll have to think about that one for a while (and play with it a lot more).

Wednesday, September 20

Update of updates

Another quickie update, nothing fun to report anyway. Been busy fighting WebSphere lately, trying to get decent performance on a Sun T2000 server. Out of the box performance was horrible. Websphere 5.1 is still far from satisfactory, I'm putting my hope on Websphere 6.0 now.

Updated Sun boxes:
Sun finally decided to update the classic vXX0 range. Just a simple face lift to be honest, they still run the same range processors. The v440 has been replaced by the new v445, the v240 with the v245 and the 1U v210 is replaced by the v215.
The main two updates are the use of SAS drives and the addition of PCIe. Same basic features as the Galaxy and Niagra servers. They updated the chassi to Andys design.
My favorite new feature is the fact that you can have twice as many drives in the v445. 8 SAS drives instead of the old 4 SCSI. Might even be usable as a Oracle server without external storage. Oh, one last thing. No Solaris 8 support, bad bad. Who uses Solaris 9 anyway?!


Oracle patch release:
Patch release 9.2.0.8 is out, not much new. Bug fixes and a patch for the dreaded select-view-permission-hack security hole. I have tried applying it to both 9.2.0.4 and 9.2.0.6 machines without any hickups. 4547809 in Metalink (now if Oracle would just make these patches available for anyone to download *sigh*).

Btw, I've switched to KDE at work as well.

Tuesday, September 12

KDE - Not that krap after all

Ok, I've been quite "anti KDE" the last few years, with good reason imo. The last time I tried KDE must have been around 2001 or 20002 and back then it wasn't that good.
Now the other week my beloved OpenSolaris powered laptops hard drive failed on me. After I spend about a week moaning about it I finally got around to buying a new drive for it. Replace the old 30gb 4200rpm with a snappy 80gb 5400rpm drive (yes, the speed difference is really significant). Since I didn't have a recent Nevada build ready and was to lazy to download one I decided to install Kubuntu instead, got a free CD at an event the other day. Simple install as always with Ubuntu. Even the widescreen res on my laptop worked out of the box. Easy setup, did the usuall apt-get install oracle-xe, install postgres.
Then I realized I really lliked using KDE, things where so much easier than GNOME. KDE has probably got less features, but it just works. The integration between the applications is great, the little IM client Kopete is much nicer than gaim. KDE seems to use less memory than GNOME as well. Oh well, I saved disk space for Solaris and will probably install that as soon as I get around to downloading a recent version. Nevada B48 should be out any day now.

Friday, September 8

Fancy group by rollup example

Played around with group by rollup today and thought I'd post a little example.

Let's look at the basic usage of the rollup clause
select
deptno,
ename,
sum(sal) sal from emp
group by rollup (deptno,ename)
/

-- Here's the output from sqlplus

DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
10 8750
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
20 10875
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 9400
29025

18 rows selected.
Ok, thats all well and cool. Oracle sums up the previous rows when the group by condition change, which in our case is for each individual and for the department. When a rollup happens all "non rolled up" columns are returned as nulls as shown in the above example. Notice the last row which has sum of all employees and hence nulls are returned for the two rollup columns.
You probably want to label the rows instead of the nulls with something more usefull, like a small "Sum" descriptor or something. As usuall, decode() is our friend.
Example with pretty labels:
with empt as
(
select
deptno,
ename,
sum(sal) sal from emp
group by rollup (deptno,ename)

)
select deptno,
decode(ename,
null,
decode(rownum,count(*) over (),'TOTAL SUM','DEPT SUM'),
ename) ename,
sal
from empt
/

-- And the output from sqlplus

DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 KING 5000
10 MILLER 1300
10 DEPT SUM 8750
20 ADAMS 1100
20 FORD 3000
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
20 DEPT SUM 10875
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250
30 DEPT SUM 9400
TOTAL SUM 29025

18 rows selected.

Cool.

Wednesday, September 6

Sending SMS from Oracle (abuse warning)

Ok, this post is mostly to annoy people who think stuff like this should be done from the application layer. I do agree with them but these things can done from the db layer as well.
Anyway, here is a little code sniplet to send a SMS text message from the database just using a insert statement. The SMS provider we use at work for basic Nagios alerts and stuff like that is Sign.Up To, cheap and quite reliable (they did have some downtime yesterday though). To send the actually message we use a trigger which in turn calls the quite handy (and abusive) dbms package utl_http, utl_http works in Oracle 8 and up (including XE).
If you haven't got utl_http installed you can do so by running the two scripts ?/rdbms/admin/utlhttp.sql and ?/rdbms/admin/prvthttp.plb as sysdba on the server. The response you get back is either a message id or the keyword "fail" if the message fails for some reason, the trigger simply catch that message and store it a table.
This code is just proof of concept and should not be used for anything "real", no error handeling or input checking. It just sends off the data to the message aggregator. And it can't handle any fancy characters in the message nor spaces. You can easily implement basic URL encoding using this function written by Tome Kyte.
create table smslog (
sms_id number(8),
smsno varchar2(18) not null,
message varchar2(160) not null,
status varchar2(150),
sendtime date
);

alter table smslog add constraint
smslog_pk primary key (sms_id);

create sequence smslog_pk_seq;

CREATE OR REPLACE TRIGGER smslog_insert_trigger
BEFORE INSERT ON smslog FOR EACH ROW
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
val VARCHAR2(2000);
url VARCHAR2(300);
BEGIN
url := 'http://sms.sign-up.to/smpp_send.php?username=username&from=oracle&
account=account_no&password=yourpassword&
message='||:new.message||'&to='||:new.smsno;
req := UTL_HTTP.BEGIN_REQUEST(url);
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0 Oracle');
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, val, TRUE);
UTL_HTTP.END_RESPONSE(resp);
:new.status := val;
:new.sendtime := sysdate;
SELECT smslog_pk_seq.nextval INTO :new.sms_id FROM dual;
END smslog_insert_trigger;
/

-- And a sample sent message.SQL> insert into smslog(message,smsno)
2 values('Eat_more_fruit','4479697xxxxx');

1 row created.

SQL> select * from smslog;

SMS_ID SMSNO MESSAGE STATUS SENDTIME
------- --------------- -------------------- ------------- ---------
1 4479697xxxxx Eat_more_fruit 3211821 06-SEP-06

Monday, September 4

NFS locking issues

Been quite busy at work, decommissioning some old servers.
We are trying to get rid of everything pre RHEL/CentOS 3.x. After buying a new shiny file server for our home directories (a Dell 2850 with lots of cheap disk space) I ran in to some very weird problems. Everything worked fine at first, I myself (as a XFCE user) didn’t have any problems at all, but our GNOME users ran it some serious problems. When logging in to GNOME the desktop did not start properly, gnome-panel looked like a grey bar without any tools and the desktop menu didn’t appear on right click. Simply, nothing in GNOME worked. Not in Centos, not in Fedora, not even JDS in Solaris worked.
After installing the GNOME debuginfo packages for Linux it appeared to be some weird NFS locking problem (haven’t we all hear that one before) in gconfd. NLM auth lock calls got denied. Hard to troubleshoot why and how at the time, we simply remounted the NFS shared with the nolock mount option in Linux and the sort of undocumented mount option llock in Solaris.
NFSv3 is just a mostly irritating patch work of addons, statd, lockd etc etc.
Hate NFSv3, why didn’t I include a NFSv4 migration while we where at it.
The nolock "hack" will have to do for now, I’ll will try to troubleshoot the problem later on.

fstab line in Linux:
fs1:/export/home  /export/home nfs rsize=16384,wsize=16384,intr,nolock
I'll try to post some rather interesting Oracle 10gR2 Sun Fire T200 server benchmarks later on this week. I've found a rather good benchmarking tool called Swingbench. Written in Java and easy to use. It comes with a data loading utility to populate the database with nice bogus data and then has a few tools to simulate different workloads.
Been reading a lot about file system tuning recently as well, ext3 tuning parameters and Oracles filesystemio_options parameter, need to dig up a good test box for that so I can evaluate the options. My current two disk machine doesn't cut it for I/O load testing.

Finally a quick movie recommendation.
Bought a new LCD TV the other day so I've been overindulging in movies recently.


And a big thanks to Lukas Smith for sending me a few DVDs from my wish list.