Oracle Live

02/04/2016

Pending Statistics

Filed under: Database,Estadisticas — mogukiller @ 1:36 am

Por norma general cuando pasamos estadisticas a una tabla se publican de forma automatica.
Pero podemos generar estadisticas en modi pendiente, validarlas en una sesion y si obtenemos el resultado esperado publicarlas.


BEGIN 
DBMS_STATS.SET_TABLE_PREFS('MOGU','CF_TABLE','PUBLISH','FALSE'); 
END; 
/ 

SELECT COUNT(*) FROM CF_TABLE WHERE ID_VALOR=10 AND VALOR LIKE 'AA%';
/*
Execution Plan
----------------------------------------------------------
Plan hash value: 1658741471

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |     1 |    29 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |              |     1 |    29 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF_TABLE     |     1 |    29 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ID_VALOR |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
*/
UPDATE CF_TABLE SET ID_VALOR=10 WHERE ID_VALOR IS NULL;	<<<< Generamos un ID_VALOR = 10 muy poco selectivo.
/*
54989 rows updated.
*/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'MOGU',TABNAME =>'CF_TABLE',CASCADE =>TRUE);
END;
/

COL TABLE_NAME FOR A30
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED FROM DBA_TAB_PENDING_STATS WHERE TABLE_NAME='CF_TABLE';
/*
TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ---------- ----------- ----------- -------------------
CF_TABLE                            82453        622          30       82453 2016/03/04 10:27:17
*/

SQL> exec print_table( 'SELECT * FROM dba_col_pending_stats WHERE TABLE_NAME=''CF_TABLE''' );
/*
OWNER                         : MOGU
TABLE_NAME                    : CF_TABLE
PARTITION_NAME                :
SUBPARTITION_NAME             :
COLUMN_NAME                   : ID_VALOR
NUM_DISTINCT                  : 27316
LOW_VALUE                     : C10B
HIGH_VALUE                    : C50B4A472E13
DENSITY                       : .000018
NUM_NULLS                     : 0
AVG_COL_LEN                   : 5
SAMPLE_SIZE                   : 5580
LAST_ANALYZED                 : 04-mar-2016 10:27:16
*/

Vemos que para ese valor se ha generado un histograma en estado pendiente.

COL TABLE_NAME FOR A25
COL COLUMN_NAME FOR A20
COL ENDPOINT_ACTUAL_VALUE FOR A40
SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,ENDPOINT_REPEAT_COUNT FROM DBA_TAB_HISTGRM_PENDING_STATS WHERE TABLE_NAME='CF_TABLE';

/*
TABLE_NAME                COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE     ENDPOINT_REPEAT_COUNT
------------------------- -------------------- --------------- -------------- ------------------------- ---------------------
CF_TABLE                  ID_VALOR                        3692             10 10                                         3692
*/

Podemos utilizar las estadisticas pendientes en una sesion

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE;

SELECT COUNT(*) FROM CF_TABLE WHERE ID_VALOR=10 AND VALOR LIKE 'AA%';
/*
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    30 |   158   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| CF_TABLE |    60 |  1800 |   158   (1)| 00:00:01 |	<<<< Vemos que elige la mejor opcion
-------------------------------------------------------------------------------
*/

BEGIN 
DBMS_STATS.PUBLISH_PENDING_STATS('MOGU','CF_TABLE'); 
END; 
/ 

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: