- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Knowing how frequent a table is accessed
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
тАО05-05-2006 03:46 AM
тАО05-05-2006 03:46 AM
Knowing how frequent a table is accessed
How could I know how frequent a table is accessed? Say, collect some I/O rate of the table, or number of reads and writes from or to the table? If yes, how can I do this?
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-05-2006 07:10 AM
тАО05-05-2006 07:10 AM
Re: Knowing how frequent a table is accessed
hope it helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-06-2006 02:51 AM
тАО05-06-2006 02:51 AM
Re: Knowing how frequent a table is accessed
Using a trigger seems invasive... Is comparing the difference in row counts between time periods better or worse?
But as I said, I am not an Oracle DBA...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-06-2006 10:21 AM
тАО05-06-2006 10:21 AM
Re: Knowing how frequent a table is accessed
I have to say I think statspack is a good idea. A pl/sql trigger also seems like it could introduce performance problems. if stats is running you should be able to go back and see what the access rate is after the fact.
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-14-2006 04:34 AM
тАО05-14-2006 04:34 AM
Re: Knowing how frequent a table is accessed
Hope this helps,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-15-2006 01:34 AM
тАО05-15-2006 01:34 AM
Re: Knowing how frequent a table is accessed
as I see none where yet allocated.
You can query the sql_area of the shared_pool with object_id
and find how many reads/write an object has
endured.
You can do this one time, ad hoc manually
, but then you are dependent on info that is still available in memory.
You can automate this query to capture on
regular basis or use a Tool. There are many
in the market.
Let me know if you are still interested,
i will give you script and tools.
If I don't hear from you I assume you
have found a way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-15-2006 02:16 AM
тАО05-15-2006 02:16 AM
Re: Knowing how frequent a table is accessed
Please send me the script as I found that it might be useful.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-23-2006 06:11 PM
тАО05-23-2006 06:11 PM
Re: Knowing how frequent a table is accessed
Sorry for the delay :)
Here is the script to check your
top sessions
select s.sid
, s.status
, s.osuser
, s.username
, s.machine
, q.sql_text
, q.first_load_time
, q.executions
, q.disk_reads
, q.buffer_gets
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and disk_reads > 2000
and buffer_gets > 2000
You can increase the 2000 to a higher
value to suit your needs.
If you know the unix process and check
what it is doing in Oracle you can use this
first:
select * from v$sqlarea sql, v$session ses
where sql.address = ses.sql_address
and ses.sid = ( select s.sid from v$session s , v$process p
where p.addr = s.paddr
and type = 'USER'
and p.spid = &unix_process )
If you have more questions, let me know,
there is more where this came from !!
Keep cooking;)