{"id":6479,"date":"2021-09-14T22:43:00","date_gmt":"2021-09-14T22:43:00","guid":{"rendered":"https:\/\/swiv.com.br\/using-time-model-to-obtain-list-of-the-top-sessions\/"},"modified":"2026-05-27T20:02:29","modified_gmt":"2026-05-27T19:02:29","slug":"using-time-model-to-obtain-list-of-the-top-sessions","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/09\/14\/using-time-model-to-obtain-list-of-the-top-sessions\/","title":{"rendered":"Using Time Model to obtain list of the top sessions"},"content":{"rendered":"\n
Como j\u00e1 citado em artigos passados, temos uma view chamada “V$SESS_TIME_MODEL” que nos ajuda a identificar sess\u00f5es no banco de dados que tem o seu reflexo direto na Time Model, e o objetivo \u00e9 via consulta explorarmos essas informa\u00e7\u00e3o.<\/p>\n\n\n\n
Deixando nosso SwingBench consumir nosso banco de dados:<\/p>\n\n\n\n<\/figure>\n\n\n\n
Na consulta, conseguimos localizar os SIDs que est\u00e3o realizando as opera\u00e7\u00f5es no banco e o seu respectivo reflexo na Time Model:<\/p>\n\n\n
\ncol SID format 999999\ncol USERNAME format a10\ncol DBTIME format 9999999999999\ncol WAITTIME format 9999999999999\ncol WAITPCT format 99999.99\ncol ONCPUPCT format 99999.99\nSELECT M.SID, S.USERNAME\n ,VALUE DBTIME\n ,WAITTIME WAITTIME\n ,ROUND((WAITTIME\/VALUE)*100,2) WAITPCT\n ,ROUND(((VALUE-WAITTIME)\/VALUE)*100,2) ONCPU_PCT\n FROM(SELECT SID, STAT_ID, STAT_NAME ,VALUE\n ,VALUE - (LEAD(VALUE,1) OVER(PARTITION BY SID ORDER BY SID,STAT_NAME DESC)) WAITTIME\n FROM V$SESS_TIME_MODEL\n WHERE STAT_NAME IN ('DB time','DB CPU')) M,\n V$SESSION S\nWHERE M.SID = S.SID\n AND STAT_NAME='DB time' AND WAITTIME>0\nORDER BY WAITPCT DESC;\n<\/pre><\/div>\n\n
\nSQL> col SID format 999999\ncol USERNAME format a10\ncol DBTIME format 9999999999999\ncol WAITTIME format 9999999999999\ncol WAITPCT format 99999.99\ncol ONCPUPCT format 99999.99\nSQL> SELECT M.SID, S.USERNAME\n ,VALUE DBTIME\n ,WAITTIME WAITTIME\n ,ROUND((WAITTIME\/VALUE)*100,2) WAITPCT\nSQL> SQL> ,ROUND(((VALUE-WAITTIME)\/VALUE)*100,2) ONCPU_PCT\n FROM(SELECT SID, STAT_ID, STAT_NAME ,VALUE\n ,VALUE - (LEAD(VASQL> LUE,1) OVER(PARTITION BY SID ORDER BY SID,STAT_NAME DESC)) WAITTIME\n FROM V$SESS_TIME_MODEL\n WHERE STAT_NAME IN ('DB time','DB CPU')) M,\nSQL> SQL> V$SESSION S\n 2 3 4 5 6 7 8 9 10 11 WHERE M.SID = S.SID\n AND STAT_NAME='DB time' AND WAITTIME>0\nORDER BY WAITPCT DESC;\n 12 13\n SID USERNAME DBTIME WAITTIME WAITPCT ONCPU_PCT\n------- ---------- -------------- -------------- --------- ----------\n 9 SYSRAC 535781 451829 84.33 15.67\n 150 SOE 168723148 139996429 82.97 17.03\n 33 SOE 166709289 138275585 82.94 17.06\n 22 SOE 165146180 136446779 82.62 17.38\n 274 SOE 164340363 135708912 82.58 17.42\n 32 SOE 166387021 137342077 82.54 17.46\n 35 SOE 164256973 135493087 82.49 17.51\n 259 SOE 165323445 136383187 82.49 17.51\n 385 SOE 164516552 135609739 82.43 17.57\n 151 SOE 165230953 136076937 82.36 17.64\n 257 SOE 164725390 135591012 82.31 17.69\n \n SID USERNAME DBTIME WAITTIME WAITPCT ONCPU_PCT\n------- ---------- -------------- -------------- --------- ----------\n 20 SOE 164638128 135447480 82.27 17.73\n 154 SOE 163337255 134281840 82.21 17.79\n 10 SOE 163324712 134209261 82.17 17.83\n 387 SOE 161281409 132298423 82.03 17.97\n 386 SOE 163700148 134218205 81.99 18.01\n 152 SOE 159821588 130991520 81.96 18.04\n 273 SOE 159152797 130146077 81.77 18.23\n 382 SOE 156862463 127944738 81.56 18.44\n 367 SOE 160049901 130482029 81.53 18.47\n 272 SOE 160056048 130338785 81.43 18.57\n 128 SYS 360087 208223 57.83 42.17\n \n22 rows selected.\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":"
Como j\u00e1 citado em artigos passados, temos uma view chamada “V$SESS_TIME_MODEL” que nos ajuda a identificar sess\u00f5es no banco de dados que tem o seu reflexo direto na Time Model, e o objetivo \u00e9 via consulta explorarmos essas informa\u00e7\u00e3o. Ambiente de laborat\u00f3rio: Deixando nosso SwingBench consumir nosso banco de dados: Na consulta, conseguimos localizar os […]<\/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-6479","post","type-post","status-publish","format-standard","hentry","category-performance"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6479","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=6479"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6479\/revisions"}],"predecessor-version":[{"id":9044,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6479\/revisions\/9044"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-34.png\")
<\/figure>\n\n\n\n