Oracle Live

02/04/2016

Trazas 10053. Como se comporta el CBO

Filed under: Debugger,Laboratorios — mogukiller @ 1:30 am

Para poder entender como trabaja el optimizador y porque decide un determinado plan de ejecución se puede utilizar las trazas 10053.

Version: 11.2.0.3

/* NOTA:
	-- Se necesitan privilegios de DBA para configurar estas trazas.
	-- Muestra la decisiones que toma el CBO.
	-- Para que se genere la traza se debe producir un hard parse
*/

ALTER SESSION SET TRACEFILE_IDENTIFIER='TRACE3_10053';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10053 trace name context off';

/* Path traza:
-rw-r----- 1 oracle oinstall  73K Jan  6 13:13 PRUEBA_ora_9139_TRACE_10053.trc
*/

-- Añadimos una traza a un sql_id
ALTER SESSION SET TRACEFILE_IDENTIFIER='TRACE_10053';
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:5kg3srau9hkt8]';

-- nota: para deshabilitar el traceo hacemos
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';

-- Podemos activar el 10053 para otra sesion
-- ON:
SYS.DBMS_SYSTEM.SET_EV(<SID>,<SERIAL#>,10053,{1|2});
-- OFF:
SYS.DBMS_SYSTEM.SET_EV(<SID>,<SERIAL#>,10053,0);

-- Paquete DBMS_SQLDIAG

SELECT /*TC*/ count(*) FROM SP_TABLE where ID_VALOR=10;
SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /*TC*/%';
/*
SQL_ID
-------------
du29x5ta2tmbg
*/

execute DBMS_SQLDIAG.DUMP_TRACE(    p_sql_id=>'du29x5ta2tmbg',     p_child_number=>0,     p_component=>'Optimizer',     p_file_id=>'TRACE_10053');
SELECT value  FROM v$diag_info  WHERE name='Default Trace File';
/*
VALUE
----------------------------------------------------------------------------
/apps/oradb/trc/diag/rdbms/tmp3/tmp3/trace/tmp3_ora_113911_TRACE_10053.trc
*/
---------------------------------
-- 1.- LABORATORIO
---------------------------------

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

-- 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.
-----------------
*/

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';

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

-- Analizamos la query que vaya por 1:

14:42:11 PRUEBA SQL>SELECT COUNT(*) FROM T where x=1;

/* SALIDA:
  COUNT(*)
----------
      5000

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

*/

SELECT COUNT(*) FROM T where x=10000;

/* SALIDA
  COUNT(*)
----------
         1


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 |
--------------------------------------------------------------------------
*/

-- Analizamos el resultado de las dos trazas.
X=1 (5000 rows)												X=10000 (1 row)
Access Path: TableScan 										Access Path: TableScan										
	  Cost:  6.17  Resp: 6.17  Degree: 0    					Cost:  6.17  Resp: 6.17  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2454429     						Cost_io: 6.00  Cost_cpu: 2454429
      Resp_io: 6.00  Resp_cpu: 2454429      					Resp_io: 6.00  Resp_cpu: 2454429	  
Access Path: index (index (FFS))							Access Path: index (index (FFS))
    Index: ID_T														Index: ID_T
    resc_io: 7.00  resc_cpu: 2155993								resc_io: 7.00  resc_cpu: 2155993
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 				ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
Access Path: index (FFS)									Access Path: index (FFS)
    Cost:  7.15  Resp: 7.15  Degree: 1								Cost:  7.15  Resp: 7.15  Degree: 1
      Cost_io: 7.00  Cost_cpu: 2155993								Cost_io: 7.00  Cost_cpu: 2155993
      Resp_io: 7.00  Resp_cpu: 2155993								Resp_io: 7.00  Resp_cpu: 2155993
Access Path: index (AllEqRange)								Access Path: index (AllEqRange)
    Index: ID_T														Index: ID_T
    resc_io: 21.00  resc_cpu: 1111475								resc_io: 1.00  resc_cpu: 7061
    ix_sel: 0.444882  ix_sel_with_filters: 0.444882 				ix_sel: 0.000092  ix_sel_with_filters: 0.000092 		
    Cost: 21.08  Resp: 21.08  Degree: 1								Cost: 1.00  Resp: 1.00  Degree: 1
Best:: AccessPath: TableScan										Best:: AccessPath: IndexRange Index: ID_T
	Cost: 6.17  Degree: 1  Resp: 6.17  Card: 4942.64  Bytes: 0		Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.03  Bytes: 0

Al ser X=1 un valor tan poco selectivo el coste de un range scan del indice se dispara en coste. Por lo que se hace imprescindible la utilización de histogramas en tablas con valores no distribuidos homogeneamente.

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: