topic Re: Knowing how frequent a table is accessed in Operating System - HP-UX

Knowing how frequent a table is accessed

ckchoi — Fri, 05 May 2006 10:46:39 GMT

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.

Re: Knowing how frequent a table is accessed

Sandman! — Fri, 05 May 2006 14:10:14 GMT

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!

Re: Knowing how frequent a table is accessed

Mark Graham — Sat, 06 May 2006 09:51:53 GMT

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

Re: Knowing how frequent a table is accessed

Steven E. Protter — Sat, 06 May 2006 17:21:09 GMT

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

Re: Knowing how frequent a table is accessed

Brian Crabtree — Sun, 14 May 2006 11:34:19 GMT

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

Re: Knowing how frequent a table is accessed

Frank de Vries — Mon, 15 May 2006 08:34:48 GMT

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.

Re: Knowing how frequent a table is accessed

ckchoi — Mon, 15 May 2006 09:16:34 GMT

Thanks Frank.

Please send me the script as I found that it might be useful.

Re: Knowing how frequent a table is accessed

Frank de Vries — Wed, 24 May 2006 01:11:54 GMT

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;)