Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Looking for a script to monitor a single table's activity

SOLVED
Go to solution
TwoProc
Honored Contributor

Looking for a script to monitor a single table's activity

I'm looking for a script or a set of them which can tell me how much activity a *single* table is getting, covering a wide array of possibles, reads (sequential and scattered), latches, etc.

I'm guessing since I'm running statspack that the raw data must be in there, otherwise, how could the statspack tell me what the "top 10" across a range of categories are?!

I'd like to be able to baseline activity on a table (like in the early morning), and watch it as it progresses to the end of the day, and compare that to the next day.

I guess I could start working on building a scriptset for this myself by pawing through the statspack tablesets, but I was hoping someone has already done so, and save me a lot of work.

Any help appreciated.
We are the people our parents warned us about --Jimmy Buffett
9 REPLIES
Yogeeraj_1
Honored Contributor

Re: Looking for a script to monitor a single table's activity

hi John,

i think the latest versions of Oracle has made provision for this. Unfortunately, i cant remember the way to do it. I will investigate further and let you know.

In the meanwhile, can you check if "touch count" feature helps you?

More information in Metalink note: 136314.1

hepe this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Frank de Vries
Respected Contributor

Re: Looking for a script to monitor a single table's activity

I use this simple script to monitor
read and write activity,
run the script in intervals should show
a difference, then you know it is still
busy

select s.username, s.sid, s.machine, s.osuser, n.statistic#,
substr(s.program,1,25),
t.value "Aantal reads - writes"
from v$session s, v$sesstat t, v$statname n
where s.sid = &SID_FROM_PID
and s.sid = t.sid
and t.statistic# = n.statistic#
and n.name like '%physical%'
and not s.username is null

regards,
Look before you leap
TwoProc
Honored Contributor

Re: Looking for a script to monitor a single table's activity

Yogee and Frank,

Thanks for your replies, but unfortunately I was looking for insight at looking at a *single* tables' activity. Like you, I've mostly got scripts which span the whole database looking for things that hit high points.

In looking at the script from the Metalink article, I just assumed that I could just limit the view of the object in x$bh to a single object_id (I assumed I could correlate object_id in dba_objects to join, but am still unsure whether or not that is a legitimate assumption). Anyway, in joining that to dba_objects, and then limiting object_name to a table_name ... I got ... no returned rows, even after taking out all elements of the "having" clause.

So, it looks like that table is calculating data on certain, but not all events (as I know the tables I want to watch are very busy).

Thanks much for the research and the willingness to help though! It's always appreciated.
We are the people our parents warned us about --Jimmy Buffett
Patti Johnson
Respected Contributor

Re: Looking for a script to monitor a single table's activity

John,

Don't have a simple answer for this one. You can enable auditing for a table and set i/o counts, but that won't tell you how the table was accessed.

You can monitor indexes, using
alter index 'index_name' monitoring usage;

Then query the V$OBJECT_USAGE view, but you can only monitor one index at a time.

Stats pack collects information at the tablespace and datafile level, not the object level. So you could move this table to its own tablespace then use the stats$filestatxs table from statspack.

Patti
TwoProc
Honored Contributor

Re: Looking for a script to monitor a single table's activity

Patty,

From digging and scrounging around, I came up what you've said, put the tables to be watched in their own tablespace (and thence files), and use queries against statspack to determine what we're dealing with. Not an elegant solution for some very large tables I've got though.

Thanks much!
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor
Solution

Re: Looking for a script to monitor a single table's activity

hi again,

You can also create a trigger that will count the number of select/insert/update/delete operations on the table.

You can also read about the "workspace manager"

good luck

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
KVS Raju
Advisor

Re: Looking for a script to monitor a single table's activity

Hi John.

Have you used Oracle AWR reports. In the section Segment Statistics, we will get object_name i,e table wise details
Time and Tide wait for none
Arturo Galbiati
Esteemed Contributor

Re: Looking for a script to monitor a single table's activity

Hi John,
if you are interested to have only the customer activity on the table you could use the logminer tool to look for a table in the logs and this will give you all the custoemr activities for the table.
HTH,
Art
TwoProc
Honored Contributor

Re: Looking for a script to monitor a single table's activity

Thanks all for the input.

Oracle AWR is a great idea, but I'm on 9i. But, as this gave me the first chance to look at it, it gives me another great reason why I need to upgrade to 10g.

Yogeeraj, your suggestion gave me a chuckle, because the reason that I wanted to observe the tables is that I'm worried about the impact that putting some triggers on on them would have, so I wanted to watch them before putting in triggers... :-)

Dataminer is good example, but won't cover what I'm after.

Thanks again all for your suggestions. Always appreciated.
We are the people our parents warned us about --Jimmy Buffett