0

Just to preface, I've seen a dozen similar SO Q/As and they appear to be a bit different (different server, different parameters etc).

Basic problem

Here's my question, we're running a select statement from the web server against our exadata 11g instance. It's a fairly simple query (it's a count). When it executes from the web server it will often take an average 150 times longer than if we run the exact same query from toad against the exact same database instance. Every once in a while the query will run just as quickly from the web server.

Additional info

When it goes bad from the web server the execution plan is significantly different including a full table scan. We can flip over to toad and run the query and get 300ms response. Turn back to the web server and 45 second response (we're watching through the oracle performance tools, new relic).

We've also tried logging in as the web user's oracle account and the performance runs great with the correct execution plan. So I'd assumed it isn't something related to the user (but I'm not totally aware if there is something else I could look at here).

Additionally we've enabled/disabled parallel query and seen no difference, similarly in toad. We can't get it to break outside of the web server.

The web server itself is a iis, using a read committed transaction (which we simulated in toad, toad still executes normally).

The degree on the tables is normal (I mean, you'd expect it to suck from both the web server and toad if this was an issues).

I don't think it could be a driver thing (could it?) since the query is already on the database server and oracle decides the execution plan regardless of who sends it and it sits in the exadata node spinning on the full table scan.

So I guess my question is, where else could I look when I'm essentially getting two different execution plans for the same query, same parameters, same user, run at the same time (or alternating) using different clients?

Community
  • 1
  • 1
Kingpin2k
  • 47,277
  • 10
  • 78
  • 96
  • 1
    Do you get the same `sql_id` in both cases? Or do you get different `sql_id` values when run from Toad and IIS? Are you using bind variables in IIS and literals in Toad? What do you mean that you "simulated" a read-committed transaction in Toad? Read-committed is the default transaction isolation level... – Justin Cave Feb 21 '14 at 20:41
  • Can you enable traces in the DB to trace both Toad and Webservice calls? Can you compare the two tkprofs and see what major difference are there? – Anjan Biswas Feb 21 '14 at 21:21
  • You can also consider running your query through Tuning Advisor and place a SQL Profile and see if that helps. – Anjan Biswas Feb 21 '14 at 21:27
  • Justin bind variables vs literals indeed, and we're doing set transaction level read committed (trying to mimic exactly what's happening in iis, I'll try that out and get back). Not sure about the sqlid, I'll check that out also. – Kingpin2k Feb 21 '14 at 21:38
  • I'll look into getting the tkprofs, oracle is only reporting long running queries, so when the query goes good its been tougher to get any information on it. – Kingpin2k Feb 21 '14 at 21:40
  • @kingpin2k - What does "bind variables vs literals indeed" mean? Are you saying that you are using bind variables in both cases? Or that you're using bind variables in the application and literals when you run it interactively? – Justin Cave Feb 21 '14 at 22:19
  • bind in application vs literal from toad – Kingpin2k Feb 21 '14 at 22:29
  • 1
    Well, if you are using binds in one and literals in another, it is not the same query. Run it with binds in toad. – OldProgrammer Feb 22 '14 at 04:16
  • Yes, and after everyone said that it made sense, I couldn't get with the dba's, so until Monday, I'll update when I do though – Kingpin2k Feb 22 '14 at 04:26
  • Just as an update, so I don't feel like an ass, we eventually ended up modifying our query to not use a view, despite the view working fine for the non parameterized view in Toad, Oracle tried to be a bit too smart and did cause issues in parameterized view. Thanks for all of your help, feel free to add an answer and I'll gladly vote/accept it. – Kingpin2k Mar 06 '14 at 15:13

1 Answers1

2

Have seen this before. My query run from two different computers and got the dramatical execution response time. Similar like yours, one execution plan picked up index while another one was using full table scan. In my case, the cause was related to cardinality feedback. Turn it off and fixed this issue. By the way, which version of 11g you are using? For pulling out the execution plan for your fast query, add /*+ monitor */ hint will help your query showing up on SQL Monitor.

Weidong Zhou
  • 21
  • 1