Oracle 性能调整－内存篇.ppt

* Tuning Goal In an OLTP environment, reparsing should be minimized. If an application makes a parse call for a SQL statement and the parsed representation of the statement does not already exist in a shared SQL area in the library cache, the Oracle server parses the statement and allocates a shared SQL area. Ensure that SQL statements can share a shared SQL area whenever possible, by using: As much generic code as possible Bind variables rather than constants If an application makes an execute call for a SQL statement, and the shared SQL area containing the parsed representation of the statement has been deallocated from the library cache to make room for another statement, the Oracle server implicitly reparses the statement, allocates a new shared SQL area for it, and executes it. Reduce library cache misses on execution calls by allocating more memory to the library cache. If a schema object is referenced in a SQL statement and that object is later modified in any way, the SQL statement held in memory is rendered invalid. * Second Tuning Goal Avoid memory fragmentation by: Ensuring availability of contiguous space for large memory requirements through the allocation of reserved space in the shared pool area Pinning frequently required large objects such as SQL and PL/SQL areas in memory, instead of aging them out with the normal LRU mechanism Using small PL/SQL packaged functions instead of large anonymous blocks. Configuring the large pool for Oracle Shared Server connections. Measuring session memory use by the shared server processes in Oracle Shared Server connections * Testing Your Applications For an existing application, you can set up a test and use the dynamic views to find out how much memory is used. Begin by setting SHARED_POOL_SIZE to a very large value (at the expense of other structures, if necessary), then run the application. Computation of Shareable Memory Used For stored objects such as packages and views, use the following query: SQL SELEC