oracle database 12c - histogramas · 103.5 4 111 1 3 rows selected. ... • combina...

© 2016 IBM Corporation

Oracle Database 12c


Oracle Database 12c - HistogramasGaston Aprile

Mail to: [email protected]

[email protected]


Oracle Database 12c

Contenido

• Performance

• Que son las estadísticas?

• Que son los Histogramas?

• Tipos de Histogramas

• Como calcularlos?

• Cuando usamos Histogramas?

18 November 2016 2


Oracle Database 12c

“Si una consulta tarda 15 horas… la consulta no es performante?”

El objetivo es comprender porque son 15 horas

18 November 2016 3


Oracle Database 12c

Que son las estadísticas?

La estadística es información que colecta la Base de Datos relacionada a los objetos y por la cual el optimizador toma sus decisiones al momento de la ejecución de consultas.

18 November 2016 4


Oracle Database 12c

Que son los Histogramas?

Son datos estadísticos que reflejan la distribución de la información dentro de una tabla.

Esto quiere decir, conoce

como es la distribución de los

datos dentro de los campos

de la tabla.

18 November 2016 5

Buckets 254 (11.2) / 2048 pero con 254 default (en 12.1)


Oracle Database 12c

Tipos de Histogramas

• Frecuency (pre-12c)

• Height-Balanced (pre-12c)

• Top Frecuency (nuevo en 12c)

• Hybrid (nuevo en 12c)

18 November 2016 6


Oracle Database 12c

Tipos de Histogramas - Frecuency (pre-12c)

• El número de Buckets es igual al número de Distinct Values

• El End_Point_Number es el número del Bucket. Representa el valor numérico en sí acumulado de la frecuencia.

• El End_Point_Value muestra una representación numérica del valor en sí del número del Bucket. En el caso de valores no-numéricos, los valores de los histogramas se transforman a un valor numérico.

18 November 2016 7


Oracle Database 12c


SQL> set linesize 220 pages 10000

SQL> select valor2, count(1) from table group by valor2 order by valor2;

VALOR2 COUNT(1)

---------- ----------

101 8

102 25

103 68

104 185105 502

106 212

6 rows selected.

SQL> SELECT endpoint_value,endpoint_number,

endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY

endpoint_value) AS frequency

FROM user_tab_histograms

WHERE table_name = 'TABLA'AND column_name = 'VALOR2'

ORDER BY endpoint_value;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY

-------------- --------------- ----------

101 8 8102 33 25

103 101 68

104 286 185

105 788 502

106 1000 212

6 rows selected.

18 November 2016 8

Buckets

Es la cantidad de

valores en el

Bucket

Nombre del Bucket


Oracle Database 12c


explain plan set statement_id '101' for select * from tabla where valor2 = 101;






select statement_id, cardinality from plan_table where id = 0;

STATEMENT_ID CARDINALITY

------------------------------ -----------

101 8

102 25

103 68

104 185

105 502

106 212

6 rows selected.


---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | 502 | 130K| 12 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TABLA | 502 | 130K| 12 (0)| 00:00:01 |

---------------------------------------------------------------------------

18 November 2016 9


Oracle Database 12c


Que pasa cuando el “valor2” no existe en el histograma?explain plan set statement_id '090' for select * from tabla where valor2 = 90;explain plan set statement_id '103.5' for select * from tabla where valor2 = 103.5;explain plan set statement_id '111' for select * from tabla where valor2 = 111;

select statement_id, cardinality from plan_table where id = 0;

STATEMENT_ID CARDINALITY------------------------------ -----------090 1103.5 4111 1

3 rows selected.

• Si el valor está entre el mínimo y el máximo � el optimizador toma el histograma con menor valor y lo divide por 2. En nuestro caso el Bucket 101 tiene 8 valores, por la cual la cardinalidad es 4

• Si el valor esta fuera del mínimo y el máximo � dependerá de la distancia entre el mínimo valor y el máximo valor respecto a la frecuencia

18 November 2016 10


Oracle Database 12c

Tipos de Histogramas - Height-Balanced (pre-12c)

• La cantidad de Buckets es menor a la cantidad de Distinct Values

• Se agrega el Bucket 0 para indicar el valor mínimo de los histogramas

• El Endpoint_Value intenta representar el valor de la columna dentro del Histograma

• El Endpoint_Number muestra el número del Bucket

• Cada Bucket representa un rango de valores que contienen aproximadamente la misma cantidadde registros.

18 November 2016 11


Oracle Database 12c


SQL> exec dbms_stats.gather_table_stats( ownname => 'SH', tabname => 'CUSTOMERS', estimate_percent => 100,method_opt => 'FOR ALL

COLUMNS SIZE 254',cascade => true);

SQL> select count(distinct CUST_CITY_ID) from sh.CUSTOMERS group by 1;

COUNT(DISTINCTCUST_CITY_ID)

---------------------------

620

SQL> SELECT count(1) FROM dba_tab_histograms WHERE owner = 'SH' and table_name = 'CUSTOMERS' AND column_name = 'CUST_CITY_ID';

COUNT(1)

----------

212

18 November 2016 12


Oracle Database 12c


SELECT owner, table_name, column_name, endpoint_number, endpoint_value

FROM dba_tab_histograms

WHERE owner = 'SH'and table_name = 'CUSTOMERS'

AND column_name = 'CUST_CITY_ID'

ORDER BY endpoint_value;

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

----- ---------- -------------------- --------------- --------------SH CUSTOMERS CUST_CITY_ID 0 51040

SH CUSTOMERS CUST_CITY_ID 1 51043




SH CUSTOMERS CUST_CITY_ID 5 51053SH CUSTOMERS CUST_CITY_ID 6 51055















18 November 2016 13

select CUST_CITY_ID, count(1)

from sh.CUSTOMERS

where CUST_CITY_ID between 51044 and 51046

group by CUST_CITY_ID order by 2 desc;

CUST_CITY_ID COUNT(1)

------------ ----------

51044 145

51045 158

51046 25

Un Bucket puede contener mas de

un Distinct Value (2 vs 3)


Oracle Database 12c


El siguiente ejemplo es la estimación realizada por el optimizador cuando se utilizan Histogramas Heigth-Balance:

explain plan set statement_id 'C52372' for select * from sh.CUSTOMERS where CUST_CITY_ID = 52372; Tiene 16 registros



STATEMENT_ID CARDINALITY

------------------------------ -----------

C52372 66

C52374 66

C51806 874

18 November 2016 14


Oracle Database 12c


El gran problema con los Histogramas Balanceados es que un cambio en la distribución de los datos dentro de la columna, puede dar lugar a que un Valor pase a ser Popular por la cual la estimación de la Cardinalidad en el cálculo del plan de ejecución cambia rotundamente:

SQL> col owner format a5

col table_name format a10

col column_name format a20

select owner, table_name, column_name, endpoint_number, endpoint_value, Jumps, decode(jumps,1,

'Non-popular Value','Popular Value') as popular from (

SELECT owner, table_name, column_name, endpoint_number,

endpoint_value,endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS Jumps

FROM dba_tab_histograms

WHERE owner = 'SH'

and table_name = 'CUSTOMERS'

AND column_name = 'CUST_CITY_ID'

ORDER BY endpoint_value)

where endpoint_value in (51806, 52372, 52374)

order by endpoint_value;

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE JUMPS POPULAR ----- ---------- ------------- --------------- -------------- ------ -------------

SH CUSTOMERS CUST_CITY_ID 142 51806 4 Popular Value (numrows_tabla)*(num_de_endpoints_del_valor/total_endpoints)

SH CUSTOMERS CUST_CITY_ID 228 52374 1 Non-popular Value (numrows_tabla) * density � 0 +certero y 1 –certero(La densidad se puede ver en la vista DBA_TAB_COLUMNS)

18 November 2016 15


Oracle Database 12c

Tipos de Histogramas - Top Frecuency (nuevo en 12c)

• Se basan en los Histogramas Frecuencia

• Para que los Top-Frecuency sean útiles, el numero de valores Populares tiene que ser al mayor o igual al número de Buckets generados, cuando los valores No-Populares tienden a ser descartados

• El % de registros Populares tiene que ser mayor al % P -> (1-(1/n))*100, donde n es el número de Buckets

18 November 2016 16


Oracle Database 12c


SQL> select registro, count(1)

from tabla3

group by registro

order by 1;

REGISTRO COUNT(1)

---------- ----------

1 1064

2 1149

3 1107

4 1190

5 1128

6 1040

7 1138

8 1083

9 1091

9991 1

9992 1

9993 1

9994 1

9995 1

9996 1

9997 1

9998 1

9999 1

10000 1

19 rows selected.

18 November 2016 17


Oracle Database 12c


set linesize 220 pages 1000col owner format a6col table_name format a10col column_name format a20select owner, table_name, column_name, endpoint_number, endpoint_value, Jumps, decode(jumps,1,'Non-popular Value','Popular Value') as Popular from(SELECT owner, table_name, column_name, endpoint_number, endpoint_value,endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS JumpsFROM dba_tab_histogramsWHERE owner = 'SYSTEM'and table_name = 'TABLA3'AND column_name = 'REGISTRO'ORDER BY endpoint_value)order by endpoint_value;

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE JUMPS POPULAR------ ---------- -------------------- --------------- -------------- ---------- -----------------SYSTEM TABLA3 REGISTRO 1064 1 1064 Popular ValueSYSTEM TABLA3 REGISTRO 2213 2 1149 Popular ValueSYSTEM TABLA3 REGISTRO 3320 3 1107 Popular ValueSYSTEM TABLA3 REGISTRO 4510 4 1190 Popular ValueSYSTEM TABLA3 REGISTRO 5638 5 1128 Popular ValueSYSTEM TABLA3 REGISTRO 6678 6 1040 Popular ValueSYSTEM TABLA3 REGISTRO 7816 7 1138 Popular ValueSYSTEM TABLA3 REGISTRO 8899 8 1083 Popular ValueSYSTEM TABLA3 REGISTRO 9990 9 1091 Popular ValueSYSTEM TABLA3 REGISTRO 9991 10000 1 Non-popular Value

10 rows selected.

SQL>

18 November 2016 18


Oracle Database 12c

Tipos de Histogramas - Hybrid (nuevo en 12c)

• Combina características de los Histogramas de Frecuencia y los Balanceados

• Cada Distinct Value es asociado a un Bucket. El concepto de Popular Value no existe

• Por lo tanto, cada Bucket tiene un número distinto de registros

• Oracle 12c tiende a utilizar los Histogramas Híbridos en lugar a los Balanceados

• Agrega el concepto de Endpoint_repeat_count que indica la cantidad de veces que un Endpoint_value se repite dentro del Bucket

18 November 2016 19


Oracle Database 12c

Tipos de Histogramas - Hybrid (nuevo en 12c)

set linesize 220 pages 1000col owner format a6col table_name format a10col column_name format a20select owner, table_name, column_name, endpoint_value, endpoint_number, endpoint_repeat_countfrom dba_tab_histogramsWHERE owner = 'SYSTEM'and table_name = 'TABLA4'AND column_name = 'REGISTRO';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER ENDPOINT_REPEAT_COUNT------ ---------- ----------------- -------------- --------------- ---------------------SYSTEM TABLA4 REGISTRO 1 47 47SYSTEM TABLA4 REGISTRO 2 102 55SYSTEM TABLA4 REGISTRO 3 148 46SYSTEM TABLA4 REGISTRO 4 188 40SYSTEM TABLA4 REGISTRO 5 240 52SYSTEM TABLA4 REGISTRO 6 314 74SYSTEM TABLA4 REGISTRO 7 361 47SYSTEM TABLA4 REGISTRO 8 415 54SYSTEM TABLA4 REGISTRO 9 470 55SYSTEM TABLA4 REGISTRO 10 511 41SYSTEM TABLA4 REGISTRO 11 575 64SYSTEM TABLA4 REGISTRO 12 629 54SYSTEM TABLA4 REGISTRO 13 675 46SYSTEM TABLA4 REGISTRO 14 723 48SYSTEM TABLA4 REGISTRO 15 769 46SYSTEM TABLA4 REGISTRO 16 810 41SYSTEM TABLA4 REGISTRO 17 878 68SYSTEM TABLA4 REGISTRO 18 934 56SYSTEM TABLA4 REGISTRO 19 975 41SYSTEM TABLA4 REGISTRO 20 1030 55SYSTEM TABLA4 REGISTRO 21 1077 47SYSTEM TABLA4 REGISTRO 22 1131 54SYSTEM TABLA4 REGISTRO 23 1177 46……

18 November 2016 20

SQL> select * from (

select registro, count(1)

from tabla4

group by registro

order by 1)

REGISTRO COUNT(1)

---------- ----------

1 47

2 55

3 46

4 40

5 52

6 74

7 47

8 54

9 55

10 41

11 64

12 54

13 46

14 48

15 46

16 41

17 68

18 56

19 41

20 55

…La información que tiene el Histograma Hibrido es mas completa en comparación

con el Histograma Balanceado


Oracle Database 12c

Como calcularlos?

• FOR ALL COLUMNS SIZE AUTO � Generara histogramas para toda columna que detecte que los datos no son “Uniformes”

• FOR ALL COLUMNS SIZE REPEAT� Re-Generara los histogramas para las columnasque ya tenga histogramas generados previamente

• METHOD_OPT=>'FOR COLUMNS REGISTRO SIZE 10’ � Le indicamos que para la columna registro, genere 10 Buckets

• METHOD_OPT=>'FOR COLUMNS SIZE 1’ � Forzamos a la No generación de Histogramas

La información de como se utilizan las columnas se encuentra en la vista SYS.COL_USAGE$. La próxima vez que se generen estadísticas, el paquete DBMS_STATS consultará la vista y determinará si se necesitan histogramas. Si la tabla nunca se uso, nunca habrá información en la COL_USAGE$.

18 November 2016 21


Oracle Database 12c

Cuando usamos Histogramas?

SI tener Histogramas• En columnas que aparecen en el Where de Querys• En columnas donde la distribución no es uniforme

No tener Histogramas• Cuando en el Where se utilizan Binds• La información en la columna esta distribuida de manera uniforma• En columnas donde no aparecen en los Where de Querys• En columnas Unique

18 November 2016 22


Oracle Database 12c

18 November 2016 23

oracle database 12c - histogramas · 103.5 4 111 1 3 rows selected. ... • combina...

Documents