.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, December 13, 2005

Playing With System Statistics

Updated 14-Dec-2005 at the end of the article

I was just musing on what we can deduce directly from system statistics, and thought of the following.

If the system recorded the average MBRC and the average MREADTIM over the period of statistics collection, then does that mean that we can learn something directly about the performance of the i/o system during that time? I ran an experiment:

drop table t
/

create table t
nologging
pctfree 99
pctused 0
tablespace REG_UA4MB_MSSM
as
select rpad(rownum,100) whatever
from dual
connect by 1=1 and
level < estimate_percent=""> 1,block_sample => true)

select blocks*
(select block_size
from user_tablespaces
where tablespace_name = 'REG_UA4MB_MSSM')
/1024/1024 table_size_Mb
from user_tables
where table_name = 'T'
/

alter system flush buffer_cache;

exec dbms_stats.gather_system_stats('START')

select count(*) from t;

exec dbms_stats.gather_system_stats('STOP')

select pname,
pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN'
/

select (select pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN' and
pname = 'MBRC') *
(select to_number(value) block_size
from v$parameter
where name = 'db_block_size') /
(select pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN' and
pname = 'MREADTIM')
/1024 sys_stat_mread_speed
from dual
/


What I'm doing there is creating a table of around 512Mb (because I'm aiming for one row per block, and each block is 8kb), flushing the buffer cache, starting the collection of system statistics, timing a select of count(*) for the 512Mb table, and comparing the read speed according to table_size/duration against MBRC*block_size/MREADTIM.

In fact the correlation is pretty good. Here's my own execution of the above script with timing on:


SQL> drop table t
2 /

Table dropped.

Elapsed: 00:00:00.37
SQL>
SQL> create table t
2 nologging
3 pctfree 99
4 pctused 0
5 tablespace REG_UA4MB_MSSM
6 as
7 select rpad(rownum,100) whatever
8 from dual
9 connect by 1=1 and
10 level <>
SQL> exec dbms_stats.gather_table_stats(user,'T',estimate_percent=> 1,block_samp
le => true)

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.29
SQL>
SQL> select blocks*
2 (select block_size
3 from user_tablespaces
4 where tablespace_name = 'REG_UA4MB_MSSM')
5 /1024/1024 table_size_Mb
6 from user_tables
7 where table_name = 'T'
8 /

TABLE_SIZE_MB
-------------
511.992188

Elapsed: 00:00:00.06
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL>
SQL> exec dbms_stats.gather_system_stats('START')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL>
SQL> select count(*) from t;

COUNT(*)
----------
65535

Elapsed: 00:00:13.12
SQL>
SQL> exec dbms_stats.gather_system_stats('STOP')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>
SQL> select pname,
2 pval1
3 from sys.aux_stats$
4 where sname = 'SYSSTATS_MAIN'
5 /

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 1132.397
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 11.039
MREADTIM 11.886
CPUSPEED 1084
MBRC 63
MAXTHR 36093952
SLAVETHR 2065408

9 rows selected.

Elapsed: 00:00:00.00
SQL>
SQL> select (select pval1
2 from sys.aux_stats$
3 where sname = 'SYSSTATS_MAIN' and
4 pname = 'MBRC') *
5 (select to_number(value) block_size
6 from v$parameter
7 where name = 'db_block_size') /
8 (select pval1
9 from sys.aux_stats$
10 where sname = 'SYSSTATS_MAIN' and
11 pname = 'MREADTIM')
12 /1024 sys_stat_mread_speed
13 from dual
14 /

SYS_STAT_MREAD_SPEED
--------------------
42.4028269

Elapsed: 00:00:00.00


So the system statistics suggest a multiblock read performance of 42.4Mb/sec, and the Count(*) timing of 13.12sec and table size of 512Mb suggest 39Mb/sec.

This is just a single-threaded multiblock read on an otherwise quiescent system and so probably represents the upper limit on what this system could achieve, rather than what it would achieve in a multiuser environment, but the method seems useful as some kind of benchmark. Shouldn't it be possible to perform some kind of real-world comparison on this basis, if the system stats were collected during a period of multiuser activity?

I intuitively feel that this calculation would represent an approximation of how many Mb/sec you could expect from each thread of a parallel query, providing that neither the CPUs nor the disk subsystem are choking performance. So if you knew that you could run a degree 8 scan of a table without any part of the system choking, this ought to tell you hw long such a scan would take.

I think.

Update 14-Dec-2005

Following comments by Jonathan Lewis I ran a slightly modified version of the script to start a 10046 level 8 trace before the start of system statistics gathering. Here's a summary in which I've just included truncated SQL statements and wait events (and summarized those where appropriate):


BEGIN dbms_stats.gather_system_stats('START'); END;
SELECT /*+ rule */ VALUE FROM V$PARAMETER WHERE UPPER(NAME) = 'STATISTICS_LEVEL'
SELECT S.SNAME, S.PNAME, S.PVAL1, S.PVAL2 FROM AUX_STATS$ S WHERE S.SNAME LIKE 'S
WAIT #2: nam='db file sequential read' ela= 230 p1=1 p2=4434 p3=1
WAIT #2: nam='db file sequential read' ela= 139 p1=1 p2=4426 p3=1
SELECT SUM(KCFIOSBR) SBLKRDS, SUM(KCFIOSBT)*10 SBLKRDTIM, SUM(KCFIOMBR) MBLKRDS,
SELECT S1.VALUE - S2.VALUE MBREADS FROM V$SYSSTAT S1, V$SYSSTAT S2 WHERE S1.NAME
delete from sys.cache_stats_0$
WAIT #20: nam='db file sequential read' ela= 8581 p1=1 p2=5425 p3=1
WAIT #20: nam='db file scattered read' ela= 1069 p1=1 p2=5426 p3=7
WAIT #20: nam='db file scattered read' ela= 10004 p1=1 p2=51393 p3=8
WAIT #20: nam='db file scattered read' ela= 13039 p1=1 p2=64049 p3=8
WAIT #20: nam='db file scattered read' ela= 3618 p1=1 p2=64073 p3=8
WAIT #20: nam='db file scattered read' ela= 10195 p1=1 p2=80513 p3=8
WAIT #20: nam='db file scattered read' ela= 12877 p1=1 p2=88233 p3=8
WAIT #20: nam='db file sequential read' ela= 4380 p1=2 p2=57 p3=1
WAIT #20: nam='db file sequential read' ela= 498 p1=1 p2=5434 p3=1
WAIT #20: nam='db file sequential read' ela= 142 p1=1 p2=5435 p3=1
WAIT #20: nam='db file sequential read' ela= 175 p1=1 p2=88257 p3=1
SELECT NVL(VALUE,'8.0') FROM V$PARAMETER WHERE NAME = 'compatible'
select sysdate, SYSDATE + 10/24/60 from dual
DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDAT
SELECT NAME FROM SYS.USER$ WHERE USER# = :B1
WAIT #22: nam='db file sequential read' ela= 11775 p1=1 p2=98 p3=1
WAIT #22: nam='db file sequential read' ela= 150 p1=1 p2=91 p3=1
SELECT NAME FROM SYS.USER$ WHERE USER# = UID
SELECT JOBSEQ.NEXTVAL FROM DUAL
INSERT INTO SYS.JOB$ (JOB, LOWNER, POWNER, COWNER, NEXT_DATE, INTERVAL#, FLAG, WH
WAIT #25: nam='db file sequential read' ela= 2854 p1=1 p2=1473 p3=1
WAIT #25: nam='db file sequential read' ela= 263 p1=1 p2=1475 p3=1
WAIT #25: nam='db file sequential read' ela= 1069 p1=1 p2=1482 p3=1
WAIT #25: nam='db file sequential read' ela= 1132 p1=1 p2=1490 p3=1
UPDATE SYS.SNAP$ SET AUTO_FUN = :B2 , AUTO_DATE = :B1 WHERE (SOWNER,VNAME) IN (SE
WAIT #26: nam='db file sequential read' ela= 577 p1=1 p2=1497 p3=1
WAIT #26: nam='db file scattered read' ela= 260 p1=1 p2=1498 p3=2
UPDATE SYS.JOB$ SET FIELD1 = :B2 WHERE SYS.JOB$.JOB = :B1
update sys.job$ set cowner=:2, nlsenv=:3, env=:4 where job=:1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'STATUS', NULL, :B1 )
WAIT #29: nam='db file sequential read' ela= 1898 p1=1 p2=4425 p3=1
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTART', NULL, :B1 )
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTOP', NULL, :B1 )
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'FLAGS', :B1 , NULL)
insert into sys.wri$_optstat_aux_history (savtime, sname, pname, pval1, pval2) se
WAIT #33: nam='db file sequential read' ela= 179 p1=3 p2=1028 p3=1
WAIT #33: nam='db file sequential read' ela= 27820 p1=3 p2=1019 p3=1
WAIT #33: nam='db file sequential read' ela= 170 p1=3 p2=1018 p3=1
WAIT #33: nam='db file sequential read' ela= 138 p1=3 p2=1017 p3=1
WAIT #33: nam='db file sequential read' ela= 228 p1=3 p2=1024 p3=1
WAIT #33: nam='db file sequential read' ela= 3783 p1=2 p2=9 p3=1
WAIT #33: nam='db file sequential read' ela= 5176 p1=2 p2=7095 p3=1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'CPUSPEEDNW', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'IOSEEKTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'IOTFRSPEED', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'SREADTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MREADTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'CPUSPEED', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MBRC', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MAXTHR', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'SLAVETHR', :B1 , NULL)
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_TEMP'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'SBLKRDS', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'SBLKRDTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBLKRDS', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBLKRDTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CPUCYCLES', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CPUTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'JOB', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CACHE_JOB', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBRTOTAL', :B1 , NULL)
insert into sys.wri$_optstat_opr (operation, target, start_time, end_time) value
WAIT #3: nam='db file sequential read' ela= 181 p1=3 p2=1035 p3=1
WAIT #3: nam='db file sequential read' ela= 126 p1=3 p2=1034 p3=1
WAIT #3: nam='db file sequential read' ela= 124 p1=3 p2=1033 p3=1
WAIT #3: nam='db file sequential read' ela= 123 p1=3 p2=1040 p3=1
WAIT #3: nam='db file sequential read' ela= 4169 p1=2 p2=25 p3=1
WAIT #3: nam='db file sequential read' ela= 665 p1=2 p2=71 p3=1
WAIT #3: nam='db file sequential read' ela= 127 p1=3 p2=1044 p3=1
WAIT #16: nam='log file sync' ela= 201 p1=342 p2=0 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 2175 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #3 len=44 dep=0 uid=40 oct=3 lid=40 tim=125426516619 hv=2242715
select /*+ noparallel (t) */ count(*) from t
END OF STMT
PARSE #3:c=0,e=3497,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=125426516613
EXEC #3:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=125426516827
WAIT #3: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0

