generate dynamic query and avoiding recompiling query [message #660073] |
Wed, 08 February 2017 08:02 |
|
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 #660078 is a reply to message #660073] |
Wed, 08 February 2017 08:40 |
John Watson
Messages: 8938 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 |
|
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.
|
|
|