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) 等
Comment