|
I
didn't think so, but then as I was ignorant of bitand(flag,power(2,19) > 0, I thought I'd check. I
was looking for a cheap way of producing a report of full table scans including
the statements which produced them. It seems the only way to do so
is through explaining the SQL..
Ian
MacGregor
Stanford Linear
Accelerator Center
[EMAIL PROTECTED]
Ian,
There is no relation between data
block in the buffer cache and statement that actually read it.
For example,
if you are about to read 8 blocks (scattered read), and block #3 already in
the buffer cache in consistence state, your I/O request will be broken up for
2(1-2) and 5(4-8) blocks. Interesting exercise will be found segments, which
experience both type of I/O (sequential and scattered).
You may found state
of the block (CR or CUR), except segment header which always read in CUR mode,
"SELECT" read in CR, while "INSERT/UPDATE/DELETE" in CUR.
Not much help, I
know.
Alex.
----- Original Message -----
Sent: Tuesday, March 12, 2002 10:48
PM
Subject: RE: how to know ,the tables
which are accessed via FTS?
Thanks Alex! The second method is
great. However knowing what tables are undergoing a full table scan is
only a part of what's needed. One also needs to know the size of the
table; i.e., blocks up to the highwater mark, and the statement which
caused the scan in the first place. The first I can
determine. Can the second be determined without explaining the
statements in the sql cache?
Ian MacGregor
Stanford Linear Accelerator
Center
[EMAIL PROTECTED]
Gopal, Ganesh, ...
Small correction:
1. v$session_event does not have file#,
block# and blocks. They are in v$session_wait, which may be hard to
catch.
2. Absolute best methode. But field name is
"flag". not "class", you can use bitand(flag,power(2,19) > 0 as a
condition. When join to dba_objects(user_objects) one should use field
data_object_id, not object_id. Take care about clusters, as all object in
cluster share the same data_object_id. Anothe (but slower) way is join
with dba_extents(user_extents).
4. This give accurate info to the file level,
not segment level.
Alex.
----- Original Message -----
Sent: Monday, March 11, 2002 11:07
AM
Subject: RE: how to know ,the
tables which are accessed via FTS?
Ganesh,
You can find FTS in the following
methods:
1. THe existence of the 'db file scattered
read' in the V$session_event. From this we can find the file#, Block#
and #of Blocks.
Using this info we can get the segment name
from the dictionary.
2. ANy block read using Sequential Scan (Full
Table Scan) will be flagged as 0x80000 in the X$BH.CLASS. So
existence of
i0x80000 in X$.BH tells there is an FTS in the
buffer cache. So from X$BH we can get the object name from the
user_object
by joining X$.BH.OBJ
3. THen the normal SQL Trace with
TKPROF
4. If the KCFIOPBR>KCFIOPYRin the X$KCFIO
(exposed as V$FILESTAT as PHYSICAL READS and PHYSICAL BLOCK
READS)
then some of the segments in that datafile is
read by FTS. (THis may not give 100% accurate info)
5.. Any other thoughts?
Best Regards,
K Gopalakrishnan
Bangalore,
INDIA
What will that Acheive... this will not give u Tables that have
FTS Done on them.
Best Regards,
Ganesh R
Tel : +971
(4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019
USE analyze table with compute
statistcs....
Thanks and regards,
Vipin Jain
----- Original Message
-----
Sent: Monday, March 11, 2002
2:20 PM
Subject: how to know ,the
tables which are accessed via FTS?
Hi Gurus ,
One little question
how will i get to know what all
tables are accessed via full table scan ?
any help will be highly appreciated
Thnax in advance
Atul Gupta
|
