0

I have a GridView loaded by an SqlDataSource with the System.Data.OracleClient provider using a select command that returns 8 rows.

When the page loads, the time between the Selecting and Selected events is 35 seconds. I've verified that the query is really taking this long by looking at the elapsed time in v$session_longops on the Oracle server.

If I cut and paste the exact same query into sql developer, it runs in 1-2 seconds and returns the same 8 rows.

If I dump the query results into a table and use that table for my page load, it runs in less than 1 second.

Does anyone have any suggestions for things I should be looking at? Other queries on the website don't seem to be affected -- the only thing I can think of that is different with this query is that it does take 1-2 seconds and most of the other queries start returning data faster than that.

ETA: The reason the query is slower is that it is using a different query plan. Presumably some session parameter is different with the two connections that causes a different query plan to be selected, but setting everything listed in v$parameter2 (including plscope, plsql_debug, and plsql_optimize_level) so they match does not replicate the problem in sql developer.

nebulopathy
  • 101
  • 7
  • Post the code that builds and executes the query. – Joel Coehoorn Apr 03 '14 at 20:23
  • It's a static query done through the SqlDataSource.SelectCommand. I can see that the query is exactly the same when it hits the Oracle side, and cutting and pasting that query as seen by Oracle into sqldev results in a completely different query plan being used. IIS or the Oracle client library appear to be setting up the session differently (I think), but I can't find any difference in the session parameters in v$parameter2 that make a difference. – nebulopathy Apr 16 '14 at 21:31
  • @nebulopathy did you find any solution for this issue??? – Shakti Shrestha Jan 31 '17 at 06:56
  • No, I never figured this one out. I think I ended up rewriting the whole query so it joined the tables in a different order and that made the problem go away. – nebulopathy Feb 01 '17 at 15:56

0 Answers0