Criando e gerenciando SQL Profiles

maio 17, 2013 - Oracle Database, Performance - Tagged: alter_sql_profile, dba_sql_profiles, dbms_auto_task_admin, dbms_tune, drop_sql_profile, import_sql_profile, index, perfil, profile, SEL$, sql profile, SQLPROF$ATTR - 2 comments

https://zorangepharmacy.com/pharmacy-resources Certamente você como DBA já viu varias vezes determinadas consultas SQL se recusando a cooperar. Em outras palavras, a consulta SQL deveria utilizar o índice1 mais opta por utilizar o índice2 o que algumas vezes deixa a performance da SQL um lixo, literalmente.

https://thermocomfort.ca/store-policy/ Vários fatores podem estar provocando a seleção incorreta deste índice no plano de execução, coleta de estatísticas, parametrizações, entre outas. Contudo, alterá-las nem sempre é algo fácil pois podemos afetar diretamente a performance de outras consultas SQL assim como a performance de toda a base de dados (no caso de uma parametrização incorreta, por exemplo).

https://deanstextiles.com/neck-ties-2/ Utilizar um Hint? Sim… ou NÃO! As vezes não temos o acesso ao “código fonte” e solicitar ao fornecedor da aplicação o ajuste pode demorar.

watch Para contornar esta situação de forma rápida podemos criar um perfil SQL (SQL Profile). A abordagem de perfil SQL apareceu na versão 10g e permite a partir de então que “melhorias” sejam agregadas a uma instrução SQL sem que seu código fonte seja alterado.

source Os perfis SQL são armazenados no dicionário de dados e podem conter detalhes de comparação de cardinalidade, seletividade de predicado, parâmetros e índices específicos. Tudo para melhorar o desempenho de uma instrução SQL.

follow link Normalmente temos contato com um perfil SQL quando executamos um SQL Tuning Advisor que dentre algumas recomendações oferece a implementação de um SQL Profile para ganhos de performance (imagem abaixo).

enter site

https://vaultbr.com.br/fallout/inimigos/ Na versão 11g com o surgimento das tarefas automatizadas de manutenção do banco de dados (DBMS_AUTO_TASK_ADMIN) temos o Automatic SQL Tuning Advisor que automaticamente identifica e tenta ajustar SQLs menos performaticos aceitando a utilização de SQL Profiles caso a opção ACCEPT_SQL_PROFILES for TRUE. Mas isto não vem a caso no momento, pois vamos implementar manualmente nosso próprio SQL Profile em uma instrução SQL.

https://louldentalstudio.com/ourphilosophy/ Observem que a query abaixo esta executando em aproximadamente 2 segundos e utilizando o índice TABLE_X_IDX_2

source link SQL> set timing on SQL> select * 2 from (SELECT this_.* 3 FROM TABLE_X this_ 4 WHERE this_.IND_EMIS = 1 5 and this_.IND_STATUS = 6 6 and this_.NUM_CONTROLE = 99999999 7 and (this_.FLAG_IMP = 'N' or 8 this_.FLAG_IMP is null) 9 ORDER BY this_.NUM_SEQ asc) 10 where rownum <= 20 11 / no rows selected Elapsed: 00:00:01.98 SQL> set timing off SQL> explain plan for 2 select * 3 from (SELECT this_.* 4 FROM TABLE_X this_ 5 WHERE this_.IND_EMIS = 1 6 and this_.IND_STATUS = 6 7 and this_.NUM_CONTROLE = 99999999 8 and (this_.FLAG_IMP = 'N' or 9 this_.FLAG_IMP is null) 10 ORDER BY this_.NUM_SEQ asc) 11 where rownum <= 20 12 / Explained. SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 1526119802 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 153K| 1 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 20 | 153K| 1 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID| TABLE_X | 214 | 339K| 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | TABLE_X_IDX_2 | 214 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter("THIS_"."IND_EMIS"=1 AND ("THIS_"."FLAG_IMP"='N' OR "THIS_"."FLAG_IMP" IS NULL)) 4 - access("THIS_"."IND_STATUS"=6 AND "THIS_"."NUM_CONTROLE"=99999999) 20 rows selected.

follow link Apesar do tempo de retorno ser baixo, ele ainda pode ser muito melhor! Observem agora se eu forçar através de um hint a utilização do índice TABLE_X_IDX_1.

https://arthritisdiabetescenter.com/contact-us SQL> set timing on SQL> select * 2 from (SELECT /*+ index(this_ TABLE_X_IDX_1) */ this_.* 3 FROM TABLE_X this_ 4 WHERE this_.IND_EMIS = 1 5 and this_.IND_STATUS = 6 6 and this_.NUM_CONTROLE = 99999999 7 and (this_.FLAG_IMP = 'N' or 8 this_.FLAG_IMP is null) 9 ORDER BY this_.NUM_SEQ asc) 10 where rownum <= 20 11 / no rows selected Elapsed: 00:00:00.03 SQL> set timing off SQL> explain plan for 2 select * 3 from (SELECT /*+ index(this_ TABLE_X_IDX_1) */ this_.* 4 FROM TABLE_X this_ 5 WHERE this_.IND_EMIS = 1 6 and this_.IND_STATUS = 6 7 and this_.NUM_CONTROLE = 99999999 8 and (this_.FLAG_IMP = 'N' or 9 this_.FLAG_IMP is null) 10 ORDER BY this_.NUM_SEQ asc) 11 where rownum <= 20 12 / Explained. SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Plan hash value: 2411228134 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 153K| 2 (50)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 214 | 1647K| 2 (50)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 214 | 339K| 2 (50)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| TABLE_X | 214 | 339K| 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | TABLE_X_IDX_1 | 214 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter(ROWNUM<=20) 5 - access("THIS_"."IND_EMIS"=1 AND "THIS_"."IND_STATUS"=6 AND "THIS_"."NUM_CONTROLE"=99999999) filter("THIS_"."FLAG_IMP"='N' OR "THIS_"."FLAG_IMP" IS NULL) 21 rows selected.

enter site Perfeito, ganho de 98% no tempo de execução. Mas e agora… como forço a utilização deste índice sem alterar o código?

follow link Primeiramente vamos identificar nosso OBJECT_ALIAS que é necessário para construirmos a syntaxe correta para o SQL Profile.

enter SQL> select a.OPERATION, a.OPTIONS, a.OBJECT_NAME, a.OBJECT_ALIAS from v$sql_plan a where a.sql_id = 'b8fby5t7jb0x8'; 2 3 OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS ------------------------------ ------------------------------ ------------------------------ ------------------------------ SELECT STATEMENT COUNT STOPKEY VIEW from$_subquery$_001@SEL$1 TABLE ACCESS BY INDEX ROWID TABLE_X THIS_@SEL$2 INDEX RANGE SCAN TABLE_X_IDX_2 THIS_@SEL$2 5 rows selected.

Tramadol Buy Online Neste momento vamos transformar a hint em uma syntaxe aceita pelo perfil SQL utilizando o OBJECT_ALIAS capturado acima.

follow site Observe a diferença na sintaxe entre o hint e o profile.

Online Doctor To Prescribe Tramadol https://gardenswhisper.com/cactus-etiolation/ HINT:

enter site index(this_ TABLE_X_IDX_1)

Buy Cheap Tramadol follow link SQL_PROFILE (sqlprof_attr):

https://www.favierguitars.com/atelier-luthier INDEX(@"SEL$2" "THIS_"@"SEL$2" ("THIS_"."TABLE_X_IDX_1"))

https://maheshwariurology.com/tramadol-legal/ de outro modo podemos dizer que:

https://www.upg-corp.com/tramadol-canada-5/ INDEX(@"OBJECT_ALIAS" "TABLE_ALIAS"@"OBJECT_ALIAS" ("TABLE_ALIAS"."INDEX_NAME"))

Order Tramadol Discount Agora criando efetivamente o SQL profile:

https://www.whitehallfire.org/annual-report-for-2017/ SQL> BEGIN 2 DBMS_SQLTUNE.IMPORT_SQL_PROFILE 3 ( 4 sql_text => 'select * 5 from (SELECT this_.* 6 FROM TABLE_X this_ 7 WHERE this_.IND_EMIS = 1 8 and this_.IND_STATUS = 6 9 and this_.NUM_CONTROLE = 99999999 10 and (this_.FLAG_IMP = ''N'' or 11 this_.FLAG_IMP is null) 12 ORDER BY this_.NUM_SEQ asc) 13 where rownum <= 20', 14 profile => sqlprof_attr('INDEX(@"SEL$2" "THIS_"@"SEL$2" ("THIS_"."TABLE_X_IDX_1"))'), 15 category => 'DEFAULT', 16 name => 'PROFILE_EXEMPLO_1', 17 force_match => TRUE 18 ); 19 END; 20 / PL/SQL procedure successfully completed.

Tramadol Mastercard Overnight O atributo https://theyoopergirl.com/2016/12/batterway-corn-muffins/ force_match = true significa que o perfil SQL será utilizado para todas as declarações semelhantes, independentemente do valor de literais (se o SQL tiver alguma).

https://benincauk.co.uk/warranty/ Executando novamente a query:

https://bycebroadcast.com/tramadol-cod-5/ SQL> set timing on SQL> select * 2 from (SELECT this_.* 3 FROM TABLE_X this_ 4 WHERE this_.IND_EMIS = 1 5 and this_.IND_STATUS = 6 6 and this_.NUM_CONTROLE = 99999999 7 and (this_.FLAG_IMP = 'N' or 8 this_.FLAG_IMP is null) 9 ORDER BY this_.NUM_SEQ asc) 10 where rownum <= 20 11 / no rows selected Elapsed: 00:00:00.05 SQL> set timing off

https://markcarterproductions.com/2012/11/02/november-2012/ Para identificarmos se realmente o SQL profile esta sendo utilizado pela SQL podemos fazer um explain, logo abaixo ele traz uma Note (SQL profile “PROFILE_EXEMPLO_1” used for this statement)

Order Tramadol Online Cod Overnight SQL> explain plan for 2 select * 3 from (SELECT this_.* 4 FROM TABLE_X this_ 5 WHERE this_.IND_EMIS = 1 6 and this_.IND_STATUS = 6 7 and this_.NUM_CONTROLE = 99999999 8 and (this_.FLAG_IMP = 'N' or 9 this_.FLAG_IMP is null) 10 ORDER BY this_.NUM_SEQ asc) 11 where rownum <= 20 12 / Explained. SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- Plan hash value: 2411228134 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 153K| 2 (50)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 214 | 1647K| 2 (50)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 214 | 339K| 2 (50)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| TABLE_X | 214 | 339K| 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | TABLE_X_IDX_2 | 214 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=20) 3 - filter(ROWNUM<=20) 5 - access("THIS_"."IND_EMIS"=1 AND "THIS_"."IND_STATUS"=6 AND "THIS_"."NUM_CONTROLE"=99999999) filter("THIS_"."FLAG_IMP"='N' OR "THIS_"."FLAG_IMP" IS NULL) Note ----- - SQL profile "PROFILE_EXEMPLO_1" used for this statement 25 rows selected.

O que muitos se questionam é:

– E se meu SQL_ID mudar, o SQL Profile ainda será utilizado?

A resposta é simples. watch Depende!

Se você criou o perfil com force_match = true e for alterar apenas o valor das condições (literais) o sql_id vai mudar e o Perfil SQL ainda será utilizado. Se o force_match for false não será utilizado.

Observe o explain abaixo onde foi alterado apenas o valor da condição NUM_CONTROLE. (Utilizou o mesmo SQL Profile criado anteriormente)

SQL> explain plan for
  2  select *
  3    from (SELECT this_.*
  4            FROM TABLE_X this_
  5           WHERE this_.IND_EMIS = 1
  6             and this_.IND_STATUS = 6
  7             and this_.NUM_CONTROLE = 99999998
  8             and (this_.FLAG_IMP = 'N' or
  9                 this_.FLAG_IMP is null)
 10           ORDER BY this_.NUM_SEQ asc)
 11   where rownum <= 20
 12  /

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2411228134

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    20 |   153K|     2  (50)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |                 |       |       |            |          |
|   2 |   VIEW                         |                 |   216 |  1663K|     2  (50)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY       |                 |   216 |   342K|     2  (50)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TABLE_X         |   216 |   342K|     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | TABLE_X_IDX_1   |   216 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   5 - access("THIS_"."IND_EMIS"=1 AND "THIS_"."IND_STATUS"=6 AND
              "THIS_"."NUM_CONTROLE"=99999998)
       filter("THIS_"."FLAG_IMP"='N' OR "THIS_"."FLAG_IMP" IS NULL)

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

25 rows selected.

Agora se você alterar a query (adicionar/remover condições, por exemplo) um novo sql_id vai ser gerado e independente se o force_match for true ou false o SQL Profile não será utilizado.

Veja abaixo: (Adicionei um “and 1=1” )

SQL> explain plan for
  2  select *
  3    from (SELECT this_.*
  4            FROM TABLE_X this_
  5           WHERE this_.IND_EMIS = 1
  6             and this_.IND_STATUS = 6
  7             and this_.NUM_CONTROLE = 99999999
  8             and (this_.FLAG_IMP = 'N' or
  9                 this_.FLAG_IMP is null)
 10        and 1=1
 11           ORDER BY this_.NUM_SEQ asc)
 12   where rownum <= 20
 13  /

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1526119802

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |    20 |   153K|     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                 |       |       |            |          |
|   2 |   VIEW                        |                 |    20 |   153K|     1   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TABLE_X         |   216 |   342K|     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TABLE_X_IDX_2   |   213 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=20)
   3 - filter("THIS_"."IND_EMIS"=1 AND ("THIS_"."FLAG_IMP"='N' OR
              "THIS_"."FLAG_IMP" IS NULL))
   4 - access("THIS_"."IND_STATUS"=6 AND "THIS_"."NUM_CONTROLE"=99999999)

19 rows selected.

SQL> set timing on
SQL> select *
  2    from (SELECT this_.*
  3            FROM TABLE_X this_
  4           WHERE this_.IND_EMIS = 1
  5             and this_.IND_STATUS = 6
  6             and this_.NUM_CONTROLE = 99999999
  7             and (this_.FLAG_IMP = 'N' or
  8                 this_.FLAG_IMP is null)
  9        and 1=1
 10           ORDER BY this_.NUM_SEQ asc)
 11   where rownum <= 20
 12  /

no rows selected

Elapsed: 00:00:01.97
SQL> set timing off

Varias outras “sugestões” podem ser atribuídas em um perfil SQL, vejamos alguns exemplos:

    'BEGIN_OUTLINE_DATA'
    'IGNORE_OPTIM_EMBEDDED_HINTS'
    'OPTIMIZER_FEATURES_ENABLE(default)'
    'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')'
    'DB_VERSION(''11.2.0.2'')'
    'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')'
    'OPT_PARAM(''_optim_peek_user_binds'' ''false'')'
    'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')'
    'OPT_PARAM(''optimizer_index_cost_adj'' 10000)'
    'OPT_PARAM(''optimizer_index_caching'' 80)'
    'OUTLINE_LEAF(@"SEL$1")'
    'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DATA_OBJECT_ID"))'
    'OPT_ESTIMATE(@"UPD$1", TABLE, "TABLE_X"@"UPD$1", SCALE_ROWS=2435195312)'
    'OPT_ESTIMATE(@"SEL$3", JOIN, ("TABLE_X"@"SEL$3", "TABLE_Y"@"SEL$3"), SCALE_ROWS=290.5026466)'
    'END_OUTLINE_DATA'

OBS: Varias sugestões podem ser atribuidas em um único perfil SQL.

Visualizando a composição/detalhes de um SQL Profile:

SQL> set lines 190
SQL> col OUTLINE_HINTS for a70
SQL> SELECT created, status, sql_attr.attr_val outline_hints
  2  FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
  3  WHERE sql_profiles.signature = sql_attr.signature
  4  AND sql_profiles.name = 'PROFILE_EXEMPLO_1'
  5  ORDER BY sql_attr.attr# ASC;

CREATED     STATUS   OUTLINE_HINTS
----------- -------- ----------------------------------------------------------------------
06-MAY-2013 ENABLED  INDEX(@"SEL$2" "THIS_"@"SEL$2" ("THIS_"."TABLE_X_IDX_1"))

Habilitando e desabilitando um SQL Profile:

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_EXEMPLO_1','STATUS','DISABLED');

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select *
  2    from (SELECT this_.*
  3            FROM TABLE_X this_
  4           WHERE this_.IND_EMIS = 1
  5             and this_.IND_STATUS = 6
  6             and this_.NUM_CONTROLE = 99999999
  7             and (this_.FLAG_IMP = 'N' or
  8                 this_.FLAG_IMP is null)
  9           ORDER BY this_.NUM_SEQ asc)
 10   where rownum <= 20
 11  /

no rows selected

Elapsed: 00:00:01.91
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_EXEMPLO_1','STATUS','ENABLED');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL> select *
  2    from (SELECT this_.*
  3            FROM TABLE_X this_
  4           WHERE this_.IND_EMIS = 1
  5             and this_.IND_STATUS = 6
  6             and this_.NUM_CONTROLE = 99999999
  7             and (this_.FLAG_IMP = 'N' or
  8                 this_.FLAG_IMP is null)
  9           ORDER BY this_.NUM_SEQ asc)
 10   where rownum <= 20
 11  /

no rows selected

Elapsed: 00:00:00.04

Removendo um Perfil SQL:

 SQL> BEGIN
  2  DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'PROFILE_EXEMPLO_1');
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br