Service wlansvc failed to start
03 Oktober 08 05:40 | Urs Meier - Blog on IT
Trying to install my mobile card on a new Windows Server 2008 ended with the message "service wlansvc failed to start. Check if you have the privilege...".

The point is, that the service was not installed.

Solution:
Administrative Tools > Server Manager > Features > Add Features > Wireless LAN Service
Using Oracle 11g client result_cache with ODP.NET
24 April 08 02:10 | Urs Meier - Blog on IT
With Oracle 11g OCI clients can store result sets on the client.
I was wondering how ODP.NET will use this feature and if there are any coding constraints.

Required setup:
The feature must be enabled server side by setting the init.ora parameter client_result_cache_size (restart required) to the amout of bytes that a user process can use.
Several sqlnet.ora parameters allow the client to customize the size of the cache. They are optional and I didnt bother testing them.
The parameter result_cache_mode specifies whether statements should always use the cache (force) or whether the SQL statements must be hinted with /*+ result_cache */ (manual which is the default).


Cache consistency
Since the the OCI client creates the cache on the client side, there might be an iconsistency between the server and the client cache, if data gets changed in the database. OCI provides two refresh possibilites.
1. The client cache is only used for client_result_cache_lag amount of time.
2. Every database roundtrip (e.g. for non cached statements) will return information about the cache validity.

Application coding
Application programmers must therefore add the /*+ result_cache */ hint (assuming manual mode). The OCI guide also describes a few other constraints in chapter 10, more OCI advanced topics.
One constraint is probably not obvious. In order to use the cache, the client statement must once loop through all the rows until end of fetch (-1403, no data found).

So far, so good.

My typical application server code looks like this (of course not with hard coded connection strings and variables, but typically with a using constraint for the connection and using a connection pool).


decimal sal = 0;
using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand("SELECT /*+ result_cache */ sal " +
" FROM EMP " +
" WHERE deptno = 10", connection);
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sal += reader.GetDecimal(0);
}
reader.Close();
connection.Close();
}
return sal;

This statement fullfills all the requirements to use the cache. But what about my using construct with the connection pooling mechanism? Since I am opening a (pooled) connection all the time, I will get a database roundtrip. This roundtrip will also bring back cache invalidation information.

Assume, that I change after every call the database table emp. The following will happen.

1. execution and creation of cache
==> Database=a, Application=a

Database gets changed to b

2. execution brings back invalidation information
local cache is used for the last time (since the database values changed)
==> Database=b, Application=a

Database gets changed to c

3. execution cannot use the local cache since it has been invalidated by the previous step, a database roundtrip happens, and again a creation of local cache
==> Database=c, Application=c

Database gets changed to d

Like 2
==> Database=d, Application=c

Database gets changed to e
Like 3
==> Database=e, Application=e


Clearly, this coding style produces roundtrips which are contradictory the to goal of the local cache. With that database roundtrip I could use the result_cache on the server side and there is little use of the local cache.

So, you will get the best performance if the the connection and command remain open. We just use another reader object.

OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sal += reader.GetDecimal(0);
}
reader.Close();


If this is then the only SQL activity in your application, the cache will be used for the time of the client_result_cache_lag setting.
Abgelegt unter: , ,
Oracle Query Notification revisited
23 April 08 11:27 | Urs Meier - Blog on IT
A few month ago I compared the .NET query notification possibilites between SQL Server and Oracle. At that time Oracle could only dectect changes at table level; not at row level.

With the new release of ODP.NET 11.1 and Oracle 11g Oracle enhances the Query Notification support. Change detection can be Query-based or Object-based (as with the prior version).

ODP.NET uses a best effort mode, which means that Query-based notification is tried first and requires a 11.1 database with enabled automatic undo management (default). The main restriction is the SELECT list, however. Only VARCHAR2 and NUMBER columns may be selected.

Example of Query-based notification:

using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand(
"SELECT empno, ename, sal, comm " +
" FROM EMP " +
" WHERE ename = 'SCOTT'", connection);

OracleDependency dependency = new OracleDependency(command);
dependency.RowidInfo = OracleRowidInfo.Include;
command.Notification.IsNotifiedOnce = false;
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

Query Notification will only be executed if one of the selected columns for SCOTT changes. Changing the column DEPTNO for SCOTT or the column SAL for KING will not fire the change event.

Example of Object-based notification:

using (OracleConnection connection = new OracleConnection("Data Source=DB1; User ID=scott; Password=tiger"))
{
OracleCommand command = new OracleCommand(
"SELECT empno, ename, sal, hiredate" +
" FROM EMP " +
" WHERE ename = 'SCOTT'", connection);

OracleDependency dependency = new OracleDependency(command);
dependency.RowidInfo = OracleRowidInfo.Include;
command.Notification.IsNotifiedOnce = false;
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);


Note, that the only difference is the presence of the HIREDATE column which prevents Query-based notification.
Query notifcation will be executed if any column in any row in the EMP table changes.

Of course, other changes such as DDL commands on the target table may also fire the events as it did in the previous versions.
Abgelegt unter: , ,
HA Events vs. TAF with RAC and ODP.NET
05 Dezember 07 07:31 | Urs Meier - Blog on IT
Lately I had a question in this Blog about an ORA-25408.
My first thought was, that TAF and HA events should not be mixed. In order to confirm this, I wrote a small application with 3 connection possibilites
1. Normal connection
2. TAF connection with or without HA events
3. Connection with HA events only




For the tests I used a 10.2.0.2 Oracle 3 node cluster and ODP.NET 11.1.0.6.10 BETA.

Once all 3 connections (pool with 5 connections each) were connected, I shutdown the current instance.

Results:
Normal connection.
If the tnsnames alias specifed FAILOVER=ON, the connection failed over, even without TAF specifications in the tnsnames.ora or on the used service. Big surprise! But a pleasant one....
With FAILOVER=OFF, I got the expected ORA-3113 error.





TAF connection:
The TAF is always fired. Regardless HA events=true or false!
So, I see no point of having TAF *and* HA events together.
Use TAF for query only applications. If you only do queries, than TAF provides a superior failover.
Use HA events for application server connection pools.
I think, this also confirms, that ODP.NET does not clear the connections with HA events if the connections has a registered TAF handler. Otherwise it would not be necessary to fire the TAF event handler any longer.

HA events connection:
ODP.NET cleans all bad connections in the pool. Reusing a pooled connection does not produce an error. Works as expected.

Abgelegt unter: , , ,
Bulk Copy with SQL Server
19 November 07 12:26 | Urs Meier - Blog on IT
SQL Server also offers an "array insert" possibility via .NET, which is called bulk copy.

I wrote a similiar application as with the Oracle ODP.NET array insert. The point was not to compare Oracle vs. SQL Server, but to verify if the bulk copy is really faster than a normal insert via the SqlAdapter.
Again, bulk copy is - depedening on the rowsize - 10 to 20 times faster. Generally speaking, the difference between SQL Server bulk copy and SQL Server SqlAdapter is somewhat bigger, than than the difference between Oracle array insert and OracleAdapter. Which only means, that it is worthwhile to use these features in both products.

The nice thing about ADO.NET bulk copy is its simplicity.

SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString);
bulkCopy.DestinationTableName = "bulktesttable";
bulkCopy.WriteToServer(this.bulkTestDataSet.bulktesttable);




Again, feel free to ask for the code I used in order to make your own tests and conclusions.

Abgelegt unter: , ,
Array Insert with ODP.NET
18 November 07 05:39 | Urs Meier - Blog on IT
ODP.NET supports array insert in two ways:

1. Binding arrays using ArrayBindCount

cmd.Parameters.Add("firstname",
OracleDbType.Varchar2,
firstNames,
ParameterDirection.Input);

where firstNames is string[]

2. Binding to PL/SQL associative Arrays

