Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Very slow OracleDataReader.Read or HasRows ???

mackrispiDec 11 2008 — edited Jul 26 2011
Hi,

I’m experiencing huge performance delays when calling Read on OracleDataReader …. It takes for ages ….. I waited for 2 minutes then stopped the execution in my .NET application…
If I run the same SQL statement in Toad for Oracle it returns in less than a second ….. Why ???

The only difference is that in .NET I call ExecuteReader to fetch rows while using parameters …. In Toad I just execute generated SQL statement as seen below.

I’m using System.Data.OracleClient.dll from .NET framework 2.0 …. Tried 3.5 , same result.

SQL:
SELECT TITLE,SUBTITLE,AUTHOR,ART_DATE,MEDIA_CODE,FULLTEXT,ID,STARTTIME,ON_PAGE,COORDINATES,COOR_TEXT,THUMBS_NR,CATEGORY,SHOW,CREATE_DATE,GENRE,USER_LAST,DURATION FROM MS_ARTICLE WHERE CREATE_DATE >= TO_DATE('10.12.2008 09:38:16','dd.MM.yyyy HH24:MI:SS') AND (MEDIA_COUNTRY = 'SLOVENIJA' AND INSTR(CHAR_14, ' METRONET',1,1)>0 )

Execution plan is:

Plan
SELECT STATEMENT ALL_ROWS Cost: 4 Bytes: 550 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE PRESCLIP.MS_ARTICLE Cost: 4 Bytes: 550 Cardinality: 1
1 INDEX RANGE SCAN INDEX PRESCLIP.MS_ARTICLE_CREATE_DATE_INDEX Cost: 3 Cardinality: 1


Any ideas why ?

Comments

502182
Do you see the same performance problem when you use ODP.NET (Oracle Data Provider for .NET)?

ODP.NET is available here - http://www.oracle.com/technology/software/tech/windows/odpnet/index.html
mackrispi
No didn't try it yet ..... I will now .....

Regarding ODP.NET .... I read that ODP.NET performs even slower then standard , in some cases ....

The thing is that ONLY this SQL statement performs so slow ...... I usually run sql statements that have more then 100 parameters and I get reply in few seconds ..... so why is this one so different ? :)

According to the date criteria in this statement, I can say for sure that oracle has to look at most 2000 records ... not more .... and there are more then 700.000 records in the table ....
683655
I have exactly the same problem only I was using the ODP.NET data provider first.

I went back to Microsoft's Oracle provider and the performance was even worse (double).

My query (executed via Sql Navigator) executes in 1.3 seconds but when I call 'reader.Read()' in my code, it takes around 60 seconds to return. Subsequent calls to Read are instantaneous.

Anybody know what's going on here please?
gdarling - oracle
Its tough to make a guess with the limited information available at this point.

Are you sure you're executing the exact same query in SQLNavigator? Same bind variables/types/etc?

To troubleshoot further, I'd enable SQLNet tracing (level 16) with Timestamps enabled, and see if you can pick out a 60 second gap in the timestamps. For example, here's an excerpt from a sqlnet trace.:


(1124) [28-JAN-2009 10:33:18:428] nttrd: entry
(1124) [28-JAN-2009 10:33:45:787] nttrd: socket 436 had bytes read=14
(1124) [28-JAN-2009 10:33:45:787] nttrd: exit

In the above trace, we can see we entered the oracle client network receive function (nttrd:entry) at 10:33:18:428, and it was 27 seconds before 14 bytes were read on the socket and the network call completed (nttrd: exit). It was waiting for info from the socket read for 27 seconds. In that case, that would generally point to a database side issue.
Matching client and server sqlnet traces should give you a more complete picture.

You may want to open a SR with Oracle support to get the traces looked at.

Hope it helps,
Greg
747332
Hi guys,

This is killing me! The first READ() call is taking 2 minutes? And the rest of the read calls are instantaneous.
The datatypes in the select query are very simple:
NOT NULL NUMBER(9)
NOT NULL VARCHAR2(30)
NOT NULL DATE
DATE
VARCHAR2(60)
VARCHAR2(15) ENCRYPT

Does ANYone have any idea on this?
I'm using OracleDataReader from Microsoft's System.Data.OracleClient.dll from .Net 2.0 v2.0.50727.

Thank you.
747584
I am experiencing the same issue.
I don't think it is the problem of Oracle client. I believe it is the problem of the Oracle Database Server. I use the smae codes to access two different servers. One is very fast. The other is very slow. Since I don't have admin access to the database servers, I don't know what is the difference between the two servers. I only know that one server is busy and the other one is not. I guess there is some memory usage setting causing the issue. My database is Oracle 9i. Anyboday finds the solution? Thanks

Edited by: user2100801 on Jan 19, 2010 9:33 AM
747332
Ok.... for my case I have found out something that increased our performance but did not really fully solve the problem.

We had a ref cursor open for our stored procedure. The select query inside the stored procedure was referencing a view which had a nested view. The nested view is what was causing the first Read() to be slower. So we removed the nested view. It is still slow, but at least it solved some of the performance problem.

So, if your first Read() is slow, it could be a slow performing query.
747584
If I move the sql codes from stored procedure and run it from Toad, it is fast.
51992
Similar issue: App with several parameterized sql statements. One statement nested (select b.x,c.y from tblBB b, (select y from tblCC) c ).

With System.Data.OracleClient, execution of the nested statement needs 20ms, but the first read() needs 23 seconds.
Same statement executed without using parameters (parameters replaced with their values in the sql command) executes & reads in ~0.3 seconds.

With Oracle.DataAccess, execution of the nested statement with parameters needs 23 seconds, but the first read() runs fast.

Seems like they are fetching their data at a different time, but thats all.

Only the nested statement shows this behaviour, the other ones are executing quite normal.

Client: Win7/64, Oracle11g, ODP.NET 2.112.1.0
Server: W2K3/64, Oracle11g 11.1.0.6.0
730216
Toad returns the first 500 rows by default. Try setting the FetchSize of the DataReader to something similar (RowSize * 500).

Other than that, slow performance is almost always a query problem. Look at the explain plan to see if anything pops out as trouble.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 23 2011
Added on Dec 11 2008
#odp-net
10 comments
9,212 views