| most accessed [message #446509] |
Mon, 08 March 2010 23:36  |
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
|
Member |
|
|
|
very urgent, has anybody come across a query that shows the most accessed user tables in you database
|
|
|
|
|
|
|
|
| Re: most accessed [message #448204 is a reply to message #446509] |
Sat, 20 March 2010 10:11  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Define "MOST ACCESSED". Though there is no "100%" right way to get this information without doing lots of work that you don't really want to do, you can use the following to get a gross idea of what it might be.
Is the most accessed table the table from which the most rows are read?
Is the most accessed table the table to which the most writes are done?
Is the most accessed table the table that is referenced most often in sql hitting the database?
I am going with #3 here.
SQL> set linesize 60
SQL> desc v$access
Name Null? Type
----------------------------- -------- --------------------
SID NUMBER
OWNER VARCHAR2(64)
OBJECT VARCHAR2(1000)
TYPE VARCHAR2(24)
SQL> col owner format a30
SQL> col object format a30 trunc
SQL> set linesize 999
SQL> l
1* select * from v$access where rownum < 10
SQL> /
SID OWNER OBJECT TYPE
---------- ------------------------------ ------------------------------ -------------
305 KM21378 V$ACCESS CURSOR
305 ORAOP DUAL CURSOR
305 PUBLIC DUAL SYNONYM
305 PUBLIC V$ACCESS SYNONYM
305 SYS ACCESS$ TABLE
305 SYS AUD$ TABLE
305 SYS COL$ TABLE
305 SYS COLTYPE$ TABLE
305 SYS DBMSOUTPUT_LINESARRAY TYPE
9 rows selected.
Read up about the virtual view V$ACCESS. You will want to scan your system during a busy time.
1* select owner,object,count(*) from v$access where type = 'TABLE' group by owner,object order by 3
SQL> /
OWNER OBJECT COUNT(*)
------------------------------ ------------------------------ ----------
SYS FET$ 1
SYS LOB$ 1
...
SYS DUAL 5
SYS USER$ 5
SYS IND$ 5
SYS HIST_HEAD$ 6
SYS COL$ 6
SYS OBJ$ 9
119 rows selected.
According to this, my most accessed table at the moment is sys.obj$. Of course this is not a perfect answer. Notice that I said "AT THE MOMENT". There are a couple of reasons why the answer you get from this query can easily be wrong. But all-in-all this is not a bad approach if you are comfortable with what is most accessed "right now".
Kevin
[Updated on: Sat, 20 March 2010 10:12] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
