Oracle Live

06/07/2016

Cambiar el plan de ejecución de una query.

Filed under: Database,Laboratorios,Troubleshooting — mogukiller @ 12:33 pm

Cambiar el plan de una query utilizando un SQL Profile
======================================================

1. Identificar el sql_id de la query que queramos y el plan_hash que queramos utilizar.

El plan hash se puede calcular utilizando hints para forzar la utilización un plan determinado.
O se puede ver en el histórico de planes utilizados con un awr o …

2. Lanzar el script coe_xfr_sql_profile.sql

Ejemplo: @coe_xfr_sql_profile.sql 1v51hfqssag62 80137940

3. Se generará a su vez otro script con los comandos pertinentes para crear el SQL Profile y aplicárselo a esa query.

4. Al ejecutar ese script, se pasará a utilizar el SQL Profile que hemos creado para ese sql_id.

El script se puede ejecutar en cualquier bdd donde se pueda ejecutar esa query.

— Ejemplo completo
— Básicamente vamos a hacer que una tabla que naturalmente usa un índice para acceder a su contenido, pase a hacer un fulltbs
— Es un ejemplo tonto, pero donde se puede ver perfectamente la potencia de los SQL Profiles

create user prueba identified by prueba default tablespace users temporary tablespace temp;

grant connect,resource to prueba;
grant unlimited tablespace to prueba;

create table prueba.objects as select * from dba_objects;
create index prueba.objects_i1 on prueba.objects (object_id);


set autotrace traceonly

select * from prueba.objects where object_id=100;

/*
8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1619407748

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     8 |  1656 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS    |     8 |  1656 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | OBJECTS_I1 |     8 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       2235  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
					
*/

--Lanzamos la misma query con un hint de FULL para generar un plan de ejecución que haga fts

select /*+ FULL (objects) */ * from prueba.objects where object_id=100;

/*
8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 80137940 <-- Este es el plan hash que nos interesa

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     8 |  1656 |  2478   (1)| 00:00:30 |
|*  1 |  TABLE ACCESS FULL| OBJECTS |     8 |  1656 |  2478   (1)| 00:00:30 |
-----------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          2  db block gets
      11307  consistent gets
          0  physical reads
     174716  redo size
       1773  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
*/

--Sacamos los sql_id de las queries que hemos ejecutado

set autotrace off

select sql_text,sql_id from v$sql where sql_text like '%prueba.objects%';

/*

SQL_TEXT                                                                                   SQL_ID
------------------------------------------------------------------------------------------ -------------
select /*+ FULL (objects) // * from prueba.objects where object_id=100                     9ygugqguxp0q6
select * from prueba.objects where object_id=100                                           fcur1tzk0g28j <-- Este es el SQLID que interesa

*/

--Lanzamos el script con los valores deseados

@coe_xfr_sql_profile.sql fcur1tzk0g28j 80137940

/*

Execute coe_xfr_sql_profile_fcur1tzk0g28j_80137940.sql
on TARGET system in order to create a custom SQL Profile
with plan 80137940 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

*/

--Ejecutamos el nuevo script generado:
@coe_xfr_sql_profile_fcur1tzk0g28j_80137940.sql

/*

NOTES
  1. Should be run as SYSTEM or SYSDBA.
  2. User must have CREATE ANY SQL PROFILE privilege.
  3. SOURCE and TARGET systems can be the same or similar.
  4. To drop this custom SQL Profile after it has been created:
 EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_fcur1tzk0g28j_80137940');
  5. Be aware that using DBMS_SQLTUNE requires a license
 for the Oracle Tuning Pack.
  6. If you modified a SQL putting Hints in order to produce a desired
 Plan, you can remove the artifical Hints from SQL Text pieces below.
 By doing so you can create a custom SQL Profile for the original
 SQL but with the Plan captured from the modified SQL (with Hints).
 
             SIGNATURE
---------------------
  3720315143014552328


           SIGNATUREF
---------------------
 14420665977222221376


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_fcur1tzk0g28j_80137940 completed
 
 */

--A partir de este punto, cada vez que se ejecute esa query, usará el SQL Profile que le hemos pasado
--En este caso obviará el índice e irá por fts

set autotrace traceonly

select * from prueba.objects where object_id=100;

/*

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 80137940

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  7435 |  1502K|  2479   (1)| 00:00:30 |
|*  1 |  TABLE ACCESS FULL| OBJECTS |  7435 |  1502K|  2479   (1)| 00:00:30 |
-----------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL profile "coe_fcur1tzk0g28j_80137940" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          1  db block gets
       8695  consistent gets
          1  physical reads
          0  redo size
       1773  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
					
*/

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

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: