直方图只存储字符串前32字节的限制-凯发app官方网站

博客访问： 1032790
博文数量： 145
博客积分： 0
博客等级：民兵
技术积分： 3510
用户组：普通用户
注册时间： 2011-03-30 13:00

个人简介

about me:oracle ace,optimistic,passionate and harmonious. focus on oracle programming,peformance tuning,db design, j2ee,linux/aix,web2.0 tech,etc

文章分类

全部博文（145）

cbo（40）
tuning&performan（31）
concepts&archite（4）
troubleshooting（5）
new feature（6）
sql（48）
pl/sql（8）
miscellaneous（3）
未分配的博文（0）

文章存档

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友

相关博文

直方图只存储字符串前32字节的限制

分类： oracle

2023-04-17 16:58:53

某天，遇到一个紧急sql性能问题，这条sql的执行频次非常高，下面模拟下此问题：
先创建表test,并且建立索引，收集统计信息：

--create table
create table test as select object_id,object_name,'file1000000000000000000000000xyz_20230215_'||rownum from dba_objects;

--create index
create index idx_test on test(file_id);

--gather statistics
exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'test',estimate_percent=>null,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'test',estimate_percent=>null,method_opt=>'for columns file_id size skewonly',no_invalidate=>false,cascade=>true,degree => 10);

下面执行对应的问题语句：

select * from test where file_id='file1000000000000000000000000xyz_20230215_999';

1 row selected.
elapsed: 00:00:00.01
execution plan
----------------------------------------------------------
plan hash value: 1357081020
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 76815 | 5776k| 247 (1)| 00:00:03 |
|* 1 | table access full| test | 76815 | 5776k| 247 (1)| 00:00:03 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("file_id"='file1000000000000000000000000xyz_20230215_999')
statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
881 consistent gets
0 physical reads
0 redo size
742 bytes sent via sql*net to client
520 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

正常情况下，此语句按照条件file_id查询，应该走索引，因为选择性非常好，选择性如下：
dingjun123@oradb> select count(*),count(distinct file_id) card from test;
count(*) card
---------- ----------
76815 76815

file_id基本唯一，为什么没有走索引呢？可以看到，file_id存储的内容是类似于：'file1000000000000000000000000xyz_20230215_'||rownum,也就是前缀很长且重复，可以想到cbo内部计算选择性是有一定规则的，这么长的重复估计有问题。

另外file_id有直方图：
dingjun123@oradb> dingjun123@oradb> select column_name,histogram from dba_tab_col_statistics where table_name='test';

column_name histogram
------------------------------ ---------------
file_id frequency
object_name none
object_id none
name none

去mos搜索下：
statistics and histograms of character columns with length longer than 32 or 64 characters (doc id 800089.1)

在12c之前，直方图只存储前32字节，12c及之后是存储前64字节：
dingjun123@oradb> select count(*),count(distinct substrb(file_id,1,32)) card from test;

count(*) card
---------- ----------
76815 1

这样看，不走索引是正常的，为了解决这个问题，对选择性好的，要删除直方图：

exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'test',estimate_percent=>null,method_opt=>'for columns file_id size 1',no_invalidate=>false,cascade=>true,degree => 10);

删除直方图后正确：
execution plan
----------------------------------------------------------
plan hash value: 2473784974

----------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
----------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 77 | 4 (0)| 00:00:01 |
| 1 | table access by index rowid| test | 1 | 77 | 4 (0)| 00:00:01 |
|* 2 | index range scan | idx_test | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

2 - access("file_id"='file1000000000000000000000000xyz_20230215_999')

statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
746 bytes sent via sql*net to client
520 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

阅读(523) | 评论(0) | 转发(0) |

下一篇：hash join问题之hash碰撞(hash collision)

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们