{"id":1058,"date":"2017-06-04T22:33:41","date_gmt":"2017-06-05T01:33:41","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1058"},"modified":"2019-11-11T18:51:41","modified_gmt":"2019-11-11T21:51:41","slug":"voce-conhece-o-sql-test-case-builder","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/voce-conhece-o-sql-test-case-builder\/","title":{"rendered":"Voc\u00ea conhece o SQL Test Case Builder?"},"content":{"rendered":"

A partir da vers\u00e3o 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\u00e7\u00e3o SQL ou pedir suporte da Oracle para um BUG relativo a instru\u00e7\u00f5es SQL. Com esta ferramenta pode-se gerar um pacote de informa\u00e7\u00f5es relativos a uma determinada instru\u00e7\u00e3o SQL que pode ser enviado para outro servidor onde \u00e9 poss\u00edvel criar todos os objetos envolvidos na instru\u00e7\u00e3o SQL e consequentemente reproduzir o problema de performance ou BUG desta instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Quais informa\u00e7\u00f5es s\u00e3o copiadas pelo TCB<\/span><\/h2>\n

O TCB ou SQL Test Case Builder pode utilizar como entrada o SQL_ID de uma instru\u00e7\u00e3o SQL ou o seu texto, baseado nesta informa\u00e7\u00e3o ele cria um script para copiar todas as informa\u00e7\u00f5es necess\u00e1rias para reproduzir a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL em outro banco de dados com a mesma vers\u00e3o, abaixo a rela\u00e7\u00e3o dos objetos copiados:<\/span><\/p>\n

1) Texto da instru\u00e7\u00e3o SQL<\/span>
\n2) PL\/SQL functions, procedures, packages<\/span>
\n3) Estatisticas<\/span>
\n4) Bind variables<\/span>
\n5) Ambiente de compila\u00e7\u00e3o<\/span>
\n6) Informa\u00e7\u00f5es de usu\u00e1rio (Como previl\u00e9gios)<\/span>
\n7) SQL profiles, stored outlines,e outro objectos de gerenciamento SQL<\/span>
\n8) Metadata de todos os objetos envolvidos<\/span>
\n9) Estat\u00edsticas do Otimizador<\/span>
\n10) Informa\u00e7\u00f5es do plano de execu\u00e7\u00e3o<\/span>
\n11) O Conteudo das tabelas (Parcial ou total)<\/span><\/p>\n

Como utilizar o TCB<\/span><\/h2>\n

Voc\u00ea pode criar um TCB de duas formas:<\/span><\/p>\n

1) A partir do Oracle Enterprise Manager utilizando o IPS (Incident Packaging Service) que permite acionar o TCB de um incident ocorrido.<\/span><\/p>\n

2) A partir do SQLPLUS onde voc\u00ea pode executar um fun\u00e7\u00e3o API em PL\/SQL que faz parte do pacote SQL Diagnostic (DBMS_SQLDIAG).<\/span><\/p>\n

Neste artigo vamos utilizar a package DBMS_SQLDIAG no SQLPLUS para uma demonstra\u00e7\u00e3o pr\u00e1tica da utiliza\u00e7\u00e3o do TCB.<\/span><\/p>\n

 <\/p>\n

Demonstra\u00e7\u00e3o pr\u00e1tica<\/span><\/h2>\n

1) Executar uma consulta no banco de dados “A”<\/span>
\n2) Gerar um pacote de informa\u00e7\u00f5es desta consulta utilizando a procedure EXPORT_SQL_TESTCASE<\/span>
\n3) Copiar arquivos gerado na etapa 2 do servidor “A” para o Servidor “B”<\/span>
\n4) Criar o usu\u00e1rio TCB no banco de dados “B”<\/span>
\n5) Importar\u00a0os objetos extra\u00eddos do banco de dados “A” no banco “B”, utilizando a procedure IMPORT_SQL_TESTCASE<\/span>
\n6) Executar a consulta no banco de dados “B”<\/span><\/p>\n

 <\/p>\n

1) Executar uma consulta no banco de dados “A”<\/span><\/h3>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET current_schema =<\/span> sh;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw001 *\/<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> Qtde
\n  2<\/span>    FROM customers  c,<\/span>
\n  3<\/span>         sales      s
\n  4<\/span>   WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id
\n  5<\/span>     AND s.<\/span>quantity_sold ><\/span> 1<\/span>;<\/span>
\n
\n      QTDE
\n----------<\/span>
\n         4<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndw5t15p94q2bz            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  dw5t15p94q2bz,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw001 *\/<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> Qtde   FROM customers  c,<\/span>
\nsales      s  WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id    AND s.<\/span>quantity_sold ><\/span>
\n:<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2841872969<\/span>
\n
\n---------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.95<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>       |<\/span>              |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.95<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.95<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.95<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.95<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>    INDEX UNIQUE SCAN  |<\/span> CUSTOMERS_PK |<\/span>      4<\/span> |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>      0<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   4<\/span> -<\/span> filter(<\/span>"S"<\/span>.<\/span>"QUANTITY_SOLD"<\/span>>:<\/span>SYS_B_1)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"CUST_ID"<\/span>)<\/span>
\n
\n
\n25<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Gerar um pacote de informa\u00e7\u00f5es desta consulta\u00a0<\/span><\/h3>\n

Vamos utiliza o pacote DBMS_SQLDIAG e procedure EXPORT_SQL_TESTCASE para coletar todas as informa\u00e7\u00f5es necess\u00e1rias para reproduzir a execu\u00e7\u00e3o desta consulta em outro banco de dados.<\/span><\/p>\n

Alguns par\u00e2metros dessa procedure s\u00e3o muito importantes:<\/span><\/p>\n

1) DIRECTORY =><\/strong> Informar o diret\u00f3rio no qual ser\u00e3o gerados todos os arquivos de informa\u00e7\u00f5es necess\u00e1rias para importa\u00e7\u00e3o no banco de dados de destino.<\/span>
\n2) SQL_ID =><\/strong> Informar o SQL_ID da consulta, opcionalmente voc\u00ea pode utilizar o par\u00e2metro SQL_TEXT<\/strong> no lugar deste par\u00e2metro, neste caso voc\u00ea deve informar o texto da consulta.<\/span>
\n3) SAMPLINGPERCENT =><\/strong> Informar a percentagem dos dados das tabelas que ser\u00e3o copiados para o banco de dados destino.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<\/div><\/td>
SQL ><\/span> show user
\nUSER \u00c9 "SYS"<\/span>
\nSQL ><\/span>
\nSQL ><\/span> declare<\/span>
\n  2<\/span>    tco           clob;<\/span>
\n  3<\/span>  begin
\n  4<\/span>    --<\/span> Export test case<\/span>
\n  5<\/span>    dbms_sqldiag.<\/span>export_sql_testcase
\n  6<\/span>    (<\/span>
\n  7<\/span>      directory           =><\/span> 'DATA_PUMP_DIR'<\/span>,<\/span>
\n  8<\/span>      sql_id              =><\/span> 'dw5t15p94q2bz'<\/span>,<\/span>
\n  9<\/span>      exportMetadata      =><\/span> TRUE<\/span>,<\/span>
\n 10<\/span>      exportData          =><\/span> TRUE<\/span>,<\/span>
\n 11<\/span>      samplingPercent     =><\/span> 100<\/span>,<\/span>
\n 12<\/span>      testcase            =><\/span> tco
\n 13<\/span>    )<\/span>;<\/span>
\n 14<\/span>
\n 15<\/span>  end<\/span><\/a>;<\/span>
\n 16<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>53.43<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Copiar arquivos gerado no servidor “A” para o Servidor “B”<\/span><\/h3>\n

Nesta etapa precisamos copiar todos os arquivos gerados na etapa anterior (2) para um diret\u00f3rio no servidor onde fica a base destino, tamb\u00e9m conhecida como a base onde queremos reproduzir a consulta da etapa 1.<\/span><\/p>\n

 <\/p>\n

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

 <\/p>\n

4) Criar o usu\u00e1rio TCB no banco de dados “B”<\/span><\/h3>\n

Antes de fazer a importa\u00e7\u00e3o das informa\u00e7\u00f5es no banco de dados destino, precisamos criar um usu\u00e1rio que receber\u00e1 os objetos necess\u00e1rios para execu\u00e7\u00e3o da consulta, no nosso caso o nome ser\u00e1 TCB mas pode ser qualquer outro nome de usu\u00e1rio v\u00e1lido.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
<\/div><\/td>
create user tcb identified by tcb default<\/span> tablespace users temporary tablespace temp;<\/span>
\n
\ngrant connect,<\/span> dba,<\/span> resource,<\/span>  query rewrite to tcb ;<\/span>
\n
\nalter user tcb quota unlimited on users;<\/span>
\n
\ncreate directory TCB_IMP_DIR as<\/span> '\/oracle\/tcb'<\/span>;<\/span>
\n
\ngrant read,<\/span>write on directory TCB_IMP_DIR to tcb;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

5) Importar os objetos na base destino<\/span><\/h3>\n

Agora vamos importar todas as informa\u00e7\u00f5es geradas na base de origem no usu\u00e1rio que criamos na base destino, antes de iniciar a importa\u00e7\u00e3o certifique-se de que todos os arquivos gerados na base de origem foram copiados para o diret\u00f3rio criado na etapa anterior (4).<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<\/div><\/td>
SQL ><\/span> conn tcb\/<\/span>tcb
\nConectado.<\/span>
\nSQL ><\/span> show user
\nUSER \u00c9 "TCB"<\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_sqldiag.<\/span>import_sql_testcase(<\/span>directory =><\/span> 'TCB_IMP_DIR'<\/span> ,<\/span>         -<\/span>
\n><\/span>                                       filename  =><\/span> 'oratcb1_0359A49F0001main.xml'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> select object_type,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> Qtde from user_objects group by object_type;<\/span>
\n
\nOBJECT_TYPE               QTDE
\n-------------------<\/span> ----------<\/span>
\nINDEX PARTITION            140<\/span>
\nTABLE PARTITION             28<\/span>
\nTABLE                        4<\/span>
\nINDEX                       13<\/span>
\n
\n4<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

6) Executar a consulta no banco de dados destino<\/span><\/h3>\n

Conclu\u00edda\u00a0a importa\u00e7\u00e3o das informa\u00e7\u00f5es podemos executar a mesma consulta que executamos na etapa 1, agora na base destino.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET current_schema =<\/span> tcb;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw001 *\/<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> Qtde
\n  2<\/span>    FROM customers  c,<\/span>
\n  3<\/span>         sales      s
\n  4<\/span>   WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id
\n  5<\/span>     AND s.<\/span>quantity_sold ><\/span> 1<\/span>;<\/span>
\n
\n      QTDE
\n----------<\/span>
\n         4<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndw5t15p94q2bz            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  dw5t15p94q2bz,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw001 *\/<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> Qtde   FROM customers  c,<\/span>
\nsales      s  WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id    AND s.<\/span>quantity_sold ><\/span>
\n:<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2841872969<\/span>
\n
\n---------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation             |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n---------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT      |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    4477<\/span> |<\/span>   4440<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>       |<\/span>              |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    4477<\/span> |<\/span>   4440<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    4477<\/span> |<\/span>   4440<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.25<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>    INDEX UNIQUE SCAN  |<\/span> CUSTOMERS_PK |<\/span>      4<\/span> |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>      1<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   4<\/span> -<\/span> filter(<\/span>"S"<\/span>.<\/span>"QUANTITY_SOLD"<\/span>>:<\/span>SYS_B_1)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>=<\/span>"S"<\/span>.<\/span>"CUST_ID"<\/span>)<\/span>
\n
\n
\n25<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Quando comparamos os planos de execu\u00e7\u00e3o da base de origem com o da base destino observamos que s\u00e3o quase id\u00eanticos e este \u00e9 o objetivo desta ferramenta, reproduzir as condi\u00e7\u00f5es de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o 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\u00e7\u00e3o da instru\u00e7\u00e3o SQL e encontrar solu\u00e7\u00f5es para melhoria do desempenho. Sem esta ferramenta a configura\u00e7\u00e3o de um banco de dados para reproduzir as mesmas condi\u00e7\u00f5es de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL em outro banco de dados torna-se uma tarefa extremamente onerosa.<\/span><\/p>\n

 <\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/blogs.oracle.com\/optimizer\/entry\/oracle_keeps_closing_my_tar_because_i_cannot_provide_a_testcase_can_you_help<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

A partir da vers\u00e3o 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\u00e7\u00e3o SQL ou pedir suporte da Oracle para um BUG relativo a instru\u00e7\u00f5es SQL. Com esta ferramenta pode-se gerar um pacote de<\/p>\n","protected":false},"author":2,"featured_media":1057,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[168,166,167,15],"class_list":["post-1058","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tuning-tools","tag-dbms_sqldiag","tag-sql-test-case-builder","tag-tcb","tag-tuning-tools"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1058","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=1058"}],"version-history":[{"count":27,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1058\/revisions"}],"predecessor-version":[{"id":2085,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1058\/revisions\/2085"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1057"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1058"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}