Horton

Re: Slow count(*) again...

Mladen Gogala <mgogala@vmsinfo.com>

View thread
From: Mladen Gogala <mgogala@vmsinfo.com>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>, "david@lang.hm" <david@lang.hm>, Craig Ringer <craig@postnewspapers.com.au>, Vitalii Tymchyshyn <tivv00@gmail.com>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: 2010-10-12T13:55:39Z
Lists: pgsql-hackers, pgsql-performance 
Jon Nelson wrote:
>
> Well, I didn't quite mean that - having no familiarity with Oracle I
> don't know what the alter system statement does, but I was talking
> specifically about the linux buffer and page cache. 
>   

Those are not utilized by Oracle.  This is a RAC instance, running on 
top of ASM, which is an Oracle volume manager, using raw devices. There 
is no file system on those disks:

SQL> select file_name from dba_data_files
  2  where tablespace_name='ADBASE_DATA';

FILE_NAME
--------------------------------------------------------------------------------
+DGDATA/stag3/datafile/adbase_data.262.727278257
+DGDATA/stag3/datafile/adbase_data.263.727278741
+DGDATA/stag3/datafile/adbase_data.264.727280145
+DGDATA/stag3/datafile/adbase_data.265.727280683

[oracle@lpo-oracle-30 ~]$ $ORA_CRS_HOME/bin/crs_stat -l
NAME=ora.STAG3.STAG31.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.STAG3.STAG32.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.STAG3.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-30.ASM1.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.LISTENER_LPO-ORACLE-30.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-31.ASM2.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.LISTENER_LPO-ORACLE-31.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31



The only way to flush cache is the aforementioned "alter system" 
command. AFAIK, Postgres doesn't have anything like that. Oracle uses 
raw devices precisely to avoid double buffering.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions