Oracle University has confirmed two 11g New Features courses just last week:
So I will definitely deliver five days of Oracle 11g New Features for Adminstrators in:
Brussels October 15th. -19th
Istanbul October 22nd. – 26th.
Looking forward to seeing you there!
=;-)
1 Comment | 
Uncategorized | 
Permalink
Posted by Lutz Hartmann
This week I am delivering RAC 10g for CERN
I am teaching a group of 16 DBAs 
who are responsible for the Oracle clusters @ CERN.
The training takes place at the in the
World Trade Center in Geneva
where Oracle Education still has some facilities.
At the end of the course Philipe,
one of these hackers surprised me with
SIX different kinds of Belgium beer 
which he had organized to convince me that there also is good beer
produced in his home country.
And I had to admitt that he was right which I did not know before!!!
So we had a little party at the end of the class…
I just love these little socializing events with my students when work is done!
This was also a good preparation for my 11g course in Brussels in three weeks.
2 Comments | 
my journeys, my special courses | 
Permalink
Posted by Lutz Hartmann
Yesterday I was invited into the IT department @ CERN.
A bunch of DBAs showed me the data center where the clusters including the tape libraries for the experiments are hosted.
This was really impressing.
I have taken a few pictures there, enjoy the slide show!
=;-)
Leave a Comment » | 
CERN | 
Permalink
Posted by Lutz Hartmann
There is a new book on RMAN by
Sam R. Alapati, Darl Kuhn, Arup Nanda.
It is titeled RMAN RECIPES and is published by APRESS.
These authors have already proven their expert competency by publishing a lot of highly interesting white papers in OTN and other books in the past. (Just search for their names in OTN!).
Here are the details of the book:
Here are the contents as announced by APRESS:
Leave a Comment » | Books worth their money | Permalink
Posted by Lutz Hartmann
Steven Feuerstein is available for questions about PL/SQL
in Oracle Technology Network
Looks like asksteven.oracle.com
=;-)
Leave a Comment » | Uncategorized | Permalink
Posted by Lutz Hartmann
Today I have added a CHAT widget to my blog.
I will be online from time to time and then you can chat with me directly on my blog.
You are also free to enter my public chatroom
Also I have added archives and recent comments into the sidebar.
Hope you like it.
=;-)
Leave a Comment » | Uncategorized | Permalink
Posted by Lutz Hartmann
This is a series about 11g New Features:
We already had:
SQL Analyzer (Sql Replay) in part I of this series ,
Database Replay in part II ,
11g New Features for the query optimizer. as part III.
One of the real highlights in Oracle 11g is the possibility to cache the results of queries as well as the return values of functions.
I want to discuss only the SQL Query Result Cache here and will post about the PL/SQL result cache later on.
Before Oracle 11g there was only one way to communicate between sessions – it was the built in package DBMS_PIPE we had to use.
Just imagine you are using a DB_LINK to read data from a remote database. Wouldn’t it be nice to have teh result for your query already in the cache over there instead of having to parse and cause physical reads from disk and all these things?
This is possible now with Oracle 11g!
As of Oracle 11gR1 it is possible to cache results of queries in a special section of the SHARED POOL called RESULT CACHE.
Let’s see how it works:
There are three different modes you can use and they are managed with the initialization parameter RESULT_CACHE_MODE. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. The default is MANUAL and it is also possible to use AUTO and FORCE. With force every result is stored in the result cache. With Auto the optimizer makes the descision what to cache.
And there are few more parameters which can be used to control caching of queries and memory utilization of the result cache of the instance.
SYSTEM @ orcl11 SQL> show parameter result
NAME TYPE VALUE
———————————— ——————————– ——————————
client_result_cache_lag big integer 3000c
lient_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 768K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
Let’s have a look at the memory utilization in the SHARED POOL:
SYSTEM @ orcl11 SQL> SELECT *
FROM v$sgastat 2
WHERE lower(name) like ‘%result%’;
POOL NAME BYTES
———— ————————– ———-
shared pool Result Cache: State Objs 2852
shared pool Result Cache: Memory Mgr 200
shared pool Result Cache: Chunk Ptrs 96
shared pool Result Cache: Bloom Fltr 2056
shared pool Result Cache: Cache Mgr 8304
Let’s also have a look at a package which you can use to get a memory utilization report for the result cache and to flush the query results :
SYSTEM @ orcl11 SQL> desc dbms_result_cache
PROCEDURE BYPASS
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
BYPASS_MODE BOOLEAN
INFUNCTION FLUSH RETURNS BOOLEAN
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
PROCEDURE FLUSH
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
RETAINMEM BOOLEAN IN
DEFAULT RETAINSTA BOOLEAN IN
DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OBJECT_ID BINARY_INTEGER IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
OBJECT_ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
ID BINARY_INTEGER IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CACHE_ID VARCHAR2 IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
CACHE_ID VARCHAR2 IN
PROCEDURE MEMORY_REPORT
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
DETAILED BOOLEAN IN DEFAULT
FUNCTION STATUS RETURNS VARCHAR2
SYSTEM @ orcl11 SQL> set serveroutput on
SYSTEM @ orcl11 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 = 1024 bytes
Maximum Cache Size = 786432 bytes (768 blocks)
Maximum Result Size = 38912 bytes (38 blocks)[Memory]
Total Memory = 13508 bytes [0.010% of the Shared Pool]…
Fixed Memory = 10656 bytes [0.008% of the Shared Pool]…
State Object Pool = 2852 bytes [0.002% of the Shared Pool]…
Cache Memory = 0 bytes (0 blocks) [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
Also there are a number of dynamic performace views which can be used to monitor the result cache:
SYSTEM @ orcl11 SQL> desc v$result_cache_objects
Name Null? Type
—————————————————————– ——– ——————————————–
ID NUMBER TYPE VARCHAR2(10)
STATUS VARCHAR2(9)
BUCKET_NO NUMBER
HASH NUMBER
NAME VARCHAR2(128)
NAMESPACE VARCHAR(5)
CREATION_TIMESTAMP DATE
CREATOR_UID NUMBER
DEPEND_COUNT NUMBER
BLOCK_COUNT NUMBER
SCN NUMBER
COLUMN_COUNT NUMBER
PIN_COUNT NUMBER
SCAN_COUNT NUMBER
ROW_COUNT NUMBER
ROW_SIZE_MAX NUMBER
ROW_SIZE_MIN NUMBER
ROW_SIZE_AVG NUMBER
BUILD_TIME NUMBER
LRU_NUMBER NUMBER
OBJECT_NO NUMBER
INVALIDATIONS NUMBER
SPACE_OVERHEAD NUMBER
SPACE_UNUSED NUMBER
CACHE_ID VARCHAR2(93)
CACHE_KEY VARCHAR2(93)
SYSTEM @ orcl11 SQL> select * from v$result_cache_objects;
no rows selected
Now let’s cache a query result:
There is a new result_cache hint which you can use to manually cache a query result in odrder to override the default setting of the parameter in the session.
(There also is a no_result_cache hint)
SYSTEM @ orcl11 SQL> SELECT /*+ RESULT_CACHE */
2 count(*) , owner
3 FROM lutz.t GROUP BY OWNER;
COUNT(*) OWNER
———- ——————————
4 LUTZ 12
TSMSYS 94256
PUBLIC 36
OUTLN 48
FLOWS_FILES 8
HR 2064
SYSTEM 32
ORACLE_OCM 1212
EXFSYS 220
DBSNMP 13136
SYSMAN 3136
XDB 6112
FLOWS_030000
SYS 117224
WMSYS 1260 15 rows selected.
# let’s take a look at the explain plan!
# I just run the statement again
SYSTEM @ orcl11 SQL> set autotrace trace explain statistics
SYSTEM @ orcl11 SQL> /
15 rows selected.
Execution Plan
———————————————————-
Plan hash value: 47235625
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|————————————————————————————————–
| 0 | SELECT STATEMENT | | 295K| 4907K| 1028 (7)| 00:00:13 |
| 1 | RESULT CACHE | 3dvq9p812snk55sb94f98ss805 | | | | |
| 2 | HASH GROUP BY | | 295K| 4907K| 1028 (7)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T | 295K| 4907K| 976 (2)| 00:00:12
|————————————————————————————————–
Result Cache Information (identified by operation id):——————————————————
1 – column-count=2; dependencies=(LUTZ.T); parameters=(nls); name=”SELECT /*+ RESULT_CACHE */count(*) , owner FROM lutz.t GROUP BY OWNER”
Note—–
- dynamic sampling used for this statement
Statistics
———————————————————-
5 recursive calls
0 db block gets
84 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed SYSTEM @ orcl11 SQL> set autotrace off
SYSTEM @ orcl11 SQL> SELECT id, type, name, scn, row_count
2* FROM v$result_cache_objects;
ID TYPE NAME SCN ROW_COUNT
———- ———- —————————— ———- ———-
0 Dependency LUTZ.T 1267310 0
1 Result SELECT /*+ RESULT_CACHE */ 1267310 15
count(*) , owner
FROM lutz.t
GROUP BY OWNER
SYSTEM @ orcl11 SQL> desc v$result_cache_statistics
Name Null? Type
—————————————————————– ——– ——————————————– ID NUMBER
NAME VARCHAR2(128)
VALUE NUMBER
SYSTEM @ orcl11 SQL> SELECT * FROM v$result_cache_statistics ;
ID NAME VALUE
———- —————————— ———-
1 Block Size (Bytes) 1024
2 Block Count Maximum 768
3 Block Count Current 32
4 Result Size Maximum (Blocks) 38
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
What I have described up to here is the server side query result cache.
If you are using an OCI application you can also configure a client side query result cache.
Again it is a set of parameters which are used to manage the OCI query result cache:
SYSTEM @ orcl11 SQL> show parameter client_result NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer
On the client side you can use an optional configuraton file to manage the client side settings in order to override the server side client settings.
8 Comments | Oracle 11g features posts | Permalink
Posted by Lutz Hartmann
Today I have travelled to Prague 
where I will deliver an 11g Change Management Seminar for Oracle University of Czech Republic tomorrow.
This is kind of the second part of the New Features Overview Seminar for 11g.
When I arrived it had just stopped raining for a while and iI took the chance to stoll around a little and have a look at the surroundings.
It is a nice city which also is very crouded with tourists. 
In a nice passage 
I found a cinema with a hanging skulpture which I would like to know more about.
Can anyone of you tell me what this is?
4 Comments | my journeys | Permalink
Posted by Lutz Hartmann
You are currently browsing the lutz hartmann as sysdba blog archives for September, 2007.
