Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 4878 invoked from network); 6 Jun 2007 15:38:39 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Jun 2007 15:38:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 09AB86F020B; Wed, 6 Jun 2007 16:36:41 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09287-05; Wed, 6 Jun 2007 16:36:40 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6F6426F067E; Wed, 6 Jun 2007 16:36:40 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 06 Jun 2007 15:56:02 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 590576EF99A for ; Wed, 6 Jun 2007 15:56:02 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04298-08 for ; Wed, 6 Jun 2007 15:56:02 -0400 (EDT) Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C80B36EF920 for ; Wed, 6 Jun 2007 15:56:01 -0400 (EDT) Received: by an-out-0708.google.com with SMTP id b36so104725ana for ; Wed, 06 Jun 2007 12:57:56 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=FBsw08m9vxqotfMt3jvU+ezgIk8oCoryq4uNeKcIE6hOKEURZHbvmd8bfXlhhlwd1D8NcL0zRZtnr96olMJWfVGSw3Hvy1Q4ZpWK02a/kz0+xNMt4QKHoZBBvCTB2uRDzHtmL7vIOmjdX0P80B4gByHTQ04Qo4sk3KqAn6d2Nso= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HLEv2qU0p/0HAj7mTmJO1r/MllUbCj1RfWN5KCuvDbATE8i+1CHODIca5AItT2IksZBYZZRsLboFCW8H01VQeBqH5sFf7uWhb8b8HkN0QrMZ44w0W6Za0DegFjx+laoQxowgbrnayJPketG0/wCcz07VLIDTPwQgp3svP6v5/EY= Received: by 10.100.216.3 with SMTP id o3mr551092ang.1181159876275; Wed, 06 Jun 2007 12:57:56 -0700 (PDT) Received: by 10.100.139.10 with HTTP; Wed, 6 Jun 2007 12:57:56 -0700 (PDT) Message-ID: <357b48a90706061257q3fa3cf3dyaf777fde971a9329@mail.gmail.com> Date: Wed, 6 Jun 2007 14:57:56 -0500 From: "Ethan Post" To: tim@evdbt.com Subject: Re: Trigger SMON to dump table statistics to ALL_TAB_MODIFICATIONS Cc: oracle-l In-Reply-To: <4666F36B.2020505@evdbt.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46150_10754932.1181159876085" References: <357b48a90706060939h26b72de4reb34ba34d37ff599@mail.gmail.com> <4666F36B.2020505@evdbt.com> X-archive-position: 49531 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: post.ethan@gmail.com Precedence: normal Reply-to: post.ethan@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_46150_10754932.1181159876085 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for answer (everyone). One more thing, I think the query below might come from Dave Ensor, anyway I was using it today and started seeing some really odd results. I saw some INSERTS into a table I know was not being inserted into. I broke the query down a bit and sure enough the SQL in gv$sql was not related to the table which was being returned by the object name in the query below. This is a 10.2.0.3 database, perhaps there are some changes in the way things need to be joined in 10G with this query. If anyone sees an obvious problem let me know. Thanks, Ethan select inst_id, ctyp action , owner , name , 0 - exem executions , gets , rowp rows_processed from ( select distinct inst_id, exem, ctyp, owner, name, gets, rowp from (select s.inst_id, decode( s.command_type , 2, 'INSERT' , 3, 'SELECT' , 6, 'UPDATE' , 7, 'DELETE' , 26, 'LOCK') ctyp , o.owner , o.name name , sum(0 - s.executions) exem , sum(s.buffer_gets) gets , sum(s.rows_processed) rowp from gv$sql s , gv$object_dependency d , gv$db_object_cache o where s.command_type in (2,3,6,7,26) and d.from_address = s.address and d.to_owner = o.owner and d.to_name = o.name and o.type = 'TABLE' and s.inst_id = d.inst_id and s.inst_id = o.inst_id group by s.inst_id, s.command_type , o.owner , o.name ) ) ------=_Part_46150_10754932.1181159876085 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for answer (everyone).

One more thing, I think the query below might come from Dave Ensor, anyway I was using it today and started seeing some really odd results. I saw some INSERTS into a table I know was not being inserted into. I broke the query down a bit and sure enough the SQL in gv$sql was not related to the table which was being returned by the object name in the query below. This is a 10.2.0.3 database, perhaps there are some changes in the way things need to be joined in 10G with this query. If anyone sees an obvious problem let me know.

Thanks,
Ethan

select
    inst_id,
    ctyp action
  , owner
  , name
  , 0 - exem executions
  , gets
  , rowp rows_processed
from (
    select distinct inst_id, exem, ctyp, owner, name, gets, rowp
    from (select s.inst_id,
              decode(   s.command_type
                      , 2,  'INSERT'
                      , 3,  'SELECT'
                      , 6,  'UPDATE'
                      , 7,  'DELETE'
                      , 26, 'LOCK')   ctyp
            , o.owner
            , o.name        name
            , sum(0 - s.executions)           exem
            , sum(s.buffer_gets )              gets
            , sum(s.rows_processed)           rowp
          from
              gv$sql                s
            , gv$object_dependency  d
            , gv$db_object_cache    o
          where
                s.command_type  in (2,3,6,7,26)
            and d.from_address  = s.address
            and d.to_owner      = o.owner
            and d.to_name       = o.name
            and o.type          = 'TABLE'
            and s.inst_id       = d.inst_id
            and s.inst_id       = o.inst_id
          group by
              s.inst_id,
              s.command_type
            , o.owner
            , o.name
    )
)
------=_Part_46150_10754932.1181159876085-- -- http://www.freelists.org/webpage/oracle-l