Home » SQL & PL/SQL » SQL & PL/SQL » generate dynamic query and avoiding recompiling query (Oracle 12.1.0.2 )
generate dynamic query and avoiding recompiling query [message #660073] Wed, 08 February 2017 08:02 Go to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Hi,

There is a dynamic query whose where clause parameters will be created at run time and parameters are from different types for example array or single value, a scheme is that use native dynamic query for generating query, for example:

Quote:
sql_stmt:= 'Select * from persons where department_id=:department_id and group_id IN (select * from table(:in_group_ids))'
Execute sql_stmt using in_gender_id, in_group_ids
But there are two problem:

1-Many other different filters may be selected at run time, so I can not use above scheme for generating final query.

2- There is another issue which is related to performance:
Query with clause "group_id IN ( 1,2,3,4)" is faster than query with clause "select * from table(:in_group_ids)", but the query with clause "group_id IN ( ?,?)" will be recompile before each execution because of different values which are selected (although It is faster than another but
It takes many seconds for recompiling).

Could you tell me how can resolve this issues?
(I tested Query Transformation Scheme, but it also takes too much time)

[Updated on: Wed, 08 February 2017 08:07]

Report message to a moderator

Re: generate dynamic query and avoiding recompiling query [message #660074 is a reply to message #660073] Wed, 08 February 2017 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
More often than not, the use of Dynamic SQL is a strong indicator of flawed design.

Why can't you have fixed/static SQL & just decide which of these to execute at run time using bind variables?

You can have Good, Fast, or Cheap. Pick any two & pay the price in the third.
Re: generate dynamic query and avoiding recompiling query [message #660076 is a reply to message #660073] Wed, 08 February 2017 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you use bind variables there is no "recompilation".
If you use dynamic query there is ALWAYS "recompilation" whatever you pass, even if it is a known query.

So the following sentence is wrong: " the query with clause "group_id IN ( ?,?)" will be recompile before each execution because of different values which are selected" unless you ask Oracle to recompile (that is use "prepare" instead of only "execute"), it will not.

The following sentence is also wrong: "It takes many seconds for recompiling" unless your query use dozen of different objects, you flushed the SGA and is in hundreds KB or more, it will not take many seconds to parse.

Re: generate dynamic query and avoiding recompiling query [message #660077 is a reply to message #660073] Wed, 08 February 2017 08:38 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
thanks for your replies:

Quote:
So the following sentence is wrong: " the query with clause "group_id IN ( ?,?)" will be recompile before each execution because of different values which are selected" unless you ask Oracle to recompile (that is use "prepare" instead of only "execute"), it will not.

The following sentence is also wrong: "It takes many seconds for recompiling" unless your query use dozen of different objects, you flushed the SGA and is in hundreds KB or more, it will not take many seconds to parse.
Yes you are right , I could not express my purpose.


Quote:
Why can't you have fixed/static SQL & just decide which of these to execute at run time using bind variables?
There are many end users which may select different filters for search operation.
You mean that we write N query based on different scenarios?
Re: generate dynamic query and avoiding recompiling query [message #660078 is a reply to message #660073] Wed, 08 February 2017 08:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you really are finding that "It takes many seconds for recompiling" the problem may be with adaptive features, particularly SQL Directives and dynamic statistics. Some sites have hit this problem in release 12.1, and by default the adaptive features are largely disabled in 12.2. You could test the effect of setting optimizer_adaptive_features=false.
Re: generate dynamic query and avoiding recompiling query [message #660079 is a reply to message #660078] Wed, 08 February 2017 08:54 Go to previous message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:
If you really are finding that "It takes many seconds for recompiling" the problem may be with adaptive features, particularly SQL Directives and dynamic statistics. Some sites have hit this problem in release 12.1, and by default the adaptive features are largely disabled in 12.2. You could test the effect of setting optimizer_adaptive_features=false.
Thanks for your reply, I will test it

are there any other test and change parameters which I should do it or any other suggestion.




Previous Topic: AND & OR Condition Usage
Next Topic: WHERE Clause With A Range
Goto Forum:
  


Current Time: Thu Mar 28 04:06:04 CDT 2024