用sys登录。
测试：
SQL> conn /as sysdba
已连接。
SQL> create table segs as select * from dba_segments where owner='SYS';
表已创建。
SQL> create table objts as select * from dba_objects where owner='SYS';
表已创建。
SQL> select count(*) from segs;
COUNT(*)
----------
1655
SQL> select count(*) from objts;
COUNT(*)
----------
22994
SQL> create index idx_segs_name on segs(segment_name);
索引已创建。
SQL> create index idx_objts_name on objts(object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'OBJTS',cascade => true);
PL/SQL 过程已成功完成。
SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select * from segs, objts where segs.segment_name = objts.object_name;
已选择2175行。
执行计划
----------------------------------------------------------
Plan hash value: 779051904
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1674 | 297K| 80 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 1674 | 297K| 80 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SEGS | 1655 | 145K| 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OBJTS | 22994 | 2065K| 72 (3)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")
统计信息
----------------------------------------------------------
972 recursive calls
0 db block gets
631 consistent gets
346 physical reads
0 redo size
173106 bytes sent via SQL*Net to client
1969 bytes received via SQL*Net from client
146 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2175 rows processed
SQL> 测试例子转自：
http://lizhen3708693.iteye.com/blog/1631360
