sql> select * from v$version;
banner
——————————————————————————–
oracle database 11g enterprise edition release 11.2.0.1.0 – 64bit production
pl/sql release 11.2.0.1.0 – production
core 11.2.0.1.0 production
tns for linux: version 11.2.0.1.0 – production
nlsrtl version 11.2.0.1.0 – production
sql> –制造一些数据
sql> drop table test purge;
sql> create table test as select * from dba_objects;
sql> update test set object_id=2;
sql> update test set object_id=1 where rownum=1;
sql> commit;
sql> create index ind_t_object_id on test(object_id);
sql> exec dbms_stats.gather_table_stats(user,’test’,cascade => true);
sql> –看看数据的分布
sql> select object_id,count(1) from test group by object_id;
object_id count(1)
———- ———-
1 1
2 72415
sql> set autotrace traceonly
sql> –应该是要走索引
sql> select * from test where object_id = 1;
执行计划
———————————————————-
plan hash value: 1357081020
————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
————————————————————————–
| 0 | select statement | | 36208 | 3359k| 290 (1)| 00:00:04 |
|* 1 | table access full| test | 36208 | 3359k| 290 (1)| 00:00:04 |
————————————————————————–
predicate information (identified by operation id):
—————————————————
1 – filter(“object_id”=1)
统计信息
———————————————————-
1 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via sql*net to client
338 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> –应该是要走全表扫描
sql> select * from test where object_id = 2;
已选择72415行。
执行计划
———————————————————-
plan hash value: 1357081020
————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
————————————————————————–
| 0 | select statement | | 36208 | 3359k| 290 (1)| 00:00:04 |
|* 1 | table access full| test | 36208 | 3359k| 290 (1)| 00:00:04 |
————————————————————————–
predicate information (identified by operation id):
—————————————————
1 – filter(“object_id”=2)
统计信息
———————————————————-
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via sql*net to client
53435 bytes received via sql*net from client
4829 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
sql> set autotrace off
sql> col table_name format a10;
sql> col column_name format a10;
sql> col endpoint_actual_value format a10;
sql> col endpoint_number format 9999999;
sql> col endpoint_value format 999999;
sql>–直方图有问题，重新收集直方图
sql> select * from user_tab_histograms s where s.table_name=’test’ and column_name=’object_id’;
table_name column_nam endpoint_number endpoint_value endpoint_a
———- ———- ————— ————– ———-
test object_id 0 1
test object_id 1 2
sql> exec dbms_stats.gather_table_stats(user, ‘test’,cascade=>true, method_opt=>’for columns object_id size 2′);
sql> set autotrace traceonly
sql> –还是不对
sql> select * from test where object_id = 1;
执行计划
———————————————————-
plan hash value: 1357081020
————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
————————————————————————–
| 0 | select statement | | 36208 | 3359k| 290 (1)| 00:00:04 |
|* 1 | table access full| test | 36208 | 3359k| 290 (1)| 00:00:04 |
————————————————————————–
predicate information (identified by operation id):
—————————————————
1 – filter(“object_id”=1)
统计信息
———————————————————-
0 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via sql*net to client
338 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> set autotrace off
sql> select * from user_tab_histograms s where s.table_name=’test’ and column_name=’object_id’;
table_name column_nam endpoint_number endpoint_value endpoint_a
———- ———- ————— ————– ———-
test object_id 5391 2
sql> –只有用analyze收集直方图
sql> analyze table test compute statistics for table for columns object_id size 2;
sql> select * from user_tab_histograms s where s.table_name=’test’ and column_name=’object_id’;
table_name column_nam endpoint_number endpoint_value endpoint_a
———- ———- ————— ————– ———-
test object_id 1 1
test object_id 72416 2
sql> set autotrace traceonly
sql> select * from test where object_id = 1;
执行计划
———————————————————-
plan hash value: 255872589
———————————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
———————————————————————————————–
| 0 | select statement | | 1 | 99 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| test | 1 | 99 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_t_object_id | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————–
predicate information (identified by operation id):
—————————————————
2 – access(“object_id”=1)
统计信息
———————————————————-
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1191 bytes sent via sql*net to client
338 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> select * from test where object_id = 2;
执行计划
———————————————————-
plan hash value: 1357081020
————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
————————————————————————–
| 0 | select statement | | 72415 | 7001k| 290 (1)| 00:00:04 |
|* 1 | table access full| test | 72415 | 7001k| 290 (1)| 00:00:04 |
————————————————————————–
predicate information (identified by operation id):
—————————————————
1 – filter(“object_id”=2)
统计信息
———————————————————-
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via sql*net to client
53435 bytes received via sql*net from client
4829 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
sql> set autotrace off