Optimizing Instance Memory
In Oracle Database 11g, Oracle has introduced an exciting new result caching feature. Oracle can now cache the results of both SQL queries and PL/SQL functions in the shared pool component of Oracle's memory.
Oracle has different minimum permissible settings for the MEMORY_TARGET parameter, depending on the operating system. If you try to set this parameter below its minimum allowable value, the database will issue an error.
In Oracle Database 11g, Oracle has introduced an exciting new result caching feature. Oracle can now cache the results of both SQL queries and PL/SQL functions in the shared pool component of Oracle's memory.
Oracle has different minimum permissible settings for the MEMORY_TARGET parameter, depending on the operating system. If you try to set this parameter below its minimum allowable value, the database will issue an error.
Configuring the Server Result Cache
If you set the RESULT_CACHE_MODE initialization parameter to FORCE, the database caches all query results unless you specify the /*+ NO_RESULT_CACHE */ hint to exclude a query's results from the cache. You can set this parameter at the system level or at the session level, as shown here:
SQL> alter session set result_cache_mode=force;
You can remove cached results from the server result cache by using the FLUSH procedure from the DBMS_RESULT_CACHE package, as shown here:
SQL> execute dbms_result_cache.flush
* If you're using automatic memory management by setting the MEMORY_TARGET parameter, Oracle allocates 0.25% of the MEMORY_TARGET parameter's value to the server result cache.
* If you're using automatic shared memory management with the SGA_TARGET parameter, the allocation is 0.5% of the SGA_TARGET parameter.
* If you're using manual memory management by setting the SHARED_POOL_SIZE parameter, the allocation is 1% of the SHARED_POOL_SIZE parameter.
The following objects or functions, when present in a query, make that query ineligible for caching:
* CURRVAL and NEXTVAL pseudo columns
* The CURRENT_DATE, CURRENT_TIMESTAMP, USERENV_CONTEXT, SYS_CONTEXT (with non-constant variables), SYSDATE, and SYS_TIMESTAMP
* Temporary tables
* Tables owned by SYS and SYSTEM
The big advantage of using a client-side query cache is that it conserves your server memory usage and helps you scale up your applications to serve more processes. The client query cache is organized on a per-process basis rather than a per-session basis.
If you set the RESULT_CACHE_MODE initialization parameter to FORCE, the database caches all query results unless you specify the /*+ NO_RESULT_CACHE */ hint to exclude a query's results from the cache. You can set this parameter at the system level or at the session level, as shown here:
SQL> alter session set result_cache_mode=force;
You can remove cached results from the server result cache by using the FLUSH procedure from the DBMS_RESULT_CACHE package, as shown here:
SQL> execute dbms_result_cache.flush
* If you're using automatic memory management by setting the MEMORY_TARGET parameter, Oracle allocates 0.25% of the MEMORY_TARGET parameter's value to the server result cache.
* If you're using automatic shared memory management with the SGA_TARGET parameter, the allocation is 0.5% of the SGA_TARGET parameter.
* If you're using manual memory management by setting the SHARED_POOL_SIZE parameter, the allocation is 1% of the SHARED_POOL_SIZE parameter.
The following objects or functions, when present in a query, make that query ineligible for caching:
* CURRVAL and NEXTVAL pseudo columns
* The CURRENT_DATE, CURRENT_TIMESTAMP, USERENV_CONTEXT, SYS_CONTEXT (with non-constant variables), SYSDATE, and SYS_TIMESTAMP
* Temporary tables
* Tables owned by SYS and SYSTEM
The big advantage of using a client-side query cache is that it conserves your server memory usage and helps you scale up your applications to serve more processes. The client query cache is organized on a per-process basis rather than a per-session basis.
Caching PLSQL Function Results
Even the Results of the PLSQL Functions can be cached in Oracle 11G stating the same parameter values. But the Function Declaration should contain a RESULT_CACHE Keyword for the Results to be cached.
Oracle Flash Cache
Oracle Database Smart Flash Cache, a feature of the Oracle Database 11.2 release, is included as part of the enterprise edition of the database server. Flash Cache takes advantage of the I/O speed of flash-based devices, which perform much better than disk-based storage. For example, small disk-based reads offer a 4-millisecond response, whereas a flash-based device takes only 0.4 milliseconds to perform the same read.
ASH - Active Session History
Retrieving ASH information is necessary if you need to get session information more current than you can retrieve from the AWR report.Again, AWR information is generated only hourly by default. ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.
You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active.The MMON background process, which manages the AWR hourly snapshots, also flushes ASH information to the historical view at the same time.If there is heavy activity on the database, and the buffer fills between the hourly AWR snapshots, the MMNL background process will wake up and flush the ASH data to the historical view.
Even the Results of the PLSQL Functions can be cached in Oracle 11G stating the same parameter values. But the Function Declaration should contain a RESULT_CACHE Keyword for the Results to be cached.
Oracle Flash Cache
Oracle Database Smart Flash Cache, a feature of the Oracle Database 11.2 release, is included as part of the enterprise edition of the database server. Flash Cache takes advantage of the I/O speed of flash-based devices, which perform much better than disk-based storage. For example, small disk-based reads offer a 4-millisecond response, whereas a flash-based device takes only 0.4 milliseconds to perform the same read.
ASH - Active Session History
Retrieving ASH information is necessary if you need to get session information more current than you can retrieve from the AWR report.Again, AWR information is generated only hourly by default. ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.
You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active.The MMON background process, which manages the AWR hourly snapshots, also flushes ASH information to the historical view at the same time.If there is heavy activity on the database, and the buffer fills between the hourly AWR snapshots, the MMNL background process will wake up and flush the ASH data to the historical view.
No comments:
Post a Comment