MSQL优化基础(frequence直方图选择率)
来源：互联网 发布：怎么改淘宝店铺名字 编辑：程序博客网 时间：2024/05/29 10:24
准备数据
drop table t1;create table t1(n1 number);begin for i in 1..100 loop for j in i*5..100 loop insert into t1 values(i); end loop; end loop;end ;/commit;//收集数据exec dbms_stats.gather_table_stats(null,'T1',method_opt=>'for all columns size 20');//查询直方图select ENDPOINT_VALUE, ENDPOINT_NUMBER, ENDPOINT_NUMBER-nvl(lag(ENDPOINT_NUMBER) over(order by endpoint_value),0) backet_size from user_histograms where table_name='T1' and column_name='N1';ENDPOINT_VALUE ENDPOINT_NUMBER BACKET_SIZE-------------- --------------- ----------- 1 96 96 2 187 91 3 273 86 4 354 81 5 430 76 6 501 71 7 567 66 8 628 61 9 684 56 10 735 51 11 781 46 12 822 41 13 858 36 14 889 31 15 915 26 16 936 21 17 952 16 18 963 11 19 969 6 20 970 120 rows selected.// 查询行数SQL> select num_rows from user_tables where table_name='T1'; NUM_ROWS----------
970//查询最大最小值 select column_name,num_distinct,low_value,high_value,num_nulls,HISTOGRAM from user_tab_columns where table_name='T1' and COLUMN_NAME='N1';COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS HISTOGRAM---------- ------------ ---------- ---------- --------- ----------N1 20 C102(1) C115(20) 0 FREQUENCY
注意：null值不会进直方图
案例一 = (in-range) frequency直方图
有自己的bucket 选择率 =BucketSize /NumRows
没有自己的bucket 选择率 =最小BucketSize/2/NumRows
最小BucketSize如果很小的话 出的问题很高
select * from t1 where n1=12;select 41/970*970 = 41 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 41 | 123 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 41 | 123 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------select * from t1 where n1=12.5;select 1/2/970*970 from dual;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------
案例二 >,<,<=,>= (in-range) frequency直方图
>,<,<=,>=选择率： (sum(BucketSize)) /NumRows
between 选择率： (sum(Bucketsize)) /Numrows
<pre class="sql" name="code">select * from t1 where n1> 17--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 17 | 51 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 17 | 51 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------select (11+6+1 )/970 * 970 from dual; 18 差一点点 情况不清， 最多差1 问题不大
案例三 如何验证是否bind peek
var b1 number;exec :b1:=50alter session set events '10053 trace name context forever ,level 1'select * from t1 where n1=:b1;alter session set events '10053 trace name context off';SQL> show parameter dump ;NAME TYPE VALUE------------------------------------ ----------- ------------------------------background_core_dump string partialbackground_dump_dest string /opt/ora10g/admin/RACDB/bdumpcore_dump_dest string /opt/ora10g/admin/RACDB/cdumpmax_dump_file_size string UNLIMITEDshadow_core_dump string partialuser_dump_dest string /opt/ora10g/admin/RACDB/udump/opt/ora10g/admin/RACDB/udumpcd /opt/ora10g/admin/RACDB/udump/opt/ora10g/admin/RACDB/udump找到 trace 文件 搜索 bind Peeked values of the binds in SQL statement*******************************************kkscoacd Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 kxsbbbfp=b7ed88d0 bln=22 avl=02 flg=05 value=50从中可以看出 绑定 的值是50 ,这一段说明了 做了bind peek alter session set "_optim_peek_user_binds"=false;alter session set events '10053 trace name context forever ,level 1'select /****/ * from t1 where n1=:b1;alter session set events '10053 trace name context off'trace 文件中 这一节要看一下，这里列出来了 被改过的优化器参数 ************************************* PARAMETERS WITH ALTERED VALUES ****************************** _optim_peek_user_binds = false ************************************* PARAMETERS WITH DEFAULT VALUES 以下内容说明木有做bind peeks *******************************************Peeked values of the binds in SQL statement*******************************************kkscoacd Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 No bind buffers allocated***************************************select * from t1 where n1=50;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------alter session set "_optim_peek_user_binds"=false;var b1 number;exec :b1:=50select * from t1 where n1=:b1;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 49 | 147 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 49 | 147 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------不管b1绑定什么 都是 49 禁用了bind peek 都是这样来算， 不知道 用直方图， 不知道out-range 的情况
案例四 不做 bind peeking
=谓词 (1/NDV)*非空率
>,<,<=,>= 0.05*非空率
between 谓词 0.0025*非空率
not 谓词 (1-0.05)*非空率
alter session set "_optim_peek_user_binds"=false;
select * from t1 where n1=:b1;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 49 | 147 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 49 | 147 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------select 0.05 * 970 from dual; 48.5select * from t1 where n1>:b1--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 49 | 147 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 49 | 147 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------select 0.05 * 970 from dual; 48.5select * from t1 where n1 between :b1 and :b2;---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 15 (0)| 00:00:01 ||* 1 | FILTER | | | | | ||* 2 | TABLE ACCESS FULL| T1 | 2 | 6 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------select * from t1 where n1<> :b1 ;(1-0.05)*1*970 = 921.5--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 922 | 2766 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 922 | 2766 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------
案例五 out-of-range
MIN(BucketSize)/2/NumRows * ((1-(val-high_value)/(high_value-low_value)))
//准备数据begin for i in 1..20 loop insert into t1 values(19);end loop;end;commit;exec dbms_stats.gather_table_stats(null,'T1',method_opt=>'for all columns size 20');select num_rows from user_tables where table_name='T1'; NUM_ROWS---------- 2970//查询直方图select ENDPOINT_VALUE, ENDPOINT_NUMBER, ENDPOINT_NUMBER-nvl(lag(ENDPOINT_NUMBER) over(order by endpoint_value),0) backet_size from user_histograms where table_name='T1' and column_name='N1';ENDPOINT_VALUE ENDPOINT_NUMBER BACKET_SIZE-------------- --------------- ----------- 1 96 96 2 187 91 3 273 86 4 354 81 5 430 76 6 501 71 7 567 66 8 628 61 9 684 56 10 735 51 11 781 46 12 822 41 13 858 36 14 889 31 15 915 26 16 936 21 17 952 16 18 963 11 19 969 6 20 2970 2001select /***^^^****/* from t1 where n1=25;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------按照公式是算得2 ，不知是不是和版本有关， 测试版本是 10.2g(6/2/2970)* ((1-(25-20)/(20-1))) * 2970=2.21052632
补充 函数索引
drop table t2; create table t2 as select * from dba_objects;select distinct status from t2; --两个值STATUS-------INVALIDVALIDselect count(*) , status from t2 group by status; COUNT(*) STATUS---------- ------- 1 INVALID 46423 VALID exec dbms_stats.gather_table_stats(null, 'T2');//如果要查询 INVALID 的值, INVALID 的值又少，可以考虑建函数索引create index indx_t2 on t2 (status); //常规索引create index indx1_t2 on t2 (decode (status, 'VALID',null,'INVALID')); // 函数索引select bytes /1024/1024 from user_segments where segment_name ='INDX_T2'; //常规索引占的大小BYTES/1024/1024--------------- .9375 select bytes /1024/1024 from user_segments where segment_name ='INDX1_T2'; //函数索引占的大小BYTES/1024/1024--------------- .0625drop index indx_t2 //删掉 常规索引 select * from t2 where decode(status, 'VALID',null,'INVALID')='INVALID'----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 464 | 43152 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 464 | 43152 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INDX1_T2 | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------走了函数索引
应用中 尽量少用 truncate , 使用truncate ，使这张表相关打开的共享游标都失效！！
频繁使用 truncate , 别人又在查询时候，可能会使数据库hang住应用中 尽量少用 truncate , 使用truncate ，使这张表相关打开的共享游标都失效！！
频繁使用 truncate , 别人又在查询时候，可能会使数据库hang住
optizer_max_permutations 各种访问路径 尝试最多的次数
in(12,13,14,15...) 会转换成 or-expansion
0 0
- MSQL优化基础(frequence直方图选择率)
- MSQL优化基础(无直方图选择率)
- MSQL优化基础(height_blanced直方图选择率)
- MSQL优化基础(Join 选择率)
- msql优化
- MSQL优化基础(SQL执行过程，链接方式案例)
- MSQL优化基础(正确取到执行计划)
- MSQL优化基础(找执行时绑定的值)
- msql优化--索引优化
- msql数据库优化设置
- MSQL数据库检索优化
- Msql优化那些事儿
- 直方图基础
- msql
- SQL优化【基础06】 - 统计信息（直方图）
- 使用工具优化 msql 5.6 的配置
- 接着上文MSQL 查的基础命令
- Frequence statement of SQL
- Openfire 系统架构
- 在应用中使用Ext Loader
- 2015华为软赛（五）——基本功能函数
- 有道词典
- C++编译器函数模版机制剖析 - 函数模版的本质
- MSQL优化基础(frequence直方图选择率)
- 根据有道词典改的多屏词典 .
- iOS学习篇之---归档和解挡
- 2.PHP如何记录错误和发送错误
- idea配置项优化
- 联网查看图片的Demo
- 简单的窗体聊天软件
- Permutations II
- Spring概述
