Number of distinct values (NDV) & synopsis & Histogram gather speed

Number of distinct values (NDV)   & synopsis & Histogram gather speed

Number of distinct values (NDV)   & synopsis

Pre 11g – Sampling based
Sample the data, get NDV from sample and scale
Computation involves sort and may spill to disk -> expensive
Can not scale the NDV well for skewed columns -> inaccurate NDV

11g – Approximate NDV
Create an auxiliary structure, synopsis by scanning data
Synopsis can be viewed as a sample of distinct values
The structure uses bounded amount of memory
Synopses on different segments of table can be aggregated to generate a single synopsis
NDV can be accurately  and efficiently derived from synopsis

 

Synopsis is a collection of hash values of distinct values
The hash value has the following properties
Bits are independent of each other
Each bit has same probability of being 0 or 1

 

 

 

SQL> select bytes/1024/1024 from dba_segments where segment_name='LARGE_HISTOGRAM';

BYTES/1024/1024
---------------
           2333

SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> set timing on;
SQL> 
SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 1', estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.14
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 1', estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.16
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100);
BEGIN dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100); END;

*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Elapsed: 00:00:28.87

SQL> alter system set pga_aggregate_target=1000M;

System altered.

Elapsed: 00:00:00.01
SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters              string
workarea_size_policy                 string      AUTO

SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100);
BEGIN dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100); END;

*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Elapsed: 00:00:28.65

SQL> alter tablespace temp add tempfile size 10g;

Tablespace altered.

Elapsed: 00:00:00.08

SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL> select * from v$sort_usage;

no rows selected

Elapsed: 00:00:00.03
SQL>  exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100);

SQL>  exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100);
BEGIN dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_1], [0], [131068], [], [],
[], [], []
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Elapsed: 00:00:30.63

SQL> create temporary tablespace temp1 tempfile size 10g;

Tablespace created.

Elapsed: 00:00:00.11
SQL> alter database default temporary tablespace temp1;

Database altered.

Elapsed: 00:00:00.03

SQL> alter tablespace temp1 add tempfile size 10g;

Tablespace altered.

Elapsed: 00:00:00.02

SQL>  exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100);
BEGIN dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>100); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [673281], [1],
[673280], [673280], [], []
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Elapsed: 00:00:31.63

SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>1);
BEGIN dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 100',estimate_percent=>1); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [673281], [1],
[673280], [673280], [], []
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

Elapsed: 00:00:01.72

SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent=>1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.57

SQL> exec dbms_stats.gather_table_stats(USER,'LARGE_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:02:02.13

11.2.0.3

 

相关BUG:

Bug 8663644  Slow Histograms gathering / Histograms are gathered serially

Bug 13583722 – slow incremental stats gather from global histogram gathers (Doc ID 13583722.8) 等

 

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569