-
Full Table Scans
Hi!
Can anyone advise how I can find out if full table scans are occuring in the database? What stats should be collected? What event should I be looking for?
Thanks.
-
select *
from v$sysstat
where name = 'table scans (long tables)';
-
PHP Code:
SQL> set time on
14:38:16 SQL> L
1 select owner,
2 object_name,
3 object_type
4 from dba_objects OB,
5 ( select /*+ NO_MERGE */
6 distinct obj
7 from x$bh
8 where bitand(flag,524288) > 0
9 ) BH
10 where Ob.data_object_id = BH.obj and
11 ob.owner != 'SYS'
12*
14:38:21 SQL> /
OWNER OBJECT_NAME OBJECT_TYPE
------------ --------------------------------- ------------
SYSTEM AQ$_QUEUES TABLE
SQL> set time on
Now I counted rows from a table. This table has no index. So Oracle does FTS.
14:38:40 SQL> select count(*) from test_table;
COUNT(*)
----------
23376
14:38:50 SQL>
14:38:23 SQL> @FTS
OWNER OBJECT_NAME OBJECT_TYPE
------------ --------------------------------- ------------
SYSTEM AQ$_QUEUES TABLE
TAMIL TEST_TABLE TABLE
14:38:59 SQL>
Tamil
Last edited by tamilselvan; 02-07-2005 at 04:38 PM.
-
This will be better than the previous one.
PHP Code:
select owner,
object_name,
object_type
from dba_objects OB,
( select obj, RN
from (select /*+ NO_MERGE */
obj ,
row_number() over (partition by obj order by obj) RN
from x$bh
where standard.bitand(flag,524288) > 0
)
where RN = 1
) BH
where Ob.data_object_id = BH.obj and
ob.owner != 'SYS'
Tamil
-
Thanks Tamil.
Thats helpful. But how can I find out how often FTS are occuring? I basically don't want one-off FTS, but need to know if the same table is involved over and over again.
-
What is your oracle release?
Tamil
-
DB is 9.2.0.5 64-bit on HP-UX 11.11
-
There's a system view called v$segment_statistics that will probably tell you all you need to know.
-
In 9i/10g, you can query from v$sql_plan that tells you FTS objects.
Code:
select object_name , options
from v$sql_plan
where upper(options) like '%FULL%'
Tamil
-
A nice document about the "bad" full table scans:
http://www.ioug.org/tech/IOUGinDefense.pdf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|