It appears you have drifted away from the subject.
Full Table Scan are not inherently evil. In some cases FTS is most efficient way to obtain result set. It appears that you have a solution, in search of a problem.
ji li wrote:Personally I'd be looking at the AWR (or statspack at level 7) - these figures are since startup, which means they may be relatively small (although the 6,000 report on 245,888 blocks looks interesting). If tablescans are a threat then they tend to show up in CPU time or Elapsed Time or Gets or Reads; and - for the large ones - in the Segments by Scans.
One thing is that when I run the query here (Full table scan I do see a lot of objects that have numerous FTSs.
These are the top ten in the list which I thought might at least be worth looking into.
OWNER NAME NUM_ROWS K BLOCKS NBR_FTS ------------------------ -------------------- --------------- - --------------- --------------- ARADMIN SERVGRP_BOARD 5 N 8 29059 ARADMIN T2127 12644912 N 245888 6079 ARADMIN CONTROLRECORDIDS 1 N 8 3263 SYSMAN MGMT_VERSIONS 5 N 8 2872 ARADMIN CHAR_MENU 2255 N 256 2331 SYSMAN MGMT_NOTIFY_DEVICES 2 N 8 1607 ARADMIN CONTROL 1 N 8 1590 ARADMIN GROUP_CACHE 320 N 8 1498 ARADMIN T384 145 N 40 1375 ARADMINE GROUP_CACHE 13 N 8 1333 ARADMIN T2119 4925 N 256 1243 ARADMIN T2154 96 N 8 1238
ji li wrote:The 191,000 long tablescans looks interesting, as does the fact that a large fraction of them are on one table.
Okay, looking at an AWR report taken for one hour this morning during a busy period, I have:
physical reads/sec 461
index fast full scans (full) 1,175
table scans (direct read) 150
table scans (long tables) 191,060
table scans (short tables) 55,991
I'm assuming this is all relative information and needs to be compared by testing.
Is there anything specific I should be looking for to determine FTS's?
If I look at Segments by Table Scans, I see one table that makes up 76% of the table scans (141,405 times).
This table is roughly 2Mb and contains 2255 rows.
ji li wrote:The absence of "db file scattered reads" combined with a large number of table scans (long) suggests that the long tables are buffered and are showing up as CPU. You can also check the statistics about table scan blocks gotten and "prefetched" blocks (I don't remember the exact name of the statistic). You may find that the tablescan blocks is high, but the prefetch is low - for tablescans prefetched will give you an idea of how much of the tablescan turned into physical I/O.
Yes, correct. It does appear they physical reads, but if they are sequential reads, wouldn't that indicate they are of indexes?
Top foreground wait events are:
db file sequential read 22.72% of DB time
direct path read 3.20%
log file sync 2.43%
others are small and insignificant
Foreground wait class:
DB CPU 68.59% of DB time
User I/O 28.80%
I don't see the particular table in either of the SQL order by charts.Do the figures for "percentage reported" at the top of the section suggest that there might be a lot of missing SQL ?