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

The term “seconds in wait” means the number of seconds that a SQL Statement, Database User, etc. was waiting on an event. It is possible that the total number of “seconds in wait” exceeds the total amount of time in the period. The following example will illustrate how this occurs.

Example: Assume that one session locks a row that is needed by ten other sessions. All ten sessions will wait for the lock. Also assume that the session holds the lock for one hour. Since each of the ten sessions waited for one hour, Ignite will show total wait time of ten hours during the one hour period.

The maximum theoretical wait time during a period is the number of session multiplied by the length of the period.

Round-trip time when running “select 1 from dual” (includes network time but not connect time) on this database.

Solution

If this is high, contact your network administrator to understand network latency.

Shareable SQL uses bind variables rather than literal values. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU and the number of times the library cache and shared pool latches may need to be acquired and released. Even parsing a simple SQL statement may need to acquire a library cache latch twenty or thirty times.

By looking at the V$SQLAREA view it is possible to see which literal statements are good candidates for converting to use bind variables. The following query shows SQL in the SGA where there are a large number of similar statements:

SELECT substr(sql_text,1,50) “SQL”,
count(*),
sum(executions) “TotExecs”
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,50)
HAVING count(*) > 30
ORDER BY 2

This query finds statements whose first 50 characters are the same and which have only been executed a few times each and have at least 30 different copies of this SQL in the shared pool. The query may need to be modified if the literals are in the first 50 characters.

There are numerous parameters in the INIT.ORA that can directly impact the efficiency of shared pool usage. For a full accounting of these, refer to MetaLink Note: 62143.1.