Esta semana que he estado probando lo de Adaptive Cursor Sharing mencionado en Mas alla de Bind Peeking. Hace cosas raras, hay que estudiarlo mas a fondo pero funciona, ahora lo veremos.
Generamos datos para tener una distribucion de datos no-uniforme:
SQL> @create
SQL> drop table t1;
Table dropped.
SQL>
SQL> create table t1
2 (
3 id number,
4 text1 varchar2(20),
5 text2 varchar2(20),
6 state number
7 );
Table created.
SQL>
SQL> insert into t1
2 with tdata as
3 (select rownum id
4 from all_objects
5 where rownum <= 1000)
6 select rownum,
7 dbms_random.string ('u', 20),
8 dbms_random.string ('u', 20),
9 case
10 when rownum/100000 create index t1_i1
2 on t1(state);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('LSC', 'T1', ESTIMATE_PERCENT => 20, -
> METHOD_OPT => 'FOR COLUMNS STATE SIZE 10', CASCADE => TRUE); -- PASAR HISTOGRAMS PARA QUE PODAMOS USAR ESTA FUNCIONALIDAD
PL/SQL procedure successfully completed.
SQL>
SQL> select state, count(*) cnt_per_state, (count(*)*100)/nr state_percent
2 from t1, (select max(id) nr
3 from t1)
4 group by state, nr;
STATE CNT_PER_STATE STATE_PERCENT
---------- ------------- -------------
2 10 .01
0 10 .01
3 10 .01
8 10 .01
1 10 .01
10 99900 99.9
5 10 .01
4 10 .01
7 10 .01
9 10 .01
6 10 .01
11 rows selected.
Hemos generado unos datos que teoricamente utilizaremos indice para esos STATE que no sean el 10. Pues bien a principio esto era muy confuso porque no importaba el valor el caso es que nunca dejaba de usar el indice, incluso para el STATE 10 (deberia hacer un FTS). La prueba era usando esta query y pasando los valores del 0 a 10 a la bind variable :STATE, hasta llegar las 11 ejecuciones.
set autotrace trace exp stat
var state number
exec :state := 0
select /* ACS-LSCC */
id, state, count(*)
from t1
where state = :state
group by id, state;
pause Press enter to continue
exec :state := 1
select /* ACS-LSCC */
id, state, count(*)
from t1
where state = :state
group by id, state;
pause Press enter to continue
exec :state := 2
select /* ACS-LSCC */
id, state, count(*)
from t1
where state = :state
group by id, state;
pause Press enter to continue
etc etc ....................... hasta 10
y durante cada ejecucion en otra sesion monitorizando v$sqlstats con esta query
select /* ACS-QRY */
sql_fulltext, sql_id, parse_calls, executions, version_count, invalidations
from v$sqlstats
where sql_fulltext like '%ACS-LSC%'
and sql_fulltext not like '%ACS-QRY%'
El caso es que el VERSION_COUNT siempre se quedaba en uno, es decir que no generaba mas que un plan de ejecucion, y me pregunto pero esto funciona? Hasta que le di la vuelta a las variables, en vez de pasar del 0 a 10 del 10 a 0, y magicamente a partir del valor 8 (el 9 ni se inmutaba) empieza a generar otro plan, el 7 generaba otro, el 6 otro pero el 5, 4 no han generado ningunplan nuevo hasta el STATE 3. Curioso para 11 valores donde 10 son iguales me ha generado 6 planes de ejecucion.
Estado final usando los valores del 10 al 0
SQL_FULLTEXT SQL_ID PARSE_CALLS EXECUTIONS VERSION_COUNT INVALIDATIONS
---------------------------------------------------------------- ------------- ----------- ---------- ------------- -------------
select /* ACS-LSCC */ 1pq13qpqfza6x 11 11 6 0
id, state, count(*)
from t1
where state = :state
Y los 6 planes de ejecucion donde solo voy a demostrar 2 porque el resto son iguales pero con diferente coste en Ilas operaciones de NDEX RANGE SCAN y TABLE ACCESS BY INDEX ROWID:
SQL> @XPLAN
SQL> select *
2 from table(dbms_xplan.display_cursor('1pq13qpqfza6x', 0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 1pq13qpqfza6x, child number 0
-------------------------------------
select /* ACS-LSCC */ id, state, count(*) from t1 where state
= :state group by id, state
Plan hash value: 136660032
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 245 (100)| |
| 1 | HASH GROUP BY | | 1 | 16 | 245 (3)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| T1 | 99998 | 1562K| 240 (1)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE"=:STATE)
SQL> select *
2 from table(dbms_xplan.display_cursor('1pq13qpqfza6x', 1));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 1pq13qpqfza6x, child number 1
-------------------------------------
select /* ACS-LSCC */ id, state, count(*) from t1 where state
= :state group by id, state
Plan hash value: 2629508439
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | HASH GROUP BY | | 1 | 16 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 80 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 5 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STATE"=:STATE)
La verdad es que no entiendo muy bien lo de 6 planes, pero pensando bien puede ser asi es ACS, se adapta a los diferentes valores y logicamente no va a parar con dos planes porque podria tener perfectamente 2,3 o 4 valores que necesiten FTS y no ir por el indice. Por ejemplo si tuviesemos esta distribucion de datos es logico hacer bind peeking cada dos por tres.
STATE CNT_PER_STATE STATE_PERCENT
---------- ------------- -------------
0 10000 10
1 10 .01
2 15000 15
3 10 .01
4 10 .01
5 22000 22
6 10 .01
7 10 .01
8 10 .01
9 15000 15
10 37940 37.94
El caso es que esto parece un arma de doble filo ya que dependiendo de los valores que hay en el campo podriamos terminar con muchos cursores hijos. Habra que subir el shared pool cuando upgradeamos a 11g ó aumentar MEMORY_TARGET, MEMORY_MAX_TARGET si vamos a usar el Automatic Memory Management (AMM).
De todos modos si no generamos ningun tipo de histogramas esto no supondria ningun problema.
Y como dije en otro post que pasaria con PL/SQL? Usamos el siguien Procedure para ver lo que pasa.
create or replace procedure LSC$ASC
as
l_id number;
l_state number;
l_cnt number;
begin
for i in reverse 1..10
loop
select /* ACS-LSC */
id, state, count(*)
into l_id, l_state, l_cnt
from t1
where state = i
and rownum < 2
group by id, state;
end loop;
end;
/
resultado
SQL_FULLTEXT SQL_ID PARSE_CALLS EXECUTIONS VERSION_COUNT INVALIDATIONS
-------------------------------------------------------------------------------- ------------- ----------- ---------- ------------- -------------
SELECT ID, STATE, COUNT(*) FROM T1 WHERE STATE = :B1 AND ROWNUM < 2 GROUP BY ID, 52r38wma52qbp 1 10 1 0
Como sospechaba, como PL/SQL no parsea mas que una vez (por eso nos gusta PL/SQL) solamente genera un plan de ejecucion.
Finalmente lo de que no genera planes diferentes si pasamos del 1 a 10 sospecho que es porque despues de varios intentos el CBO ve que el plan sigue siendo el mismo y asume que no es necesario generar planes diferentes. Lo tendre que probarlo.
). La aplicacion esta hecha por J2EE sobre Oracle 9iR2 y como servidor de aplicaciones Bea Weblogic. Se veian queries muy simples contra tablas correctamente indexados realizando Full Table Scan, no era trivial porque lanzaba las mismas queries desde SQLPLUS y me iba de fabula asi que como casi siempre me acorde del bind peeking (los malditos!) pero despues de revisar unas 6 docenas de queries donde algunas no usaban bind variables y tenian el mismo problema de ir rapido en SQLPLUS y lento desde la aplicacion.