Good morning everyone.
I have a procedure similar to this in our database:
FUNCTION Get_Tag_Element_Id_For_Set(Element_Attr_Id_Table_In in ATTRIBUTE_TABLE_ID_TAB) RETURN NUMBER IS
v_Select_Statement VARCHAR2(32767);
v_Element_Id ELEMENT.Element_Id%TYPE;
BEGIN
FOR I IN 1..Element_Attr_Id_Table_In.COUNT LOOP
IF v_Select_Statement IS NULL THEN
v_Select_Statement := 'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Tab_In(i);
ELSE
v_Select_Statement := v_Select_Statement || ' intersect ' ||
'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Table_In(i);
END IF;
END LOOP;
--
EXECUTE IMMEDIATE v_Select_Statement INTO v_Element_id;
--
RETURN v_Element_Id;
END;
What this does is to create a query similar to this:
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 1 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 2 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 3 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 4 intersect
SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 5;
I am using Dynamic SQL because the number of intersect pieces can vary from 1 to 15 and in the future it can grow.
The problem that I have is that because the literals are part of the string, the query is being hard parsed every single time. In our production environment this means 100s of thousands of times per day which according to our DBA has added 15% plus to our average load. I know that I can come up with a rewrite to avoid this, but I was wondering if there is a parameter I can set at the session or at the database level where the database itself can convert this kind of query into one with bind variables.
Thanks,
Tom
Edited by: Thomas Morgan on May 3, 2013 8:21 PM