Re: Trigger SMON to dump table statistics to ALL_TAB_MODIFICATIONS

From: Ethan Post <post.ethan_at_gmail.com>
Date: Wed, 6 Jun 2007 14:57:56 -0500
Message-ID: <357b48a90706061257q3fa3cf3dyaf777fde971a9329@mail.gmail.com>

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

    )
)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 06 2007 - 14:57:56 CDT