Sunday, January 21

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;

---------- ----------------------------
1 30-NOV-06 PM
2 06-NOV-06 AM

SQL> update a set d=sysdate;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from a;

---------- ----------------------------
1 21-JAN-07 PM
2 21-JAN-07 PM

-- Commited and all! We sure screwed that up

SQL> select tname,tabtype from tab;

------------------------------ -------

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

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;

------------------------------ -------

4 rows selected.

SQL> select * from a;

---------- ----------------------------
1 30-NOV-06 PM
2 06-NOV-06 AM

2 rows selected.
-- And we are back in business.
Download my script here:

Read more about flashback here.

Tuesday, January 16

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

------------------------------------ ----------- ------------
sga_max_size big integer 2283246744

SQL> select * from v$sgastat order by pool,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;

----------- ----------------- -------------------------
.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.

Wednesday, January 10

Cleaning up sqlplus cut and pastes in vim

Classical problem, you write a short piece of SQL in sqlplus, you then want to cut and paste it to vim edit it. But then you get the really annoying line numbers in front of each line.
Sure you can use save myquery in sqlplus and open the file myquery.sql in vim, but perhaps you want to run vim on another machine, perhaps you run gvim.
So ok, time to get rid of the line numbers.

Here's what we got in sqlplus:
SQL> list
1 select
2 e.ename,
3 d.dname
4 from
5 dept d,
6 emp e
7 where
8 d.deptno=e.deptno
9* order by e.ename
Lets cut n paste those numbered lines to a vim session.
We now need to delete the first 4 characters of each line. Easy.
Switch to visual mode, jump to the end of the file, step two rows right, hit delete char.
Or in vim command terms (in normal mode (esc-esc)).
ctrl-v G l l x
ctrl-v to get in to visual mode
G to jump to the end of the file
l to move the cursor right
x to delete the selected area

Visual mode is quite powerful, you can use almost the same commands to comment out code.
Place the cursor in the first line you want to comment out, hit ctrl-v to do a visual mark, use j to move the cursor down to the last line you want to comment out, hit I to get insert, type two hyphens and hit escape. Done.
In vim command terms:
ctrl-v j j j I -- esc

Sunday, January 7

Time for HP Proliant?

Now I can't say I've blogged much about HP server, simply because it's not been very exciting hardware. Dell and Sun have always had better products at better prices and have always been better to deal with.
Now recently, especially with the G5 series of it's Proliant line HP have some quite exciting and unique products. The one thing I've always liked with HP is that they've been a long term AMD Opteron supporter. The where the second big server vendor after IBM to launch Opteron based servers. The DL385 was unique at the time and served me well in a previous company I worked for. The following DL585 (4 sockets, 128Gb RAM max and 8x 2.5" SAS slots) is still a pretty ok Oracle server, the G2 version comes with a pretty cool front accessible CPU and memory drawer for easy upgrades and repairs is even better. But price wise it's on par with the Sun x4600, and the x4600 will let you scale to 8-sockets over of the 4-socket DL585.
The Intel equivalent, the DL580 offers front accessible memory access, CPU's are still top lid accessible.

Now with the Intel-based (dual and quad core Xeon 3000, 5000, 5100 and 5300 boxes) machines in the "G5" series we have a few pretty cool new features, the focus on 2.5" SAS drives is nice to see, Sun is going the same way but Dell is clinging on to 3.5" drives (probably to keep the price tag down), some Dell machines can be fitted with a 2.5" backplane (like the PE1950).
The DL360 makes a decent app server with two sockets and up to 32Gb RAM, nice 6 slot SAS backplane and the DL380 adds more PCI I/O and can take up to 8 drives.
Big brother (in the 2-socket famaily) is the ML370, this is a pretty large machine for a 2-socket box, 5U in the rack, but it makes a great VMWare server, loads of local disk (16 SAS slots), loads of I/O and can take a massive 64Gb RAM. Great for stand-alone Oracle deployments as well.
The smallest member in the 300-family is the DL320, pretty standard 1-socket Xeon 3000 machine (3.5" drives though), makes a decent webserver or network appliance.

Ok, now on to the reason I actually started getting excited about HP hardware again.
The recently introduced DL 320s hybrid data server (or "storage server").
It's pretty much the same as the DL320, but the "s" adds some pretty cool stuff. It can take 12 SAS or SATA drives. Which makes it a pretty interesting machine, one could say this is the "HP Thumper", the HP equivalent of the Sun x4500. It's not as big and impressive as the x4500 but it's still interesting. Applications like distributed data mining, data analysis and video processing fits like a glove on this box.

A few bad things about HP, everything is extra. The price you see is not the price you pay. The list price of a normal, say DL380, includes exactly what is listed. You even have to add the second sets of fans to get redundancy. Half the features in the iLO2 (remote management interface) require extra licenses to be activated. I think HP needs to re-think this. As it stands today I would not look at buying HP servers as generic servers, possibly if I have a project that require something only HP can offer (like a DL320s). It simply doesn't make sense financially.
If I want standard server, I go to Dell. If I want business critical servers, I go to Sun. It's as easy as that. If HP can offer competitive pricing I would not hesitate to at least evaluate deploying HP instead of Dell and in some cases Sun boxes.

Tuesday, January 2

Happy new 2007

It's been a pretty good year. Lots of cool stuff has happened and lots of interesting technology is maturing and evolving. Most notably OpenSolaris, While most other UNIX-vendors are pretty much sitting on their behinds watching time go by Sun is kicking in a higher gear. ZFS is being adopted by the FreeBSD and MacOS communities. Iscsi is maturing and VMWare is getting some pretty cool features, Veritas is giving away the basic edition of Storage Foundation. Oracle 11g looks very promising.

All well, I'm flying back to the UK tomorrow (finally), can't wait to get back to London.