Tom, the second link detailed above viz.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/memory.htmGBBIACC

gives errors when clicked. The errors says:



We're sorry, the page you requested was not found.

We have recorded this error (404) to help us fix the problem.
You may wish to try again using one of the tools below.

Back to Previous Page
Site Map
Product Index
To search for your page, try our Search function.

Warm Regards,

that seems to me one of the most exciting features of 11g !

So you mean, in order to check for changes, we could re-query using the Client Query Result Cache and then check if
Invalidation Count in CLIENT_RESULT_CACHE_STATS$
was incremented ?

Tom, I was checking the link [10g Change notification] and it was very interesting. One question that was not clear from the docs is, Can I have my change notification sent to another database [pl/sql procedure] instead of a web-client?
[I mean database 2 will be waiting on change notification from database 1 and then as soon as it receives notification, query database 1 with required rows..]

If so, can you provide a link with example?
Thanks

Tom, thanks for your response. I know you are trying to tell me about Replication on other questions but that's a different scenario for me. I am trying 2/3 things at a time and we are trying different technologies for different things. Replication [Streams] was for replicating a database. I understood that. But in this scenario we are implementing a 3rd party tool and we need to send data [like jobs/customer resumes etc..] to that instantly to get results back to users.
So, we were thinking to implement this in either .NET client application or a windows/web service. But since we get source data from 3-4 different applications , they all need to accomadate this web service.
or another apporach was to consider everything in database and send data to that tool instantly from database. What i read about change notification was talking mostly about client application and database.I was wondering if that's possible without client..Not sure if I am making any sense :-)

Thanks again,

I think instead of explaining all the details ,If I rephrase my question, that would be better.
Is it possible to trigger an event when new rows are inserted in a database without using Triggers and Materialized views? If so, how? I was thinking Change notification is the only other way, but I might be wrong.
Thanks,

Hi Tom,

Could you please someone advise whether the query change notification is supported by oracle 11g release1(11.1.0.7.0)?

SQL> declare
  2  reginfo cq_notification$_reg_info;
  3  v_cursor sys_refcursor;
  4  regid number;
  5  begin
  6  reginfo := cq_notification$_reg_info('testbks',DBMS_CQ_NOTIFICATION.QOS_QUERY,0,0,0);
  7  regid := dbms_cq_notification.new_reg_start(reginfo);
  8  open v_cursor for select name from kk where gid=2;
  9  close v_cursor;
 10  dbms_cq_notification.reg_end;
 11  end;
 12
 13  /
declare
*
ERROR at line 1:
ORA-29976: Unsupported query for registration in guaranteed mode
ORA-06512: at line 8


Oracle documentation cliams that it's supported from oracle 11g release 1 onwards.. here is the link

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_cqnotif.htm

Please let me know if I am missing something here?

Regards,
Richard

ops$tkyte%ORA11GR2> drop table kk;

Table dropped.

ops$tkyte%ORA11GR2> create table kk ( gid number primary key, name varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> declare
  2    reginfo cq_notification$_reg_info;
  3    v_cursor sys_refcursor;
  4    regid number;
  5  begin
  6    reginfo := cq_notification$_reg_info('testbks',DBMS_CQ_NOTIFICATION.QOS_QUERY,0,0,0);
  7    regid := dbms_cq_notification.new_reg_start(reginfo);
  8    open v_cursor for select name from kk where gid=2;
  9    close v_cursor;
 10    dbms_cq_notification.reg_end;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Thanks Tom for your quick response. I am still getting the same error message. Here are the details. Please advise.

RCM_DB> create table kk ( gid number primary key, name varchar2(20) ) ;

Table created.

RCM_DB> declare
2 reginfo cq_notification$_reg_info;
3 v_cursor sys_refcursor;
4 regid number;
5 begin
6 reginfo := cq_notification$_reg_info('testbks',DBMS_CQ_NOTIFICATION.QOS_QUERY,0,0,0);
7 regid := dbms_cq_notification.new_reg_start(reginfo);
8 open v_cursor for select name from kk where gid=2;
9 close v_cursor;
10 dbms_cq_notification.reg_end;
11 end;
12 /
declare
*
ERROR at line 1:
ORA-29976: Unsupported query for registration in guaranteed mode
ORA-06512: at line 8


db parameters

RCM_DB> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
_job_queue_interval integer 1
job_queue_processes integer 1000
dml_locks integer 1472


Version : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

Hello Tom,

I am doing test of Client side result set cache on oracle 11 g rel2 ,O/S- RHEL

i am doing test as below on 2 Oracle 11g rel2 test servers on RHEL

one oracle 11g rel2 instance is used as server1,from other oracle 11g rel2 server2, i am running queries connecting to server1 through sqlplus,listener(tnsnames )

i run query few times without any client cache settings then
i have set init.ora paras for oracle 11g rel2 for client result cache,cache size/cache lag
i run the same query few times
in both the cases after each run i am noting statistics
select * from V$RESULT_CACHE_OBJECTS;
select * from V$CLIENT_RESULT_CACHE_STATS;
select * from gv$cpool_conn_info;
also autotrace,plan etc
but after using cache this views are getting poplulated ,autotrace plan shows cache is used
but i cant see any improvement in performance after using client cache like speed,reduce network trip, etc
in fact without using cache/with no hint ,query executes faster

let me know if i am missing something,i believe sqlplus internally using OCI
or i can see the benefits of this feature through some application code etc

Thanks in advance

Hello Tom,

As asked i am giving details as below
(though physical read-i/o seems to reduced with cache
but no change in network trip or execution time as expected)

Thanks

Query
select LIERID,type
,created,WTCD ,PID,reg,OWN,ADT,IPTDT,INSDT,MDLYR,SNUMVFLG,DFLG,CATDT,RP,count(anum)
from test.test1 group by
LIERID,type
,created,WTCD ,PID,reg,OWN,ADT,IPTDT,INSDT,MDLYR,SNUMVFLG,DFLG,CATDT,RP;



with cache
select /*+ RESULT_CACHE */ LIERID,type
,created,WTCD ,PID,reg,OWN,ADT,IPTDT,INSDT,MDLYR,SNUMVFLG,DFLG,CATDT,RP,count(anum)
from test.test1 group by
LIERID,type
,created,WTCD ,PID,reg,OWN,ADT,IPTDT,INSDT,MDLYR,SNUMVFLG,DFLG,CATDT,RP;





without cache
-------------------
Elapsed: 00:00:11.42

Execution Plan
----------------------------------------------------------
Plan hash value: 2701798090

--------------------------------------------------------------------------------
------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 416K| 16M| | 12426 (2)| 00:0
2:30 |

| 1 | HASH GROUP BY | | 416K| 16M| 60M| 12426 (2)| 00:0
2:30 |

| 2 | TABLE ACCESS FULL| TEST1 | 416K| 16M| | 7917 (2)| 00:0
1:36 |

--------------------------------------------------------------------------------
------



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35633 consistent gets
35630 physical reads
0 redo size
246582 bytes sent via SQL*Net to client
6401 bytes received via SQL*Net from client
295 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4407 rows processed















With Cache
=========

Multiple runs time
Elapsed: 00:00:12.21
Elapsed: 00:00:06.52
Elapsed: 00:00:08.62
Elapsed: 00:00:13.73
Elapsed: 00:00:11.97








Execution Plan
----------------------------------------------------------
Plan hash value: 2701798090

--------------------------------------------------------------------------------
--------------------------

| Id | Operation | Name | Rows | Bytes |TempSp
c| Cost (%CPU)| Time |

--------------------------------------------------------------------------------
--------------------------

| 0 | SELECT STATEMENT | | 416K| 16M|
| 12426 (2)| 00:02:30 |

| 1 | RESULT CACHE | 849a9vv2hqjky7n8ktx2u62552 | | |
| | |

| 2 | HASH GROUP BY | | 416K| 16M| 60
M| 12426 (2)| 00:02:30 |

| 3 | TABLE ACCESS FULL| TEST1 | 416K| 16M|
| 7917 (2)| 00:01:36 |

--------------------------------------------------------------------------------
--------------------------


Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=16; dependencies=(SIEBEL.TEST1); parameters=(nls); name="s
elect /*+ RESULT_CACHE */ LIERID,type
,created,WTCD ,PID,reg,OWN,ADT,IPTDT,INSDT,MDLYR,SNUMVFLG,DFLG,CATDT,RP"



Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
246599 bytes sent via SQL*Net to client
6401 bytes received via SQL*Net from client
295 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4407 rows processed

Hello Tom,

Uploaded test/output result as above

thanks