sql计划执行顺序
我很不理解oracle的查询计划为什么不直接给出执行的顺序编码。还要oem或者sqlt或者自己看10046，我看了一下sqlt中实现的那段代码，看着好复杂，摘抄了一部分下来。
如果我每次新建个表，然后加入EXEC_ORDER字段
1. CREATE TABLE T_SQL_PLAN AS SELECT * FROM GV$SQL_PLAN;
2. ALTER TABLE T_SQL_PLAN ADD EXEC_ORDER NUMBER(9);
然后调用sqlt中摘抄的存储过程，是否可以得到真实的查询计划执行顺序？
CREATE OR REPLACE PROCEDURE EXECUTION_ORDER(P_SQL_ID IN VARCHAR2) IS
L_EXEC_ORDER NUMBER;
/* -------------------------
*
* RECURSIVE EXECUTION_ORDER.ASSIGN_EXECUTION_ORDER
*
* ------------------------- */
PROCEDURE ASSIGN_EXECUTION_ORDER(P_PLAN_HASH_VALUE IN NUMBER,
P_INST_ID IN NUMBER,
P_CHILD_NUMBER IN NUMBER,
P_CHILD_ADDRESS IN VARCHAR2,
P_ID IN NUMBER) IS
BEGIN
FOR J IN (SELECT ID
FROM T_SQL_PLAN
WHERE SQL_ID = P_SQL_ID
AND PLAN_HASH_VALUE = P_PLAN_HASH_VALUE
AND INST_ID = P_INST_ID
AND CHILD_NUMBER = P_CHILD_NUMBER
AND CHILD_ADDRESS = P_CHILD_ADDRESS
AND PARENT_ID = P_ID
ORDER BY POSITION) LOOP
ASSIGN_EXECUTION_ORDER(P_PLAN_HASH_VALUE => P_PLAN_HASH_VALUE,
P_INST_ID => P_INST_ID,
P_CHILD_NUMBER => P_CHILD_NUMBER,
P_CHILD_ADDRESS => P_CHILD_ADDRESS,
P_ID => J.ID);
END LOOP;
L_EXEC_ORDER := L_EXEC_ORDER + 1;
UPDATE T_SQL_PLAN
SET EXEC_ORDER = L_EXEC_ORDER
WHERE SQL_ID = P_SQL_ID
AND PLAN_HASH_VALUE = P_PLAN_HASH_VALUE
AND INST_ID = P_INST_ID
AND CHILD_NUMBER = P_CHILD_NUMBER
AND CHILD_ADDRESS = P_CHILD_ADDRESS
AND ID = P_ID;
END ASSIGN_EXECUTION_ORDER;
BEGIN
FOR I IN (SELECT PLAN_HASH_VALUE, INST_ID, CHILD_NUMBER, CHILD_ADDRESS, ID
FROM T_SQL_PLAN
WHERE SQL_ID = P_SQL_ID
AND PARENT_ID IS NULL) LOOP
L_EXEC_ORDER := 0;
ASSIGN_EXECUTION_ORDER(P_PLAN_HASH_VALUE => I.PLAN_HASH_VALUE,
P_INST_ID => I.INST_ID,
P_CHILD_NUMBER => I.CHILD_NUMBER,
P_CHILD_ADDRESS => I.CHILD_ADDRESS,
P_ID => I.ID);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('execution_order: ' || SQLERRM);
END EXECUTION_ORDER;
/
一方面是历史遗留问题， 另一方面并不是执行顺序这么简单。存在 循环 并行等情况。
页:
[1]