Home » RDBMS Server » Performance Tuning » most accessed (11g, solaris 9)
most accessed [message #446509] Mon, 08 March 2010 23:36 Go to next message
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 #446510 is a reply to message #446509] Mon, 08 March 2010 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
orafacjublu wrote on Mon, 08 March 2010 21:36
very urgent, has anybody come across a query that shows the most accessed user tables in you database


Why is it very urgent for me to solve this for you?
Re: most accessed [message #446513 is a reply to message #446509] Mon, 08 March 2010 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now for the past when you didn't do anything to find this: there is no way.

Please very urgently read the OraFAQ Forum Guide.

Regards
Michel

[Updated on: Mon, 08 March 2010 23:52]

Report message to a moderator

Re: most accessed [message #448204 is a reply to message #446509] Sat, 20 March 2010 10:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
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

Re: most accessed [message #448409 is a reply to message #446509] Tue, 23 March 2010 04:27 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try V$SEGMENT_STATISTICS view

HTH

[Updated on: Tue, 23 March 2010 04:28]

Report message to a moderator

Previous Topic: 11g sql tuning(urgent)
Next Topic: Swap Partition or Recreate Synonym
Goto Forum:
  


Current Time: Tue Sep 27 05:57:55 CDT 2016

Total time taken to generate the page: 0.12723 seconds