Using Time Model to know which operation type is mostly used

Performance / 15/07/2022

Este artigo será bem simples, apenas para registrar algumas consultas que podemos utilizar no banco de dados Oracle para saber quais os “tipos” de database time que estão sendo gastos no ambiente.

Ambiente de laboratório:

[oracle@oel7 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 19:27:26 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
 
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CORTEX    READ WRITE           ARCHIVELOG

Usando o SwingBench, vou deixar uma carga rodando no banco:

Com o comando abaixo, são exibidas as informações acumuladas desde o start da instance, considerando o Top 7 estatísticas coletadas:

COL STAT_NAME FORMAT A43
SELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time') 
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;

SQL> COL STAT_NAME FORMAT A43
SELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;SQL>   2    3    4    5
 
STAT_NAME                                   TIME_MICRO_S
------------------------------------------- ----------------
DB time                                        1,446,038,564
sql execute elapsed time                       1,350,766,144
DB CPU                                            98,757,896
PL/SQL execution elapsed time                     42,067,987
parse time elapsed                                29,276,450
hard parse elapsed time                           11,243,392
PL/SQL compilation elapsed time                    1,867,032
 
7 rows selected.

Já a consulta abaixo exibe as mesmas informações, apresentadas em porcentagem:

COL STAT_NAME FORMAT A43
SELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S
, ROUND(VALUE/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;

SQL> COL STAT_NAME FORMAT A43
SELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S
, ROUND(VALUE/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;SQL>   2    3    4    5    6
 
STAT_NAME                                   TIME_MICRO_S            PCT
------------------------------------------- ---------------- ----------
DB time                                        1,669,210,890        100
sql execute elapsed time                       1,498,828,666      89.79
DB CPU                                           213,343,730      12.78
PL/SQL execution elapsed time                     77,315,515       4.63
parse time elapsed                                30,792,527       1.84
hard parse elapsed time                           11,246,408        .67
PL/SQL compilation elapsed time                    1,867,032        .11
 
7 rows selected.

E por último, o script abaixo exibe a hierarquia das estatísticas, que já mencionamos em outro artigo:

col STAT_NAME format a60
SELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME, 
       TRUNC(VALUE/1000000,2) SECONDS 
  FROM (
SELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union
SELECT DECODE(STAT_NAME,'DB time',10) ID,
       DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'DB time' union
SELECT DECODE(STAT_NAME,'DB CPU',20) ID,
       DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'DB CPU' union
SELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,
       DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'connection management call elapsed time' union
SELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,
       DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'sequence load elapsed time' union
SELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,
       DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'sql execute elapsed time' union
SELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,
       DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'parse time elapsed' union
SELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,
       DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'hard parse elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,
       DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,
       DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,
       DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'failed parse elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,
       DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL execution elapsed time',25) ID,
       DECODE(STAT_NAME,'PL/SQL execution elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'PL/SQL execution elapsed time' union
SELECT DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',26) ID,
       DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'inbound PL/SQL rpc elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL compilation elapsed time',27) ID,
       DECODE(STAT_NAME,'PL/SQL compilation elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'PL/SQL compilation elapsed time' union
SELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,
       DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'Java execution elapsed time' union
SELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,
       DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'repeated bind elapsed time' union
SELECT DECODE(STAT_NAME,'background elapsed time',60) ID,
       DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'background elapsed time' union
SELECT DECODE(STAT_NAME,'background cpu time',61) ID,
       DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'background cpu time' union
SELECT DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',62) ID,
       DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',61) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'RMAN cpu time (backup/restore)')
CONNECT BY PRIOR id = pid START WITH id = 0;

SQL> col STAT_NAME format a60
SELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME,
       TRUNC(VALUE/1000000,2) SECONDS
  FROM (
SELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union
SELECT DECODE(STAT_NAME,'DB time',10) ID,
SQL>   2    3    4    5    6         DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'DB time' union
SELECT DECODE(STAT_NAME,'DB CPU',20) ID,
       DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
  7   WHERE STAT_NAME = 'DB CPU' union
SELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,
       DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'connection management call elapsed time' unio  8  n
SELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,
  9   10   11   12   13   14   15         DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'sequence load elapsed time' union
 16  SELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,
       DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE
 17   18   19   20   21   22   23    FROM v$sys_time_model
 24   WHERE STAT_NAME = 'sql execute elapsed time' union
SELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,
 25   26         DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 27   28   WHERE STAT_NAME = 'parse time elapsed' union
SELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,
 29   30         DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 31   32   WHERE STAT_NAME = 'hard parse elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,
 33   34         DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE
 35    FROM v$sys_time_model
 WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,
 36         DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union
 37   38   39   40   41  SELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,
       DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'failed parse elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,
 42   43   44   45   46         DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union
 47   48   49  SELECT DECODE(STAT_NAME,'PL/SQL execution elapsed time',25) ID,
       DECODE(STAT_NAME,'PL/SQL execution elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 50   51   52   WHERE STAT_NAME = 'PL/SQL execution elapsed time' union
SELECT DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',26) ID,
       DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'inbound PL/SQL rpc elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL compilation elapsed time',27) ID,
       DECODE(STAT_NAME,'PL/SQL compilation elapsed time',10) PID , STAT 53  _NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'PL/SQL compilation elapsed time' union
SELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,
       DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'Java execution elapsed time' union
 54  SELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,
       DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE
 55   56   57   58   59    FROM v$sys_time_model
 WHERE STAT_NAME = 'repeated bind elapsed time' union
SELECT DECODE(STAT_NAME,'background elapsed time',60) ID,
       DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 60   WHERE STAT_NAME = 'background elapsed time' union
SELECT DECODE(STAT_NAME,'background cpu time',61) ID,
       DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 61   62   WHERE STAT_NAME = 'background cpu time' union
 63  SELECT DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',62) ID,
       DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',61) PID , STAT_NAME, VALUE
  FROM v$sys_time_model
 WHERE STAT_NAME = 'RMAN cpu time (backup/restore)')
CONNECT BY PRIOR id = pid START WITH id = 0; 64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80   81
 
STAT_NAME                                                       SECONDS
------------------------------------------------------------ ----------
 
   DB time                                                      2086.27
     DB CPU                                                      273.72
     connection management call elapsed time                       1.16
     sequence load elapsed time                                       0
     sql execute elapsed time                                   1588.25
     parse time elapsed                                           31.74
       hard parse elapsed time                                    11.35
         hard parse (sharing criteria) elapsed time                 .14
           hard parse (bind mismatch) elapsed time                    0
       failed parse elapsed time                                    .26
 
STAT_NAME                                                       SECONDS
------------------------------------------------------------ ----------
         failed parse (out of shared memory) elapsed time             0
     PL/SQL execution elapsed time                                96.14
     inbound PL/SQL rpc elapsed time                                  0
     PL/SQL compilation elapsed time                               1.86
     Java execution elapsed time                                      0
     repeated bind elapsed time                                     .05
   background elapsed time                                       387.08
     background cpu time                                          48.47
       RMAN cpu time (backup/restore)                                 0
 
20 rows selected.
 
SQL>

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish