More on Oracle 11g DRCP

Due to comments from Sreekumar Seshadri and Krishna Mohan Itikarlapalli from Oracle, I've corrected my previous post on DRCP Functionality. I must own up that I've omitted reading chapter on DRCP in OCI Programmers Guide. Based on name "Database Resident Connection Pooling" I implied that it's only required to configure it at Database Server Side. In Fact it is partially true, because if you want to take advantage of Connection pooling it's needed to change client code too. There are 3 levels of benefits of DRCP and OCI enhancements in Oracle 11g:

1. Configuring DRCP at DB Side without any change in Application. In this case only processes are pooled, and every time new connection is made, new session is created. This is what I've observed in previous post when I was using SQL*plus.
2. and 3. need changes in Application in code that connect to DB. I will just quote OCI Programmers Guide:
   

   • Applications that use OCISessionGet() outside of an OCISessionPool, and specify the connection class and set purity=SELF will be able to reuse both DRCP pooled server processes and sessions. However, following an OCISessionRelease(), OCI will terminate the connection to the connection broker. On the next OCISessionGet() call, the application will reconnect to the broker. Once it reconnects, the DRCP will assign a pooled server (and session) belonging to the connection class specified. Reconnecting, however, incurs the cost of connection establishment and re-authentication. Such applications achieve better sharing of DRCP resources (processes and sessions) but do not get the benefit of caching connections to the connection broker.

   • Applications that use OCISessionPool APIs and specify the connection class and set purity=SELF make full use of the Database Resident Connection Pool functionality through reuse of both the pooled server process and the associated session and also getting the benefit of cached connections to the connection broker. Cached connections do not incur the cost of re-authentication on OCISessionGet().

So to really test DRCP I need to write some code to test it.

Cheers, Paweł

Oracle 11g DRCP: Functionality Test

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is so called Connection Broker process that is authenticating connections and assigning them to another server for processing. According to my tests Connection Broker this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł

Blog roll reorganization

Today i've reorganized my blogroll. Until now I was reading news using OraNA.info maintained by Eddie Awad. But there is too many post that are out of my interest. So i've decided to subscribe to feeds from some of them. Here is full list: Adrian Bilington, Alex Gorbachev, Alexander Kjerulf - CHO, Alexander Kornbrust, Alison Holloway, AMIS Blog, Blogging about Oracle, Carl Backstrom, Chen Shapira, Christopher Jones, Coskan Gundogar, Creating Passionate Users, Dimitri Gielis, Dominic Brooks, Don Seiler, Eddie Awad, Eric S. Emrick, Escape from Cubicle Nation, Gary Myers, H.Tonguç Yilmaz, Howard Rogers - Dizwell, Jeff Moss, Jeremy Schneider, Jonathan Lewis, Jorrit Nijssen, Kevin Closson, Laurent Schneider, Lewis Cunningham, Lutz Hartmann, Marco Gralike, Niall Litchfield, OraQA, Patch, Patrick Wolf, Penelope Trunk, Pythian Group, Robert Baillie, Sayed Jaffar Hussain, Steven Feuerstein, Steven Karam, Tanel Poder, Tim Hall, Tom Kyte, Venkatakrishnan J, YAS.

Cheers, Paweł

Operating on XMLType containing & (Ampersands)

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:

set define off
declare
  v_xml xmltype;
begin
  select xmlelement("ROWSET", 
           xmlelement("ROW", 
             xmlelement("COLA", 'lewis&me')))
    into v_xml 
    from dual;
end;
/

Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert &amp; to & you can use function dbms_xmlgen.convert with second parameter set to 1:

SQL> select dbms_xmlgen.convert('me&lewis', 1) from dual;

me&lewis

Cheers, Paweł

Alternative Syntax for Insert

Alternative Syntax for Insert

Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:

insert into table_a
    (column_1, column_2, column_3, column_4)
  values
    (value_1, value_2, value_3, value_4);

What I would like to have is something like that:

insert into table_a
   set column_1 = value_1
      ,column_2 = value_2
      ,column_3 = value_3
      ,column_4 = value_4;

With this syntax code would be easier to maintain. Especially, when you have to add or delete, or find error in insert to table with many columns. Biggest advantage of this would be close relationship between column name and value that will be set for that column. In INSERT that we know it column name and value are quite far from each other, so even for table with 10 columns it might be hard to find corresponding entries.
In PL/SQL there is an workaround to get similar solution:

declare
    v_row table_a%rowtype;
begin
    v_row.column_1 := value_1;
    v_row.column_2 := value_2;
    v_row.column_3 := value_3;
    v_row.column_4 := value_4;
    insert into table_a values v_row;
end;

But it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...

Cheers, Paweł

One Year Blogging

It's one year since I started blogging. It's time for some statistics and summary. During last year:

1. I've published 69 posts - it's 1 post every 6 days (on average)
2. 51 post are about Oracle (74%)
3. Over 29200 page views
4. 84% of traffic comes from Google
5. 35% of traffic is from US, Next on list are: UK (8%), India (7%), Germany (5%) and Poland (4%)

Top 10 of most often visited pages:

1. Oracle 11g new features
2. Restore CONTROLFILE and/or SPFILE from autobackup
3. ORA-03297 on empty datafile
4. ORA-30625 and XMLType
5. Visual SourceSafe - Invalid DOS Path
6. Empty String IS NOT NULL?
7. Oracle XMLType: exctractvalue vs. extract
8. Applying Oracle 10.2.0.3 patch
9. XMLType and Namespace problem
10. UTL_MATCH - String Similarity in Oracle

Most commented posts:

1. Visual SourceSafe - Invalid DOS Path
2. Yet another tokenizer in Oracle
3. Log Buffer #48
4. What tool do you use to make blog backup?
5. Rolling Back the DDL

And finally post that I think are valuable but not so often seen or commented:

1. Moving domain index to another tablespace
2. Monitor progress of long running processes
3. Extracting Data to Excel using only SQL*Plus
4. Rows to single string or aggregating strings
5. Flashback Table and Materialized View – not working together
6. Group Comparison Conditions
7. Search Oracle Blogs - I was before Eddie with this idea; but Eddie has more readers...
8. Binding list variable
9. Blogger Beta Backup tool
10. Moving/Adding Control file when using SPFile
11. Table that cannot be deleted

I hope that in next months I will find time and good subjects to blog about.

Cheers, Paweł