Como identificar instruções SQL candidatas a melhoria de desempenho?

instrução SQL com baixo desempenho

Uma instrução SQL se torna um problema quando ela falha em atender algumas métricas predefinidas e o seu ajuste costuma buscar um dos dois objetivos abaixo ou os dois simultaneamente:

Reduzir o tempo de resposta para o usuário, ou seja diminuir o tempo entre o momento em que o usuário submete a instrução SQL para o banco de dados e o momento que ele obtêm a resposta. Neste caso precisamos identificar as instruções SQL com tempos de execução longos (Elapsed time).

Reduzir o DBTime, ou seja utilizar o minimo de recurso do banco para acessar os registros que o usuário deseja como retorno na sua instrução SQL. Neste caso precisamos identificar as instruções SQL consumindo mais CPU ou com eventos de espera elevados (Os eventos relacionados a “Logical I/O” ou “Buffer Gets” costumam ser os lideres na lista de evento de espera com valores elevados).

 

 

Existem algumas maneiras de gerar as informações necessárias para localizar essas instruções SQL:

Consulta na visão V$SQLAREA

A visão V$SQLAREA mantem registros das execuções das instruções SQL por um pequeno período de tempo, esse período depende da carga de trabalho da base e da quantidade de memoria que foi destinada ao banco de dados Oracle. Como esta visão gera relatório de um período curto de tempo, caso escolha essa opção para identificar instruções SQL candidatas a ajuste de desempenho, sugerimos que a sua consulta seja programada para executar periodicamente, desta forma você conseguira cobrir um período de tempo maior.

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
curso01@ODBSRV11 > @Top10_elapse_time.sql

