- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Looking for a script to monitor a single table's a...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-16-2006 09:49 AM
тАО01-16-2006 09:49 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-16-2006 03:58 PM
тАО01-16-2006 03:58 PM
Re: Looking for a script to monitor a single table's activity
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-16-2006 05:50 PM
тАО01-16-2006 05:50 PM
Re: Looking for a script to monitor a single table's activity
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 04:04 AM
тАО01-17-2006 04:04 AM
Re: Looking for a script to monitor a single table's activity
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 04:11 AM
тАО01-17-2006 04:11 AM
Re: Looking for a script to monitor a single table's activity
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 09:31 AM
тАО01-17-2006 09:31 AM
Re: Looking for a script to monitor a single table's activity
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 03:42 PM
тАО01-17-2006 03:42 PM
SolutionYou 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 06:49 PM
тАО01-17-2006 06:49 PM
Re: Looking for a script to monitor a single table's activity
Have you used Oracle AWR reports. In the section Segment Statistics, we will get object_name i,e table wise details
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-17-2006 09:04 PM
тАО01-17-2006 09:04 PM
Re: Looking for a script to monitor a single table's activity
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2006 08:06 AM
тАО01-18-2006 08:06 AM
Re: Looking for a script to monitor a single table's activity
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.