Hampus Linden

Subscribe to Hampus Linden feed
Hampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.comBlogger199125
Updated: 10 hours 8 min ago

Run system commands from Oracle with PL/SQL

Wed, 2007-05-30 15:55
I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
He had full access to Oracle with sysdba/dba roles etc, but not SSH.
I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
The temporary spool file handling in my example is quite poor, but works. :)
A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

Example of use:
oracle@htpc:~$ rsqlplus hlinden/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> @system_run

Procedure created.

SQL> exec system_run('ls -l /home/oracle/bin');
total 12
-rwxr-xr-x 1 oracle dba 797 Nov 5 2006 backup_controlfile.sh


PL/SQL procedure successfully completed.

SQL> exec system_run('uname -a');
Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

PL/SQL procedure successfully completed.

SQL>
And here is the procedure code:
CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)
IS
script_file varchar2(40) := 'my-temp-script.sh';
script_data varchar2(4000);
MyFile utl_file.file_type;
d varchar2(4000);
dump_file varchar2(40) := '/tmp/my-temp-file.dat';
dump_type utl_file.file_type;
BEGIN
-- Open file
MyFile := utl_file.fopen('TMP',script_file,'w');
-- Write data to file
script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
utl_file.put_line(MyFile, script_data, FALSE);
-- Close file
utl_file.fflush(MyFile);
utl_file.fclose(MyFile);
-- Purge old logs, no fun anyway
dbms_scheduler.purge_log(JOB_NAME=>'TEST');
-- Execute script
-- The job is created as disabled as
-- we execute it manually and will
-- drop itself once executed.
dbms_scheduler.create_job(
job_name => 'TEST',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
enabled => FALSE);
dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
dbms_scheduler.enable('TEST');
-- Wait for the job to be executed
-- usually done within 1 second but
-- I set it to 2 just in case.
dbms_lock.sleep(2);
-- Open the output file and
-- print the result.
dump_type := utl_file.fopen('TMP',dump_file,'r');
loop
begin
utl_file.get_line(dump_type,d);
dbms_output.put_line(d);
exception
when others then
exit;
end;
end loop;
utl_file.fclose(dump_type);
-- Clean up our temp files
utl_file.fremove('TMP', script_file);
utl_file.fremove('TMP', dump_file);
END;
/

Cyrus IMAP file system tuning

Mon, 2007-05-28 04:59
Been busy, not enough blogging, bla bla bla. I know.
Just a lot of stuff going on at work at the moment, mergers and integrations.

We've had some problems with one of our IMAP servers at work running Postfix and Cyrus IMAPd. A for the job quite well speced machine, dual Xeons and 3x146Gb disk in RAID5 (4x146 in RAID10 would have been nicer). Anyway, the machine has got 50 or so IMAP users and perhaps 100Gb spool data on a ReiserFS partition.
The machines' avg. load has peaked at over 6.00 with about 75% in iowait on a bad day. I suspect that modern fancy e-mail search tools are to blame for the problems, applications building search indexes and such (Apple Mail anyone?).
Monitoring of the server showed quite a lot of inode update activity, even though there isn't *that* much new email coming in.
Must be our old (not so) dear friend atime that's making a little mess, I've used the noatimea and nodiratime mount options in the past with great success. Seen performance improvements of a couple of percent.
The mount-options noatime and nodiratime simply disables the feature to update the access timestamp of a file (and directory). I.e. when someone clicks and reads an email in their mail application the inode atime timestamp is updated. When is this atime timestamp used? Never.
Did a quick online remount of the spool fs with noatime and nodiratime.
The result?
Avg. load hasn't touch 1.00 since. Wow! I was expecting an improvement, but not that big.
Great and easy way to improve performance on IMAP spools.
mount -o remount,noatime,nodiratime /var/spool/imap
And don't forget to update /etc/fstab with the same mount options.
Cool.

Insider for Oracle - funky GUI bits

Wed, 2007-05-09 15:47
We've been doing a lot of application benchmarking and tuning at work in the last couple of months as we are prepare for a new major release. I've been involved in the Oracle side of things and have spent a lot of time tracking down expensive (or plain weird) SQL-statements and trying to optimize the Oracle databases we use.

After spending about 6 - 7 hours a day running my normal sqlplus scripts over and over trying different things, monitoring benchmark runs I got a bit bored of text output and had a quick google for some sort of basic v$whats_up GUI tool and found this little cool little app called Insider for Oracle, it a great app for doing spot-on monitoring, sort of like Spot light from Quest, but without the hassle. Insider tries to be a more proactive tool with round the clock monitoring, but for that, it's no good.

The thing I find it useful for is when we start a benchmark I can trail what's going on with the system. See new SQL statements, wait events, SGA usage, PGA usage, hot segments etc. Nothing I can't do from sqlplus but it makes my tasks a bit easier.
Price wise it's quite a bargain for what I use it for, the license is for each database on the application "workspace" (i.e. how many databases you want to look at at once). And for what I use it for just looking at one DB at a time, that works out pretty cheap, 599USD or so. Cheap as chips.
I've found a couple of bugs (and submitted bug reports accordingly) and I've even submitted a couple of enhancement suggestions.
Check out their company blog.

OpenSolaris SXDE upgrade time

Mon, 2007-05-07 16:04
It's been a while since I last played with Solaris Express Community Edition, so long that it actually changed names. There are now two versions, one for "random people" and one version for developers. SXCE is not released as often as SXDE but is more tested and stable, SXDE also comes with a lot more goodies, like Sun Studio 11 and Netbeans.
Anyway, the install of SXDE b63 under VMWare workstation 6 RC was as easy as ever.
However, at first boot I almost thought the VM was frozen for a minute or so, after grub it just sits there with a cursor in the corner of the screen for about 2 minutes before kicking off the kernel. All good after that though.
One thing I wanted to test was the new simplified resource capping in Solaris Zones. It was very straightforward and easy to use.
A couple of extra commands in zonecfg and the zone is capped to one core and 512Megs of memory. No more messing around with projects, except if you want really fine grained capping that is.
Cool!

Took a screenshoot:
b63

How to do an "insert ignore" in Oracle

Mon, 2007-04-30 14:28
Ok, I should start off with a disclaimer. This is not a good idea to do.
Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.

Ok, in MySQL we can do this.
mysql> insert ignore into a select * from b;
Query OK, 1 row affected (0.00 sec)
Records: 2 Duplicates: 1 Warnings: 0
But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the merge statement?
Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.
-- So what do we got?
SQL> select * from a;

A B
---------- ----------
1 2
2 3

SQL> select * from b;

A B
---------- ----------
1 3
3 4

SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.

SQL> select * from a;

A B
---------- ----------
1 2
2 3
3 4

SQL>

-- Let's rollback that and have a look at the exeuction plan.

SQL> rollback;

Rollback complete.

SQL> set autotrace on
SQL> merge into a using b on (a.a=b.a)
when not matched then insert values (b.a,b.b);

1 row merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 1973318225

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 64 | 7 (15)| 00:00:01 |
| 1 | MERGE | A | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 64 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 2 | 52 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| A | 2 | 12 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."A"(+)="B"."A")

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
15 consistent gets
0 physical reads
0 redo size
819 bytes sent via SQL*Net to client
778 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
Looks ok.

Pages