From oracle-l-bounce@freelists.org Fri Oct 22 17:26:40 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9MMQda19988 for ; Fri, 22 Oct 2004 17:26:39 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9MMQdI19981 for ; Fri, 22 Oct 2004 17:26:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3DE0272D68A; Fri, 22 Oct 2004 17:32:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06323-49; Fri, 22 Oct 2004 17:32:49 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8BA6672CF73; Fri, 22 Oct 2004 17:32:48 -0500 (EST) Message-ID: From: Smiley John - IL To: "'jreyes@dazasoftware.com'" , oracle-l@freelists.org Subject: RE: Is important to GATHER_SYSTEM_STATS??? Date: Fri, 22 Oct 2004 17:31:02 -0500 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 11473 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: SMILEYJ@tusc.com Precedence: normal Reply-To: SMILEYJ@tusc.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org System stats give the CBO information about how fast your CPUs are, how long single and multi-block reads take, and what the disk throughput is. It can make a very big difference in the execution plans chosen (usually for the better). In a recent case, I was able to smooth the transition from nested loops to hash joins by setting system stats to appropriate values. Prior to this, the CBO would switch from NL to hash joins much too early, resulting in a big discontinuity in the response time curve for certain queries as the number of rows processed increased. Be sure to gather the stats during a load that represents the target state of your system. You can also set them manually to anything you like. John Smiley -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Juan Carlos Reyes Pacheco Sent: Friday, October 22, 2004 4:11 PM To: oracle-l@freelists.org Subject: Is important to GATHER_SYSTEM_STATS??? Hi, 1) does you have some experience about using it, and its impact in CBO. 2) Do you get some benefit gathering it as normal gathering statistics process, even when your database always is dss or always oltp Thank you Juan Carlos Reyes Pacheco OCP -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l