Skip to main content

Posts

Showing posts from September, 2015

Oracle 11g result cache

One of the production system is very slow and users are giving up. when I analyze the AWR report, there are a few things that can be improved on the top "elapsed time" queries, one of the improvement is to enable the result cache on query level by adding a hint to it. The database is running on 11.2.0.3. 1.    Change parameter result_cache_max_size from 0 to 100m 2.    Change parameter result_cache_max_result from 5 to 100 SELECT /*+ result_cache */ 1       INTO NCOUNT       FROM myview   now I can see that the query statistics:   from sqlplus:   set autotrace traceonly   select /*+ result_cache */ 1...       Execution Plan ---------------------------------------------------------- Plan hash value: 2064300782 ------------------------------------------------------------------------------------------------------------------ | Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | ----