Re: "Hot" tables in Schema

The usual caveat applies with nice friendly v$ --

Don't use them as the basis for regular inspection
until you're sure they've been checked as low-cost

In the case of v$segment_statistics, it is better to
get deltas by checking v$segstat - then join to
other tables for names after you've got the important
numbers out.

Execution plan for select * from v$segment_statistics:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           FIXED TABLE (FULL) OF 'X$KSOLSFTS'
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   7    6             INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
   8    3         TABLE ACCESS (CLUSTER) OF 'TS$'
   9    8           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  10    2       TABLE ACCESS (CLUSTER) OF 'USER$'
  11   10         INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  12    1     TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
  13   12       INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "Don Granaman" <granaman@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>; "'LazyDBA.com Discussion'"
<oracledba@xxxxxxxxxxx>
Sent: Thursday, May 27, 2004 8:50 PM
Subject: Re: "Hot" tables in Schema


: In 9i, you can get a lot from v$segment_statistics (or
gv$segment_statistics
: with RAC) by doing deltas between runs.
:
: Don Granaman
: SQL*Plus-addicted OraSaurus
:
: ----- Original Message ----- 
: From: "Daiminger, Helmut" <HELMUT.DAIMINGER@xxxxxx>
: To: "'LazyDBA.com Discussion'" <oracledba@xxxxxxxxxxx>;
: <oracle-l@xxxxxxxxxxxxx>
: Sent: Wednesday, May 26, 2004 6:36 AM
: Subject: "Hot" tables in Schema
:
:
: > Hi!
: >
: > Does anybody out there know how to get the hot tables in a specific
schema
: > in a specific period of time? I.e. find the tables that get hit the most
: > during a specific period of time?
:
:
: ----------------------------------------------------------------
: Please see the official ORACLE-L FAQ: http://www.orafaq.com
: ----------------------------------------------------------------
: To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
: put 'unsubscribe' in the subject line.
: --
: Archives are at http://www.freelists.org/archives/oracle-l/
: FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
: -----------------------------------------------------------------
:


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: