Você conhece o SQL Test Case Builder?

sql test case builder

A partir da versão 11g o Oracle Database disponibilizou um novo recurso chamado SQL Test Case Builder (TCB), essa ferramenta foi desenvolvida pensando em facilitar a vida de quem precisa pedir suporte num processo de tuning de uma instrução SQL ou pedir suporte da Oracle para um BUG relativo a instruções SQL. Com esta ferramenta pode-se gerar um pacote de informações relativos a uma determinada instrução SQL que pode ser enviado para outro servidor onde é possível criar todos os objetos envolvidos na instrução SQL e consequentemente reproduzir o problema de performance ou BUG desta instrução SQL.

Quais informações são copiadas pelo TCB

O TCB ou SQL Test Case Builder pode utilizar como entrada o SQL_ID de uma instrução SQL ou o seu texto, baseado nesta informação ele cria um script para copiar todas as informações necessárias para reproduzir a execução da instrução SQL em outro banco de dados com a mesma versão, abaixo a relação dos objetos copiados:

1) Texto da instrução SQL
2) PL/SQL functions, procedures, packages
3) Estatisticas
4) Bind variables
5) Ambiente de compilação
6) Informações de usuário (Como previlégios)
7) SQL profiles, stored outlines,e outro objectos de gerenciamento SQL
8) Metadata de todos os objetos envolvidos
9) Estatísticas do Otimizador
10) Informações do plano de execução
11) O Conteudo das tabelas (Parcial ou total)

Como utilizar o TCB

Você pode criar um TCB de duas formas:

1) A partir do Oracle Enterprise Manager utilizando o IPS (Incident Packaging Service) que permite acionar o TCB de um incident ocorrido.

2) A partir do SQLPLUS onde você pode executar um função API em PL/SQL que faz parte do pacote SQL Diagnostic (DBMS_SQLDIAG).

Neste artigo vamos utilizar a package DBMS_SQLDIAG no SQLPLUS para uma demonstração prática da utilização do TCB.

 

Demonstração prática

1) Executar uma consulta no banco de dados “A”
2) Gerar um pacote de informações desta consulta utilizando a procedure EXPORT_SQL_TESTCASE
3) Copiar arquivos gerado na etapa 2 do servidor “A” para o Servidor “B”
4) Criar o usuário TCB no banco de dados “B”
5) Importar os objetos extraídos do banco de dados “A” no banco “B”, utilizando a procedure IMPORT_SQL_TESTCASE
6) Executar a consulta no banco de dados “B”

 

1) Executar uma consulta no banco de dados “A”

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1 linha selecionada.

SQL >
SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > ALTER SESSION SET current_schema = sh;

Sessão alterada.

SQL >
SQL >
SQL > SELECT /* dbtw001 */ count(1) Qtde
  2    FROM customers  c,
  3         sales      s
  4   WHERE c.cust_id = s.cust_id
  5     AND s.quantity_sold > 1;

      QTDE
----------
         4

1 linha selecionada.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw001%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dw5t15p94q2bz            0

1 linha selecionada.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dw5t15p94q2bz, child number 0
-------------------------------------
SELECT /* dbtw001 */ count(:"SYS_B_0") Qtde   FROM customers  c,
sales      s  WHERE c.cust_id = s.cust_id    AND s.quantity_sold >
:"SYS_B_1"

Plan hash value: 2841872969

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:01.95 |    4477 |   4439 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:01.95 |    4477 |   4439 |
|   2 |   NESTED LOOPS        |              |      1 |      4 |      4 |00:00:01.95 |    4477 |   4439 |
|   3 |    PARTITION RANGE ALL|              |      1 |      4 |      4 |00:00:01.95 |    4471 |   4439 |
|*  4 |     TABLE ACCESS FULL | SALES        |     28 |      4 |      4 |00:00:01.95 |    4471 |   4439 |
|*  5 |    INDEX UNIQUE SCAN  | CUSTOMERS_PK |      4 |      1 |      4 |00:00:00.01 |       6 |      0 |
---------------------------------------------------------------------------------------------------------

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

   4 - filter("S"."QUANTITY_SOLD">:SYS_B_1)
   5 - access("C"."CUST_ID"="S"."CUST_ID")


25 linhas selecionadas.

SQL >

 

2) Gerar um pacote de informações desta consulta 

Vamos utiliza o pacote DBMS_SQLDIAG e procedure EXPORT_SQL_TESTCASE para coletar todas as informações necessárias para reproduzir a execução desta consulta em outro banco de dados.

Alguns parâmetros dessa procedure são muito importantes:

1) DIRECTORY => Informar o diretório no qual serão gerados todos os arquivos de informações necessárias para importação no banco de dados de destino.
2) SQL_ID => Informar o SQL_ID da consulta, opcionalmente você pode utilizar o parâmetro SQL_TEXT no lugar deste parâmetro, neste caso você deve informar o texto da consulta.
3) SAMPLINGPERCENT => Informar a percentagem dos dados das tabelas que serão copiados para o banco de dados destino.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL > show user
USER É "SYS"
SQL >
SQL > declare
  2    tco           clob;
  3  begin
  4    -- Export test case
  5    dbms_sqldiag.export_sql_testcase
  6    (
  7      directory           => 'DATA_PUMP_DIR',
  8      sql_id              => 'dw5t15p94q2bz',
  9      exportMetadata      => TRUE,
 10      exportData          => TRUE,
 11      samplingPercent     => 100,
 12      testcase            => tco
 13    );
 14
 15  end;
 16  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:53.43
SQL >

 

3) Copiar arquivos gerado no servidor “A” para o Servidor “B”

Nesta etapa precisamos copiar todos os arquivos gerados na etapa anterior (2) para um diretório no servidor onde fica a base destino, também conhecida como a base onde queremos reproduzir a consulta da etapa 1.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[oracle@odbsrv11 oracle]$ cd /oracle/app/11.2.0.4/db/rdbms/log/
[oracle@odbsrv11 log]$ ll
total 41660
-rw-r----- 1 oracle oinstall      116 Apr  3 12:59 dp.log
-rw-r----- 1 oracle oinstall 42577920 May 24 21:47 oratcb1_0359A49F0001dpexp.dmp
-rw-r--r-- 1 oracle oinstall     4152 May 24 21:47 oratcb1_0359A49F0001dpexp.log
-rw-r--r-- 1 oracle oinstall     4840 May 24 21:46 oratcb1_0359A49F0001dpexp.sql
-rw-r--r-- 1 oracle oinstall     4297 May 24 21:47 oratcb1_0359A49F0001dpimp.sql
-rw-r--r-- 1 oracle oinstall     1907 May 24 21:47 oratcb1_0359A49F0001main.xml
-rw-r--r-- 1 oracle oinstall     2660 May 24 21:46 oratcb1_0359A49F0001ol.xml
-rw-r--r-- 1 oracle oinstall     2768 May 24 21:46 oratcb1_0359A49F0001README.txt
-rw-r--r-- 1 oracle oinstall     1372 May 24 21:46 oratcb1_0359A49F0001sql.xml
-rw-r--r-- 1 oracle oinstall      847 May 24 21:47 oratcb1_0359A49F0001ssimp.sql
-rw-r--r-- 1 oracle oinstall       63 May 24 21:46 oratcb1_0359A49F0001ts.xml
-rw-r--r-- 1 oracle oinstall      556 May 24 21:47 oratcb1_0359A49F0001xplf.sql
-rw-r--r-- 1 oracle oinstall      956 May 24 21:47 oratcb1_0359A49F0001xplo.sql
-rw-r--r-- 1 oracle oinstall      530 May 24 21:47 oratcb1_0359A49F0001xpls.sql
-rw-r--r-- 1 oracle oinstall     5581 May 24 21:47 oratcb1_0359A49F0001xpl.txt

 

4) Criar o usuário TCB no banco de dados “B”

