在收集統計資訊時指定method_opt=>.. size auto 時,有可能不收集直方圖
DBMS_STATS With METHOD_OPT =>'..SIZE auto' May Not Collect Histograms (文件 ID 557594.1)
在收集統計資訊時指定method_opt=>.. size auto 時,有可能不收集直方圖。
APPLIES TO:
Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
After stats are first gathered using DBMS_STATS with METHOD_OPT =>'FOR all COLUMNS SIZE auto', query runs poorly.
Histograms are not collected properly on the columns. After re-gathering the stats with the same procedure, the query runs fine.
症狀:
第一次用size auto收集統計資訊,查詢緩慢
原因直方圖沒有被收集,用同樣的過程重新收集後,查詢速度變快
CAUSE
When 'SIZE AUTO' is specified, histograms are considered by information of col_usage$. If a column (mycol in this case) has its column statistics and it is used in predicates of SQL statements, then col_usage$ is updated to show that the column has been used as predicates. When DBMS_STATS.GATHER_TABLE_STATS is executed with 'SIZE AUTO' specified, it checks col_usage$ to see whether the column has been used as predicates or not . If it has, histogram is considered for that column.
size auto收集直方圖的條件:
1.列的資料傾斜。
2.列在查詢語句中做謂語
測試:
SQL> create table hurp (mycol number);
Table created.
--插入1000條-1
begin
for i in 1 .. 100000 loop
insert into hurp values (-1);
commit;
end loop;
for i in 1 .. 1000 loop
insert into hurp values (0);
commit;
end loop;
for i in 1 .. 100 loop
insert into hurp values(7);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
---建立索引對列mycol
SQL> create index indx_hurp on hurp(mycol);
Index created.
--對列索引列收集統計資訊
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'HURP',
method_opt =>'for all indexed columns size auto',
cascade =>TRUE);
end;
/
select lpad(owner,5) owner,
lpad(table_name,5)table_name,
column_name,
endpoint_value,
endpoint_number
from dba_histograms
where owner='SYS'
and table_name='HURP';
OWNER LPAD(TABLE COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
SYS HURP MYCOL -1 0
SYS HURP MYCOL 7 1
-- Update col_usage$
SQL> select count(*) from hurp where mycol = 1;
COUNT(*)
----------
0
---重新收集統計資訊
SQL> begin
2 dbms_stats.gather_table_stats(ownname =>'SYS',
3 tabname =>'HURP',
4 method_opt =>'for all indexed columns size auto',
5 cascade =>TRUE);
6 end;
7 /
PL/SQL procedure successfully completed.
---檢視發現已經有了直方圖
SQL> select lpad(owner,5) owner,
2 lpad(table_name,5)table_name,
3 column_name,
4 endpoint_value,
5 endpoint_number
6 from dba_histograms
7 where owner='SYS'
8 and table_name='HURP';
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
SYS HURP MYCOL -1 5419
SYS HURP MYCOL 0 5472
SYS HURP MYCOL 7 5480
select count(*)from hurp where mycol=-1;
-----測試資料不傾斜
drop table hurp purge;
create table hurp(mycol number);
begin
for i in 1 .. 10000 loop
insert into hurp values(-1);
commit;
end loop;
for i in 1 .. 10000 loop
insert into hurp values(0);
commit;
end loop;
for i in 1 .. 10000 loop
insert into hurp values(7);
commit;
end loop;
end;
/
create index indx_hurp on hurp(mycol);
select count(*) from hurp where mycol=1;
exec dbms_stats.flush_database_monitoring_info;
begin
dbms_stats.gather_table_stats(ownname=>'SYS',
tabname=>'HURP',
method_opt=>'for all indexed columns size auto',
cascade=>true);
end;
/
select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='HURP';
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'TEST',
method_opt =>'for all columns size auto',
cascade =>TRUE);
end;
/
select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='TEST';
SELECT OWNER,TABLE_NAME,BU
在收集統計資訊時指定method_opt=>.. size auto 時,有可能不收集直方圖。
APPLIES TO:
Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
After stats are first gathered using DBMS_STATS with METHOD_OPT =>'FOR all COLUMNS SIZE auto', query runs poorly.
Histograms are not collected properly on the columns. After re-gathering the stats with the same procedure, the query runs fine.
症狀:
第一次用size auto收集統計資訊,查詢緩慢
原因直方圖沒有被收集,用同樣的過程重新收集後,查詢速度變快
CAUSE
When 'SIZE AUTO' is specified, histograms are considered by information of col_usage$. If a column (mycol in this case) has its column statistics and it is used in predicates of SQL statements, then col_usage$ is updated to show that the column has been used as predicates. When DBMS_STATS.GATHER_TABLE_STATS is executed with 'SIZE AUTO' specified, it checks col_usage$ to see whether the column has been used as predicates or not . If it has, histogram is considered for that column.
size auto收集直方圖的條件:
1.列的資料傾斜。
2.列在查詢語句中做謂語
測試:
SQL> create table hurp (mycol number);
Table created.
--插入1000條-1
begin
for i in 1 .. 100000 loop
insert into hurp values (-1);
commit;
end loop;
for i in 1 .. 1000 loop
insert into hurp values (0);
commit;
end loop;
for i in 1 .. 100 loop
insert into hurp values(7);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
---建立索引對列mycol
SQL> create index indx_hurp on hurp(mycol);
Index created.
--對列索引列收集統計資訊
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'HURP',
method_opt =>'for all indexed columns size auto',
cascade =>TRUE);
end;
/
select lpad(owner,5) owner,
lpad(table_name,5)table_name,
column_name,
endpoint_value,
endpoint_number
from dba_histograms
where owner='SYS'
and table_name='HURP';
OWNER LPAD(TABLE COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
SYS HURP MYCOL -1 0
SYS HURP MYCOL 7 1
-- Update col_usage$
SQL> select count(*) from hurp where mycol = 1;
COUNT(*)
----------
0
---重新收集統計資訊
SQL> begin
2 dbms_stats.gather_table_stats(ownname =>'SYS',
3 tabname =>'HURP',
4 method_opt =>'for all indexed columns size auto',
5 cascade =>TRUE);
6 end;
7 /
PL/SQL procedure successfully completed.
---檢視發現已經有了直方圖
SQL> select lpad(owner,5) owner,
2 lpad(table_name,5)table_name,
3 column_name,
4 endpoint_value,
5 endpoint_number
6 from dba_histograms
7 where owner='SYS'
8 and table_name='HURP';
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
SYS HURP MYCOL -1 5419
SYS HURP MYCOL 0 5472
SYS HURP MYCOL 7 5480
select count(*)from hurp where mycol=-1;
-----測試資料不傾斜
drop table hurp purge;
create table hurp(mycol number);
begin
for i in 1 .. 10000 loop
insert into hurp values(-1);
commit;
end loop;
for i in 1 .. 10000 loop
insert into hurp values(0);
commit;
end loop;
for i in 1 .. 10000 loop
insert into hurp values(7);
commit;
end loop;
end;
/
create index indx_hurp on hurp(mycol);
select count(*) from hurp where mycol=1;
exec dbms_stats.flush_database_monitoring_info;
begin
dbms_stats.gather_table_stats(ownname=>'SYS',
tabname=>'HURP',
method_opt=>'for all indexed columns size auto',
cascade=>true);
end;
/
select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='HURP';
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'TEST',
method_opt =>'for all columns size auto',
cascade =>TRUE);
end;
/
select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='TEST';
SELECT OWNER,TABLE_NAME,BU
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29446986/viewspace-1173584/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle收集直方圖時 method_opt中引數 auto跟skewonly的區別Oracle直方圖
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- oracle統計資訊--直方圖的收集:Oracle直方圖
- 收集直方圖及檢視直方圖資訊直方圖
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- 微課sql最佳化(7)、統計資訊收集(5)-關於直方圖SQL直方圖
- col_usage$與直方圖的收集直方圖
- ORACLE19c新特性-實時統計資訊收集Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- Oracle 11g 建立臨時window時間視窗 收集統計資訊Oracle
- oracle 建立或重建索引時收集統計資訊不自動收集(_optimizer_compute_index_stats)Oracle索引Index
- 全域性臨時表GTT的統計資訊收集辦法:
- oracle外部表建立以及收集統計資訊以及臨時表Oracle
- oracle統計資訊和直方圖Oracle直方圖
- 收集全庫統計資訊
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- 修改自動收集統計資訊任務的執行時間
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- Oracle 12c資料庫優化器統計資訊收集的最佳實踐(三)|何時不需要收集統計資訊Oracle資料庫優化
- 臨時表(GLOBAL TEMPORARY TABLE)及統計資訊收集,動態取樣
- Oracle 12c資料庫最佳化器統計資訊收集的最佳實踐(三)|何時不需要收集統計資訊Oracle資料庫
- SAMPLE語句在統計資訊收集中應用
- Oracle統計資訊自動收集Oracle
- oracle收集統計資訊job停止Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- MySQL系統如何收集統計資訊MySql
- SIZE AUTO和SIZE SKEWONLY在gather_table_stats時的區別
- Oracle直方圖統計資訊的應用Oracle直方圖
- 關於列的直方圖統計資訊直方圖
- oracle 統計資訊檢視與收集Oracle
- 6 收集資料庫統計資訊資料庫
- Fixed Objects Statistics統計資訊收集 - 2Object
- oracle 11g統計資訊收集Oracle
- EMD_MAINTENANCE 引起統計資訊收集AINaN