Oracle Live

11/04/2016

Adaptive Cursor Sharing

Filed under: SQL Tuning — mogukiller @ 6:37 pm
-----------------------------
-- MENU
-----------------------------
-- TEORIA
-- ENTORNO DE PRUEBAS
-- LABORATORIO 1: Demostracion Adaptive Cursor Sharing.

/* -- TEORIA

	-. Adaptive Cursor Sharing es una nueva funcionabilidad que se implementa en la version 11g en la que dependiendo de los histogramas para un sql_id que utilice bind variables es capaz de elegir el mejor plan
	-. Bind Peeking: Se implementa en la 10g y es el proceso mediante el cual una query que se lanza con bind variables, en la hard parse (1ª ejecucion) se elige el plan de ejecucion atendiendo el valor de la bind
			variable. A veces se puede dar que el plan de ejecucion elegido no se el optimo
	-. CURSOR_SHARING = FORCE | EXACT | SIMILAR
			FORCE: Transforma los literales en bind variables.
			EXACT: No cambia los literales, habria un cursor nuevo por query.
			SIMILAR: El optimizador puede elegir entre cambiar la query o no.

*/

------------------------------------
-- ENTORNO DE PRUEBAS
------------------------------------

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           FORCE
optimizer_features_enable            string                           11.2.0.3

create table emp
(
 empno   number,
 ename   varchar2(20),
 phone   varchar2(20),
 deptno  number
);

insert into emp
  with tdata as
       (select rownum empno
         from all_objects
         where rownum <= 1000)
  select rownum,
         dbms_random.string ('u', 20),
         dbms_random.string ('u', 20),
         case
                when rownum/100000 <= 0.001 then mod(rownum, 10)
                else 10
         end
    from tdata a, tdata b
   where rownum <= 100000;

create index emp_i1 on emp(deptno);

begin 
 dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'emp', METHOD_OPT => 'FOR COLUMNS DEPTNO SIZE 10', CASCADE => TRUE);
end;
    /

alter system flush shared_pool;
	
09:48:39 PRUEBA SQL>SELECT deptno, count(*) FROM EMP GROUP BY deptno ORDER BY deptno;

/* SALIDA
    DEPTNO   COUNT(*)
---------- ----------
         0         10
         1         10
         2         10
         3         10
         4         10
         5         10
         6         10
         7         10
         8         10
         9         10
        10      99900
		 
*/

---------------------------------------------------------
-- LABORATORIO 1: Demostracion Adaptive Cursor Sharing.
---------------------------------------------------------

exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''EMP''');

/*SALIDA
TABLE_NAME                    : EMP
COLUMN_NAME                   : DEPTNO
NUM_DISTINCT                  : 11
LOW_VALUE                     : 80
HIGH_VALUE                    : C10B
DENSITY                       : .0000101814331385286
NUM_NULLS                     : 0
NUM_BUCKETS                   : 10
LAST_ANALYZED                 : 27-jan-2014 17:37:12
SAMPLE_SIZE                   : 5402
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : HEIGHT BALANCED
-----------------
*/

-- Valor muy selectivo

cat >sql3.sql
VARIABLE l_var NUMBER
exec :l_var := 3
SELECT /* SALIDA */ COUNT(*), max(deptno) FROM emp WHERE deptno=:l_var; 

-- Valor muy poco selectivo

cat >sql10.sql
VARIABLE l_var NUMBER
exec :l_var := 10
SELECT /* SALIDA */ COUNT(*), max(deptno) FROM emp WHERE deptno=:l_var; 

1.- Probamos como funciona bind peeking

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.1';

@sql3

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |    29 |    87 |     1   (0)| 00:00:01 | << Al ser selectivo Plan OK
----------------------------------------------------------------------------
*/

@sql10

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*	
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |    29 |    87 |     1   (0)| 00:00:01 | << Muy poco selectivo Plan NO OK
----------------------------------------------------------------------------		
*/

1.1- Probamos con la ejecucion primero del valor poco selectivo
		
ALTER SYSTEM FLUSH SHARED_POOL;

@sql10

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    56 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| EMP_I1 | 95000 |   278K|    56   (2)| 00:00:01 | << Fast full scan del indice es el plan optimo
--------------------------------------------------------------------------------
*/

@sql3

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    56 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| EMP_I1 | 95000 |   278K|    56   (2)| 00:00:01 | << El plan NO es optimo
--------------------------------------------------------------------------------
*/

2.- Probamos la ejecucion del Adaptive Cursor

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1';

@sql3

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |     1 |     3 |     1   (0)| 00:00:01 | << Elige el plan optimo
----------------------------------------------------------------------------
*/

@sql10

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*	
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |     1 |     3 |     1   (0)| 00:00:01 | << !!! No ha cogido el plan bueno.
----------------------------------------------------------------------------
*/

-- NOTA: Son necesarias dos ejecuciones para que cambie de plan.

@sql10

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    56 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| EMP_I1 | 95000 |   278K|    56   (2)| 00:00:01 | << Ha cogido el plan correcto.
--------------------------------------------------------------------------------
*/

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

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1';

-. Pasamos estadisticas sin crear histogramas

begin 
 dbms_stats.gather_table_stats(ownname => 'SH',tabname => 'emp', METHOD_OPT => 'FOR COLUMNS DEPTNO SIZE 1', CASCADE => TRUE);
end;
    /

SQL>exec SYS.print_table('select * from user_tab_col_statistics where table_name = ''EMP''');
/*
TABLE_NAME                    : EMP
COLUMN_NAME                   : DEPTNO
NUM_DISTINCT                  : 11
LOW_VALUE                     : 80
HIGH_VALUE                    : C10B
DENSITY                       : .0909090909090909
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 30-jan-2014 14:34:21
SAMPLE_SIZE                   : 100000
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE << Sin Histogramas
*/

@sql3

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    18 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |  9091 | 27273 |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------
*/

@sql10

select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    18 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_I1 |  9091 | 27273 |    18   (0)| 00:00:01 | << Al no tener informacion del indice los toma todos por igual
----------------------------------------------------------------------------
*/

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: