1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

one problem about extensible optimizer

Discussion in 'General' started by hshwang, Aug 19, 2010.

  1. hshwang

    hshwang Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    using the function of extensible optimizer,we can define the cost of user defined function,which is implement thought the interface of ODCIStatsFunctionCost().
    now,i create two object type: the first object named test_obj is that it has a attribution which is the type of nested table and two member function which are function a and function b,the second object named db_obj is mainly implement the interface of ODCIGetInterfaces and ODCIStatsFunctionCost, i define the cost of function a and function b in the funtion of ODCIStatsFunctionCost,and the cost of function a is great than the cost of function b.lastly executing sql "associate statistics with types test_obj using db_obj".
    then, i create a table named t1 which has a segment named t_o whose type is test_obj.
    when i want to execute the sql,such that select * from t1 t where t.t_o.a and t.t_o.b ,the optimizer shoud first execute t.t_o.b and then execute t.t_o.a ,because the cost of function b is less than the cost of function a.
    but though the query plan, i can see the optimizer first execute t.t_o.a and then execute t.t_o.b. why and how to deal with this problem?

    a simple test is following:
    database is oracle 10g
    Code (SQL):

    CREATE OR REPLACE TYPE type_nested_table IS TABLE OF INTEGER;
    /

    CREATE OR REPLACE TYPE test_n AS object
    (
    t_n_t type_nested_table,
    member FUNCTION a (n1 NUMBER) RETURN varchar2,
    member FUNCTION b (n2 NUMBER) RETURN varchar2
    );

    CREATE OR REPLACE TYPE body test_n IS

    member FUNCTION a (n1 NUMBER) RETURN varchar2
    IS
    BEGIN
    IF (n1 > 10) THEN
    RETURN 'TRUE';
    ELSE
    RETURN 'FALSE';
    END IF;
    END;

    member FUNCTION b (n2 NUMBER) RETURN varchar2
    IS
    BEGIN
    IF (n2 <= 10) THEN
    RETURN 'TRUE';
    ELSE
    RETURN 'FALSE';
    END IF;
    END;
    END;
    /
    CREATE TABLE t1(id NUMBER,t_n test_n) nested TABLE t_n.t_n_t store AS t_ex;
    /
    BEGIN
    FOR i IN 1..20 loop
    INSERT INTO t1 VALUES(i,test_n(type_nested_table(i)));
    END loop;
    END;

    CREATE OR REPLACE TYPE dbtest AS object
    (
    num NUMBER,
    STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
    RETURN NUMBER,
    STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
    cost OUT sys.ODCICost, args sys.ODCIArgDescList,
    p NUMBER, env sys.ODCIEnv) RETURN NUMBER,
    PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)


    );

    CREATE OR REPLACE TYPE body dbtest IS

    STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
    RETURN NUMBER IS
    BEGIN
    ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCISTATS2'));
    RETURN ODCIConst.Success;
    END ODCIGetInterfaces;

    STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
    cost OUT sys.ODCICost, args sys.ODCIArgDescList,
    p NUMBER ,env sys.ODCIEnv) RETURN NUMBER IS
    fname VARCHAR2(30);
    BEGIN
    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    IF bitand(func.Flags, ODCIConst.ObjectFunc) > 0 THEN
    fname := UPPER(func.ObjectName);
    ELSE
    fname := UPPER(func.MethodName);
    END IF;
    IF fname LIKE UPPER('%a') THEN


    cost.CPUCost := 100;


    RETURN ODCIConst.Success;
    elsif fname LIKE UPPER('%b%') THEN
    cost.CPUCost := 1;


    RETURN ODCIConst.Success;
    ELSE RETURN ODCIConst.Error;
    END IF;
    END ODCIStatsFunctionCost;
    END;
    /
    associate statistics WITH types test_n USING dbtest;
    /

    SELECT * FROM t1 t WHERE t.t_n.a(15) = 'FALSE' AND t.t_n.b(5) = 'TRUE' ; -- it is also first execute t.t_n.a() and then execte t.t_n.b().


     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It has been stated in the documentation that order of execution of functions cannot be guaranteed. In my experience Oracle has executed them in alphabetic order, as you've reported. Setting the cost will likely have no effect on the order of execution.
     
  3. hshwang

    hshwang Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    David Fitzjarrell, thank you for your replying.
    In your reply,you said :eek:rder of execution of functions cannot be guaranteed that stated in the documentation ,which documentation state? and Setting the cost will have effect on the order of execution which can see in the execution explain.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The Oracle online documentation. It is stated here for triggers:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1179

    however this applies to other PL/SQL objects as well, such as functions if more than one function is called in the same query. Experience has shown this arbitrary order is based on the collating sequence of the database.

    Setting the cost has no effect on the order of execution of PL/SQL units such as functions. Cost DOES have an effect on the join order of tables in the FROM clause, however.
     
  5. hshwang

    hshwang Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    In the document , I find that Oracle Database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, then Oracle Database chooses an arbitrary order to execute these triggers. According to this, the execution explain will be different from each other if we execte the same sql statement which contains triggers or functions. But when i set the cost of functions, the execution explain is same every time, if setting the the cost has no affect on the order of exection,how can i explain this? thanks again.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First you stated this:

    "when i want to execute the sql,such that select * from t1 t where t.t_o.a and t.t_o.b ,the optimizer shoud first execute t.t_o.b and then execute t.t_o.a ,because the cost of function b is less than the cost of function a.
    but though the query plan, i can see the optimizer first execute t.t_o.a and then execute t.t_o.b. why and how to deal with this problem?"

    which clearly reports that setting the cost has no effect on the execution order. Now you state the exact opposite. Which of these statements is true?? Oracle has historically executed such things in collation sequence order (groups of materialized views for refresh, functions called in queries, etc.) and your original post stated that. Now you change your story and, absent any proof, claim that the exact opposite of your original statement is true.

    Prove one or the other.
     
  7. hshwang

    hshwang Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    This problem is present when the object type has an attribution of nested table, if the the object type has an attribution of other type except nested table type ,the execute order is first execute the low cost function.You can execute the follow test code in you database.
    Code (SQL):
    CREATE OR REPLACE TYPE test_v_aa AS object
    (
     v_a NUMBER,
     member FUNCTION a (n1 NUMBER) RETURN varchar2,
     member FUNCTION b (n2 NUMBER) RETURN varchar2
    );

    CREATE OR REPLACE TYPE body test_v_aa IS

    member FUNCTION a (n1 NUMBER) RETURN varchar2
    IS
    BEGIN
    IF (n1 > 10) THEN
      RETURN 'TRUE';
    ELSE
      RETURN 'FALSE';
    END IF;
    END;

    member FUNCTION b (n2 NUMBER) RETURN varchar2
    IS
    BEGIN
    IF (n2 <= 10) THEN
      RETURN 'TRUE';
    ELSE
      RETURN 'FALSE';
    END IF;
    END;
    END;
    /
    CREATE TABLE t2(id NUMBER,t_n test_v_aa);

    BEGIN
    FOR i IN 1..50 loop
    INSERT INTO t2 VALUES(i,test_v_aa(i));
    END loop;
    END;

    SELECT * FROM t2 t  WHERE  t.t_n.b(t.id) = 'TRUE' AND t.t_n.a(t.id) = 'FALSE'; --you can get that  the execute order is first executing function b and then executing function a.
    --define the cost of funcion
    CREATE OR REPLACE TYPE dbtest_v AS object
    (
    num NUMBER,
    STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
    RETURN NUMBER,
    STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
    cost OUT sys.ODCICost, args sys.ODCIArgDescList,
    p NUMBER, env sys.ODCIEnv) RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)
    );

    CREATE OR REPLACE TYPE body dbtest_v IS

    STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
    RETURN NUMBER IS
    BEGIN
    ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCISTATS2'));
    RETURN ODCIConst.Success;
    END ODCIGetInterfaces;

    STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
    cost OUT sys.ODCICost, args sys.ODCIArgDescList,
    p NUMBER ,env sys.ODCIEnv) RETURN NUMBER IS
    fname VARCHAR2(30);
    BEGIN
    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    IF bitand(func.Flags, ODCIConst.ObjectFunc) > 0 THEN
      fname := UPPER(func.ObjectName);
    ELSE
      fname := UPPER(func.MethodName);
    END IF;
    IF fname LIKE UPPER('%a') THEN
      --cost.CPUCost := 100;
      cost.CPUCost := 1;
      --cost.IOCost := 137;
      RETURN ODCIConst.Success;
    elsif fname LIKE UPPER('%b%') THEN
      cost.CPUCost := 100;
      --cost.IOCost := 0;
      RETURN ODCIConst.Success;
    END IF;
    END ODCIStatsFunctionCost;
    END;
    /
    associate statistics WITH types test_v_aa USING dbtest_v;
    /
    SELECT * FROM t2 t  WHERE  t.t_n.b(t.id) = 'TRUE' AND t.t_n.a(t.id) = 'FALSE'; -- executing the same sql,you can find that function a is first executing, according to what you said: setting the cost has no effect on the execution order,how can explain this?

     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Nested tables do not follow the usual costing algorithm so I am not surprised that this specific example isn't working as you might expect. Had you posted this code at the beginning, along with the version of Oracle in use, the 'runaround' in responses would not have happened. The quality of response is directly related to the quality of the post; posts lacking information most often produce lacklustre results.
     
  9. hshwang

    hshwang Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much!
    I think someone had met the same problem,so i didn't post this test code. you said that Nested tables do not follow the usual costing algorithm,do you know how the optimization done by orace when using nested tables?
    Thank you again!