在前面的章节已经介绍了如何使用autotrace,下面我们简单学习如何结合autotrace来分析表。
SQL> analyze table test compute
statistics;——只有执行分析语句时,oracle才会对数据进行基于成本的分析,否则就会按照oracle的优先原则来分析表
——如果不想每次都分析表这么麻烦,就修改optimizer_mode这个参数
SQL> alter system set
optimizer_mode=all_rows;---选择优化器的模式Table analyzed.
SQL> set autotrace on
SQL> select * from test where rownum=1;
ID NAME SEX
----------
----------------------------- ------------
1
user1 M
Execution
Plan
----------------------------------------------------------
Plan hash
value: 3931117773
---------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
|* 1
| COUNT STOPKEY | | | | | |
| 2
| TABLE ACCESS FULL| TEST | 1 | 14 | 2 (0)| 00:00:01
|---------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent
gets
0 physical reads
0 redo size
0
bytes sent via SQL*Net to client
0 bytes received via SQL*Net from
client
0 SQL*Net roundtrips to/from client
0 sorts
(memory)
0 sorts (disk)
1 rows processed