1. 在当前用户下创建PLAN_TABLE:CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (STATEMENT_ID VARCHAR2(30) NULL, TIMESTAMP DATE NULL, REMARKS VARCHAR2(80) NULL, OPERATION VARCHAR2(30) NULL, OPTIONS VARCHAR2(30) NULL, OBJECT_NODE VARCHAR2(128) NULL, OBJECT_OWNER VARCHAR2(30) NULL, OBJECT_NAME VARCHAR2(30) NULL, OBJECT_INSTANCE NUMBER NULL, OBJECT_TYPE VARCHAR2(30) NULL, OPTIMIZER VARCHAR2(255) NULL, SEARCH_COLUMNS NUMBER NULL, ID NUMBER NULL, PARENT_ID NUMBER NULL, POSITION NUMBER NULL, COST NUMBER NULL, CARDINALITY NUMBER NULL, BYTES NUMBER NULL, OTHER_TAG VARCHAR2(255) NULL, PARTITION_START VARCHAR2(255) NULL, PARTITION_STOP VARCHAR2(255) NULL, PARTITION_ID NUMBER NULL, OTHER LONG NULL) ON COMMIT DELETE ROWS;(我已经在DRMSPDVP下创建好了)2. 运行如下SQL:DELETE FROM PLAN_TABLE; EXPLAIN PLAN SET STATEMENT_ID = '1' FOR SELECT * FROM DEALER WHERE DISTID=:a ;SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options ||' '||object_name ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" FROM plan_table START WITH id = 0 AND statement_id = '1' CONNECT BY PRIOR id = parent_id AND statement_id ='1';注意:将红色部分替换为要解释的SQL即可.如果用Command的SQL包含参数符号'?',则要将'?'依次替换为:a, :b, ...等的参数形式.3. 上面的SQL将作如下格式的输出Query Plan ------------------------------------------------------------------------------------ SELECT STATEMENT Cost = 3 TABLE ACCESS BY INDEX ROWID DEALER INDEX UNIQUE SCAN PK_DEALER上面是我最常看的输出形式,还有其他一些输出格式的SQL,可参考Oracle文档的Explain Plan命令 4. 除了看索引外,有时表与表的Join方法以及Join的顺序也很重要,一些很特殊的情况下,Oracle可能选择 了一个很慢的Execution Plan,这时要用Hints来调整 这样SQL是不会执行的,但是可以看到执行计划.是真正的不执行 而不是象 set autotrace traceonly 只是不显示结果
楼上大侠,我按照你的说法做了,过程如下,但是还是看不太懂啊,帮忙瞧瞧:) SQL> EXPLAIN PLAN SET STATEMENT_ID = '1' 2 FOR 3 SELECT * FROM g_nmc.con_obj_relation where p_class_id=105;已解释。SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options 2 ||' '||object_name 3 ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" 4 FROM plan_table 5 START WITH id = 0 AND statement_id = '1' 6 CONNECT BY PRIOR id = parent_id AND statement_id ='1';Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT Cost = VIEW CON_OBJ_RELATION UNION-ALL FILTER TABLE ACCESS FULL OBJ FILTER TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ NESTED LOOPS NESTED LOOPS NESTED LOOPSQuery Plan ------------------------------------------------------------------------- TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX UNIQUE SCAN PK_OBJ NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPSQuery Plan ------------------------------------------------------------------------- NESTED LOOPS TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ INDEX UNIQUE SCAN PK_TREE_BSC_BTS_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX UNIQUE SCAN PK_OBJQuery Plan ------------------------------------------------------------------------- NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJQuery Plan ------------------------------------------------------------------------- TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN IDX_CLASS_ID_OBJ INDEX RANGE SCAN PK_TREE_BSC_BTS_OBJ INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX UNIQUE SCAN PK_OBJ INDEX UNIQUE SCAN PK_TREE_BTS_CELL_OBJ TABLE ACCESS BY INDEX ROWID OBJ INDEX UNIQUE SCAN PK_OBJ FILTER TABLE ACCESS BY INDEX ROWID OBJQuery Plan ------------------------------------------------------------------------- INDEX RANGE SCAN IDX_CLASS_ID_OBJ已选择56行。
问题终于在不经意的浏览oracle FAQ时解决: 把 SELECT * FROM CON_OBJ_RELATION WHERE P_OBJ_ID = 1472252 and P_CLASS_ID = 105 ORDER BY CLASS_ID, NAME 改为: SELECT /*+ no_index(p_class_id) */ * FROM g_nmc.CON_OBJ_RELATION WHERE P_OBJ_ID = 1472252 and P_CLASS_ID = 105 ORDER BY CLASS_ID, NAME 即可! 估计原因是引用了索引,反而慢了,在此也很感谢大家热心参与!
REMARKS VARCHAR2(80) NULL, OPERATION VARCHAR2(30) NULL,
OPTIONS VARCHAR2(30) NULL, OBJECT_NODE VARCHAR2(128) NULL,
OBJECT_OWNER VARCHAR2(30) NULL, OBJECT_NAME VARCHAR2(30) NULL,
OBJECT_INSTANCE NUMBER NULL, OBJECT_TYPE VARCHAR2(30) NULL,
OPTIMIZER VARCHAR2(255) NULL, SEARCH_COLUMNS NUMBER NULL,
ID NUMBER NULL, PARENT_ID NUMBER NULL, POSITION NUMBER NULL,
COST NUMBER NULL, CARDINALITY NUMBER NULL, BYTES NUMBER NULL,
OTHER_TAG VARCHAR2(255) NULL, PARTITION_START VARCHAR2(255) NULL,
PARTITION_STOP VARCHAR2(255) NULL, PARTITION_ID NUMBER NULL, OTHER LONG NULL)
ON COMMIT DELETE ROWS;(我已经在DRMSPDVP下创建好了)2. 运行如下SQL:DELETE FROM PLAN_TABLE;
EXPLAIN PLAN SET STATEMENT_ID = '1'
FOR
SELECT * FROM DEALER WHERE DISTID=:a
;SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
||' '||object_name
||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0 AND statement_id = '1'
CONNECT BY PRIOR id = parent_id AND statement_id ='1';注意:将红色部分替换为要解释的SQL即可.如果用Command的SQL包含参数符号'?',则要将'?'依次替换为:a, :b, ...等的参数形式.3. 上面的SQL将作如下格式的输出Query Plan
------------------------------------------------------------------------------------
SELECT STATEMENT Cost = 3
TABLE ACCESS BY INDEX ROWID DEALER
INDEX UNIQUE SCAN PK_DEALER上面是我最常看的输出形式,还有其他一些输出格式的SQL,可参考Oracle文档的Explain Plan命令
4. 除了看索引外,有时表与表的Join方法以及Join的顺序也很重要,一些很特殊的情况下,Oracle可能选择
了一个很慢的Execution Plan,这时要用Hints来调整
这样SQL是不会执行的,但是可以看到执行计划.是真正的不执行
而不是象
set autotrace traceonly
只是不显示结果
SQL> EXPLAIN PLAN SET STATEMENT_ID = '1'
2 FOR
3 SELECT * FROM g_nmc.con_obj_relation where p_class_id=105;已解释。SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
2 ||' '||object_name
3 ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0 AND statement_id = '1'
6 CONNECT BY PRIOR id = parent_id AND statement_id ='1';Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost =
VIEW CON_OBJ_RELATION
UNION-ALL
FILTER
TABLE ACCESS FULL OBJ
FILTER
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
NESTED LOOPS
NESTED LOOPS
NESTED LOOPSQuery Plan
------------------------------------------------------------------------- TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX UNIQUE SCAN PK_OBJ
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPSQuery Plan
------------------------------------------------------------------------- NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ
INDEX UNIQUE SCAN PK_TREE_BSC_BTS_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX UNIQUE SCAN PK_OBJQuery Plan
------------------------------------------------------------------------- NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJQuery Plan
------------------------------------------------------------------------- TABLE ACCESS BY INDEX ROWID OBJ
INDEX RANGE SCAN IDX_CLASS_ID_OBJ
INDEX RANGE SCAN PK_TREE_BSC_BTS_OBJ
INDEX UNIQUE SCAN PK_TREE_MSC_BSC_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX UNIQUE SCAN PK_OBJ
INDEX UNIQUE SCAN PK_TREE_BTS_CELL_OBJ
TABLE ACCESS BY INDEX ROWID OBJ
INDEX UNIQUE SCAN PK_OBJ
FILTER
TABLE ACCESS BY INDEX ROWID OBJQuery Plan
------------------------------------------------------------------------- INDEX RANGE SCAN IDX_CLASS_ID_OBJ已选择56行。
把
SELECT * FROM CON_OBJ_RELATION WHERE P_OBJ_ID = 1472252 and P_CLASS_ID = 105 ORDER BY CLASS_ID, NAME
改为:
SELECT /*+ no_index(p_class_id) */ *
FROM g_nmc.CON_OBJ_RELATION
WHERE P_OBJ_ID = 1472252 and P_CLASS_ID = 105 ORDER BY CLASS_ID, NAME
即可!
估计原因是引用了索引,反而慢了,在此也很感谢大家热心参与!