Query result cache in 11g

In 11g its possible to store the result of the SQL in a special section of shared pool called RESULT CACHE.

As the name suggests, this cache stores results. This makes subsequent SQLs run damn fast resulting in better performance.

To me, this features sounds like Materialized view - because those also when introduced boosted the performance of queries which used to take a lot of time while computing the result. The resultant data of mview was stored in table (i.e. segment/disk). And this case the resultant data is stored in memory. Though memory not being persistent, this feature still helps in subsequent runs of the same query.

The feature is broadly governed by init.ora parameter - RESULT_CACHE_MODE - which can be changed using "alter system" or "alter session". The default value of this parameter is MANUAL. You can switch it to FORCE (not recommended).

Now after this parameter has taken effect, you can modify your SQL to see its affect.

SQL> select /*+ result_cache */ deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750

NOw run this query again - with hint
and this time with autotrace traconly explain option:

SQL> set autotrace traceonly exp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | bw8bd7rpb6h0sg1rcrwyx3rz0x | | | | |
| 2 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Here you see data being fetched from result cache.

There are some dynamic views which can give more insight.

SQL> select table_name from dict where lower(table_name) like '%result%';

TABLE_NAME
------------------------------
DBA_METHOD_RESULTS
USER_METHOD_RESULTS
ALL_METHOD_RESULTS
V$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS

TABLE_NAME
------------------------------
V$RESULT_CACHE_STATISTICS
GV$CLIENT_RESULT_CACHE_STATS
CLIENT_RESULT_CACHE_STATS$



I used V$RESULT_CACHE_OBJECTS:

1* select id,status,block_count,name from V$RESULT_CACHE_OBJECTS
SQL> /

ID STATUS BLOCK_COUNT
---------- --------- -----------
NAME
----------------------------------------------------------------------------

0 Published 1
SCOTT.EMP

1 Published 1
select /*+ result_cache */ deptno,sum(sal) from emp group by deptno


Similar to the hint "result_cache" - there is another hint - "no_result_cache"

>> no_result_cache is to be used when you have set database parameter: RESULT_CACHE_MODE to FORCE - which means oracle will try to cache the result of all queries.

Ex: select /*+ no_result_cache */ deptno,sum(sal) from emp group by deptno

To see the memory utlization - there are few dynamic views, database functions available.

SQL> set serverout on
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 3488K bytes (3488 blocks)
Maximum Result Size = 174K bytes (174 blocks)
[Memory]
Total Memory = 136340 bytes [0.064% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 131200 bytes [0.061% of the Shared Pool]
....... Overhead = 65664 bytes
....... Cache Memory = 64K bytes (64 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 35 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 34 blocks
................... SQL = 34 blocks (34 count)

PL/SQL procedure successfully completed.

There is again an init.ora parameter governing the size of max result cache size i.e result_cache_max_size

This value is also seen in dbms_result_cache.memory_report output in "Maximum Cache Size " header.

Conclusion: SQL Result cache seems to be a great feature which can immensly improve the sql performance of many applications SQLs. I wish there are no major bugs in this :)

Note: Its just the begining that I have started looking into these features. If you happen to research something which I may have missed, please add to the note.

Password is case sensitive now - Oracle 11g

Good news for people who always wanted oracle schema passwords to be case sensitive.
Oracle has done this in 11g.

Just to show an example:

C:\Documents and Settings\sachin.arora>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 28 15:02:56 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user test identified by AroRa default tablespace users;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> connect test/arora
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect test/AroRa
Connected.

With 11g, Oracle has made significant changes in password maintenence algorithm.
The password verifier is based on 160 bit SHA-1 hashing algorithm. This empowers Oracle to not only allow case sensitive passwords, but also enable the users to keep a strong password that can include special characters.

For pre-11g users, when they migrate to 11g, their password still behave password insensitive. However, i feel its good to take the advantage of this useful feature and change the password.

For applications which cant afford to change their passwords for xyzee purpose, Oracle has supplied an init.ora i.e. SEC_CASE_SENSITIVE_LOGON that can be set to true/false.
Default value of this parameter is true which means passwords should be case sensitive.

Memory parameters in 11g - MEMORY_TARGET and MEMORY_MAX_TARGET

Few days back, I was checking metalink and came across few good notes on 11g memory management.
Though we are not using 11g in our development environment, i have downloaded a copy of 11g rel1 on my windows machine.

Testing out these new paarmeters gave me an insight on how Oracle has improvised its memory management.

Oracle has introduced 2 new parameters (along with many others!) - MEMORY_TARGET and MEMORY_MAX_TARGET

Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g)

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

For instance :
If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

• SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
• SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
• SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
• PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.
When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.
This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.


I tested out few of these experiments mentioned above. Here are the results:

Test 1: When Memory_target is 1G

init.ora parameter
> memory_target=1073741824
> sga_max_size=0

SQL> startup
ORACLE instance started.

Total System Global Area 640303104 bytes
Fixed Size 1335024 bytes
Variable Size 226492688 bytes
Database Buffers 406847488 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.

>> It seems around 600M is used for SGA. The decesion of using 600 MB of SGA is of Oracle itself.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- -------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 612M
sga_target big integer 0

Test 2: When MEMORY_TARGET is 2g (with no change in any other parameter)

SQL> alter system set memory_target=2048m scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1288978432 bytes
Fixed Size 1339344 bytes
Variable Size 226492464 bytes
Database Buffers 1052770304 bytes
Redo Buffers 8376320 bytes
Database mounted.
Database opened.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 0

Test 3: When sga_max_size/sga_target=700M and memory_target=1G

SQL> startup
ORACLE instance started.

Total System Global Area 732352512 bytes
Fixed Size 1335696 bytes
Variable Size 192941680 bytes
Database Buffers 532676608 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 700M
sga_target big integer 700M
SQL>
SQL> sho parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G
shared_memory_address integer 0

This time, oracle has acknowledged the SGA_MAX_SIZE parameter.