(start of summary)
1024*db file scattered read, ela total = 11752972 (avg 11478)
3*db file sequential read, ela total = 625
(end of summary)

(start of stat lines for count(*) from t)
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=65590 pr=65537 pw=0 t
STAT #3 id=2 cnt=65535 pid=1 pos=1 obj=260948 op='TABLE ACCESS FULL T (cr=65590 p
(end of stat lines()

WAIT #3: nam='SQL*Net message from client' ela= 230 p1=1111838976 p2=1 p3=0
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1111838976 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 2549 p1=1111838976 p2=1 p3=0
BEGIN dbms_stats.gather_system_stats('STOP'); END;
SELECT 1 FROM SYS.JOB$ WHERE JOB = :B2 AND POWNER = :B1 FOR UPDATE
DELETE FROM JOB$ WHERE JOB = :B1
UPDATE SYS.SNAP$ SET AUTO_FUN = 'null' WHERE (SOWNER,VNAME) IN (SELECT RC.OWNER,
insert into sys.wri$_optstat_aux_history (savtime, sname, pname, pval1, pval2) se
WAIT #6: nam='db file sequential read' ela= 10237 p1=2 p2=41 p3=1
WAIT #6: nam='db file sequential read' ela= 7822 p1=2 p2=2145 p3=1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_TEMP'
insert into sys.wri$_optstat_opr (operation, target, start_time, end_time) value
WAIT #16: nam='log file sync' ela= 195 p1=388 p2=0 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 3981 p1=1111838976 p2=1 p3=0
select pname,
pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN'
WAIT #5: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 217 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 8720 p1=1111838976 p2=1 p3=0
select (select pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN' and
pname = 'MBRC') *
(select to_number(value) block_size
from v$parameter
where name = 'db_block_size') /
(select pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN' and
pname = 'MREADTIM')
/1024 sys_stat_mread_speed
from dual
WAIT #16: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 838 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 2492 p1=1111838976 p2=1 p3=0


So it does look to me like the sequential reads are mostly generated by recursive SQL, although there are a few associated with the count(*) from t.

Not a clue on the thread statistics though.

10 Comments:

At 7:39 AM, Anonymous Anonymous said...

There is something a little odd about your results.

Despite doing "only a serial tablescan", you have recorded times for single AND multiblock reads - more significantly you have recorded something that I've always assumed was for parallel execution before now, the MAXTHR and SLAVETHR.

I don't know how Oracle calculates SLAVETHR - there are no obvious clues in the traces - but MAXTHR may simply be the bytes per second: which makes it look like your tablescan may have been a parallel one.

System Statistics may become an interesting discussion point for complex systems - Christo Kutrovsky (from Pythian) has pointed out that he often sees cases where the hardware actually supplies multiblock reads faster than singleblock reads - which is bad news because Oracle stops doing CPU costing calculations when mreadtim < sreadtim.

Problem is, it can be perfectly valid for a SAN to be that useless - great for massive tablescans with automatic read-ahead kicking in when you don't always want it; but completely useless for single block reads and OLTP systems in general.

Jonathan Lewis

 
At 8:53 AM, Blogger David Aldridge said...

Nuno posted an example of MREADTIM less than SREADTIM here: http://dizwell.com/forum/index.php?topic=34.0

An OLTP system with a very small MBRC, so read ahead caching is very likely. It makes me wonder whether the MBRC actually ought to be higher, to approach whatever the read size is of the i/o subsystem. But as he says he's using RBO ...

Anyway, my guess would be that there's some recursive SQL associated with starting and stopping the system stat gathering that falls between the snapshots of the file io that the process presumably takes, and that sequential reads are involved. I'll run a 10046 trace and see, and also make of that the process is serial (I can usually detect parallel operations on my test system just from HD noise -- didn't notice anything but I'll check)

Thanks Jonathan

 
At 9:54 AM, Anonymous Anonymous said...

The mreadtim < sreadtim is another whole new area where you may have to end up faking it to tell the truth. I saw the thread on HJR forum- and this led to your blog -and that kicked me off on thinking about the stuff I'm supposed to be writing about system stats.

I think the idea I chatted about with Christo was the need to multiply the MBRC and mreadtim time up (to keep them in synch) until the mreadtim was larger than the sreadtim. This way the CBO would use the figures, and the I/O rate would be described nearly truthfully in the mreadtim/MBRC.

Jonathan Lewis

 
At 10:27 AM, Blogger David Aldridge said...

I'm intrigued by the following SQL statement in the trace file:

SELECT S1.VALUE - S2.VALUE MBREADS
FROM V$SYSSTAT S1, V$SYSSTAT S2
WHERE S1.NAME = 'physical reads'
AND S2.NAME = 'physical reads direct'

This may be a knee-jerk reaction but it implies to me that direct reads are being deliberately excluded by something (and I guess that they're not directly sized by DFMBRC either?)

That's an interesting thought, to multiply up the MBRC and MREADTIM. On a system by system basis I'd be interested in exploring changes to the DFMBRC to see what effect that had on those numbers also - if read ahead caching is responsible for the low MREADTIM then an increase to the MBRC ought to be fairly "cheap", I'd think.

And it also ought to show up in a 10046 level 8 trace as alternatingly high and low elapsed times I guess, from which the read ahead cache size might usefully be estimated.

 
At 8:40 PM, Blogger Noons said...

"if read ahead caching is responsible for the low MREADTIM then an increase to the MBRC ought to be fairly "cheap", I'd think."


I'll try that tonight. 8 and 16 to see if any major diff from previous numbers from 10046.
Can't run a gather_system_stats with reasonable production load until probably Monday: this server is being tested at this stage and the next test cycle starts then.

MBRC*MREADTIME when mreadtime < sreadtime makes a LOT of sense.

 
At 1:44 AM, Anonymous Anonymous said...

David,
regarding readahead.
I have seen trace files with "db file scattered read" showing patterns of consecutive lines like:

ela= 12000 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8
ela= 12000 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8
ela= 12000 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8
ela= 800 p3 = 8

It's usually been a special feature of SANs where there HASN'T BEEN some sort of destructive interference between striping at the SAN and secondary striping at the host. (Where there has been a collision, the read times are uniformly poor to very bad - there's a hint of one such case on the HJR forum)

It can be very revealing in 10g to look at v$event_histogram for "db file scattered read" to get an overview of this.

The Direct Reads and the subtraction in the queryh against v$sysstat may be how Oracle works out the MAXTHR figure - which is making the assumption that any direct reads are PX slave processes.

Jonathan Lewis

 
At 6:23 AM, Blogger Noons said...

Nope, not much difference in pattern with 16 in dfmbrc. Two or three slow reads at the start, then they settle quickly into the fast read - at twice the time to read, as expected.

Same pattern as before. Happens a lot faster though, compared to the 8 or so requests before to get into read ahead mode. Two or three requests are enough to trigger the read ahead. My guess is because the number of blocks to be read on each dfmbrc has just doubled, so the xserve firmware "detects" a sequential read faster.

This is why I had it set low before: we have a lot of smallish tables that just happen to be around the 50K or so mark and they get a lot of FTS work on them. I don't want the firmware to go into read ahead mode where it just plain doesn't need to.

The reverse: as far as I know 9ir2 does not communicate to the xserve that it is not doing a FTS, just a range scan. So even these can be a problem sometimes, depending on disk access pattern.

This is where database level optimizations such as dfmbrc can easily conflict with firmware level optimizations. And why in the absence of any "handshaking" between the two I prefer to turn one side off and let the other do the work rather than have them trod on each other's turf. It might not be optimal in 100% of the cases, but at least it is predictable.

 
At 8:20 AM, Blogger David Aldridge said...

I wonder if this is a circumstance where you would be justified in caching those small FTS-prone tables in a seperate keep buffer pool?

Or to put it more theoretically, if you have a database where you have a lot of small tables that are subject to FTS, then in order to avoid read ahead kicking in on the smallest tables you have to set MBRC artificially small, thus disadvantaging FTS of less small tables. If those smaller tables were cached so that a physical reads requirement was rare then you could up the MBRC and get more efficient scans on the larger tables.

Bit of a niche case, admittedly.

Was there any detectable increase in the FTS performance due to the large MBRC size invoking read ahead more quickly, Noons? If you wanted to email me trace files at oracle.sponge@yahoo.com I'd be happy to work the math if you like.

 
At 8:47 AM, Blogger Noons said...

No appreciable difference on FTS or create index, for that matter, between 16 and 8. That's why I'm not so keen on letting read-ahead take over. If you look at the times on the dumps in dizwell, the dfmbrc = 16 are approximately twice the ones of dfmbrc = 8, when the read ahead has kicked in. That means I'm not really gaining that much other than the initial reads.

What worries me is I can't detect the cycling you folks talk about! That makes me think I'm either getting a very large read ahead "chunk" (something I don't necessarily want!) or the xserve is doing something very weird. Either way, I'd rather stay away from it at the moment. Given that it makes bugger all difference if I have 16 or 8 except for the start, not a problem.

Yes, I'm definitely considering putting those smaller tables undergoing fts into a keep cache. Definitely a niche case. It's not OLTP, it's not DSS, it's not DW. More a data mart, if that's still a valid expression nowadays?

 
At 10:54 AM, Blogger David Aldridge said...

Nothing wrong with Data Marts at all.

Well I'm no hardware wonk but I'd imagine that the read ahead functionality is configurable somehow, maybe by size, maybe by threshold -- no ideas other than that though.

 

Post a Comment

Links to this post:

Create a Link

<< Home