I have the following C# code in my .NET stored procedure:
int tempNumber = 0;
OracleConnection OracleConn = new OracleConnection();
// The StatementCacheSize parameter in my registry for ODP.NET is set to 10.
// Maybe it is not needed to set "Statement Cache Size" here to ensure statement caching is enabled?
OracleConn.ConnectionString = "context connection=true; Statement Cache Size=10";
OracleCommand OracleCmd = new OracleCommand();
OracleCmd.Connection = OracleConn;
OracleCmd.CommandText = "select distinct g3e_fid, " + ColumnName + " from " + TableName + " order by g3e_fid";
OracleDataReader OracleReader = OracleCmd.ExecuteReader();
OracleReader.FetchSize = OracleCmd.RowSize * 1000;
tempNumber = 10000;
tempNumber = Convert.ToInt32(OracleReader.GetValue(1)) + 10000;
OracleCmd.AddToStatementCache = true; // Maybe this is not needed because statement caching is enabled?
OracleCmd.CommandText = "update " + TableName + " set " + ColumnName + " = :num where g3e_fid = :fid";
OracleCmd.Parameters.Add(":num", OracleDbType.Int32, tempNumber, ParameterDirection.Input);
OracleCmd.Parameters.Add(":fid", OracleDbType.Int32, Convert.ToInt32(OracleReader.GetValue(0)), ParameterDirection.Input);
When I ran it against a table that has about 46000 rows, it took about 50 seconds.
I also wrote a PL/SQL procedure that does the same thing. I used bulk collect with limit clause. I ran it against the same table and it only took about 3 seconds.
I know PL/SQL has performance advantage for data intensive operations. Are there some ways to improve the performance of my .NET SP?
Thank you so much!
Yes, the same code in a Console Application ran much faster than the .NET stored procedure, it took about 6 seconds (compared to the 50 seconds). I also noticed in the database trace file that the same UPDATE table statement was only parsed once in Console Application and PL/SQL stored procedure but was parsed 45663 times (the number of rows in the table was 45869) in the .NET SP. Does this mean the statement caching does not work in .NET SP? Could the worse performance in .NET SP be caused by other reasons?
Thank you very much for the help!
Few more questions to answer -
1. If you use a "non-context" (e.g. scott/tiger) connection in .NET stored procedure whether performance remains the same or whether it become comparable to that of console application?
2. What version of "Oracle Database Extensions for .NET" are you using?
Here are the answers to your questions:
(1) When I used the non-context connection in my .NET SP, the performance became comparable to that of Console Application.
I may need to clarify on those numbers I used for comparing performance. I ran database trace by using the "ALTER SESSION SET SQL_TRACE" statement. When I traced the stored procedures in SQL*Plus, I set it to True then called the SP. The trace files showed the following average "elapsed time" when calling different SPs:
PL/SQL SP - 3 seconds
.NET SP with non-context connection - 30 seconds
.NET SP with context connection - 50 seconds
I also put the "ALTER SESSION SET SQL_TRACE" statements in C# code in Console Application and .NET SPs. The trace files showed the following combined "elapsed time" for all non-recursive and recursive statements:
Console Application - 6.65 seconds
.NET SP with non-context connection - 6.75 seconds
.NET SP with context connection - 7.02 seconds
As you can see, they are almost the same. In fact, the stats were almost identical between the Console Application and .NET SP with non-context connection. The major difference for .NET SP with context connection was that the UPDATE table statement was parsed 45663 times instead of once. It seemed that the statement caching didn't work in the case of context connection but worked for non-context connection in .NET SP.
(2) The Oracle.Database.Extensions.dll ran by extproc.exe was 188.8.131.52.
You have helped me solve a machine ODP.NET and ODE configuration problem recently. Please see the "context connection and error ORA-20100: System.AccessViolationException" thread for more details (link shown below). Maybe it will shed more light on this performacne problem. context connection and error ORA-20100: System.AccessViolationException
Thank you so much for the help!
In your particular version of ODE (10.2.0.4), statement caching was not supported on context connection and that could be the reason for this performance difference. This fact should have been mentioned in readme.txt (or readme.html) of ODP.NET and/or ODE.
Thank you for the quick response!
Two more questions:
(1) Do you know which version of ODE supports statement caching if I don't want to have Oracle 11g yet?
(2) Even with statement caching in non-context .NET SP, the running time was 30 seconds compared to 3 seconds with PL/SQL SP. Is this big difference normal with .NET SP? Is there other ways to improve the performance for .NET SP?
Here are answers to your questions -
(1) I think that statement caching on context connection is supported only starting from 11g.
(2) Code written in PL/SQL SP does run faster than a .NET SP. For a .NET SP there are several layers between it and the db e.g. extproc, .NET, ODP.NET, OCI etc. The real difference really depends on what you are trying to achieve in it. The performance tricks applicable for an ODP.NET application (like correct fetch size etc) are obviously applicable for a .NET SP too. One additional thing to take care of is to use multithreaded extproc instead of dedicated extproc i.e. Start the extproc beforehand using OraClrAgent service and make sure that it is running before you ran your .NET SP.
I am trying to use multithreaded extproc as you suggested. I have never used it before so I searched the web and found a topic called "A Multithreaded extproc Agent" in Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1). It suggests to use an agent control utility called agtctl. I followed the instructions and was able to start a extproc process (the User Name for the process was my login name). However, when my .NET SP was called, a new extproc process was started and the User Name for the process was SYSTEM.
You have suggested to use OraClrAgent service to start the extproc. Can you please give me more detailed info on how to do this or direct me to some Oracle document? Thank you so much!
I would prefer using OraClrAgent service instead of using agtctl commands.
Please look in ODE documentation pdf available in your database server's <OracleHome>\ODE\doc folder. Check "Configuring Extproc Agent Using Windows Service" in this doc ("Oracle Database Extensions for .NET Developer’s Guide"). It should have something like following -
+"As part of Oracle Database Extensions for .NET installation, a Windows service is installed. The service is called OraClrAgnt and can be accessed through the Service Control Panel, as Oracle<OracleHomeName>ClrAgnt, where <OracleHomeName> represents your Oracle home.+
+This service is used for the startup, configuration, and shutdown of the extproc agent."+
I was able to use OraClrAgent service to start the extproc process. In fact, all I need to do was to re-start the service. No new extproc process was started when the .NET SP was called. So I guess the extproc process started by the OraClrAgent service was used. However, I didn't see any performance gain at all. My .NET SP even took 5 seconds longer (~10% more time) to finish with context connection and half second longer (~2% more time) with non-context connection.
I tried to change the OraClrAgent service parameters in the registry from the default:
C:\oracle\product\10.2.0\db_1\bin\OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=1 tcp_dispatchers=0 max_task_threads=2 max_sessions=5
C:\oracle\product\10.2.0\db_1\bin\OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=5 tcp_dispatchers=4 max_task_threads=10 max_sessions=20
But this change made the .NET SP perform even worse. What am I missing here?
It seems that the extproc startup time was not a significant part of the total time taken in your test case. Probably that is why you do not see any performance improvement. Not sure why it degraded performance though.
You may want to look again at your .NET SP for any possible ODP.NET optimizations such as using Array Binding for reducing number of calls to "OracleCmd.ExecuteNonQuery".
Array Binding has fixed my .NET SP performance problem! After I used it for the UPDATE statement in my .NET SP, my .NET SP ran even a little faster than my PL/SQL SP (without multithreaded extproc). With multithreaded extproc approach, my .NET SP ran even a little faster. Thank you so much for all the big help!!