From oracle-l-bounce@freelists.org Wed Jun 16 18:00:30 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5GN05105018 for ; Wed, 16 Jun 2004 18:00:15 -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 i5GMxs604993 for ; Wed, 16 Jun 2004 18:00:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AAF5872C2C2; Wed, 16 Jun 2004 17:44:22 -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 03202-27; Wed, 16 Jun 2004 17:44:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B876A72C3B5; Wed, 16 Jun 2004 17:44:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 16 Jun 2004 17:42:47 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AAF4672C4CA for ; Wed, 16 Jun 2004 17:42:46 -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 03048-02 for ; Wed, 16 Jun 2004 17:42:46 -0500 (EST) Received: from priv-edtnes42.telusplanet.net (outbound05.telus.net [199.185.220.224]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 37C2472C160 for ; Wed, 16 Jun 2004 17:42:46 -0500 (EST) Received: from localhost ([199.185.220.240]) by priv-edtnes42.telusplanet.net (InterMail vM.6.00.05.02 201-2115-109-103-20031105) with ESMTP id <20040616230317.CDAR2689.priv-edtnes42.telusplanet.net@localhost> for ; Wed, 16 Jun 2004 17:03:17 -0600 Received: from 57.80.136.6 ( [57.80.136.6]) as user centrex@hosting.telus.net@192.168.200.1 by webmail.hosting.telus.net with HTTP; Wed, 16 Jun 2004 16:03:17 -0700 Message-ID: <1087426997.40d0d1b5133e8@webmail.hosting.telus.net> Date: Wed, 16 Jun 2004 16:03:17 -0700 From: Wolfgang Breitling To: oracle-l@freelists.org Subject: RE: db file scattered/sequential read wait References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 57.80.136.6 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2855 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org That background job does not have the where clause " where p2 between block_id and (block_id+blocks-1)" it just reads dba_extents (same as my nightly job does). It takes a while, but it does it only once. I was referring to the fact that the OP was monitoring db_file_... wait events and was warning against frequent executions of the sql in order to determine the object name. That sql does practically the same work as the batch jobs and then throws away most of the result set. Try doing that frequently with tables/indexes in LMTs and see what happens. I still don't find it a good idea to run that job every hour during prime time, but hey, whatever turns you on. Interesting idea. Buying spare expensive cpu cycles so that you can run that sort of stuff, but then balk at the cost of disk and go with raid5 because it is cheaper. I am not insinuating that that is the case at your shop, but I am confident that there is enough of this around. Quoting Roger Xu : > We are running SAP and there is a hourly background job which > runs SQL against dba_segmen and dba_extents. I believe the > program name is SAPLSDO0. Anyway, my question is: why it never > bring down our databases? > -- regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------