Re: "Hot" tables in Schema
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 May 2004 08:00:41 +0100
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 //www.freelists.org/archives/oracle-l/
: FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
