HISTOGRAM 与解析
如果oracle采用histogram信息 使得 有些信息改变 比如plan中 ROWS(CARD)之类 是否会造成 新的hard parse
SQL> create table t1 (a int);
表已创建。
SQL> insert into t1 values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select distinct sid from v$mystat;
SID
----------
140
SQL> show user
USER 为 "XH"
SQL> execute dbms_stats.gather_table_stats('XH','T1');
PL/SQL 过程已成功完成。
SQL> alter system flush shared_pool;
系统已更改。
SQL>
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 210
session cursor cache count 29
parse count (total) 245
parse count (hard) 43
SQL> select * from t1;
A
----------
1
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
2245880055 0
SQL> col sql_text format a30
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2245880055;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 1 1 1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 210
session cursor cache count 30
parse count (total) 258
parse count (hard) 44~~~~~~~~~~一次
SQL> select * from t1;
A
----------
1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 210
session cursor cache count 30
parse count (total) 259~~~~~
parse count (hard) 44
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2245880055;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 1 2 2
SQL> select * from t1;
A
----------
1
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2245880055;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 1 3 3
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 210
session cursor cache count 30
parse count (total) 260
parse count (hard) 44
SQL> select * from t1;
A
----------
1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 211~~~~~~~~~~~
session cursor cache count 30
parse count (total) 261
parse count (hard) 44
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2245880055;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 1 4 4
现在收集histogram
SQL> ed
已写入 file afiedt.buf
1 begin
2 dbms_stats.gather_table_stats(
3 'XH',
4 't1',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 120');
8* end;
SQL> /
PL/SQL 过程已成功完成。
例中该列distinct 只有一个值,HISTOGRAM BUCKET 有120个,这个是一个频率HISTOGRAM,每个bucket 存一个值
SQL> select * from t1;
A
----------
1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 212~~~~~~~~~~~~`
session cursor cache count 30
parse count (total) 262
parse count (hard) 44
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2245880055;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 1 5 5
~~~~~~~~~~~
create table t2 (a int);
SQL>
SQL> ed
已写入 file afiedt.buf
1 declare
2 begin
3 for i in 1..1000 loop
4 insert into t2 values(1);
5 end loop;
6 for i in 2..9001 loop
7 insert into t2 values(i);
8 end loop;
9 commit;
10* end;
11 /
PL/SQL 过程已成功完成。
SQL> execute dbms_stats.gather_table_stats('XH','T2');
PL/SQL 过程已成功完成。
SQL> select count(*) from t2;
COUNT(*)
----------
10000
SQL> select num_rows from user_tables where table_name='T2';
NUM_ROWS
----------
10000
SQL> select table_name,density,num_nulls,num_distinct from user_tab_col_statistics
where table_name='T2';
TABLE_NAME DENSITY NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
T2 .000111099 0 9001
SQL> select count(*) from t2 where a=1;
COUNT(*)
----------
1000
SQL> select * from xh.t2 where a=1;~~~~~~~~另一SESSION 执行一次 发现 CARD既 ROWS 不对
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
SQL> alter system flush shared_pool;
系统已更改。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 362
session cursor cache count 29
parse count (total) 434
parse count (hard) 77
SQL> select * from t2 where a=1;
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1106427497 0
SQL> col sql_text format a30
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1106427497;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a=1 1 1 1
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 362
session cursor cache count 30
parse count (total) 447
parse count (hard) 78
SQL> select * from t2 where a=1;
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 362
session cursor cache count 30
parse count (total) 448~~~
parse count (hard) 78
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1106427497;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a=1 1 2 2
SQL> ed~~~~~~~~~~~~~~~~~另一session
已写入 file afiedt.buf
1 begin
2 dbms_stats.gather_table_stats(
3 'XH',
4 't2',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 120');
8* end;
9 /
PL/SQL 过程已成功完成。
SQL> select * from xh.t2 where a=1;
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 917 | 2751 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 917 | 2751 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------~~~~~~~~~ROW变了
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
SQL> select * from t2 where a=1;
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 362
session cursor cache count 30
parse count (total) 449~~~~~~~~~
parse count (hard) 78
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1106427497;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a=1 1 3 3
oracle使用了histogram信息 计算card 实际返回card 也有变化 但 还是一次soft parse
所以前面2次说明histogram 信息改变plan返回的基数等时不会造成hard parse,因为没有造成执行计划改变oracle没有尝试评估新的执行计划,还是老的执行计划,只是返回信息中
计算时才用了HISTOGRAM,执行计划执行步骤没有变 不会产生hard parse