I have a database with Oracle 12c (12.1), where when making a query locally and even from TOAD or another remote tool, it returns the response in milliseconds, reviewing the execution plan I can see that it makes correct use of the indexes created, the problem is when I run the same query (same query, same tables ... everything exactly the same) but from my application server, where I have installed an oracle instantclient and instantclient-sqlplus (12.1), it turns out that if I launch The query from there (web server), takes a long time and when I check the execution plan I can see that it does not use any of the indexes unlike the executions from other positions different from the web, evidently there is the delay when not using the indexes, I would like to know why this can happen and how I could solve it, both servers (web and database) are on the same network segment, there is no connectivity element such as a firewall or any other type of routing involved, changing the query is not an option. Thank you.
asked Oct 15, 2021 at 19:27
-
Does your application use a parameterized query, by chance?– mustaccioCommented Oct 15, 2021 at 21:32
-
Yes... use a parameterized query.– miguel ramiresCommented Oct 16, 2021 at 4:24
-
2Does this answer your question? Does oracle database suffer from parameter sniffing issue– mustaccioCommented Oct 16, 2021 at 12:02
Add a comment
|
1 Answer
Simplest solution: the queries are actually not the same.
Not so obvious solution: the sessions from the application server use different optimizer or NLS parameters and this affects the optimizer in a way it choses another execution plan due to different features enabled, different estimated query cost, NLS specific indexes. You can find some of these parameters used in views such as V$SES_OPTIMIZER_ENV, V$SQL_OPTIMIZER_ENV. Most NLS parameters are not included there, for that you need to check within the session or make the session store/display them somewhere.
answered Oct 15, 2021 at 19:58
