Oracle Live

02/04/2016

Histogramas

Filed under: Database,Estadisticas,Laboratorios — mogukiller @ 1:34 am

Para ayudar al optimizador a obtener un plan de ejecución optimo en queries donde la distribución de los valores que componen un indice no es homogenea es necesario entender como funcionan los histogramas y cómo podemos calcularlos.

Version:11.2.0.4

------------------------------------------
-- MENU
------------------------------------------
-- TEORIA
-- 1.- LABORATORIO
	-- EJEMPLO 1: Tabla de 11110 con 4 valores diferentes.
	-- EJEMPLO 2: Creamos una tabla con valores muy diferentes.
-- 2.- LABORATORIO
-- 3.- Quitar histogramas de una tabla
	
	

-------------------------------------------
-- TEORIA:
-------------------------------------------

/*	METHOD_OPT regula la creacion de histogramas sobre las columnas.
	Sintaxis:
		FOR ALL [INDEXED|HIDDEN] COLUMNS SIZE [SIZE_CLAUSE]
		-------------------------------- ------------------
		                |						 |
						|				  Controla la creacion de histogramas
						|
			Controla de que columnas se crean las estadisticas.
			
	FOR ALL COLUMNS: Toma estadisticas a todas las columnas. By default.
	FOR ALL INDEXED COLUMNS: Toma estadisticas unicamente de las columnas que pertenecen a un indice. No recomendado.
	FOL ALL HIDDEN COLUMNS: Toma unicamente estadisticas de las columnas virtuales. No recomendado
	FOLL ALL COLUMNS SIZE 5 Mi_COL: Crea un histograma de 5 buckets en la columna Mi_COL.
	
	SIZE controla la creacion de histogramas y puede tomar estos valores:
		AUTO: Oracle determina de que columnas tomar histogramas atendiendo a el uso (sys.COL_USAGE$)
		n: Indica que se creara un histograma usando n buckets.
		REPEAT: Crea histogramas sobre columnas que ya tenian uno.
		SKEWONLY: Unicamente crea histogramas sobre columnas muy disgregadas
		
	
*/
/*	DENSIDAD: Es la probabilidad de encontrar un determinado valor en la tabla. Ayuda al CBO

*/
-------------------------------------------------
-- 1.- LABORATORIO
-------------------------------------------------
----------------------------------------------------
-- EJEMPLO 1: Tabla de 11110 con 4 valores diferentes.
-----------------------------------------------------
create table t
    as select case when (rownum between 1 and 10) then 1
                   when (rownum between 11 and 110) then 2
                   when (rownum between 111 and 1110) then 3
                   when (rownum between 1111 and 11110) then 4
              end x
    from dual
    connect by rownum <= 11110;
	
09:48:39 PRUEBA SQL>SELECT X, count(*) from t group by x;

/* DISTRIBUCION
         X   COUNT(*)
---------- ----------
         1         10
         2        100
         4      10000
         3       1000
*/
		 
begin 
 dbms_stats.gather_table_stats(ownname => 'tpcc',tabname => 'T');
end;
    /

10:12:38 PRUEBA SQL>exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''T''');
/* SALIDA
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : ,25
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 06-ene-2014 09:54:09
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE
-----------------

PL/SQL procedure successfully completed.
*/

1.- Obtenemos que existen 4 valores diferentes. Una densidad de 0.25 (1/"Num Valores").
2.- HISTOGRAM esta a NONE por que no hemos especificado el parametro method_opt

-- NOTA: Por defecto method_opt recive el valor de "for all columns size auto"

10:22:29 PRUEBA SQL>select * from user_tab_histograms where table_name = 'T'

TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
T                              X                                            0              1
T                              X                                            1              4

begin 
 dbms_stats.gather_table_stats(ownname => 'tpcc',tabname => 'T', method_opt =>'for all columns size 4');
end;
    /

10:36:55 PRUEBA SQL>exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''T''');

TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : ,000045004500450045 <<--- Obtenemos una densidad mucho menor
NUM_NULLS                     : 0
NUM_BUCKETS                   : 4
LAST_ANALYZED                 : 06-ene-2014 10:43:34
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : FREQUENCY <<----- Ahora si tenemos histogramas.
-----------------

PL/SQL procedure successfully completed.

10:44:11 PRUEBA SQL>select * from user_tab_histograms where table_name = 'T';

TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
T                              X                                           10              1
T                              X                                          110              2
T                              X                                         1110              3
T                              X                                        11110              4

-----------------------------------------------------
-- EJEMPLO 2: Creamos una tabla con valores muy diferentes.
-----------------------------------------------------

CREATE TABLE t AS SELECT rownum x FROM DUAL CONNECT by ROWNUM <=11110;

UPDATE t SET x=1 WHERE rownum <=5000;
UPDATE t SET x=2 WHERE rownum <=3000 AND X !=1;

CREATE INDEX ID_t ON t(x);

begin 
 dbms_stats.gather_table_stats(ownname => 'tpcc',tabname => 'T', cascade =>TRUE, method_opt =>'for all columns size 1');
end;
    /

-- NOTA: Utilizo method_opt =>'for all columns size 1' para evitar que me cree histogramas

11:54:24 PRUEBA SQL>exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''T''');
-- No devuelve ninguna fila,

-- Miramos el plan de ejecucion de esta query:

12:07:39 PRUEBA SQL>SELECT count(*) from t where x=1;
/* EXPLAIN PLAN: INDEX RANGE
  COUNT(*)
----------
      5000


	Execution Plan
----------------------------------------------------------
Plan hash value: 3233502396

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| ID_T |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=1)
*/
  
--NOTA: El optimizador piensa que tenemos solo 4 filas con x=1 cuando en realidad tenemos 5000.

-- Pasamos estadisticas:

begin 
 dbms_stats.gather_table_stats(ownname => 'tpcc',tabname => 'T', cascade =>TRUE);
end;
    /

-- no indicamos el parametro method_opt por que oracle crea histogramas de forma automatica si la distribucion no es homogenea.
	
12:15:08 PRUEBA SQL>exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 3112
LOW_VALUE                     : C102
HIGH_VALUE                    : C3020C0B
DENSITY                       : ,00009000900090009
NUM_NULLS                     : 0
NUM_BUCKETS                   : 254
LAST_ANALYZED                 : 06-ene-2014 12:15:08
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 4
HISTOGRAM                     : HEIGHT BALANCED << -- Ha creado histogramas.
-----------------

-- nota: vemos que el tipo de histograma es HEIGHT BALANCED ha existir muchos valores diferentes


12:17:14 PRUEBA SQL>SELECT COUNT(*) FROM T WHERE x=1;
/* EXPLAIN PLAN: FULL SCAND
  COUNT(*)
----------
      5000


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4943 | 14829 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

*/
  
  

COL TABLE_NAME FOR A20
COL COLUMN_NAME FOR A20
COL ENDPOINT_ACTUAL_VALUE FOR A30
select * from user_tab_histograms where table_name = 'T';

/* SALIDA:

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------------------- -------------------- --------------- -------------- ------------------------------
T                    X                                113              1
T                    X                                181              2
T                    X                                182           8008
T                    X                                183           8052
T                    X                                184           8096
T                    X                                185           8140
T                    X                                186           8184
T                    X                                187           8228
T                    X                                188           8272
T                    X                                189           8315
T                    X                                190           8358
T                    X                                191           8401
T                    X                                192           8444
T                    X                                193           8487
T                    X                                194           8530
T                    X                                195           8573
T                    X                                196           8616
T                    X                                197           8659
T                    X                                198           8702
T                    X                                199           8745
T                    X                                200           8788
T                    X                                201           8831
T                    X                                202           8874
T                    X                                203           8917
T                    X                                204           8960
T                    X                                205           9003
T                    X                                206           9046
T                    X                                207           9089
T                    X                                208           9132
T                    X                                209           9175
T                    X                                210           9218
T                    X                                211           9261
T                    X                                212           9304
T                    X                                213           9347
T                    X                                214           9390
T                    X                                215           9433
T                    X                                216           9476
T                    X                                217           9519
T                    X                                218           9562
T                    X                                219           9605
T                    X                                220           9648
T                    X                                221           9691
T                    X                                222           9734
T                    X                                223           9777
T                    X                                224           9820
T                    X                                225           9863
T                    X                                226           9906
T                    X                                227           9949
T                    X                                228           9992
T                    X                                229          10035
T                    X                                230          10078
T                    X                                231          10121
T                    X                                232          10164
T                    X                                233          10207
T                    X                                234          10250
T                    X                                235          10293
T                    X                                236          10336
T                    X                                237          10379
T                    X                                238          10422
T                    X                                239          10465
T                    X                                240          10508
T                    X                                241          10551
T                    X                                242          10594
T                    X                                243          10637
T                    X                                244          10680
T                    X                                245          10723
T                    X                                246          10766
T                    X                                247          10809
T                    X                                248          10852
T                    X                                249          10895
T                    X                                250          10938
T                    X                                251          10981
T                    X                                252          11024
T                    X                                253          11067
T                    X                                254          11110

75 rows selected.
*/

/* 
TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------------------- -------------------- --------------- -------------- ------------------------------
T                    X                                113              1
T                    X                                181              2
.
.
T                    X                                254          11110
*/

-- nota se ve que el histograma se representa con 254 buckets. para el 1 se asignaro 113.(11110*113/254)=4942,6378;

12:26:41 PRUEBA SQL>SELECT COUNT(*) FROM T WHERE x=1233;
/* SALIDA
  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 3233502396

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| ID_T |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=1233)
*/

-- nota: vemos que para valores con una densidad menor el plan de ejecucion cambia.


------------------------------------------------------------
-- 2.- LABORATORIO
------------------------------------------------------------

/* TEORIA:
	- Extended Stadistics: Permiten ayudar al CBO en el caso de queries donde aparecen varias columnas en el predicado.
*/

-- Utilizamos una copia de la dba_objects

09:37:27 PRUEBA SQL> SELECT OWNER, COUNT(*) FROM DBA_OBJECTS GROUP BY OWNER ORDER BY 2 DESC;
/* SALIDA
OWNER                            COUNT(*)
------------------------------ ----------
SYS                                  9449
PUBLIC                               3413
SYSTEM                                578
WMSYS                                 306
DBSNMP                                 55
TPCC                                   30
OUTLN                                  10
ORACLE_OCM                              8
APPQOSSYS                               5
USER_IMPORT                             2

*/


09:37:28 PRUEBA SQL>SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS GROUP BY OBJECT_TYPE ORDER BY 2 DESC;
/* SALIDA
OBJECT_TYPE           COUNT(*)
------------------- ----------
VIEW                      3878
SYNONYM                   3429
INDEX                     1528
TYPE                      1330
TABLE                     1210
PACKAGE                    610
PACKAGE BODY               586
LOB                        213
SEQUENCE                   146
LIBRARY                    145
INDEX PARTITION            127
TABLE PARTITION            111
TYPE BODY                  110
PROCEDURE                  108
FUNCTION                    93
TABLE SUBPARTITION          32
CONSUMER GROUP              25
QUEUE                       22
PROGRAM                     19
OPERATOR                    16
JOB CLASS                   13
RULE SET                    13
JOB                         11
UNDEFINED                   11
CLUSTER                     10
RESOURCE PLAN               10
EVALUATION CONTEXT          10
WINDOW                       9
*/

CREATE TABLE dbaobjects_test AS SELECT * FROM DBA_OBJECTS;

CREATE INDEX id_dbaobjects ON DBAOBJECTS_TEST(OWNER,OBJECT_TYPE);

begin
     DBMS_STATS.GATHER_TABLE_STATS(NULL,'DBAOBJECTS_TEST', method_opt => 'for all columns size skewonly');
end;
    /

SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='DBAOBJECTS_TEST' ORDER BY 1;

/* SALIDA

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
CREATED                                 628         254 HEIGHT BALANCED
DATA_OBJECT_ID                         2946         254 HEIGHT BALANCED
EDITION_NAME                              0           0 NONE
GENERATED                                 2           2 FREQUENCY
LAST_DDL_TIME                           949         254 HEIGHT BALANCED
NAMESPACE                                15          15 FREQUENCY
OBJECT_ID                             13856         254 HEIGHT BALANCED
OBJECT_NAME                           10686         254 HEIGHT BALANCED
OBJECT_TYPE                              38          38 FREQUENCY
OWNER                                    10          10 FREQUENCY
SECONDARY                                 1           1 FREQUENCY
STATUS                                    1           1 FREQUENCY
SUBOBJECT_NAME                          103         103 FREQUENCY
TEMPORARY                                 2           2 FREQUENCY
TIMESTAMP                               838         254 HEIGHT BALANCED

*/

-- Comprobamos la selectividad de nuestro indice.

09:51:54 PRUEBA SQL>SELECT * FROM DBAOBJECTS_TEST WHERE OWNER='SYS' AND OBJECT_TYPE='SYNONYM';
/*
6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1044156872

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |  2338 |   203K|    49   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DBAOBJECTS_TEST |  2338 |   203K|    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
*/

-- nota: Comprobamos que el plan de execucion no es optimo.

-- Creamos las estadisticas estendidas para esas dos columnas.

DECLARE
      CG_NAME VARCHAR2(30);
    BEGIN
     CG_NAME := DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'DBAOBJECTS_TEST', '(OWNER,OBJECT_TYPE)');
    END;
    /
	
SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='DBAOBJECTS_TEST';

/* SALIDA
EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUXJ8K0YTS_5QD1O0PEA514IY ("OWNER","OBJECT_TYPE")

*/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'DBAOBJECTS_TEST',  METHOD_OPT =>'FOR ALL COLUMNS SIZE SKEWONLY');
END;
 /
 /* SALIDA
 COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
CREATED                                 628         254 HEIGHT BALANCED
DATA_OBJECT_ID                         2946         254 HEIGHT BALANCED
EDITION_NAME                              0           0 NONE
GENERATED                                 2           2 FREQUENCY
LAST_DDL_TIME                           949         254 HEIGHT BALANCED
NAMESPACE                                15          15 FREQUENCY
OBJECT_ID                             13856         254 HEIGHT BALANCED
OBJECT_NAME                           10686         254 HEIGHT BALANCED
OBJECT_TYPE                              38          38 FREQUENCY
OWNER                                    10          10 FREQUENCY
SECONDARY                                 1           1 FREQUENCY
STATUS                                    1           1 FREQUENCY
SUBOBJECT_NAME                          103         103 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY           90          90 FREQUENCY   << --- Aparece nuestra nueva agrupacion
TEMPORARY                                 2           2 FREQUENCY
TIMESTAMP                               838         254 HEIGHT BALANCED

*/

10:13:47 PRUEBA SQL>SET AUTOTRACE ON
10:13:54 PRUEBA SQL>SELECT * FROM DBAOBJECTS_TEST WHERE OWNER='SYS' AND OBJECT_TYPE='SYNONYM';

/* SALIDA
6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 545257043

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     6 |   606 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DBAOBJECTS_TEST |     6 |   606 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_DBAOBJECTS   |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

*/

-- Comparamos las tracas 10053
/* TRACAS 10053

****************                                                ****************
QUERY BLOCK TEXT                                                QUERY BLOCK TEXT
****************                                                ****************
SELECT * FROM DBAOBJECTS_TEST WHERE OWNER='SYS' AND OBJECT_TY   SELECT * FROM DBAOBJECTS_TEST WHERE OWNER='SYS' AND OBJECT_TY

***************************************                         ***************************************
BASE STATISTICAL INFORMATION                                    BASE STATISTICAL INFORMATION
***********************                                         ***********************
Table Stats::                                                   Table Stats::
  Table: DBAOBJECTS_TEST  Alias: DBAOBJECTS_TEST                  Table: DBAOBJECTS_TEST  Alias: DBAOBJECTS_TEST
    #Rows: 13857  #Blks:  394  AvgRowLen:  89.00  ChainCnt:   |     #Rows: 13857  #Blks:  394  AvgRowLen:  101.00  ChainCnt: 
Index Stats::                                                   Index Stats::
  Index: ID_DBAOBJECTS  Col#: 1 6                                 Index: ID_DBAOBJECTS  Col#: 1 6
    LVLS: 1  #LB: 91  #DK: 90  LB/K: 1.00  DB/K: 16.00  CLUF:       LVLS: 1  #LB: 91  #DK: 90  LB/K: 1.00  DB/K: 16.00  CLUF:
Access path analysis for DBAOBJECTS_TEST                        Access path analysis for DBAOBJECTS_TEST
***************************************                         ***************************************
SINGLE TABLE ACCESS PATH                                        SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for DBAOBJECTS_TEST[DBA     Single Table Cardinality Estimation for DBAOBJECTS_TEST[DBA
  Column (#1):                                                    Column (#1): 
    NewDensity:0.000072, OldDensity:0.000036 BktCnt:13857, Po       NewDensity:0.000072, OldDensity:0.000036 BktCnt:13857, Po
  Column (#1): OWNER(                                             Column (#1): OWNER(
    AvgLen: 5 NDV: 10 Nulls: 0 Density: 0.000072                    AvgLen: 5 NDV: 10 Nulls: 0 Density: 0.000072
    Histogram: Freq  #Bkts: 10  UncompBkts: 13857  EndPtVals:       Histogram: Freq  #Bkts: 10  UncompBkts: 13857  EndPtVals:
  Column (#6):                                                    Column (#6): 
    NewDensity:0.000036, OldDensity:0.000036 BktCnt:13857, Po       NewDensity:0.000036, OldDensity:0.000036 BktCnt:13857, Po
  Column (#6): OBJECT_TYPE(                                       Column (#6): OBJECT_TYPE(
    AvgLen: 7 NDV: 38 Nulls: 0 Density: 0.000036                    AvgLen: 7 NDV: 38 Nulls: 0 Density: 0.000036
    Histogram: Freq  #Bkts: 38  UncompBkts: 13857  EndPtVals:       Histogram: Freq  #Bkts: 38  UncompBkts: 13857  EndPtVals:
  ColGroup (#1, Index) ID_DBAOBJECTS                          |   Column (#16): 
                                                              >     NewDensity:0.000036, OldDensity:0.000036 BktCnt:13857, Po
                                                              >   Column (#16): SYS_STUXJ8K0YTS_5QD1O0PEA514IY(
                                                              >     AvgLen: 12 NDV: 90 Nulls: 0 Density: 0.000036 Min: 823348
                                                              >     Histogram: Freq  #Bkts: 90  UncompBkts: 13857  EndPtVals:
                                                              >   ColGroup (#1, VC) SYS_STUXJ8K0YTS_5QD1O0PEA514IY
    Col#: 1 6    CorStregth: 4.22                                   Col#: 1 6    CorStregth: 4.22
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:        |   ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Se
  Table: DBAOBJECTS_TEST  Alias: DBAOBJECTS_TEST                  Table: DBAOBJECTS_TEST  Alias: DBAOBJECTS_TEST
    Card: Original: 13857.000000  Rounded: 2338  Computed: 23 |     Card: Original: 13857.000000  Rounded: 6  Computed: 6.00 
	
	
  Access Path: TableScan                                          Access Path: TableScan
    Cost:  49.49  Resp: 49.49  Degree: 0                      |     Cost:  49.46  Resp: 49.46  Degree: 0
      Cost_io: 49.00  Cost_cpu: 7038814                       |       Cost_io: 49.00  Cost_cpu: 6619054
      Resp_io: 49.00  Resp_cpu: 7038814                       |       Resp_io: 49.00  Resp_cpu: 6619054
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:        |   ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Se
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:        |   ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Se
  
  
  Access Path: index (AllEqRange)                                 Access Path: index (AllEqRange)
    Index: ID_DBAOBJECTS                                            Index: ID_DBAOBJECTS
    resc_io: 263.00  resc_cpu: 3002639                        |     resc_io: 2.00  resc_cpu: 16251
    ix_sel: 0.168739  ix_sel_with_filters: 0.168739           |     ix_sel: 0.000433  ix_sel_with_filters: 0.000433 <<<<< -- LA SELECTIVIDAD DEL INDICE HA MEJORADO
    Cost: 263.21  Resp: 263.21  Degree: 1                     |     Cost: 2.00  Resp: 2.00  Degree: 1
	
	
	
  Best:: AccessPath: TableScan                                |   Best:: AccessPath: IndexRange
         Cost: 49.49  Degree: 1  Resp: 49.49  Card: 2338.21   |   Index: ID_DBAOBJECTS
                                                              >          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 6.00  Bytes
                                                        
============                                                    ============
Plan Table                                                      Plan Table
============                                                    ============
--------------------------------------------+---------------- | ------------------------------------------------------+------
| Id  | Operation          | Name           | Rows  | Bytes | | | Id  | Operation                    | Name           | Rows 
--------------------------------------------+---------------- | ------------------------------------------------------+------
| 0   | SELECT STATEMENT   |                |       |       | | | 0   | SELECT STATEMENT             |                |      
| 1   |  TABLE ACCESS FULL | DBAOBJECTS_TEST|  2338 |  203K | | | 1   |  TABLE ACCESS BY INDEX ROWID | DBAOBJECTS_TEST|     6
--------------------------------------------+---------------- | | 2   |   INDEX RANGE SCAN           | ID_DBAOBJECTS  |     6
                                                              > ------------------------------------------------------+------
*/
----------------------------------------------
-- 3.- Quitar histogramas de una tabla
----------------------------------------------

begin 
 dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'SALES', method_opt=>'FOR ALL COLUMNS SIZE 1');
end;
    /

exec SYS.print_table('select * from dba_tab_col_statistics where table_name = ''SALES''');

/* SALIDA
12:02:29 orcl SQL>exec SYS.print_table('select * from dba_tab_col_statistics where table_name = ''SALES''');
OWNER                         : SH
TABLE_NAME                    : SALES
COLUMN_NAME                   : AMOUNT_SOLD
NUM_DISTINCT                  : 3586
LOW_VALUE                     : C10729
HIGH_VALUE                    : C2125349
DENSITY                       : .000278862242052426
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 27-jan-2014 12:02:00
SAMPLE_SIZE                   : 924451
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 5
HISTOGRAM                     : NONE <<<
-----------------
OWNER                         : SH
TABLE_NAME                    : SALES
COLUMN_NAME                   : QUANTITY_SOLD
NUM_DISTINCT                  : 1
LOW_VALUE                     : C102
HIGH_VALUE                    : C102
DENSITY                       : 1
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 27-jan-2014 12:02:00
SAMPLE_SIZE                   : 924451
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE <<<
-----------------

*/


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: