NHibernate + Oracle: speed problems when querying data
We have an WCF application which uses NHibernate to query data from the database. After installing the application into a new test environment we're facing some performance problems with queries. Our old and new environment use different Oracle-servers but both of the databases have the same data.
We have went through our NHibernate logs and identified the problematic part:
2010-12-02 07:14:22,673 NHibernate.SQL - SELECT this_.CC... 2010-12-02 07:14:22,688 NHibernate.Loader.Loader - processing result set 2010-12-02 07:14:27,235 NHibernate.Loader.Loader - result set row: 0
In this case the query returned one row. But it seems that in our new environment the "processing result set" is taking much longer (5 seconds vs 0.5 seconds) than in our other environment. Is there some way to find out exactly what inside the "processing result set" is taking so long?
Note. Executing the same exact query directly into the DB with Toad doesn't reproduce the problem. With Toad, both database server are equally fast.
We are using DetachedCriteria to create the query and then it is executed like this:
Dim criteria As ICriteria = crit.GetExecutableCriteria(GetSession()) Return New Generic.List(Of T)(criteria.List(Of T))
The version of NHibernate is 188.8.131.52 and we're using ActiveRecord 2.1.0 to create the mappings. Oracle servers are of version 10g.
So in our case we have two environments that have the same version of the application with identical configuration files and are querying against identical databases, but which have different application and oracle servers. In one environment querying through the NHibernate takes around 5.5 seconds and in the another 0.5 seconds. The results are consistent and the same query has been executed around 50 times to both environments.
Is there something in the Oracle configuration which could cause it to misbehave with NHiberate? And is there a way to get more detailed logging out from NHibernate so that the exact problem inside the "processing result set" could be found?
Any advice is greatly appreciated.
We were able to fix our problem by switching the database drivers from Microsoft's to Oracle's ODP.net. Now, both servers are equally fast and even our previously fast server executes the queries much more rapidly. We don't know what setting in our new server made the Microsoft's Oracle driver so slow.
And it seems that Microsoft is nowadays recommending everyone to use something else than their own Oracle-drivers. http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx
Do a sql trace on both environments by adding this statement to your session:
alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited; alter session set events '10046 trace name context forever, level 8' ;
-- your query goes here select * from mytable where x= 1;
alter session set events '10046 trace name context off';
then use tkprof to examine the trace file ( goto the user_dump_dest usually a directory with udump in the name and tkprof outputfile.log inputtracefilename.trc )
by itself to see help screen and command options
Check that you are using the same settings in the INIT.ORA for things like CURSOR_SHARING=
in both databases