OracleParameter pFirstname = new OracleParameter("firstname",
OracleDbType.Varchar2,
ParameterDirection.Input);
pFirstname.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstname.Value = firstNames;

where firstNames is string[]
and the stored procedures accepts firstname as TYPE id_type IS TABLE OF bulktesttable.id%TYPE INDEX BY PLS_INTEGER;




The syntax is well documented in the ODP.NET guide.
So far so good.

The interesting question is the performance difference. In order to get an idea about the difference, I wrote a small application which allows me to choose between
1. standard ADO.NET adapter insert
2. array insert using ArrayBindCount
3. array insert using PL/SQL associative Arrays



and changing the number of rows and the the rowsize to be inserted.



Of course, I expected a difference in performance. But array inserts are really faster. I measured up to 10 times better performance, depending on row size and number of rows.
Array inserts are best performing having a high number of small records. Using Stored Procedures offers sometimes a small performance gain, but it will be mostly useful if you have some more logic to be applied (such as generating primary keys).

If you would like to extend the tests for yourself, just ask for my code in order to get started.
Abgelegt unter: , ,
Barrier free web design
22 August 07 06:21 | Urs Meier - Blog on IT
Excellent presentation, but in German, about barrier free Web Design. Direct Google-Link Weiter lesen...
Abgelegt unter:
RAC FAN and FCF
15 Mai 07 07:59 | Urs Meier - Blog on IT
After the successfull RLB tests with RAC FAN events we re-tested the FCF with both Java and ODP.NET Startup As soon as the DBA puts an additional instance into a RAC service the client receives a FAN / FCF event. Both Java and .NET use the additional...
Abgelegt unter: , , , ,
More on RAC and RLB
15 Mai 07 07:32 | Urs Meier - Blog on IT
My last RAC tests with runtime load balancing and .NET were not successful. ODP.NET always distributed the workload in a round robin manner. Now I know why: It is a AQ bug. I had more than one IP address defined (my real one and one for my vmWare). Since...
Abgelegt unter: , , , ,
Linked Server Performance
07 März 07 04:23 | Urs Meier - Blog on IT
Stefan Frutiger from Trivadis has extended my performance tests with linked server between SQL Server 2005 and Oracle 10g. First the hardware setup was enhanced: - 2 IBM laptops (T41 and T42) with W2003 and 1GB RAM - crossed cable as dedicated network...
Abgelegt unter: , ,
Linked Server to Oracle
24 Dezember 06 03:42 | Urs Meier - Blog on IT
Because of some performance problems with Linked Severs between SQL Server and Oracle, I did some additional tests. I tested Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer...
Abgelegt unter: , ,
Refactoring Databases
21 Oktober 06 04:35 | Urs Meier - Blog on IT
" Refactoring Databases" is a new book from Scott W. Ambler and Pramod J. Sadalage . They basically promote the agile development principles of software development into databases. The difficulty with databases is the fact, that we must manage...
Abgelegt unter: ,
citizendium
18 Oktober 06 12:19 | Urs Meier - Blog on IT
The "better" wikipedia starts under citizendium . I really admire and support people who are willing to put so much effort into public know-how or software, such as open source. But as as user of these open products, it is hard to understand...
Agile Software Development
05 Oktober 06 06:41 | Urs Meier - Blog on IT
Agile Development puts people over processes. In my opinion this is a good trend, even though not completely new. Lately I was reading a very interesting blog about Agile Software Development at Google . I agree with the comments of many readers: Steve...
Abgelegt unter: ,
CLB_GOAL LONG or SHORT?
04 Oktober 06 09:39 | Urs Meier - Blog on IT
With Oracle 10g R2 FAN and FCF we can load balance connections and requests over a "service". The service is defined to be run on one or more instances on the RAC. Services take a CLB_GOAL LONG or SHORT and are created with DBMS_SERVICE.CREATE_SERVICE...
Abgelegt unter: , ,
Mehr Beiträge Nächste Seite »