在前面的章节已经介绍了如何使用autotrace,下面我们简单学习如何结合autotrace来分析
表。
SQL> analyze table test compute statistics;
——只有执行分析语句时,oracle才会对数据进行基于成本的分析,否则就会按照oracle的优先原则来分析表
——如果不想每次都分析表这么麻烦,就修改optimizer_mode这个参数
——只有执行分析语句时,oracle才会对数据进行基于成本的分析,否则就会按照oracle的优先原则来分析表
——如果不想每次都分析表这么麻烦,就修改optimizer_mode这个参数
SQL> alter system set optimizer_mode=all_rows;---选择优化器的模式
Table analyzed.
Table analyzed.
SQL> set autotrace on
SQL> select * from test where rownum=1;
SQL> select * from test where rownum=1;
ID NAME SEX
---------- ----------------------------- ------------
1 user1 M
---------- ----------------------------- ------------
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 |
---------------------------------------------------------------------------
| 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
转载于:https://blog.51cto.com/19880614/1160105