Antes de fazer a importação das informações no banco de dados destino, precisamos criar um usuário que receberá os objetos necessários para execução da consulta, no nosso caso o nome será TCB mas pode ser qualquer outro nome de usuário válido.

 

1
2
3
4
5
6
7
8
9
create user tcb identified by tcb default tablespace users temporary tablespace temp;

grant connect, dba, resource,  query rewrite to tcb ;

alter user tcb quota unlimited on users;

create directory TCB_IMP_DIR as '/oracle/tcb';

grant read,write on directory TCB_IMP_DIR to tcb;

 

5) Importar os objetos na base destino

Agora vamos importar todas as informações geradas na base de origem no usuário que criamos na base destino, antes de iniciar a importação certifique-se de que todos os arquivos gerados na base de origem foram copiados para o diretório criado na etapa anterior (4).

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL > conn tcb/tcb
Conectado.
SQL > show user
USER É "TCB"
SQL >
SQL > exec dbms_sqldiag.import_sql_testcase(directory => 'TCB_IMP_DIR' ,         -
>                                       filename  => 'oratcb1_0359A49F0001main.xml');

Procedimento PL/SQL concluído com sucesso.

SQL > select object_type, count(1) Qtde from user_objects group by object_type;

OBJECT_TYPE               QTDE
------------------- ----------
INDEX PARTITION            140
TABLE PARTITION             28
TABLE                        4
INDEX                       13

4 linhas selecionadas.

SQL >

 

6) Executar a consulta no banco de dados destino

Concluída a importação das informações podemos executar a mesma consulta que executamos na etapa 1, agora na base destino.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1 linha selecionada.

SQL >
SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > ALTER SESSION SET current_schema = tcb;

Sessão alterada.

SQL >
SQL >
SQL > SELECT /* dbtw001 */ count(1) Qtde
  2    FROM customers  c,
  3         sales      s
  4   WHERE c.cust_id = s.cust_id
  5     AND s.quantity_sold > 1;

      QTDE
----------
         4

1 linha selecionada.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw001%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dw5t15p94q2bz            0

1 linha selecionada.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dw5t15p94q2bz, child number 0
-------------------------------------
SELECT /* dbtw001 */ count(:"SYS_B_0") Qtde   FROM customers  c,
sales      s  WHERE c.cust_id = s.cust_id    AND s.quantity_sold >
:"SYS_B_1"

Plan hash value: 2841872969

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:00.25 |    4477 |   4440 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:00.25 |    4477 |   4440 |
|   2 |   NESTED LOOPS        |              |      1 |      4 |      4 |00:00:00.25 |    4477 |   4440 |
|   3 |    PARTITION RANGE ALL|              |      1 |      4 |      4 |00:00:00.25 |    4471 |   4439 |
|*  4 |     TABLE ACCESS FULL | SALES        |     28 |      4 |      4 |00:00:00.25 |    4471 |   4439 |
|*  5 |    INDEX UNIQUE SCAN  | CUSTOMERS_PK |      4 |      1 |      4 |00:00:00.01 |       6 |      1 |
---------------------------------------------------------------------------------------------------------

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

   4 - filter("S"."QUANTITY_SOLD">:SYS_B_1)
   5 - access("C"."CUST_ID"="S"."CUST_ID")


25 linhas selecionadas.

SQL >

 

Conclusão

Quando comparamos os planos de execução da base de origem com o da base destino observamos que são quase idênticos e este é o objetivo desta ferramenta, reproduzir as condições de execução de uma instrução SQL para que um especialista da Oracle possa analisar um determinado BUG no banco de dados ou um especialista de SQL Tuning possa analisar a execução da instrução SQL e encontrar soluções para melhoria do desempenho. Sem esta ferramenta a configuração de um banco de dados para reproduzir as mesmas condições de execução de uma instrução SQL em outro banco de dados torna-se uma tarefa extremamente onerosa.

 

Referências

https://blogs.oracle.com/optimizer/entry/oracle_keeps_closing_my_tar_because_i_cannot_provide_a_testcase_can_you_help

 

 

Mídia social

 

 

 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *