'Physical Temp Page Writes' high: <number of Pages> per command creating big result tables

While creating temporary database pages for building temporary results (for example, for joins, ORDER BY statements, and so on), the data cache was not large enough to accept all the temporary pages, displacing data on to the disk. Physical writes of temporary pages should be avoided, as these pages must be read back in for further processing of the SQL statement,. This problem is often caused by problems with application design (for example, missing indexes) or the database optimizer. Collection of large result quantities can considerably reduce performance of the entire system due to a number of negative affects (I/O, overwriting of the data cache, CPU load, and so on).

First rebuild the internal database statistics to see if the database optimizer can find a better search strategy. You can use CONTROL to update statistics, or use the UPDCOL tool from an operating system command line. As large amounts of data may need to be checked, these statements may run for a long time and should therefore not be started during active applications, if possible. This will also help to avoid any lock conflicts. (stopsap r3 - Update Statistics - startsap r3).

If this is not successful, check the statement that triggered the result creation. You can do this by starting the diagnostic monitor or an R/3 SQL trace, and then checking for any long-running statements where the EXPLAIN statement is used to check the search strategy used by the optimizer.