{"id":1031,"date":"2017-05-01T20:44:18","date_gmt":"2017-05-01T23:44:18","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1031"},"modified":"2019-10-10T23:02:37","modified_gmt":"2019-10-11T02:02:37","slug":"sql-monitor-como-monitorar-consultas-rapidas","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-monitor-como-monitorar-consultas-rapidas\/","title":{"rendered":"SQL Monitor: Como monitorar consultas r\u00e1pidas?"},"content":{"rendered":"

A partir da vers\u00e3o 11g o Oracle Database disponibiliza um recurso fant\u00e1stico chamado SQL Monitor que possibilita a visualiza\u00e7\u00e3o de uma infinidade de informa\u00e7\u00f5es sobre a execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, tanto para instru\u00e7\u00f5es encerradas\u00a0como para instru\u00e7\u00f5es que ainda est\u00e3o em execu\u00e7\u00e3o, \u00e9 poss\u00edvel acessar esse recurso de forma gr\u00e1fica atrav\u00e9s do Oracle Cloud Control e\u00a0SQLDeveloper ou em formato texto atrav\u00e9s da package DBMS_SQLTUNE.<\/span><\/p>\n

Para n\u00e3o sobrecarregar o banco de dados, o SQL Monitor s\u00f3 captura informa\u00e7\u00f5es de instru\u00e7\u00f5es que demoram mais 5 segundos ou que executam na modalidade paralela. Neste artigo vamos mostrar na pr\u00e1tica como utilizar este recurso fant\u00e1stico para analisar um consulta que demora menos de 5 segundos. No nosso exemplo pr\u00e1tico vamos utilizar a package DBMS_SQLTUNE para visualizar as informa\u00e7\u00f5es capturadas pelo SQL Monitor.<\/span><\/p>\n

Pr\u00e9-requisitos para utilizar SQL Monitor<\/span><\/h1>\n

1) Para utilizar o SQL Monitor \u00e9 preciso que a base tenha a licen\u00e7a das “OPTIONS DIAGNOSTIC PACK AND TUNING PACK<\/span><\/strong>“<\/span>
\n2) O par\u00e2metro STATISTICS_LEVEL deve estar configurado como TYPICAL ou ALL<\/span>
\n3) O par\u00e2metro CONTROL_MANAGEMENT_PACK_ACCESS deve estar configurado como DIAGNOSTIC+TUNING<\/span><\/p>\n

Demostra\u00e7\u00e3o pr\u00e1tica<\/span><\/h1>\n

As pr\u00f3ximas etapas desse artigo mostram como podemos for\u00e7ar a captura de uma instru\u00e7\u00e3o SQL pelo SQL Monitor:<\/span><\/p>\n

1) O SQL Monitor n\u00e3o captura instru\u00e7\u00f5es SQL com tempo inferior a 5 segundos<\/span><\/strong><\/p>\n

\u00a0 1.1) Execu\u00e7\u00e3o de uma consulta com tempo de execu\u00e7\u00e3o inferior a 5 segundos<\/span>
\n\u00a0 1.2) Verificar se SQL Monitor capturou a consulta<\/span><\/p>\n

2) For\u00e7ar o SQL Monitor capturar a instru\u00e7\u00e3o SQL<\/span><\/strong><\/p>\n

\u00a0 2.1) Executar a mesma consulta com HINT MONITOR<\/span>
\n\u00a0 2.2) Verificar se SQL Monitor capturou a consulta<\/span>
\n\u00a0 2.3) Emitir o relat\u00f3rio do SQL Monitor<\/span><\/p>\n

3) For\u00e7ar a captura do SQL Monitor sem alterar a instru\u00e7\u00e3o SQL<\/span><\/strong><\/p>\n

\u00a0 3.1) Executar a mesma consulta <\/span>
\n\u00a0 3.2) Gerar uma SQL Profile para for\u00e7ar a captura SQL Monitor <\/span>
\n\u00a0 3.3) Executar a consulta para captura pelo SQL Monitor<\/span>
\n\u00a0 3.4) Emitir o relat\u00f3rio do SQL Monitor<\/span><\/p>\n

1) O SQL Monitor n\u00e3o captura instru\u00e7\u00f5es SQL com tempo inferior a 5 segundos<\/span><\/h1>\n

1.1) Execu\u00e7\u00e3o de uma consulta com tempo de execu\u00e7\u00e3o inferior a 5 segundos<\/span><\/h2>\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
<\/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
\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
\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
\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.29<\/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.29<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.29<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.29<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.29<\/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

1.2) Verificar se SQL Monitor capturou a consulta<\/span><\/h2>\n
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<\/div><\/td>
SQL ><\/span> select sid,<\/span>
\n  2<\/span>         sql_id,<\/span>
\n  3<\/span>         sql_exec_id,<\/span>
\n  4<\/span>         to_char(<\/span>sql_exec_start,<\/span>'DD-Mon-YY HH24:MI:SS'<\/span>)<\/span> sql_exec_start,<\/span>
\n  5<\/span>         sql_plan_hash_value plan_hash_value,<\/span>
\n  6<\/span>         elapsed_time\/<\/span>1000000<\/span> etime,<\/span>
\n  7<\/span>         buffer_gets,<\/span>
\n  8<\/span>         disk_reads
\n  9<\/span>    from v$sql_monitor<\/span>
\n 10<\/span>   where sql_id =<\/span> 'dw5t15p94q2bz'<\/span>;<\/span>
\n
\nn\u00e3o h\u00e1 linhas selecionadas
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Como podemos observar o SQL Monitor n\u00e3o registrou informa\u00e7\u00f5es dessa consulta.<\/span><\/p>\n

2) For\u00e7ar o SQL Monitor capturar a instru\u00e7\u00e3o SQL<\/span><\/h1>\n

2.1) Executar a mesma consulta com HINT MONITOR<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/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 \/*+ MONITOR  *\/<\/span> \/* dbtw002 *\/<\/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
\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 '%dbtw002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n5q107s0svt6rm            0<\/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  5q107s0svt6rm,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/*+ MONITOR  *\/<\/span> \/* dbtw002 *\/<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> Qtde   FROM
\ncustomers  c,<\/span>        sales      s  WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id    AND
\ns.<\/span>quantity_sold ><\/span> :<\/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.46<\/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.46<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.46<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.46<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.46<\/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

2.2) Verificar se SQL Monitor capturou a consulta<\/span><\/h2>\n
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> select sid,<\/span>
\n  2<\/span>         session_serial#,
\n<\/span>  3<\/span>         sql_id,<\/span>
\n  4<\/span>         sql_exec_id,<\/span>
\n  5<\/span>         to_char(<\/span>sql_exec_start,<\/span>'DD-Mon-YY HH24:MI:SS'<\/span>)<\/span> sql_exec_start,<\/span>
\n  6<\/span>         sql_plan_hash_value plan_hash_value,<\/span>
\n  7<\/span>         elapsed_time\/<\/span>1000000<\/span> etime,<\/span>
\n  8<\/span>         buffer_gets,<\/span>
\n  9<\/span>         disk_reads
\n 10<\/span>    from v$sql_monitor<\/span>
\n 11<\/span>   where sql_id =<\/span> '5q107s0svt6rm'<\/span>;<\/span>
\n
\n       SID SESSION_SERIAL# SQL_ID        SQL_EXEC_ID SQL_EXEC_START       PLAN_HASH_VALUE      ETIME BUFFER_GETS DISK_READS
\n<\/span>----------<\/span> ---------------<\/span> -------------<\/span> -----------<\/span> --------------------<\/span> ---------------<\/span> ----------<\/span> -----------<\/span> ----------<\/span>
\n      1290<\/span>            2327<\/span> 5q107s0svt6rm    33554432<\/span> 24<\/span>-<\/span>Abr-<\/span>17<\/span> 20<\/span>:<\/span>42<\/span>:<\/span>11<\/span>        2841872969<\/span>   1<\/span>,<\/span>467301<\/span>        4479<\/span>        155<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

2.3) Emitir o relat\u00f3rio do SQL Monitor<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> set long 999999999<\/span>
\nSQL ><\/span> set lines 280<\/span>
\nSQL ><\/span> col report for<\/span> a279
\nSQL ><\/span> select DBMS_SQLTUNE.<\/span>REPORT_SQL_MONITOR(<\/span>session_id<\/span><\/a>=><\/span> 1290<\/span>,<\/span> session_serial=><\/span> 2327<\/span>,<\/span> sql_id=><\/span> '5q107s0svt6rm'<\/span>,<\/span> sql_exec_id=><\/span> '33554432'<\/span>,<\/span> report_level=><\/span>'ALL'<\/span>)<\/span> as<\/span> report from dual;<\/span>
\n
\nREPORT
\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL Monitoring Report
\n
\nSQL Text
\n------------------------------<\/span>
\nSELECT \/*+ MONITOR *\/<\/span> \/* dbtw002 *\/<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> Qtde FROM customers c,<\/span> sales s WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id AND s.<\/span>quantity_sold ><\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nGlobal<\/span> Information
\n------------------------------<\/span>
\n Status              :<\/span>  DONE (<\/span>ALL ROWS)<\/span>
\n Instance ID         :<\/span>  2<\/span>
\n Session             :<\/span>  VALTER (<\/span>1290<\/span>:<\/span>2327<\/span>)<\/span>
\n SQL ID              :<\/span>  5q107s0svt6rm
\n SQL Execution ID    :<\/span>  33554432<\/span>
\n Execution Started   :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 20<\/span>:<\/span>42<\/span>:<\/span>11<\/span>
\n First Refresh Time<\/span><\/a>  :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 20<\/span>:<\/span>42<\/span>:<\/span>11<\/span>
\n Last Refresh Time<\/span><\/a>   :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 20<\/span>:<\/span>42<\/span>:<\/span>12<\/span>
\n Duration            :<\/span>  1s
\n Module\/<\/span>Action       :<\/span>  SQL*<\/span>Plus\/-<\/span>
\n Service             :<\/span>  ODBSRV01
\n Program             :<\/span>  sqlplus.<\/span>exe
\n Fetch Calls         :<\/span>  1<\/span>
\n
\nBinds
\n========================================================================================================================<\/span>
\n|<\/span>   Name   |<\/span> Position |<\/span>  Type  |<\/span>                                         Value                                         |<\/span>
\n========================================================================================================================<\/span>
\n|<\/span> :<\/span>SYS_B_1 |<\/span>        2<\/span> |<\/span> NUMBER |<\/span> 1<\/span>                                                                                     |<\/span>
\n========================================================================================================================<\/span>
\n
\nGlobal<\/span> Stats
\n=========================================================================================<\/span>
\n|<\/span> Elapsed |<\/span>   Cpu   |<\/span>    IO    |<\/span> Application |<\/span>  Other   |<\/span> Fetch |<\/span> Buffer |<\/span> Read |<\/span> Read  |<\/span>
\n|<\/span> Time<\/span><\/a>(<\/span>s)<\/span> |<\/span> Time<\/span><\/a>(<\/span>s)<\/span> |<\/span> Waits(<\/span>s)<\/span> |<\/span>  Waits(<\/span>s)<\/span>   |<\/span> Waits(<\/span>s)<\/span> |<\/span> Calls |<\/span>  Gets  |<\/span> Reqs |<\/span> Bytes |<\/span>
\n=========================================================================================<\/span>
\n|<\/span>    1.47<\/span> |<\/span>    0.04<\/span> |<\/span>     1.40<\/span> |<\/span>        0.01<\/span> |<\/span>     0.02<\/span> |<\/span>     1<\/span> |<\/span>   4479<\/span> |<\/span>  155<\/span> |<\/span>  35MB |<\/span>
\n=========================================================================================<\/span>
\n
\nSQL Plan Monitoring Details (<\/span>Plan Hash<\/span><\/a> Value=<\/span>2841872969<\/span>)<\/span>
\n=========================================================================================================================================================<\/span>
\n|<\/span> Id |<\/span>        Operation        |<\/span>     Name     |<\/span>  Rows   |<\/span> Cost |<\/span>   Time<\/span><\/a>    |<\/span> Start  |<\/span> Execs |<\/span>   Rows   |<\/span> Read |<\/span> Read  |<\/span> Activity |<\/span>   Activity Detail    |<\/span>
\n|<\/span>    |<\/span>                         |<\/span>              |<\/span> (<\/span>Estim)<\/span> |<\/span>      |<\/span> Active(<\/span>s)<\/span> |<\/span> Active |<\/span>       |<\/span> (<\/span>Actual)<\/span> |<\/span> Reqs |<\/span> Bytes |<\/span>   (<\/span>%<\/span>)<\/span>    |<\/span>     (<\/span># samples)      |
\n<\/span>=========================================================================================================================================================<\/span>
\n|<\/span>  0<\/span> |<\/span> SELECT STATEMENT        |<\/span>              |<\/span>         |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        1<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  1<\/span> |<\/span>   SORT<\/span><\/a> AGGREGATE<\/span><\/a>        |<\/span>              |<\/span>       1<\/span> |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        1<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  2<\/span> |<\/span>    NESTED LOOPS         |<\/span>              |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  3<\/span> |<\/span>     PARTITION RANGE<\/span><\/a> ALL |<\/span>              |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  4<\/span> |<\/span>      TABLE ACCESS FULL  |<\/span> SALES        |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>    28<\/span> |<\/span>        4<\/span> |<\/span>  155<\/span> |<\/span>  35MB |<\/span>   100.00<\/span> |<\/span> direct path read (<\/span>1<\/span>)<\/span> |<\/span>
\n|<\/span>  5<\/span> |<\/span>     INDEX UNIQUE SCAN   |<\/span> CUSTOMERS_PK |<\/span>       1<\/span> |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     4<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n=========================================================================================================================================================<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Com a utiliza\u00e7\u00e3o do hint MONITOR for\u00e7amos a captura das informa\u00e7\u00f5es da consulta pelo SQL Monitor o que permite a emiss\u00e3o do relat\u00f3rio da consulta.<\/span><\/p>\n

No ambiente de trabalho muitas vezes a reprodu\u00e7\u00e3o de uma consulta e seu plano de execu\u00e7\u00e3o \u00e9 uma tarefa relativamente complexa e isso dificulta a utiliza\u00e7\u00e3o do HINT como recurso para for\u00e7ar a captura da instru\u00e7\u00e3o pelo SQL Monitor, nesses casos podemos utilizar a solu\u00e7\u00e3o a seguir.<\/span><\/p>\n

3) For\u00e7ar a captura do SQL Monitor sem alterar a instru\u00e7\u00e3o SQL<\/span><\/h1>\n

3.1) Executar a mesma consulta<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/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 \/* dbtw003 *\/<\/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
\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 '%dbtw003%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ngb0h2ra62zg18            0<\/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  gb0h2ra62zg18,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw003 *\/<\/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.54<\/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.54<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.54<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.54<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.54<\/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

3.2) Gerar uma SQL Profile para for\u00e7ar a captura SQL Monitor<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> set serveroutput on size 9999<\/span>
\nSQL ><\/span> declare<\/span>
\n  2<\/span>     v_profile_name varchar2(<\/span>30<\/span>)<\/span>;<\/span>
\n  3<\/span>     v_sql_text clob;<\/span>
\n  4<\/span>  begin
\n  5<\/span>
\n  6<\/span>     select sql_fulltext into v_sql_text
\n  7<\/span>       from v$sqlarea<\/span>
\n  8<\/span>      where sql_id =<\/span> 'gb0h2ra62zg18'<\/span>;<\/span>
\n  9<\/span>
\n 10<\/span>     v_profile_name :=<\/span> 'SQLPROFILE_'<\/span>||<\/span>'gb0h2ra62zg18'<\/span>;<\/span>
\n 11<\/span>
\n 12<\/span>     dbms_sqltune.<\/span>import_sql_profile(<\/span>
\n 13<\/span>          sql_text =><\/span> v_sql_text,<\/span>
\n 14<\/span>          profile =><\/span>  sqlprof_attr(<\/span>'monitor'<\/span>)<\/span>,<\/span>
\n 15<\/span>          category =><\/span> 'DEFAULT'<\/span>,<\/span>
\n 16<\/span>          name =><\/span>     v_profile_name)<\/span>;<\/span>
\n 17<\/span>
\n 18<\/span>     dbms_output.<\/span>put_line(<\/span>'-----------------------------------------------------------'<\/span>)<\/span>;<\/span>
\n 19<\/span>     dbms_output.<\/span>put_line(<\/span>'SQL_Profile '<\/span>||<\/span>v_profile_name||<\/span>' criada.'<\/span>)<\/span>;<\/span>
\n 20<\/span>     dbms_output.<\/span>put_line(<\/span>'---------------------------------------------------------- '<\/span>)<\/span>;<\/span>
\n 21<\/span>
\n 22<\/span>  end<\/span><\/a>;<\/span>
\n 23<\/span>  \/<\/span>
\n-----------------------------------------------------------<\/span>
\nSQL_Profile SQLPROFILE_gb0h2ra62zg18 criada.<\/span>
\n----------------------------------------------------------<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 
\nObserve que na cria\u00e7\u00e3o da profile acima estamos especificando um atributo “monitor” (linha 15) que vai ser respons\u00e1vel por for\u00e7ar o SQL Monitor a capturar as informa\u00e7\u00f5es dessa consulta.<\/span><\/p>\n

3.3) Executar a consulta para captura pelo SQL Monitor<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/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 \/* dbtw003 *\/<\/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
\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 '%dbtw003%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ngb0h2ra62zg18            0<\/span>
\ngb0h2ra62zg18            1<\/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  gb0h2ra62zg18,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw003 *\/<\/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.47<\/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.47<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.47<\/span> |<\/span>    4477<\/span> |<\/span>   4439<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    PARTITION RANGE<\/span><\/a> ALL|<\/span>              |<\/span>      1<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.47<\/span> |<\/span>    4471<\/span> |<\/span>   4439<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>     TABLE ACCESS FULL |<\/span> SALES        |<\/span>     28<\/span> |<\/span>    166<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.47<\/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
\nNote
\n-----<\/span>
\n   -<\/span> SQL profile SQLPROFILE_gb0h2ra62zg18 used for<\/span> this statement
\n
\n
\n29<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Na execu\u00e7\u00e3o acima podemos observar na linha 71 que a profile gerada na etapa anterior foi utilizada.<\/span><\/p>\n

3.4) Emitir o relat\u00f3rio do SQL Monitor<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> select sid,<\/span>
\n  2<\/span>         session_serial#,
\n<\/span>  3<\/span>         sql_id,<\/span>
\n  4<\/span>         sql_exec_id,<\/span>
\n  5<\/span>         to_char(<\/span>sql_exec_start,<\/span>'DD-Mon-YY HH24:MI:SS'<\/span>)<\/span> sql_exec_start,<\/span>
\n  6<\/span>         sql_plan_hash_value plan_hash_value,<\/span>
\n  7<\/span>         elapsed_time\/<\/span>1000000<\/span> etime,<\/span>
\n  8<\/span>         buffer_gets,<\/span>
\n  9<\/span>         disk_reads
\n 10<\/span>    from v$sql_monitor<\/span>
\n 11<\/span>   where sql_id =<\/span> 'gb0h2ra62zg18'<\/span>;<\/span>
\n
\n       SID SESSION_SERIAL# SQL_ID        SQL_EXEC_ID SQL_EXEC_START       PLAN_HASH_VALUE      ETIME BUFFER_GETS DISK_READS
\n<\/span>----------<\/span> ---------------<\/span> -------------<\/span> -----------<\/span> --------------------<\/span> ---------------<\/span> ----------<\/span> -----------<\/span> ----------<\/span>
\n      1290<\/span>            2327<\/span> gb0h2ra62zg18    33554433<\/span> 24<\/span>-<\/span>Abr-<\/span>17<\/span> 21<\/span>:<\/span>16<\/span>:<\/span>40<\/span>        2841872969<\/span>   1<\/span>,<\/span>481456<\/span>        4490<\/span>        156<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set long 999999999<\/span>
\nSQL ><\/span> set lines 280<\/span>
\nSQL ><\/span> col report for<\/span> a279
\nSQL ><\/span> select DBMS_SQLTUNE.<\/span>REPORT_SQL_MONITOR(<\/span>session_id<\/span><\/a>=><\/span> 1290<\/span>,<\/span> session_serial=><\/span> 2327<\/span>,<\/span> sql_id=><\/span> 'gb0h2ra62zg18'<\/span>,<\/span> sql_exec_id=><\/span> '33554433'<\/span>,<\/span> report_level=><\/span>'ALL'<\/span>)<\/span> as<\/span> report from dual;<\/span>
\n
\nREPORT
\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL Monitoring Report
\n
\nSQL Text
\n------------------------------<\/span>
\nSELECT \/* dbtw003 *\/<\/span> count<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> Qtde FROM customers c,<\/span> sales s WHERE c.<\/span>cust_id =<\/span> s.<\/span>cust_id AND s.<\/span>quantity_sold ><\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nGlobal<\/span> Information
\n------------------------------<\/span>
\n Status              :<\/span>  DONE (<\/span>ALL ROWS)<\/span>
\n Instance ID         :<\/span>  2<\/span>
\n Session             :<\/span>  VALTER (<\/span>1290<\/span>:<\/span>2327<\/span>)<\/span>
\n SQL ID              :<\/span>  gb0h2ra62zg18
\n SQL Execution ID    :<\/span>  33554433<\/span>
\n Execution Started   :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 21<\/span>:<\/span>16<\/span>:<\/span>40<\/span>
\n First Refresh Time<\/span><\/a>  :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 21<\/span>:<\/span>16<\/span>:<\/span>40<\/span>
\n Last Refresh Time<\/span><\/a>   :<\/span>  04<\/span>\/<\/span>24<\/span>\/<\/span>2017<\/span> 21<\/span>:<\/span>16<\/span>:<\/span>41<\/span>
\n Duration            :<\/span>  1s
\n Module\/<\/span>Action       :<\/span>  SQL*<\/span>Plus\/-<\/span>
\n Service             :<\/span>  ODBSRV01
\n Program             :<\/span>  sqlplus.<\/span>exe
\n Fetch Calls         :<\/span>  1<\/span>
\n
\nBinds
\n========================================================================================================================<\/span>
\n|<\/span>   Name   |<\/span> Position |<\/span>  Type  |<\/span>                                         Value                                         |<\/span>
\n========================================================================================================================<\/span>
\n|<\/span> :<\/span>SYS_B_1 |<\/span>        2<\/span> |<\/span> NUMBER |<\/span> 1<\/span>                                                                                     |<\/span>
\n========================================================================================================================<\/span>
\n
\nGlobal<\/span> Stats
\n=========================================================================================<\/span>
\n|<\/span> Elapsed |<\/span>   Cpu   |<\/span>    IO    |<\/span> Application |<\/span>  Other   |<\/span> Fetch |<\/span> Buffer |<\/span> Read |<\/span> Read  |<\/span>
\n|<\/span> Time<\/span><\/a>(<\/span>s)<\/span> |<\/span> Time<\/span><\/a>(<\/span>s)<\/span> |<\/span> Waits(<\/span>s)<\/span> |<\/span>  Waits(<\/span>s)<\/span>   |<\/span> Waits(<\/span>s)<\/span> |<\/span> Calls |<\/span>  Gets  |<\/span> Reqs |<\/span> Bytes |<\/span>
\n=========================================================================================<\/span>
\n|<\/span>    1.48<\/span> |<\/span>    0.04<\/span> |<\/span>     1.35<\/span> |<\/span>        0.04<\/span> |<\/span>     0.05<\/span> |<\/span>     1<\/span> |<\/span>   4490<\/span> |<\/span>  156<\/span> |<\/span>  35MB |<\/span>
\n=========================================================================================<\/span>
\n
\nSQL Plan Monitoring Details (<\/span>Plan Hash<\/span><\/a> Value=<\/span>2841872969<\/span>)<\/span>
\n=========================================================================================================================================================<\/span>
\n|<\/span> Id |<\/span>        Operation        |<\/span>     Name     |<\/span>  Rows   |<\/span> Cost |<\/span>   Time<\/span><\/a>    |<\/span> Start  |<\/span> Execs |<\/span>   Rows   |<\/span> Read |<\/span> Read  |<\/span> Activity |<\/span>   Activity Detail    |<\/span>
\n|<\/span>    |<\/span>                         |<\/span>              |<\/span> (<\/span>Estim)<\/span> |<\/span>      |<\/span> Active(<\/span>s)<\/span> |<\/span> Active |<\/span>       |<\/span> (<\/span>Actual)<\/span> |<\/span> Reqs |<\/span> Bytes |<\/span>   (<\/span>%<\/span>)<\/span>    |<\/span>     (<\/span># samples)      |
\n<\/span>=========================================================================================================================================================<\/span>
\n|<\/span>  0<\/span> |<\/span> SELECT STATEMENT        |<\/span>              |<\/span>         |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        1<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  1<\/span> |<\/span>   SORT<\/span><\/a> AGGREGATE<\/span><\/a>        |<\/span>              |<\/span>       1<\/span> |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        1<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  2<\/span> |<\/span>    NESTED LOOPS         |<\/span>              |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  3<\/span> |<\/span>     PARTITION RANGE<\/span><\/a> ALL |<\/span>              |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     1<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n|<\/span>  4<\/span> |<\/span>      TABLE ACCESS FULL  |<\/span> SALES        |<\/span>     166<\/span> |<\/span> 1290<\/span> |<\/span>         2<\/span> |<\/span>     +<\/span>0<\/span> |<\/span>    28<\/span> |<\/span>        4<\/span> |<\/span>  155<\/span> |<\/span>  35MB |<\/span>   100.00<\/span> |<\/span> direct path read (<\/span>1<\/span>)<\/span> |<\/span>
\n|<\/span>  5<\/span> |<\/span>     INDEX UNIQUE SCAN   |<\/span> CUSTOMERS_PK |<\/span>       1<\/span> |<\/span>      |<\/span>         1<\/span> |<\/span>     +<\/span>1<\/span> |<\/span>     4<\/span> |<\/span>        4<\/span> |<\/span>      |<\/span>       |<\/span>          |<\/span>                      |<\/span>
\n=========================================================================================================================================================<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

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

Nesse artigo verificamos na pr\u00e1tica como utilizar o SQL Monitor para gerar um relat\u00f3rio com informa\u00e7\u00f5es valiosas para analisar o desempenho de uma instru\u00e7\u00e3o SQL e identificar o poss\u00edveis pontos de melhoria, existem outras ferramentas que tamb\u00e9m fornecem essas informa\u00e7\u00f5es, mas sem d\u00favida o SQL Monitor \u00e9 uma ferramenta que agiliza muito a obten\u00e7\u00e3o dessas informa\u00e7\u00f5es.<\/span><\/p>\n

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

http:\/\/kerryosborne.oracle-guy.com\/2011\/04\/realtime-sql-monitoring-designed-with-exadata-in-mind\/<\/a><\/strong>
\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 disponibiliza um recurso fant\u00e1stico chamado SQL Monitor que possibilita a visualiza\u00e7\u00e3o de uma infinidade de informa\u00e7\u00f5es sobre a execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, tanto para instru\u00e7\u00f5es encerradas\u00a0como para instru\u00e7\u00f5es que ainda est\u00e3o em execu\u00e7\u00e3o, \u00e9 poss\u00edvel acessar esse recurso de forma gr\u00e1fica atrav\u00e9s do Oracle Cloud Control e\u00a0SQLDeveloper ou em formato texto<\/p>\n","protected":false},"author":2,"featured_media":1032,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[165,36,11,164,118],"class_list":["post-1031","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tuning-tools","tag-dbms_sqltune","tag-execution-plan","tag-plano-de-execucao","tag-sql-monitor","tag-sql-profile"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1031","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=1031"}],"version-history":[{"count":25,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1031\/revisions"}],"predecessor-version":[{"id":2057,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1031\/revisions\/2057"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1032"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1031"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}