oracle實驗記錄 （histogram是否影響解析）

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

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-616295/，如需轉載，請註明出處，否則將追究法律責任。