{"id":6431,"date":"2021-09-14T00:13:02","date_gmt":"2021-09-14T00:13:02","guid":{"rendered":"https:\/\/swiv.com.br\/using-time-model-to-measure-the-database-workload-and-performance\/"},"modified":"2026-05-27T20:02:29","modified_gmt":"2026-05-27T19:02:29","slug":"using-time-model-to-measure-the-database-workload-and-performance","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/09\/14\/using-time-model-to-measure-the-database-workload-and-performance\/","title":{"rendered":"Using Time Model to measure the database workload and performance"},"content":{"rendered":"\n
Neste artigo, vamos simular o processamento no banco de dados, aumentando gradativamente o seu workload, e ver o seu reflexo na Time Model. Criaremos uma tabela para salvar os snapshots sobre DB Time, DB CPU, e o total de waits, com objetivo de analisar os dados posteriormente para compara\u00e7\u00e3o. Obviamente, na vida real, isso n\u00e3o \u00e9 necess\u00e1rio, pois temos um recurso que executa isso de forma muito mais completa e autom\u00e1tica, que abordaremos em outros artigos (AWR).<\/p>\n\n\n\n
Gerando script que criar\u00e1 a tabela que abrigar\u00e1 os dados hist\u00f3ricos:<\/p>\n\n\n
\ncat > create_tm_history.sql <<EOL \n \n-- create table to save DBTIME, DB CPU, and total waits in it \nDROP TABLE TM_HISTORY; \nDROP SEQUENCE S; \nCREATE SEQUENCE S; \nCREATE TABLE TM_HISTORY AS SELECT S.NEXTVAL AS SNAP_ID, \n DBTIME.VALUE\/1000000 DBTIME, \n DBCPU.VALUE\/1000000 DBCPU, \n (DBTIME.VALUE-DBCPU.VALUE)\/1000000 WAIT_TIME, \n (SELECT COUNT(*) FROM V\\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT \nFROM V\\$SYS_TIME_MODEL DBTIME, V\\$SYS_TIME_MODEL DBCPU \nWHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU'; \nEOL\n<\/pre><\/div>\n\n\n
Execu\u00e7\u00e3o:<\/p>\n\n\n
\n[oracle@oel7 scripts]$ pwd\n\/oracle\/scripts\n[oracle@oel7 scripts]$ cat > create_tm_history.sql <<EOL\n>\nWHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU';\nEOL> -- create table to save DBTIME, DB CPU, and total waits in it\n> DROP TABLE TM_HISTORY;\n> DROP SEQUENCE S;\n> CREATE SEQUENCE S;\n> CREATE TABLE TM_HISTORY AS SELECT S.NEXTVAL AS SNAP_ID,\n> DBTIME.VALUE\/1000000 DBTIME,\n> DBCPU.VALUE\/1000000 DBCPU,\n> (DBTIME.VALUE-DBCPU.VALUE)\/1000000 WAIT_TIME,\n> (SELECT COUNT(*) FROM V\\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT\n> FROM V\\$SYS_TIME_MODEL DBTIME, V\\$SYS_TIME_MODEL DBCPU\n> WHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU';\n> EOL\n[oracle@oel7 scripts]$\n<\/pre><\/div>\n\n\n
Script para retornar os dados da tabela criada:<\/p>\n\n\n
\ncat > display_tm_history.sql << EOL \n \n-- retrieve data from TM_HISTORY \n \nset linesize 180 \nSELECT \n TO_CHAR(DBTIME,'999,999,999') DBTIME, \n TO_CHAR(DBCPU,'999,999,999') DBCPU, \n ROUND(DBCPU - LAG(DBCPU, 1, 0) OVER (ORDER BY DBCPU)) AS DBCPU_DIFF, \n TO_CHAR(WAIT_TIME,'999,999,999,999') WAIT_TIME, \n ROUND(WAIT_TIME - LAG(WAIT_TIME, 1, 0) OVER (ORDER BY WAIT_TIME)) AS WAIT_TIME_DIFF, \n TO_CHAR((DBTIME-DBCPU)\/DBTIME*100,'99.99') || '%' WAIT_PCT, USERS_CNT, \n ROUND((DBTIME-DBCPU)\/USERS_CNT) WAIT_USER_SHARE \nFROM TM_HISTORY \nORDER BY SNAP_ID; \nEOL\n<\/pre><\/div>\n\n\n
Log:<\/p>\n\n\n
\n[oracle@oel7 scripts]$ cat > display_tm_history.sql << EOL\n>\n> -- retrieve data from TM_HISTORY\n>\n> set linesize 180\n> SELECT\n> TO_CHAR(DBTIME,'999,999,999') DBTIME,\n> TO_CHAR(DBCPU,'999,999,999') DBCPU,\n> ROUND(DBCPU - LAG(DBCPU, 1, 0) OVER (ORDER BY DBCPU)) AS DBCPU_DIFF,\n> TO_CHAR(WAIT_TIME,'999,999,999,999') WAIT_TIME,\n> ROUND(WAIT_TIME - LAG(WAIT_TIME, 1, 0) OVER (ORDER BY WAIT_TIME)) AS WAIT_TIME_DIFF,\n> TO_CHAR((DBTIME-DBCPU)\/DBTIME*100,'99.99') || '%' WAIT_PCT, USERS_CNT,\n> ROUND((DBTIME-DBCPU)\/USERS_CNT) WAIT_USER_SHARE\n> FROM TM_HISTORY\n> ORDER BY SNAP_ID;\n> EOL\n[oracle@oel7 scripts]$\n<\/pre><\/div>\n\n\n
Script para gerar os snapshots:<\/p>\n\n\n
\ncat > take_tm_snapshot.sql <<EOL \n \n-- save the current time model basic statistics into the TM_HISTORY table \nINSERT INTO TM_HISTORY \nSELECT \n S.NEXTVAL AS SNAP_ID, \n DBTIME.VALUE\/1000000 DBTIME, \n DBCPU.VALUE\/1000000 DBCPU, \n (DBTIME.VALUE-DBCPU.VALUE)\/1000000 WAIT_TIME, \n (SELECT COUNT(*) FROM V\\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT \nFROM V\\$SYS_TIME_MODEL DBTIME, V\\$SYS_TIME_MODEL DBCPU \nWHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU'; \nCOMMIT; \nEOL\n<\/pre><\/div>\n\n\n
Log:<\/p>\n\n\n
\n[oracle@oel7 scripts]$ cat > take_tm_snapshot.sql <<EOL\n>\n> -- save the current time model basic statistics into the TM_HISTORY table\n> INSERT INTO TM_HISTORY\n> SELECT\n> S.NEXTVAL AS SNAP_ID,\n> DBTIME.VALUE\/1000000 DBTIME,\n> DBCPU.VALUE\/1000000 DBCPU,\n> (DBTIME.VALUE-DBCPU.VALUE)\/1000000 WAIT_TIME,\n> (SELECT COUNT(*) FROM V\\$SESSION WHERE USERNAME IS NOT NULL) USERS_CNT\n> FROM V\\$SYS_TIME_MODEL DBTIME, V\\$SYS_TIME_MODEL DBCPU\n> WHERE DBTIME.STAT_NAME = 'DB time' AND DBCPU.STAT_NAME = 'DB CPU';\n> COMMIT;\n> EOL\n[oracle@oel7 scripts]$\n<\/pre><\/div>\n\n\n
Abrindo o SwingBench, e configurando uma carga simulando 10 usu\u00e1rios simult\u00e2neos:<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\n
Assim que vejo que os processos est\u00e3o sendo realizados no banco, executo o script para tirar o snapshot:<\/p>\n\n\n\n<\/figure>\n\n\n
\n[oracle@oel7 scripts]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 13 20:37:16 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> @take_tm_snapshot.sql\n \n1 row created.\n \n \nCommit complete.\n<\/pre><\/div>\n\n\n
Parando o processamento:<\/p>\n\n\n\n<\/figure>\n\n\n\n
Analisando o conte\u00fado de nossa tabela. O n\u00fameros de usu\u00e1rios aumentaram, e a porcetagem de Waits (que j\u00e1 eram altas, mas que podemos desconsiderar pois eu havia acabado de levantar o banco) tamb\u00e9m:<\/p>\n\n\n
Agora vou fazer a mesma din\u00e2mica, mas aumentando o n\u00famero de usu\u00e1rio para 30, e depois para 60, retirando um snapshot de cada momento:<\/p>\n\n\n\n<\/figure>\n\n\n
Com o material, podemos analisar o ambiente, at\u00e9 em rela\u00e7\u00e3o \u00e0 sua escalabilidade. Em ambientes reais, o ideal \u00e9 nos atentarmos na porcentagem de waits gerados, pois podem ser um indicativo de lentid\u00e3o. Quanto maior o n\u00famero de usu\u00e1rios clientes, maior ser\u00e1 a CPU Time e DB Time. Por\u00e9m, caso este n\u00famero aumente, e a propor\u00e7\u00e3o de CPU e DB Time n\u00e3o \u00e9 a mesma a mesma, temos um problema de escalabilidade, onde os DB Waits aumentam mais que o DB CPU.<\/p>\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":"
Neste artigo, vamos simular o processamento no banco de dados, aumentando gradativamente o seu workload, e ver o seu reflexo na Time Model. Criaremos uma tabela para salvar os snapshots sobre DB Time, DB CPU, e o total de waits, com objetivo de analisar os dados posteriormente para compara\u00e7\u00e3o. Obviamente, na vida real, isso n\u00e3o […]<\/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-6431","post","type-post","status-publish","format-standard","hentry","category-performance"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6431","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=6431"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6431\/revisions"}],"predecessor-version":[{"id":9046,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6431\/revisions\/9046"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-11.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-28.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-29.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-30.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-31.png\")
<\/figure>\n\n\n![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-32.png\")
<\/figure>\n\n\n