关于LEADING、ORDERED:
/*+ORDERED*/提示按照from后表的顺序执行。
/*+LEADING(table1 table2 ...)*/提示按照指定顺序执行。
通常在中出现了join的时候,可能需要用到这两个hint,会比较有用。这里引用下在Taobao DBA Team上看到的一个例子:
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 31598426
----------------------------------------------------------
Operation | Name |Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------
SELECT STATEMENT | |784K|124M|2132(1)|00:00:26|
NESTED LOOPS | |784K|124M|2132(1)|00:00:26|
TABLE ACCESS FULL | table_detail|96 |15744| 11(0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
-------------------------------------------------------------
t2是个虚拟的表,没有准确的统计信息,很容易出现执行计划错误,这种sql最好要用hint来固化其执行计划。
使用/*+ ORDERED use_nl(t,t2) */提示。
2 from table(cast(str2varlist('123') as vartabletype)) t2,
3 table_detail t
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
跟踪对应的session,客户提供对应session sid为877
SQL>select sid,serial# from v$session where sid=877;
SQL>exec sys.dbms_system.set_sql_trace_in_session(877,1687,true);
生成trace文件scjfzw1_ora_18741.trc 针对该trace文件进行分析,
$tkprof scjfzw1_ora_18741.trc scjfzw1_ora_18741.txt
查看执行计划也可以用:
SQL> set lines 200
SQL> set pages 80
SQL> explain plan for
SQL> set lines 200 pages 80
SQL> spool
SQL> select * from table(dbms_xplan.display);
explain plan set statement_id='jzmu' for select * from a;
select * from table(dbms_xplan.display(null,'jzmu',null));
SQL> spool off
通过sql_id,查看某一个特定sql的执行计划
SQL>select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/730796/viewspace-580368/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/730796/viewspace-580368/