Oracle on ext4 warning
I bought a new laptop recently ( Dell Studio XPS 16 ) Core 7i, 8Gb, 7200RPM disk. I installed my Linux distro of choice, Fedora 12, and started installing Oracle. Yeah I know Fedora isn’t a supported OS, but I’ve got almost ever version from 8.1.7.4 to 11.2 and I’ve never really hit any problems for testing use.
To justify my new purchase, I thought I check how lighting fast it was in comparison to my 3+ year old Dell D620. I timed how long it would take to create a 10.2.0.4 database – problem was ….
Old Laptop : ~10 mins
New Laptop : ~20 mins
The only difference I could see was that the old machine was 32bit the new one was 64bit and wasted a bit of time going down that dead end. Anyway after lots of investigation I eventually noticed that the old machine the database filesystem was built with ext3 ( it was created back when Fedora 6 was current), the new laptop was using ext4.
I found a blog Mount options to improve ext4 file system performance and tried the options suggested – and magically the database creation time dropped from 20mins to ~6mins ! Then to confirm I rebuilt the filesystem as ext3, and retained the 6min build time.
ext4 has been standard on the bleeding edge distros for a while now, and is starting to appear in the enterprise Linux distos, Red Hat EL6 beta lists as a feature. So watch your Oracle performance if you are considering ext4.
Andy
Quick Note
Just noticed this on the Apress site, looks like can expect a new book from Tom Kyte in the summer …
http://apress.com/book/view/9781430229469
Thought he had been quiet recently.
Has there been a big change in data volume ?
Ever get requests from users/developers complaining that queries or feeds are suddenly running slower – and claiming nothing has changed ? Are you pretty sure its just going to data volume or some sort of change in table statistics?
One of the useful features introduced in 10g was DBMS_STATS automatically saving old statistics before it replaces them, this allows you run a query to see the history of the number of rows and sample size for a table and spot sudden changes or patterns.
SELECT obj.owner
,obj.object_name table_name
,NVL(obj.subobject_name,'GLOBAL') partition_name
,TO_CHAR(hist.savtime) save_time
,rowcnt
,DECODE( rowcnt,0,100, ROUND( samplesize / rowcnt * 100 )) SampleSize
FROM dba_objects obj
,sys.wri$_optstat_tab_history hist
WHERE hist.obj# = obj.object_id
AND owner LIKE '%&OWNER%'
AND object_name LIKE '%&TABLE_NAME%'
ORDER BY obj.owner,
obj.object_name,
obj.subobject_name,
hist.savtime;
Which would could produce output something like the following ( edited to fit ) …
OWNER Table Name SAVE_TIME ROWCNT SAMPLESIZE
---------- ---------- ----------------------------------- ---------- ----------
TST MYTABLE 21-DEC-09 10.48.49.657130 PM -08:00 4599783 35
22-DEC-09 08.51.03.804101 PM -08:00 4609457 35
22-DEC-09 10.25.03.426489 PM -08:00 4606449 35
23-DEC-09 10.53.17.058652 PM -08:00 4602537 35
24-DEC-09 11.01.33.769205 PM -08:00 4609786 35
25-DEC-09 10.29.19.524311 PM -08:00 4608197 35
26-DEC-09 10.29.57.033672 PM -08:00 4607169 35
27-DEC-09 08.42.30.261394 PM -08:00 4608031 35
28-DEC-09 09.01.04.458258 AM -08:00 4610931 35
28-DEC-09 08.58.45.685068 PM -08:00 4611994 35
29-DEC-09 08.54.35.136462 PM -08:00 4610089 35
<snip>
19-JAN-10 08.48.50.453766 PM -08:00 4621849 35
19-JAN-10 08.51.17.329342 PM -08:00 4625629 35
19-JAN-10 10.04.42.517133 PM -08:00 4625584 100
20-JAN-10 09.53.32.024996 PM -08:00 4622763 35
20-JAN-10 10.59.40.301349 PM -08:00 4627791 100
21-JAN-10 09.01.01.591324 PM -08:00 4624446 35
Here the data volumes aren’t changing much although steadily increasing, but you can see gather stats is getting run twice on days without much change in data volume, but with different sample sizes. It needs a little more investigation but this probably the scheduled automatic job (SYS.GATHER_STATS_JOB ) and a manually submitted gather_stats from a batch job or app support.
How much history is kept? By default 31 days, but this can be increased.
SELECT 'History Retention'</pre>
,TO_CHAR(dbms_stats.get_stats_history_retention)
FROM dual
How much does space my stats history take ?
SELECT occupant_name
,schema_name
,space_usage_kbytes Used_kb
FROM v$sysaux_occupants
WHERE OCCUPANT_NAME = 'SM/OPTSTAT'
OCCUPANT_NAME SCHEMA_NAME USED_KB -------------------- --------------- ---------- SM/OPTSTAT SYS 1616384
I’ve used the history a number of times to spot sudden changes in data, and is a useful script to have in your back pocket.
Andy
Not So Read Only …
I’ve always assume making a tablespace READ ONLY meant NO changes could happen to its contents. So for example if I create new tablespace, put a table in it and set it to be READ ONLY …
1:27:43 ops$oracle@ORA10204> create tablespace TEST; Tablespace created. 11:27:58 ops$oracle@ORA10204> create table andy tablespace test as select * from dba_objects; Table created. 11:28:21 ops$oracle@ORA10204> alter tablespace TEST READ ONLY; Tablespace altered.
So I can’t change the rows in the table, good …
11:28:35 ops$oracle@ORA10204> update andy set object_name = NULL; update andy set object_name = NULL * ERROR at line 1: ORA-00372: file 6 cannot be modified at this time ORA-01110: data file 6: '/db1/oradata/ORA10204/datafile/o1_mf_test_5oft5vpq_.dbf'
But I can drop the table !
11:38:34 ops$oracle@ORA10204> drop table andy; Table dropped.
One workaround for this could be to disable DDL locks on the tables to make dropping tables harder …
11:43:48 ops$oracle@ORA10204> alter table andy disable table lock; Table altered. 11:45:37 ops$oracle@ORA10204> drop table andy; drop table andy * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for ANDY