Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 67F291337BB0 for ; Tue, 12 Oct 2010 10:55:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 66779-09 for ; Tue, 12 Oct 2010 13:55:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lpo-relay-04.vmsinfo.com (lpo-relay-04.vmsinfo.com [216.169.137.115]) by mail.postgresql.org (Postfix) with ESMTP id 0C9281337BA2 for ; Tue, 12 Oct 2010 10:55:39 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.57,320,1283745600"; d="scan'208";a="26253464" Received: from unknown (HELO lpo-mailbox-01.vmsinfo.com) ([216.169.137.251]) by lpo-relay-04.vmsinfo.com with ESMTP; 12 Oct 2010 09:55:40 -0400 Received: from [10.101.43.35] (unknown [216.169.135.235]) by lpo-mailbox-01.vmsinfo.com (Postfix) with ESMTP id D76307D00B; Tue, 12 Oct 2010 09:55:39 -0400 (EDT) Message-ID: <4CB468DB.8040902@vmsinfo.com> Date: Tue, 12 Oct 2010 09:55:39 -0400 From: Mladen Gogala Organization: VMS User-Agent: Thunderbird 2.0.0.24 (X11/20100411) MIME-Version: 1.0 To: Jon Nelson CC: Mladen Gogala , "david@lang.hm" , Craig Ringer , Vitalii Tymchyshyn , "pgsql-performance@postgresql.org" Subject: Re: Slow count(*) again... References: <201010091626.18636.neil.whelchel@gmail.com> <4CB34D69.6060108@emolecules.com> <4CB4149D.8030803@gmail.com> <4CB41902.1020503@postnewspapers.com.au> <4CB4542E.5010406@vmsinfo.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/253 X-Sequence-Number: 40671 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