{"id":6468,"date":"2021-09-14T22:36:07","date_gmt":"2021-09-14T22:36:07","guid":{"rendered":"https:\/\/swiv.com.br\/using-time-model-to-know-which-operation-type-is-mostly-used\/"},"modified":"2026-05-27T20:02:29","modified_gmt":"2026-05-27T19:02:29","slug":"using-time-model-to-know-which-operation-type-is-mostly-used","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/09\/14\/using-time-model-to-know-which-operation-type-is-mostly-used\/","title":{"rendered":"Using Time Model to know which operation type is mostly used"},"content":{"rendered":"\n
Este artigo ser\u00e1 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\u00e3o sendo gastos no ambiente.<\/p>\n\n\n\n
Ambiente de laborat\u00f3rio:<\/p>\n\n\n
\n[oracle@oel7 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 14 19:27:26 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nNAME OPEN_MODE LOG_MODE\n--------- -------------------- ------------\nCORTEX READ WRITE ARCHIVELOG\n<\/pre><\/div>\n\n\n
Usando o SwingBench, vou deixar uma carga rodando no banco:<\/p>\n\n\n\n<\/figure>\n\n\n\n
Com o comando abaixo, s\u00e3o exibidas as informa\u00e7\u00f5es acumuladas desde o start da instance, considerando o Top 7 estat\u00edsticas coletadas:<\/p>\n\n\n
\nCOL STAT_NAME FORMAT A43\nSELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S\nFROM V$SYS_TIME_MODEL\nWHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time') \nORDER BY VALUE DESC\nFETCH FIRST 7 ROWS ONLY;\n<\/pre><\/div>\n\n
\nSQL> COL STAT_NAME FORMAT A43\nSELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S\nFROM V$SYS_TIME_MODEL\nWHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')\nORDER BY VALUE DESC\nFETCH FIRST 7 ROWS ONLY;SQL> 2 3 4 5\n \nSTAT_NAME TIME_MICRO_S\n------------------------------------------- ----------------\nDB time 1,446,038,564\nsql execute elapsed time 1,350,766,144\nDB CPU 98,757,896\nPL\/SQL execution elapsed time 42,067,987\nparse time elapsed 29,276,450\nhard parse elapsed time 11,243,392\nPL\/SQL compilation elapsed time 1,867,032\n \n7 rows selected.\n<\/pre><\/div>\n\n\n
J\u00e1 a consulta abaixo exibe as mesmas informa\u00e7\u00f5es, apresentadas em porcentagem:<\/p>\n\n\n
\nCOL STAT_NAME FORMAT A43\nSELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S\n, ROUND(VALUE\/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT\nFROM V$SYS_TIME_MODEL\nWHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')\nORDER BY VALUE DESC\nFETCH FIRST 7 ROWS ONLY;\n<\/pre><\/div>\n\n
\nSQL> COL STAT_NAME FORMAT A43\nSELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S\n, ROUND(VALUE\/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT\nFROM V$SYS_TIME_MODEL\nWHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')\nORDER BY VALUE DESC\nFETCH FIRST 7 ROWS ONLY;SQL> 2 3 4 5 6\n \nSTAT_NAME TIME_MICRO_S PCT\n------------------------------------------- ---------------- ----------\nDB time 1,669,210,890 100\nsql execute elapsed time 1,498,828,666 89.79\nDB CPU 213,343,730 12.78\nPL\/SQL execution elapsed time 77,315,515 4.63\nparse time elapsed 30,792,527 1.84\nhard parse elapsed time 11,246,408 .67\nPL\/SQL compilation elapsed time 1,867,032 .11\n \n7 rows selected.\n<\/pre><\/div>\n\n\n
E por \u00faltimo, o script abaixo exibe a hierarquia das estat\u00edsticas, que j\u00e1 mencionamos em outro artigo:<\/p>\n\n\n
\ncol STAT_NAME format a60\nSELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME, \n TRUNC(VALUE\/1000000,2) SECONDS \n FROM (\nSELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union\nSELECT DECODE(STAT_NAME,'DB time',10) ID,\n DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'DB time' union\nSELECT DECODE(STAT_NAME,'DB CPU',20) ID,\n DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'DB CPU' union\nSELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,\n DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'connection management call elapsed time' union\nSELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,\n DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'sequence load elapsed time' union\nSELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,\n DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'sql execute elapsed time' union\nSELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,\n DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'parse time elapsed' union\nSELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,\n DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'hard parse elapsed time' union\nSELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,\n DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union\nSELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,\n DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union\nSELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,\n DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'failed parse elapsed time' union\nSELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,\n DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union\nSELECT DECODE(STAT_NAME,'PL\/SQL execution elapsed time',25) ID,\n DECODE(STAT_NAME,'PL\/SQL execution elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'PL\/SQL execution elapsed time' union\nSELECT DECODE(STAT_NAME,'inbound PL\/SQL rpc elapsed time',26) ID,\n DECODE(STAT_NAME,'inbound PL\/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'inbound PL\/SQL rpc elapsed time' union\nSELECT DECODE(STAT_NAME,'PL\/SQL compilation elapsed time',27) ID,\n DECODE(STAT_NAME,'PL\/SQL compilation elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'PL\/SQL compilation elapsed time' union\nSELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,\n DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'Java execution elapsed time' union\nSELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,\n DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'repeated bind elapsed time' union\nSELECT DECODE(STAT_NAME,'background elapsed time',60) ID,\n DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'background elapsed time' union\nSELECT DECODE(STAT_NAME,'background cpu time',61) ID,\n DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'background cpu time' union\nSELECT DECODE(STAT_NAME,'RMAN cpu time (backup\/restore)',62) ID,\n DECODE(STAT_NAME,'RMAN cpu time (backup\/restore)',61) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'RMAN cpu time (backup\/restore)')\nCONNECT BY PRIOR id = pid START WITH id = 0;\n<\/pre><\/div>\n\n
\nSQL> col STAT_NAME format a60\nSELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME,\n TRUNC(VALUE\/1000000,2) SECONDS\n FROM (\nSELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union\nSELECT DECODE(STAT_NAME,'DB time',10) ID,\nSQL> 2 3 4 5 6 DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'DB time' union\nSELECT DECODE(STAT_NAME,'DB CPU',20) ID,\n DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 7 WHERE STAT_NAME = 'DB CPU' union\nSELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,\n DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'connection management call elapsed time' unio 8 n\nSELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,\n 9 10 11 12 13 14 15 DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'sequence load elapsed time' union\n 16 SELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,\n DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE\n 17 18 19 20 21 22 23 FROM v$sys_time_model\n 24 WHERE STAT_NAME = 'sql execute elapsed time' union\nSELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,\n 25 26 DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 27 28 WHERE STAT_NAME = 'parse time elapsed' union\nSELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,\n 29 30 DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 31 32 WHERE STAT_NAME = 'hard parse elapsed time' union\nSELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,\n 33 34 DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE\n 35 FROM v$sys_time_model\n WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union\nSELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,\n 36 DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union\n 37 38 39 40 41 SELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,\n DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'failed parse elapsed time' union\nSELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,\n 42 43 44 45 46 DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union\n 47 48 49 SELECT DECODE(STAT_NAME,'PL\/SQL execution elapsed time',25) ID,\n DECODE(STAT_NAME,'PL\/SQL execution elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 50 51 52 WHERE STAT_NAME = 'PL\/SQL execution elapsed time' union\nSELECT DECODE(STAT_NAME,'inbound PL\/SQL rpc elapsed time',26) ID,\n DECODE(STAT_NAME,'inbound PL\/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'inbound PL\/SQL rpc elapsed time' union\nSELECT DECODE(STAT_NAME,'PL\/SQL compilation elapsed time',27) ID,\n DECODE(STAT_NAME,'PL\/SQL compilation elapsed time',10) PID , STAT 53 _NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'PL\/SQL compilation elapsed time' union\nSELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,\n DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'Java execution elapsed time' union\n 54 SELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,\n DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE\n 55 56 57 58 59 FROM v$sys_time_model\n WHERE STAT_NAME = 'repeated bind elapsed time' union\nSELECT DECODE(STAT_NAME,'background elapsed time',60) ID,\n DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 60 WHERE STAT_NAME = 'background elapsed time' union\nSELECT DECODE(STAT_NAME,'background cpu time',61) ID,\n DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n 61 62 WHERE STAT_NAME = 'background cpu time' union\n 63 SELECT DECODE(STAT_NAME,'RMAN cpu time (backup\/restore)',62) ID,\n DECODE(STAT_NAME,'RMAN cpu time (backup\/restore)',61) PID , STAT_NAME, VALUE\n FROM v$sys_time_model\n WHERE STAT_NAME = 'RMAN cpu time (backup\/restore)')\nCONNECT 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\n \nSTAT_NAME SECONDS\n------------------------------------------------------------ ----------\n \n DB time 2086.27\n DB CPU 273.72\n connection management call elapsed time 1.16\n sequence load elapsed time 0\n sql execute elapsed time 1588.25\n parse time elapsed 31.74\n hard parse elapsed time 11.35\n hard parse (sharing criteria) elapsed time .14\n hard parse (bind mismatch) elapsed time 0\n failed parse elapsed time .26\n \nSTAT_NAME SECONDS\n------------------------------------------------------------ ----------\n failed parse (out of shared memory) elapsed time 0\n PL\/SQL execution elapsed time 96.14\n inbound PL\/SQL rpc elapsed time 0\n PL\/SQL compilation elapsed time 1.86\n Java execution elapsed time 0\n repeated bind elapsed time .05\n background elapsed time 387.08\n background cpu time 48.47\n RMAN cpu time (backup\/restore) 0\n \n20 rows selected.\n \nSQL>\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Este artigo ser\u00e1 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\u00e3o sendo gastos no ambiente. Ambiente de laborat\u00f3rio: Usando o SwingBench, vou deixar uma carga rodando no banco: Com o comando abaixo, s\u00e3o exibidas as informa\u00e7\u00f5es acumuladas desde […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-6468","post","type-post","status-publish","format-standard","hentry","category-performance"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=6468"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6468\/revisions"}],"predecessor-version":[{"id":9045,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6468\/revisions\/9045"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-33.png\")
<\/figure>\n\n\n\n