概 要
SQL> conn sys/oracle as sysdba
已连接。
SQL> grant create any outline to scott;
conn scott/tiger
设置概要，名称为test
SQL> alter session set create_stored_outlines=test;
做查询
SQL> select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno;
ENAME LOC
---------- -------------
CLARK NEW YORK
KING NEW YORK
MILLER NEW YORK
SMITH DALLAS
ADAMS DALLAS
FORD DALLAS
SCOTT DALLAS
JONES DALLAS
ALLEN CHICAGO
BLAKE CHICAGO
MARTIN CHICAGO
JAMES CHICAGO
TURNER CHICAGO
WARD CHICAGO
已选择14行。
结束概要
SQL> alter session set create_stored_outlines=false;
查询概要
SQL> select name,category,used,sql_text from user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
SQL_TEXT
-------------------------------------------------------------------------------
SYS_OUTLINE_060927135756281 TEST UNUSED
select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno
使用概要
SQL> alter session set use_stored_outlines=test;
USE_PRIVATE_OUTLINES私有概要
OUTLN_PKG
补充：
监视索引
ALTER INDEX XXX MONITERING USAGE;监视索引使用
SELECT * FROM V$OBJECT_USAGE;
ALTER INDEX XXX NOMONITERING USAGE;停止监视索引
普通索引对函数不起作用，要添加索引暗示/*+ INDEX(tablename indexname) */
select count(*) from e;全表扫描
select /*+index(emp pk_emp)*/count(*) from e;索引扫描
select count(*) from e where emp>0;索引扫描
explain table
SQL> @?\sqlplus\admin\plustrce.sql
set autotrace on [stat];
set autotrace trace only;
@?\rdbms\admin\utlxplan.sql生成plan_table
explain table for select * from emp;
select id,lpad(operation,level+length(operation)-1,' ') plantree,object_name
alter session set sql_trace=true;
tkprof d:\1.trc d:\1.txt [sys=no不解释SYS用户的操作];from plan_table start with id=0 connect by prior id=parent_id;
