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

Knowing how frequent a table is accessed

ckchoi
Advisor

Knowing how frequent a table is accessed

Hi,

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.
7 REPLIES
Sandman!
Honored Contributor

Re: Knowing how frequent a table is accessed

What kind of database are we talking about? An RDBMS like Oracle is handled better by creating a PL/SQL trigger, on the table in question, which is fired when ever the table is accessed or modified. The trigger can populate another table created and maintained specifically for maintaining I/O stats on the user and/or application table(s).

hope it helps!
Mark Graham
Advisor

Re: Knowing how frequent a table is accessed

I am not an Oracle DBA by trade but quite a bit of performance related information can be gained from comparing output from Statspak and Glance and/or Performance Manager/PerfView...

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...

No Bucks, No Buck Rogers...
Steven E. Protter
Exalted Contributor

Re: Knowing how frequent a table is accessed

Shalom,

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Brian Crabtree
Honored Contributor

Re: Knowing how frequent a table is accessed

You do have an option, at least for the updates/inserts/deletes. You can activate table monitoring (alter table table_name monitoring), and then look at the dba_tab_modifications view. This keeps track of the updates/inserts/deletes from the last generated statistics (so you will want to analyze the table when you turn this on). Also, the numbers will get reset with the next statistic run, so if you really want the data, you will probably need to automate something that will store it somewhere.

Hope this helps,

Brian
Frank de Vries
Respected Contributor

Re: Knowing how frequent a table is accessed

I don't know if I still can earn points,
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.

Look before you leap
ckchoi
Advisor

Re: Knowing how frequent a table is accessed

Thanks Frank.

Please send me the script as I found that it might be useful.
Frank de Vries
Respected Contributor

Re: Knowing how frequent a table is accessed

Hi,

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;)
Look before you leap