| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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/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
: 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_at_WWK.DE>
: To: "'LazyDBA.com Discussion'" <oracledba_at_lazydba.com>;
: <oracle-l_at_freelists.org>
: 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_at_freelists.org
: 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
: -----------------------------------------------------------------
:
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri May 28 2004 - 01:57:48 CDT
 |
 |