USERNAME           EXECUTIONS ROWS_PROCESSED cpu_time(s) elapsed_time(s) et_por_exec(s) SQL_ID        SQL_TEXT
------------------ ---------- -------------- ----------- --------------- -------------- ------------- --------------------------------------------------------------------------------
APPLSCHM              1331743        1331008     1332142         1342356       1.007969 5wsq9wpkav926 SELECT COUNT ( :"SYS_B_00" )  FROM WRK_PENDENCIATASK pendencia, WRK_TASK tar
APPLSCHM              1007289        1007244         837           67918        .067427 bxafmnqrg2h4u SELECT "EIW_SERIALIZADA" from "WRK_INSTANCE_STATE_WKF" where  "EIW_ID" = :1
APPLSCHM2               37317       36993865       32665           35731        .957504 f34y04nry9ump select PLAN_TABLE_OUTPUT from TABLE (DBMS_XPLAN.display_cursor (:"SYS_B_0",:SYS
APPLSCHM2               37116       36363348       32615           35532        .957316 g3f3cw3zy5aat SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 )
APPLSCHM2               36663       32041340       29554           31997        .872741 0s391ymw1zm7b SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ EXTRACTVALUE(VALU
APPLSCHM                12078         176858       31249           31354       2.596001 040agrnfvbc6h SELECT    EMPLOYEE.EMPCODE                   AS NCODPLACE,    LOTACOES.LODESC
APPLSCHM                76318          76316       20917           23046        .301975 fmm1brqan9qn1 SELECT COUNT ( :"SYS_B_00" )  FROM WRK_TASK TASK, WRK_PENDENCIATASK penden
APPLSCHM              5489754        5489729        1694           22646        .004125 4a48wz4vm6x7r INSERT INTO WRK_VALORHISTORY (VAL_VERSAO, VAL_TEXTO, VAL_IDTIPO, VAL_ID)
APPLSCHM                16876          16876       15291           19308       1.144138 1d5qk9sv3xdt0 SELECT COUNT(:"SYS_B_0") FROM ( (SELECT prh_id , prh_code_process , prh_versa
APPLSCHM               380412         379386        2800           19295        .050722 08jb73ps6sz6u UPDATE WRK_INSTANCE_STATE_WKF SET EIW_VERSAO = :p0, EIW_SERIALIZADA = :p1, EIW

10 rows selected.

curso01@ODBSRV11 > @Top10_buffer_gets.sql

USERNAME            EXECUTIONS ROWS_PROCESSED cpu_time(s) BUFFER_GETS BG_POR_EXEC SQL_ID        SQL_TEXT
------------------- ---------- -------------- ----------- ----------- ----------- ------------- ------------------------------------------------------------
APPLSCHM                 66929         207033       10455  3790410725       56633 9r6pd0cwp4m9w select * from ( SELECT tfa_id as TFA1_262_0_, tfa_nome as TF
APPLSCHM                 16877          16877       15292  3739250293      221559 1d5qk9sv3xdt0 SELECT COUNT(:"SYS_B_0") FROM ( (SELECT prh_id , prh_code_
APPLSCHM                 15209          85535       13587  3353186901      220474 ajany95zz62nd select * from ( ( (SELECT prh_id as PRH1_259_0_, prh_code_
APPLSCHM                 13316         133164        4215  2547022916      191275 3j9gtsf132tmp SELECT INSTANCE.ITP_ID, INSTANCE.ITP_DATAINI, INSTANCE.IT
APPLSCHM                  8659         126035       14274  2331772645      269289 6y0k8n98d99bn SELECT   TRUNC(EMPLOYEE.EMPCODE/:"SYS_B_00") AS NCODSITE,
APPLSCHM                  7107       37858200        5494  2129395092      299619 451ku3gsgr4pf SELECT    EMPLOYEE.FUMATFUNC           AS NMATRICULADOFUNCIO
APPLSCHM                165115         165102        9355  1909665881       11566 bunpwt6qqwgrk SELECT DISTINCT CODE_SECRET FROM PRHOPERADORES WHE
APPLSCHM                  5656       30287353        4326  1699478670      300474 373n01230s7yt SELECT    EMPLOYEE.FUMATFUNC   AS NMATRICULADOEMPLOYEERIO,
APPLSCHM                 12078         176858       31249  1271287206      105256 040agrnfvbc6h SELECT    EMPLOYEE.EMPCODE                   AS NCODPLACE
APPLSCHM                   374         193324        1977  1245449855     3330080 2nm6hrx12dgny SELECT    FUCODEMP AS AA_CODCOMPANY      ,FUMATFUNC AS AA_MA

10 rows selected.

Obs: Os scripts utilizados no exemplo acima podem ser visto acessando os respectivos links na seção SCRIPTS desse post.

Consulta na visão V$SQL_MONITOR

A visão V$SQL_MONITOR mantem registros das execuções das instruções SQL por um período um pouco maior pois ela registra somente instruções SQL que consomem pelo menos 5 segundos DBTime ou cuja execução utilize paralelismo. A utilização dessa visão exige a contratação das “Options Diagnostic & Tuning Pack”, como na visão anterior você poderá utilizar o mesmo procedimento para expandir o tempo de monitoração das instruções SQL candidatas a serem ajustadas.

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
curso01@ODBSRV11 > @Top10_bg_monitor.sql

SQL_ID        Media BufGets      EXECS Total BufGets SQL Texto

------------- ------------- ---------- ------------- ------------------------------------------------------------

3hafwgds8zxn8       5920810        108     639447476 SELECT DISTINCT A.ID_CALLFROM , A.ID_TYPE , A.ST_TYPE , A.ID_
4msrcdgz3c8xn       4908741        152     746128590 SELECT DISTINCT A.ID_CALLFROM , A.ID_TYPE , A.ST_TYPE , A.ID_
db4yh2p0g6m3w        909244          4       3636974 SELECT DISTINCT   A.ST_DESC_PLAC AS PLAC, A.ST_MANAGER002 AS
a5d1zvzhmw7sn        753493          3       2260479 SELECT DISTINCT  A.ST_DESC_PLAC AS PLAC, A.ST_MANAGER002 AS
bq9cz3v4wk0zs        513063          3       1539190 SELECT B.ST_DESC_PLAC AS PLAC, A.ST_TARGET AS TARGET, M.ST_D
5nr5u5yh8uhyb        210470          2        420939 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WIT
16j4761wmv1d2        194264          2        388527 SELECT DISTINCT A.ID_RECURSOATEN AS ID_RECURSOATEN,
372frgwv22gs4        157067          2        314134 SELECT * FROM HLC.HLC_URA_CLIENT_INFO WHERE CALLDATE between
65d2p1qrc04un         62258         11        684841 SELECT * FROM (SELECT U.ST_NAME_USER001 AS NAME, U.ST_MATRIC
gp09h5km5vmuf         36178         19        687388 SELECT DISTINCT A.ID_RECURSOATEN AS ID_RECURSOATEN,

curso01@ODBSRV11 > @Top10_et_monitor.sql

SQL_ID        Media ET(s)      EXECS Total ET(s) SQL Texto

------------- ----------- ---------- ----------- ------------------------------------------------------------

bq9cz3v4wk0zs       230.9          3       692.7 SELECT B.ST_DESC_PLAC AS PLAC, A.ST_TARGET AS TARGET, M.ST_D
16j4761wmv1d2        43.7          2        87.4 SELECT DISTINCT A.ID_RECURSOATEN AS ID_RECURSOATEN,
db4yh2p0g6m3w          30          4       119.9 SELECT DISTINCT   A.ST_DESC_PLAC AS PLAC, A.ST_MANAGER002 AS
5nr5u5yh8uhyb        29.5          2        59.1 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WIT
372frgwv22gs4        27.7          2        55.3 SELECT * FROM HLC.HLC_URA_CLIENT_INFO WHERE CALLDATE between
gp09h5km5vmuf        22.8         19       433.3 SELECT DISTINCT A.ID_RECURSOATEN AS ID_RECURSOATEN,
9tm21hdu5g99w          22          3          66 begin ypkvend.PKG_RECURSOATEN.PRC_INSERIR_RECURSOATEN(PI_ID
904df4pu5c7d2        21.9          3        65.8 UPDATE TB_CUSTOM1 SET NU_STATUS = DECODE(:B7 , 0, 3, 2), DT_
a5d1zvzhmw7sn        11.2          3        33.6 SELECT DISTINCT  A.ST_DESC_PLAC AS PLAC, A.ST_MANAGER002 AS
65d2p1qrc04un         9.2         11       101.3 SELECT * FROM (SELECT U.ST_NAME_USER001 AS NAME, U.ST_MATRIC

curso01@ODBSRV11 >

Obs: Os scripts utilizados no exemplo acima podem ser visto acessando os respectivos links na seção SCRIPTS desse post.

Consulta no relatório AWR

A geração de um relatório AWR também permite que façamos a identificação das instruções SQL candidatas a serem ajustadas, para tanto podemos consultar as seções: SQL ordered by Elapsed Time e SQL ordered by Gets

Relatorio AWR

Obs: A utilização do relatório AWR está condicionada a contratação da “Option Diagnostic Pack”.

Consulta no relatório StatsPack

A geração de um relatório StatsPack não exige a contratação de licença adicional, porem para utiliza-lo precisamos configurá-lo e agendar um job no banco para coletar as informações necessárias periodicamente. Assim como o relatório AWR podemos identificar as instruções SQL candidatas a serem ajustadas nas seções: SQL ordered by Elapsed Time e SQL ordered by Gets 

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
SQL ordered by Elapsed time for DB: ODBSRV11  Instance: ODBSRV11  Snaps: 23 -25
-> Total DB Time (s):           1,022
-> Captured SQL accounts for   64.1% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time

  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
    421.57           18      23.42   41.2      28.39         190,545  116748235
Module: SQL*Plus
SELECT :"SYS_B_00"||MAX(ROUND(( SUM (total_mb)- SUM (free_mb))/
SUM (max_mb)*:"SYS_B_01")) PCT FROM (    SELECT tablespace_name,
 SUM (bytes)/:"SYS_B_02"/:"SYS_B_03" FREE_MB,    :"SYS_B_04" TOT
AL_MB, :"SYS_B_05" MAX_MB    FROM dba_free_space    GROUP BY tab

     68.55            2      34.28    6.7       3.27          21,305 1530729544
Module: SQL*Plus
SELECT :"SYS_B_0"||sum(bytes) as total FROM   (SELECT nvl(a.byte
s - nvl(f.bytes, :"SYS_B_1"), :"SYS_B_2") bytes      FROM sys.db
a_tablespaces d,           (SELECT tablespace_name, sum(bytes) b
ytes              FROM dba_data_files             GROUP BY table

     62.13            1      62.13    6.1      38.59           4,655 1352845115
Module: SQLTools.exe
DECLARE PI_ST_NOME_ARQUIVO    VARCHAR2(150):='VIVO_201511130400.
txt'
; PO_NU_QTDE_REGISTROS  NUMBER(10):=0  ; PO_QTDE_ERRO_REG
   NUMBER(10):=0  ; PO_TOTAL_REGISTRO     NUMBER(10):=0  ; PO_QT
D_NAO_LOCAL      NUMBER(10):=0  ; PO_NU_ERRO            NUMBER(1

     41.35            4      10.34    4.0       5.27         865,644 2469443127
Module: Oracle Enterprise Manager.Metric Engine
SELECT TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_
B_1"
) AS curr_timestamp, COUNT(username) AS failed_count, TO_CHA
R(MIN(timestamp), :"SYS_B_2") AS first_occur_time, TO_CHAR(MAX(t
imestamp), :"SYS_B_3") AS last_occur_time FROM  sys.dba_audit_se

     13.32            2       6.66    1.3       6.50             414 3467637305
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TI
ME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2
(30) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  j
ob_owner VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH T

 

O relatório Statspack não fornece a identificação da instrução SQL através do campo SQL_ID, ao invés disso ele informa o campo OLD HASH VALUE, podemos recuperar o SQL_ID facilmente através de uma consulta na visão V$SQLAREA, veja a seguir:

 

1
2
3
4
5
6
7
8
curso01@ODBSRV11 > SELECT SQL_ID, OLD_HASH_VALUE FROM V$SQLAREA WHERE OLD_HASH_VALUE=116748235;

SQL_ID           OLD_HASH_VALUE
---------------- --------------
1cyrv6hmaf3x6         116748235

Elapsed: 00:00:00.06
curso01@ODBSRV11 >

Softwares de Performance e Tuning

Outra opção que temos para identificar as instruções SQL candidatas a serem ajustadas, são softwares disponíveis no mercado, com por exemplo:

1) O Oracle Enterprise Manager cuja utilização das ferramentas para analise de Performance e Tuning estão condicionadas a contratação das licenças “Options Diagnostic & Tuning Pack”.

2) O DBTimeWizard que tem uma interface muito simples e permite identificar a instruções candidatas com rapidez.

DBTimeWizard

Conclusão

Existem muitas alternativas para identificar as instruções SQL candidatas a serem ajustadas, algumas delas requerem licença adicional para serem utilizadas, independente da alternativa que você escolher o importante é começar a utiliza-la e ajustar o desempenho dessas instruções pois esse trabalho trará benefícios para os usuários das aplicações e aumentará a capacidade do seu banco de dados.

Referências

https://docs.oracle.com/database/121/TGSQL/tgsql_intro.htm#TGSQL113

http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3064.htm#REFRN30259

http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3052.htm#REFRN30479

Scripts

Top10_elapse_time.sql
Top10_buffer_gets.sql
Top10_et_monitor.sql
Top10_bg_monitor.sql

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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