SQL & PL/SQL

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