Re: Trigger SMON to dump table statistics to ALL_TAB_MODIFICATIONS
- From: "Ethan Post" <post.ethan@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Wed, 6 Jun 2007 14:57:56 -0500
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
)
)
Other related posts:
