ÕâƪÎÄÕ²ûÊöÁËÈçºÎ¹ÜÀíoracle ERPµÄinterface±í

ÕâƪÎÄÕ²ûÊöÁËÈçºÎ¹ÜÀíoracle ERPµÄinterface±í
ÕâƪÎÄÕ²ûÊöÁËÈçºÎ¹ÜÀíoracle ERPµÄinterface±í
http://blog.oraclecontractors.com/?p=212
There are a number of tables used by Oracle Applications that should have no rows in them when all is running well, and if any, only a few rows that are in error. Open Interface tables are an example of such tables; they are populated with data to be interfaced in to the system, and an interface specific concurrent program is then used to validate the data and import it in to the main system tables. Once the row has been imported it is removed from the interface table, leaving only invalid rows behind.
Assuming that the system is correctly configured and these interfaces are working correctly, the table is left empty.
Often, when these tables are accessed it is to process all the rows in the table, which involves a full table scan. However over time, depending on data volumes, these tables can have a large number of blocks allocated to them that no longer contain data.
A table segment is divided in to used blocks (i.e. blocks that at some time contained data) and free blocks (i.e. blocks that have never contained data) and the point of separation between the 2 groups is called the high water mark. Whenever Oracle performs a full table scan, it scans all the used blocks up to the high watermark.
I have found that when I am working with interface tables, they often seem to take a long time to return data; this is down to there being a huge number of used blocks that no longer contain data. Certainly if open interface tables are used during the go live for data migration, then the high watermark will be very high.
Examples of such tables are:
MTL_TRANSACTIONS_INTERFACE
OE_HEADERS_IFACE_ALL
RA_INTERFACE_LINES_ALL
SQL> set timing on
SQL> select count(*)
2 from MTL_TRANSACTIONS_INTERFACE
3 /
COUNT(*)
———
20
real: 2750
Nearly 3 seconds to count 20 rows!
As these tables are interface tables used b