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

novembro 15, 2015 V. J.A. Metodologia

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 >

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

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

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.

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.