From oracle-l-bounce@freelists.org Mon Jul 12 10:10:57 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6CFAWH12195 for ; Mon, 12 Jul 2004 10:10:42 -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 i6CFAK612141 for ; Mon, 12 Jul 2004 10:10:31 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1716672CF27; Mon, 12 Jul 2004 09:50:33 -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 18956-45; Mon, 12 Jul 2004 09:50:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 32DD372CFCD; Mon, 12 Jul 2004 09:50:32 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 12 Jul 2004 09:48:58 -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 CDD1072CF3B for ; Mon, 12 Jul 2004 09:48:57 -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 18890-08 for ; Mon, 12 Jul 2004 09:48:57 -0500 (EST) Received: from www.hotsos.com (hotsos.com [209.120.206.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6D6E572CF44 for ; Mon, 12 Jul 2004 09:48:57 -0500 (EST) Received: from CVMLAP02 (66.169.163.33.ts46v-12.otne2.ftwrth.tx.charter.com [66.169.163.33]) (authenticated (0 bits)) by www.hotsos.com (8.12.11/8.11.0) with ESMTP id i6CFDJ3D016910; Mon, 12 Jul 2004 10:13:19 -0500 From: "Cary Millsap" To: Subject: RE: Hot indexes Date: Mon, 12 Jul 2004 10:11:46 -0500 Message-ID: <002201c46822$8faf3ea0$6701a8c0@CVMLAP02> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: <0186754BC82DD511B5C600B0D0AAC4D607B009B1@EXCHMN3> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4868 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cary.millsap@hotsos.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org That's what we do with our Laredo product: capture SQL from various = sources, run EXPLAIN PLAN, store the results in a database, and then use a smart comparison tool to do reporting. It works very well. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San = Francisco - SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 = Hartford - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of DENNIS WILLIAMS Sent: Monday, July 12, 2004 7:18 AM To: 'oracle-l@freelists.org' Subject: RE: Hot indexes When this question was asked earlier, one suggestion was to capture =3D samples of the SQL in v$sql, then run it through EXPLAIN PLAN to find SQL =3D statements that are using indexes in this tablespace. I don't recall the success = =3D or failure of this method being posted. Dennis Williams DBA Lifetouch, Inc. dwilliams@lifetouch.com=3D20 I said it "looked" clear - Riddick -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mogens N=3DF8rgaard Sent: Sunday, July 11, 2004 11:55 PM To: oracle-l@freelists.org Subject: Re: Hot indexes If it really is a problem (somebody complaining, devices being=3D20 overloaded, whatever), then you would need to track which = user(s)/apps=3D20 issued the LIO calls to the datablock adresses of this tablespace. Tracking of individual LIO calls including the file number, block =3D number=3D20 and number of blocks is externalized in v$session_wait and in 10046=3D20 trace files (excluding exotic direct memory access methods and probably = =3D some decent 3rd party tools). So putting on 10046 level 8 trace on your whole instance will show = it,=3D20 but will require at least 42 GB of storage for the trace files and = 42=3D20 days to interpret the results afterwards :). Mogens solbeach@cox.net wrote: > I can see that one tablespace which contains only a few > indexes is doing many, many reads. > How do I track back to the SQL & user(s) behind this activity? ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------