这篇文章阐述了如何管理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 by concurrent background processes, any poor performance can often be hidden from view. However a very visible effect can be seen when the table FND_FILE_TEMP has a high watermark. This table is used to hold the details of the file that needs to be read when you click on the Output or Log button on the Concurrent Request form. It is only used temporarily and the entry is deleted once used, but with a busy live system this can push the high watermark for the table. Truncating these tables when they are empty, or if there are only a small number of rows using the ALTER TABLE… MOVE command to rebuild the table, remembering to ALTER INDEX .. REBUILD any indexes, can significantly improve the performance of Open Interfaces and the opening of concurrent request output and log files. I don’t need to remind you that the DBA should do this out of hours on a production system, but you probably would be ok to do it on your development box yourself