{"id":57,"date":"2015-11-15T20:33:39","date_gmt":"2015-11-15T22:33:39","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=57"},"modified":"2022-10-21T21:29:16","modified_gmt":"2022-10-22T00:29:16","slug":"como-identificar-instrucoes-sql-candidatas-a-melhoria-de-desempenho","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/como-identificar-instrucoes-sql-candidatas-a-melhoria-de-desempenho\/","title":{"rendered":"Como identificar instru\u00e7\u00f5es SQL candidatas a melhoria de desempenho?"},"content":{"rendered":"

Uma instru\u00e7\u00e3o SQL se torna um problema quando ela falha em atender algumas m\u00e9tricas predefinidas e o seu ajuste costuma buscar um dos dois objetivos abaixo ou os dois simultaneamente:<\/span><\/p>\n

Reduzir o tempo de resposta para o usu\u00e1rio, ou seja diminuir o tempo entre o momento em que o usu\u00e1rio submete a instru\u00e7\u00e3o SQL para o banco de dados e o momento que ele obt\u00eam a resposta. Neste caso precisamos identificar as instru\u00e7\u00f5es SQL com tempos de execu\u00e7\u00e3o longos (Elapsed time).<\/span><\/p>\n

Reduzir o DBTime, ou seja utilizar o minimo de recurso do banco para acessar os registros que o usu\u00e1rio deseja como retorno na sua instru\u00e7\u00e3o SQL. Neste caso precisamos identificar as instru\u00e7\u00f5es SQL consumindo mais CPU ou com eventos de espera elevados (Os eventos relacionados\u00a0a “Logical I\/O” ou “Buffer Gets” costumam ser os lideres na lista de evento de espera com valores elevados).<\/span><\/p>\n

 <\/p>\n

 <\/p>\n

Existem algumas maneiras de gerar as informa\u00e7\u00f5es necess\u00e1rias para localizar essas instru\u00e7\u00f5es SQL:<\/span><\/p>\n

Consulta na vis\u00e3o V$SQLAREA<\/span><\/h2>\n

A vis\u00e3o V$SQLAREA<\/a> mantem registros das execu\u00e7\u00f5es das instru\u00e7\u00f5es SQL por um pequeno per\u00edodo de tempo, esse per\u00edodo depende da carga de trabalho da base e da quantidade de memoria que foi destinada ao banco de dados Oracle. Como esta vis\u00e3o gera relat\u00f3rio de um\u00a0per\u00edodo curto de tempo, caso escolha essa op\u00e7\u00e3o para identificar instru\u00e7\u00f5es SQL candidatas a ajuste de desempenho, sugerimos que a sua consulta seja programada para executar periodicamente, desta forma voc\u00ea conseguira cobrir um per\u00edodo de tempo maior.<\/span><\/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
<\/div><\/td>
curso01@<\/span>ODBSRV11 ><\/span> @<\/span>Top10_elapse_time.<\/span>sql
\n
\nUSERNAME           EXECUTIONS ROWS_PROCESSED cpu_time(<\/span>s)<\/span> elapsed_time(<\/span>s)<\/span> et_por_exec(<\/span>s)<\/span> SQL_ID        SQL_TEXT
\n------------------<\/span> ----------<\/span> --------------<\/span> -----------<\/span> ---------------<\/span> --------------<\/span> -------------<\/span> --------------------------------------------------------------------------------<\/span>
\nAPPLSCHM              1331743<\/span>        1331008<\/span>     1332142<\/span>         1342356<\/span>       1.007969<\/span> 5wsq9wpkav926 SELECT COUNT<\/span><\/a> (<\/span> :<\/span>"SYS_B_00"<\/span> )<\/span>  FROM WRK_PENDENCIATASK pendencia,<\/span> WRK_TASK tar
\nAPPLSCHM              1007289<\/span>        1007244<\/span>         837<\/span>           67918<\/span>        .067427<\/span> bxafmnqrg2h4u SELECT "EIW_SERIALIZADA"<\/span> from "WRK_INSTANCE_STATE_WKF"<\/span> where  "EIW_ID"<\/span> =<\/span> :<\/span>1<\/span>
\nAPPLSCHM2               37317<\/span>       36993865<\/span>       32665<\/span>           35731<\/span>        .957504<\/span> f34y04nry9ump select PLAN_TABLE_OUTPUT from TABLE (<\/span>DBMS_XPLAN.<\/span>display_cursor (<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>SYS
\nAPPLSCHM2               37116<\/span>       36363348<\/span>       32615<\/span>           35532<\/span>        .957316<\/span> g3f3cw3zy5aat SELECT PLAN_TABLE_OUTPUT FROM TABLE(<\/span>CAST(<\/span>DBMS_XPLAN.<\/span>PREPARE_RECORDS(<\/span>:<\/span>B1 ,<\/span> :<\/span>B2 )<\/span>
\nAPPLSCHM2               36663<\/span>       32041340<\/span>       29554<\/span>           31997<\/span>        .872741<\/span> 0s391ymw1zm7b SELECT \/*+ opt_param('parallel_execution_enabled', 'false') *\/<\/span> EXTRACTVALUE(<\/span>VALU
\nAPPLSCHM                12078<\/span>         176858<\/span>       31249<\/span>           31354<\/span>       2.596001<\/span> 040agrnfvbc6h SELECT    EMPLOYEE.<\/span>EMPCODE                   AS<\/span> NCODPLACE,<\/span>    LOTACOES.<\/span>LODESC
\nAPPLSCHM                76318<\/span>          76316<\/span>       20917<\/span>           23046<\/span>        .301975<\/span> fmm1brqan9qn1 SELECT COUNT<\/span><\/a> (<\/span> :<\/span>"SYS_B_00"<\/span> )<\/span>  FROM WRK_TASK TASK,<\/span> WRK_PENDENCIATASK penden
\nAPPLSCHM              5489754<\/span>        5489729<\/span>        1694<\/span>           22646<\/span>        .004125<\/span> 4a48wz4vm6x7r INSERT INTO WRK_VALORHISTORY (<\/span>VAL_VERSAO,<\/span> VAL_TEXTO,<\/span> VAL_IDTIPO,<\/span> VAL_ID)<\/span>
\nAPPLSCHM                16876<\/span>          16876<\/span>       15291<\/span>           19308<\/span>       1.144138<\/span> 1d5qk9sv3xdt0 SELECT COUNT<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> FROM (<\/span> (<\/span>SELECT prh_id ,<\/span> prh_code_process ,<\/span> prh_versa
\nAPPLSCHM               380412<\/span>         379386<\/span>        2800<\/span>           19295<\/span>        .050722<\/span> 08jb73ps6sz6u UPDATE WRK_INSTANCE_STATE_WKF SET EIW_VERSAO =<\/span> :<\/span>p0,<\/span> EIW_SERIALIZADA =<\/span> :<\/span>p1,<\/span> EIW
\n
\n10<\/span> rows selected.<\/span>
\n
\ncurso01@<\/span>ODBSRV11 ><\/span> @<\/span>Top10_buffer_gets.<\/span>sql
\n
\nUSERNAME            EXECUTIONS ROWS_PROCESSED cpu_time(<\/span>s)<\/span> BUFFER_GETS BG_POR_EXEC SQL_ID        SQL_TEXT
\n-------------------<\/span> ----------<\/span> --------------<\/span> -----------<\/span> -----------<\/span> -----------<\/span> -------------<\/span> ------------------------------------------------------------<\/span>
\nAPPLSCHM                 66929<\/span>         207033<\/span>       10455<\/span>  3790410725<\/span>       56633<\/span> 9r6pd0cwp4m9w select *<\/span> from (<\/span> SELECT tfa_id as<\/span> TFA1_262_0_,<\/span> tfa_nome as<\/span> TF
\nAPPLSCHM                 16877<\/span>          16877<\/span>       15292<\/span>  3739250293<\/span>      221559<\/span> 1d5qk9sv3xdt0 SELECT COUNT<\/span><\/a>(<\/span>:<\/span>"SYS_B_0"<\/span>)<\/span> FROM (<\/span> (<\/span>SELECT prh_id ,<\/span> prh_code_
\nAPPLSCHM                 15209<\/span>          85535<\/span>       13587<\/span>  3353186901<\/span>      220474<\/span> ajany95zz62nd select *<\/span> from (<\/span> (<\/span> (<\/span>SELECT prh_id as<\/span> PRH1_259_0_,<\/span> prh_code_
\nAPPLSCHM                 13316<\/span>         133164<\/span>        4215<\/span>  2547022916<\/span>      191275<\/span> 3j9gtsf132tmp SELECT INSTANCE.<\/span>ITP_ID,<\/span> INSTANCE.<\/span>ITP_DATAINI,<\/span> INSTANCE.<\/span>IT
\nAPPLSCHM                  8659<\/span>         126035<\/span>       14274<\/span>  2331772645<\/span>      269289<\/span> 6y0k8n98d99bn SELECT   TRUNC(<\/span>EMPLOYEE.<\/span>EMPCODE\/:<\/span>"SYS_B_00"<\/span>)<\/span> AS<\/span> NCODSITE,<\/span>
\nAPPLSCHM                  7107<\/span>       37858200<\/span>        5494<\/span>  2129395092<\/span>      299619<\/span> 451ku3gsgr4pf SELECT    EMPLOYEE.<\/span>FUMATFUNC           AS<\/span> NMATRICULADOFUNCIO
\nAPPLSCHM                165115<\/span>         165102<\/span>        9355<\/span>  1909665881<\/span>       11566<\/span> bunpwt6qqwgrk SELECT DISTINCT CODE_SECRET FROM PRHOPERADORES WHE
\nAPPLSCHM                  5656<\/span>       30287353<\/span>        4326<\/span>  1699478670<\/span>      300474<\/span> 373n01230s7yt SELECT    EMPLOYEE.<\/span>FUMATFUNC   AS<\/span> NMATRICULADOEMPLOYEERIO,<\/span>
\nAPPLSCHM                 12078<\/span>         176858<\/span>       31249<\/span>  1271287206<\/span>      105256<\/span> 040agrnfvbc6h SELECT    EMPLOYEE.<\/span>EMPCODE                   AS<\/span> NCODPLACE
\nAPPLSCHM                   374<\/span>         193324<\/span>        1977<\/span>  1245449855<\/span>     3330080<\/span> 2nm6hrx12dgny SELECT    FUCODEMP AS<\/span> AA_CODCOMPANY      ,<\/span>FUMATFUNC AS<\/span> AA_MA
\n
\n10<\/span> rows selected.<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Obs: Os scripts utilizados no exemplo acima podem ser visto acessando os respectivos links na se\u00e7\u00e3o SCRIPTS desse post.<\/span><\/p>\n

Consulta na vis\u00e3o V$SQL_MONITOR<\/span><\/h2>\n

A vis\u00e3o V$SQL_MONITOR<\/a> mantem registros das execu\u00e7\u00f5es das instru\u00e7\u00f5es SQL por um per\u00edodo um pouco maior pois ela registra somente instru\u00e7\u00f5es SQL que consomem pelo menos 5 segundos DBTime ou cuja execu\u00e7\u00e3o utilize paralelismo. A utiliza\u00e7\u00e3o dessa vis\u00e3o exige a contrata\u00e7\u00e3o das “Options Diagnostic & Tuning Pack”, como na vis\u00e3o anterior voc\u00ea poder\u00e1 utilizar o mesmo procedimento para expandir o tempo de monitora\u00e7\u00e3o das instru\u00e7\u00f5es SQL candidatas a serem ajustadas.<\/span><\/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
<\/div><\/td>
curso01@<\/span>ODBSRV11 ><\/span> @<\/span>Top10_bg_monitor.<\/span>sql
\n
\nSQL_ID        Media BufGets      EXECS Total BufGets SQL Texto
\n
\n-------------<\/span> -------------<\/span> ----------<\/span> -------------<\/span> ------------------------------------------------------------<\/span>
\n
\n3hafwgds8zxn8       5920810<\/span>        108<\/span>     639447476<\/span> SELECT DISTINCT A.<\/span>ID_CALLFROM ,<\/span> A.<\/span>ID_TYPE ,<\/span> A.<\/span>ST_TYPE ,<\/span> A.<\/span>ID_
\n4msrcdgz3c8xn       4908741<\/span>        152<\/span>     746128590<\/span> SELECT DISTINCT A.<\/span>ID_CALLFROM ,<\/span> A.<\/span>ID_TYPE ,<\/span> A.<\/span>ST_TYPE ,<\/span> A.<\/span>ID_
\ndb4yh2p0g6m3w        909244<\/span>          4<\/span>       3636974<\/span> SELECT DISTINCT   A.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_MANAGER002 AS<\/span>
\na5d1zvzhmw7sn        753493<\/span>          3<\/span>       2260479<\/span> SELECT DISTINCT  A.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_MANAGER002 AS<\/span>
\nbq9cz3v4wk0zs        513063<\/span>          3<\/span>       1539190<\/span> SELECT B.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_TARGET AS<\/span> TARGET,<\/span> M.<\/span>ST_D
\n5nr5u5yh8uhyb        210470<\/span>          2<\/span>        420939<\/span> DECLARE<\/span> job BINARY_INTEGER :=<\/span> :<\/span>job;<\/span>  next_date TIMESTAMP WIT
\n16j4761wmv1d2        194264<\/span>          2<\/span>        388527<\/span> SELECT DISTINCT A.<\/span>ID_RECURSOATEN AS<\/span> ID_RECURSOATEN,<\/span>
\n372frgwv22gs4        157067<\/span>          2<\/span>        314134<\/span> SELECT *<\/span> FROM HLC.<\/span>HLC_URA_CLIENT_INFO WHERE CALLDATE between
\n65d2p1qrc04un         62258<\/span>         11<\/span>        684841<\/span> SELECT *<\/span> FROM (<\/span>SELECT U.<\/span>ST_NAME_USER001 AS<\/span> NAME,<\/span> U.<\/span>ST_MATRIC
\ngp09h5km5vmuf         36178<\/span>         19<\/span>        687388<\/span> SELECT DISTINCT A.<\/span>ID_RECURSOATEN AS<\/span> ID_RECURSOATEN,<\/span>
\n
\ncurso01@<\/span>ODBSRV11 ><\/span> @<\/span>Top10_et_monitor.<\/span>sql
\n
\nSQL_ID        Media ET(<\/span>s)<\/span>      EXECS Total ET(<\/span>s)<\/span> SQL Texto
\n
\n-------------<\/span> -----------<\/span> ----------<\/span> -----------<\/span> ------------------------------------------------------------<\/span>
\n
\nbq9cz3v4wk0zs       230.9<\/span>          3<\/span>       692.7<\/span> SELECT B.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_TARGET AS<\/span> TARGET,<\/span> M.<\/span>ST_D
\n16j4761wmv1d2        43.7<\/span>          2<\/span>        87.4<\/span> SELECT DISTINCT A.<\/span>ID_RECURSOATEN AS<\/span> ID_RECURSOATEN,<\/span>
\ndb4yh2p0g6m3w          30<\/span>          4<\/span>       119.9<\/span> SELECT DISTINCT   A.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_MANAGER002 AS<\/span>
\n5nr5u5yh8uhyb        29.5<\/span>          2<\/span>        59.1<\/span> DECLARE<\/span> job BINARY_INTEGER :=<\/span> :<\/span>job;<\/span>  next_date TIMESTAMP WIT
\n372frgwv22gs4        27.7<\/span>          2<\/span>        55.3<\/span> SELECT *<\/span> FROM HLC.<\/span>HLC_URA_CLIENT_INFO WHERE CALLDATE between
\ngp09h5km5vmuf        22.8<\/span>         19<\/span>       433.3<\/span> SELECT DISTINCT A.<\/span>ID_RECURSOATEN AS<\/span> ID_RECURSOATEN,<\/span>
\n9tm21hdu5g99w          22<\/span>          3<\/span>          66<\/span> begin ypkvend.<\/span>PKG_RECURSOATEN.<\/span>PRC_INSERIR_RECURSOATEN(<\/span>PI_ID
\n904df4pu5c7d2        21.9<\/span>          3<\/span>        65.8<\/span> UPDATE TB_CUSTOM1 SET NU_STATUS =<\/span> DECODE(<\/span>:<\/span>B7 ,<\/span> 0<\/span>,<\/span> 3<\/span>,<\/span> 2<\/span>)<\/span>,<\/span> DT_
\na5d1zvzhmw7sn        11.2<\/span>          3<\/span>        33.6<\/span> SELECT DISTINCT  A.<\/span>ST_DESC_PLAC AS<\/span> PLAC,<\/span> A.<\/span>ST_MANAGER002 AS<\/span>
\n65d2p1qrc04un         9.2<\/span>         11<\/span>       101.3<\/span> SELECT *<\/span> FROM (<\/span>SELECT U.<\/span>ST_NAME_USER001 AS<\/span> NAME,<\/span> U.<\/span>ST_MATRIC
\n
\ncurso01@<\/span>ODBSRV11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Obs: Os scripts utilizados no exemplo acima podem ser visto acessando os respectivos links na se\u00e7\u00e3o SCRIPTS desse post.<\/span><\/p>\n

Consulta no relat\u00f3rio AWR<\/span><\/h2>\n

A gera\u00e7\u00e3o de um relat\u00f3rio AWR tamb\u00e9m permite que fa\u00e7amos a identifica\u00e7\u00e3o das instru\u00e7\u00f5es SQL candidatas a serem ajustadas, para tanto podemos consultar as se\u00e7\u00f5es: SQL ordered by Elapsed Time e SQL ordered by Gets<\/span><\/p>\n

\"Relatorio<\/a><\/p>\n

Obs: A utiliza\u00e7\u00e3o do relat\u00f3rio AWR est\u00e1 condicionada\u00a0a contrata\u00e7\u00e3o da “Option Diagnostic Pack”.<\/p>\n

Consulta no relat\u00f3rio StatsPack<\/span><\/h2>\n

A gera\u00e7\u00e3o de um relat\u00f3rio StatsPack n\u00e3o exige a contrata\u00e7\u00e3o de licen\u00e7a adicional, porem para utiliza-lo precisamos configur\u00e1-lo<\/a>\u00a0e agendar\u00a0um job no banco para coletar as informa\u00e7\u00f5es necess\u00e1rias periodicamente. Assim como o relat\u00f3rio AWR podemos identificar as instru\u00e7\u00f5es SQL candidatas a serem ajustadas nas se\u00e7\u00f5es: SQL ordered by Elapsed Time e SQL ordered by Gets\u00a0<\/span><\/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
<\/div><\/td>
SQL ordered by Elapsed time<\/span><\/a> for<\/span> DB:<\/span> ODBSRV11  Instance:<\/span> ODBSRV11  Snaps:<\/span> 23<\/span> -<\/span>25<\/span>
\n-><\/span> Total<\/span> DB Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>           1<\/span>,<\/span>022<\/span>
\n-><\/span> Captured<\/span> SQL accounts for<\/span>   64.1<\/span>%<\/span> of Total DB Time<\/span><\/a>
\n-><\/span> SQL<\/span> reported below exceeded  1.0<\/span>%<\/span> of Total DB Time<\/span><\/a>
\n
\n  Elapsed                Elap per            CPU                        Old
\n  Time<\/span><\/a> (<\/span>s)<\/span>   Executions  Exec<\/span><\/a> (<\/span>s)<\/span>  %<\/span>Total   Time<\/span><\/a> (<\/span>s)<\/span>  Physical Reads Hash<\/span><\/a> Value
\n----------<\/span> ------------<\/span> ----------<\/span> ------<\/span> ----------<\/span> ---------------<\/span> ----------<\/span>
\n    421.57<\/span>           18<\/span>      23.42<\/span>   41.2<\/span>      28.39<\/span>         190<\/span>,<\/span>545<\/span>  116748235<\/span>
\nModule:<\/span> SQL*<\/span>Plus
\nSELECT :<\/span>"SYS_B_00"<\/span>||<\/span>MAX<\/span><\/a>(<\/span>ROUND<\/span><\/a>(<\/span>(<\/span> SUM (<\/span>total_mb)<\/span>-<\/span> SUM (<\/span>free_mb)<\/span>)<\/span>\/<\/span>
\nSUM (<\/span>max_mb)<\/span>*:<\/span>"SYS_B_01"<\/span>)<\/span>)<\/span> PCT FROM (<\/span>    SELECT tablespace_name,<\/span>
\n SUM (<\/span>bytes)<\/span>\/:<\/span>"SYS_B_02"<\/span>\/:<\/span>"SYS_B_03"<\/span> FREE_MB,<\/span>    :<\/span>"SYS_B_04"<\/span> TOT
\nAL_MB,<\/span> :<\/span>"SYS_B_05"<\/span> MAX_MB    FROM dba_free_space    GROUP BY tab
\n
\n     68.55<\/span>            2<\/span>      34.28<\/span>    6.7<\/span>       3.27<\/span>          21<\/span>,<\/span>305<\/span> 1530729544<\/span>
\nModule:<\/span> SQL*<\/span>Plus
\nSELECT :<\/span>"SYS_B_0"<\/span>||<\/span>sum(<\/span>bytes)<\/span> as<\/span> total FROM   (<\/span>SELECT nvl(<\/span>a.<\/span>byte
\ns -<\/span> nvl(<\/span>f.<\/span>bytes,<\/span> :<\/span>"SYS_B_1"<\/span>)<\/span>,<\/span> :<\/span>"SYS_B_2"<\/span>)<\/span> bytes      FROM sys.<\/span>db
\na_tablespaces d,<\/span>           (<\/span>SELECT tablespace_name,<\/span> sum(<\/span>bytes)<\/span> b
\nytes              FROM dba_data_files             GROUP BY table
\n
\n     62.13<\/span>            1<\/span>      62.13<\/span>    6.1<\/span>      38.59<\/span>           4<\/span>,<\/span>655<\/span> 1352845115<\/span>
\nModule:<\/span> SQLTools.<\/span>exe
\nDECLARE<\/span> PI_ST_NOME_ARQUIVO    VARCHAR2(<\/span>150<\/span>)<\/span>:=<\/span>'VIVO_201511130400.
\ntxt'<\/span>;<\/span> PO_NU_QTDE_REGISTROS  NUMBER(<\/span>10<\/span>)<\/span>:=<\/span>0<\/span>  ;<\/span> PO_QTDE_ERRO_REG
\n   NUMBER(<\/span>10<\/span>)<\/span>:=<\/span>0<\/span>  ;<\/span> PO_TOTAL_REGISTRO     NUMBER(<\/span>10<\/span>)<\/span>:=<\/span>0<\/span>  ;<\/span> PO_QT
\nD_NAO_LOCAL      NUMBER(<\/span>10<\/span>)<\/span>:=<\/span>0<\/span>  ;<\/span> PO_NU_ERRO            NUMBER(<\/span>1<\/span>
\n
\n     41.35<\/span>            4<\/span>      10.34<\/span>    4.0<\/span>       5.27<\/span>         865<\/span>,<\/span>644<\/span> 2469443127<\/span>
\nModule:<\/span> Oracle Enterprise Manager.<\/span>Metric Engine
\nSELECT TO_CHAR(<\/span>current_timestamp AT TIME<\/span><\/a> ZONE :<\/span>"SYS_B_0"<\/span>,<\/span> :<\/span>"SYS_
\nB_1"<\/span>)<\/span> AS<\/span> curr_timestamp,<\/span> COUNT<\/span><\/a>(<\/span>username)<\/span> AS<\/span> failed_count,<\/span> TO_CHA
\nR(<\/span>MIN<\/span><\/a>(<\/span>timestamp)<\/span>,<\/span> :<\/span>"SYS_B_2"<\/span>)<\/span> AS<\/span> first_occur_time,<\/span> TO_CHAR(<\/span>MAX<\/span><\/a>(<\/span>t
\nimestamp)<\/span>,<\/span> :<\/span>"SYS_B_3"<\/span>)<\/span> AS<\/span> last_occur_time FROM  sys.<\/span>dba_audit_se
\n
\n     13.32<\/span>            2<\/span>       6.66<\/span>    1.3<\/span>       6.50<\/span>             414<\/span> 3467637305<\/span>
\nModule:<\/span> DBMS_SCHEDULER
\nDECLARE<\/span> job BINARY_INTEGER :=<\/span> :<\/span>job;<\/span>  next_date TIMESTAMP WITH TI
\nME ZONE :=<\/span> :<\/span>mydate;<\/span>  broken BOOLEAN :=<\/span> FALSE<\/span>;<\/span>  job_name VARCHAR2
\n(<\/span>30<\/span>)<\/span> :=<\/span> :<\/span>job_name;<\/span>  job_subname VARCHAR2(<\/span>30<\/span>)<\/span> :=<\/span> :<\/span>job_subname;<\/span>  j
\nob_owner VARCHAR2(<\/span>30<\/span>)<\/span> :=<\/span> :<\/span>job_owner;<\/span>  job_start TIMESTAMP WITH T<\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

O relat\u00f3rio Statspack n\u00e3o fornece a identifica\u00e7\u00e3o da instru\u00e7\u00e3o SQL atrav\u00e9s do campo SQL_ID, ao inv\u00e9s disso ele informa o campo OLD HASH VALUE, podemos recuperar o SQL_ID facilmente atrav\u00e9s de uma consulta na vis\u00e3o V$SQLAREA, veja a seguir:<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
<\/div><\/td>
curso01@<\/span>ODBSRV11 ><\/span> SELECT SQL_ID,<\/span> OLD_HASH_VALUE FROM V$SQLAREA<\/span> WHERE OLD_HASH_VALUE=<\/span>116748235<\/span>;<\/span>
\n
\nSQL_ID           OLD_HASH_VALUE
\n----------------<\/span> --------------<\/span>
\n1cyrv6hmaf3x6         116748235<\/span>
\n
\nElapsed:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span>
\ncurso01@<\/span>ODBSRV11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

Softwares de Performance e Tuning<\/span><\/h2>\n

Outra op\u00e7\u00e3o que temos para identificar as instru\u00e7\u00f5es SQL candidatas a serem ajustadas, s\u00e3o softwares dispon\u00edveis no mercado, com por exemplo:<\/span><\/p>\n

1) O Oracle Enterprise Manager cuja utiliza\u00e7\u00e3o das ferramentas para analise de Performance e Tuning est\u00e3o condicionadas a contrata\u00e7\u00e3o das licen\u00e7as “Options Diagnostic & Tuning Pack”.<\/span><\/p>\n

2) O DBTimeWizard<\/a> que tem uma interface muito simples e permite identificar a instru\u00e7\u00f5es candidatas com rapidez.<\/span><\/p>\n

\"DBTimeWizard\"<\/a><\/p>\n

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

Existem muitas alternativas para identificar as instru\u00e7\u00f5es SQL candidatas a serem ajustadas, algumas delas requerem licen\u00e7a adicional para serem utilizadas, independente da alternativa que voc\u00ea escolher o importante \u00e9 come\u00e7ar a utiliza-la e ajustar o desempenho dessas instru\u00e7\u00f5es pois esse trabalho trar\u00e1 benef\u00edcios para os usu\u00e1rios das aplica\u00e7\u00f5es e aumentar\u00e1 a capacidade do seu banco de dados.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/database\/121\/TGSQL\/tgsql_intro.htm#TGSQL113<\/a><\/p>\n

http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e40402\/dynviews_3064.htm#REFRN30259<\/a><\/p>\n

http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e40402\/dynviews_3052.htm#REFRN30479<\/a><\/p>\n

Scripts<\/span><\/h2>\n

Top10_elapse_time.sql<\/a>
\nTop10_buffer_gets.sql<\/a>
\nTop10_et_monitor.sql<\/a>
\nTop10_bg_monitor.sql<\/a><\/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":"

Uma instru\u00e7\u00e3o SQL se torna um problema quando ela falha em atender algumas m\u00e9tricas predefinidas e o seu ajuste costuma buscar um dos dois objetivos abaixo ou os dois simultaneamente: Reduzir o tempo de resposta para o usu\u00e1rio, ou seja diminuir o tempo entre o momento em que o usu\u00e1rio submete a instru\u00e7\u00e3o SQL para o banco de dados e<\/p>\n","protected":false},"author":2,"featured_media":59,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[22,25,21,19,24,23,20,17,18],"class_list":["post-57","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-metodologia","tag-awr","tag-buffer-gets","tag-cloudcontrol","tag-diagnostic-pack","tag-elapsed-time","tag-statspack","tag-tuning-pack","tag-vsqlarea","tag-vsql_monitor"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/57","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=57"}],"version-history":[{"count":39,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":2374,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/57\/revisions\/2374"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/59"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}