{"id":231,"date":"2013-03-28T11:42:02","date_gmt":"2013-03-28T11:42:02","guid":{"rendered":"http:\/\/tomas.papp.me.uk\/?p=231"},"modified":"2013-03-28T11:42:02","modified_gmt":"2013-03-28T11:42:02","slug":"prehlad-o-vytazeni-databazy-v-kocke","status":"publish","type":"post","link":"https:\/\/tomas.papp.me.uk\/?p=231","title":{"rendered":"Prehlad o vytazeni databazy, v kocke"},"content":{"rendered":"<pre>set serveroutput on\r\ndeclare \r\ncursor c1 is select version\r\nfrom v$instance;\r\ncursor c2 is\r\n\u00a0\u00a0\u00a0 select\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 host_name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 instance_name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime\r\n\u00a0\u00a0\u00a0\u00a0 from v$instance;\r\ncursor c4 is\r\nselect * from (SELECT count(*) cnt, substr(event,1,50) event\r\nFROM v$session_wait\r\nWHERE wait_time = 0\r\nAND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',\r\n'SQL*Net message from client')\r\nGROUP BY event\r\nORDER BY 1 DESC) where rownum &lt;6;\r\ncursor c5 is\r\nselect round(sum(value)\/1048576) as sgasize from v$sga;\r\ncursor c6 is select round(sum(bytes)\/1048576) as dbsize\r\nfrom v$datafile;\r\ncursor c7 is select 'top physical i\/o process' category, sid,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 username, total_user_io amt_used,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 round(100 * total_user_io\/total_io,2) pct_used\r\nfrom (select b.sid sid, nvl(b.username, p.name) username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(value) total_user_io\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('physical reads', 'physical writes',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical reads direct',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical reads direct (lob)',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical writes direct',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical writes direct (lob)')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 group by b.sid, nvl(b.username, p.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by 3 desc),\r\n\u00a0\u00a0\u00a0\u00a0 (select sum(value) total_io\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('physical reads', 'physical writes',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical reads direct',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical reads direct (lob)',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical writes direct',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'physical writes direct (lob)'))\r\nwhere rownum &lt; 2\r\nunion all\r\nselect 'top logical i\/o process', sid, username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_user_io amt_used,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 round(100 * total_user_io\/total_io,2) pct_used\r\nfrom (select b.sid sid, nvl(b.username, p.name) username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(value) total_user_io\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('consistent gets', 'db block gets')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 group by b.sid, nvl(b.username, p.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by 3 desc),\r\n\u00a0\u00a0\u00a0\u00a0 (select sum(value) total_io\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('consistent gets', 'db block gets'))\r\nwhere rownum &lt; 2\r\nunion all\r\nselect 'top memory process', sid,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 username, total_user_mem,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 round(100 * total_user_mem\/total_mem,2)\r\nfrom (select b.sid sid, nvl(b.username, p.name) username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(value) total_user_mem\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('session pga memory', 'session uga memory')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 group by b.sid, nvl(b.username, p.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by 3 desc),\r\n\u00a0\u00a0\u00a0\u00a0 (select sum(value) total_mem\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name in ('session pga memory', 'session uga memory'))\r\nwhere rownum &lt; 2\r\nunion all\r\nselect 'top cpu process', sid, username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 total_user_cpu,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 round(100 * total_user_cpu\/greatest(total_cpu,1),2)\r\nfrom (select b.sid sid, nvl(b.username, p.name) username,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(value) total_user_cpu\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name = 'CPU used by this session'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 group by b.sid, nvl(b.username, p.name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 order by 3 desc),\r\n\u00a0\u00a0\u00a0\u00a0 (select sum(value) total_cpu\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from v$statname c, v$sesstat a,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v$session b, v$bgprocess p\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where a.statistic# = c.statistic#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and p.paddr (+) = b.paddr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and b.sid = a.sid\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 and c.name = 'CPU used by this session')\r\nwhere rownum &lt; 2;\r\n\r\n\r\ncursor c8 is select username, sum(VALUE\/100) cpu_usage_sec\r\nfrom v$session ss, v$sesstat se, v$statname sn\r\nwhere se.statistic# = sn.statistic#\r\nand name like '%CPU used by this session%'\r\nand se.sid = ss.sid\r\nand username is not null\r\nand username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')\r\ngroup by username\r\norder by 2 desc;\r\nbegin\r\ndbms_output.put_line ('Database Version');\r\ndbms_output.put_line ('-----------------');\r\nfor rec in c1\r\nloop\r\ndbms_output.put_line(rec.version);\r\nend loop;\r\ndbms_output.put_line( chr(13) );\r\ndbms_output.put_line('Hostname');\r\ndbms_output.put_line ('----------');\r\nfor rec in c2\r\nloop\r\n\u00a0\u00a0\u00a0\u00a0 dbms_output.put_line(rec.host_name);\r\nend loop;\r\ndbms_output.put_line( chr(13) );\r\ndbms_output.put_line('SGA Size (MB)');\r\ndbms_output.put_line ('-------------');\r\nfor rec in c5\r\nloop\r\n\u00a0\u00a0\u00a0\u00a0 dbms_output.put_line(rec.sgasize);\r\nend loop;\r\ndbms_output.put_line( chr(13) );\r\ndbms_output.put_line('Database Size (MB)');\r\ndbms_output.put_line ('-----------------');\r\nfor rec in c6\r\nloop\r\n\u00a0\u00a0\u00a0\u00a0 dbms_output.put_line(rec.dbsize);\r\nend loop;\r\ndbms_output.put_line( chr(13) );\r\ndbms_output.put_line('Instance start-up time');\r\ndbms_output.put_line ('-----------------------');\r\nfor rec in c2 loop\r\n\u00a0dbms_output.put_line( rec.starttime );\r\n\u00a0 end loop;\r\ndbms_output.put_line( chr(13) );\r\n\u00a0 for b in\r\n\u00a0\u00a0\u00a0 (select total, active, inactive, system, killed\r\n\u00a0\u00a0\u00a0 from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (select count(*) total from v$session)\r\n\u00a0\u00a0\u00a0\u00a0 , (select count(*) system from v$session where username is null)\r\n\u00a0\u00a0\u00a0\u00a0 , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)\r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0 , (select count(*) inactive from v$session where status = 'INACTIVE')\r\n\u00a0\u00a0\u00a0\u00a0 , (select count(*) killed from v$session where status = 'KILLED')) loop\r\ndbms_output.put_line('Active Sessions');\r\ndbms_output.put_line ('---------------');\r\ndbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');\r\n\u00a0 end loop;\r\n\u00a0 dbms_output.put_line( chr(13) );\r\n\u00a0dbms_output.put_line( 'Sessions Waiting' );\r\n\u00a0 dbms_output.put_line( chr(13) );\r\ndbms_output.put_line('Count\u00a0\u00a0\u00a0\u00a0\u00a0 Event Name');\r\ndbms_output.put_line('-----\u00a0\u00a0\u00a0\u00a0\u00a0 -----------------------------------------------------');\r\nfor rec in c4 \r\nloop\r\ndbms_output.put_line(rec.cnt||'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '||rec.event);\r\nend loop;\r\ndbms_output.put_line( chr(13) );\r\n\r\n\r\ndbms_output.put_line('-----\u00a0\u00a0\u00a0\u00a0\u00a0 -----------------------------------------------------');\r\n\r\n\r\ndbms_output.put_line('TOP Physical i\/o, logical i\/o, memory and CPU processes');\r\ndbms_output.put_line ('---------------');\r\nfor rec in c7\r\nloop\r\ndbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);\r\nend loop;\r\n\r\n\r\ndbms_output.put_line('------------------------------------------------------------------');\r\n\r\n\r\ndbms_output.put_line('TOP CPU users by usage');\r\ndbms_output.put_line ('---------------');\r\nfor rec in c8\r\nloop\r\n\r\n\r\ndbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);\r\ndbms_output.put_line ('---------------');\r\nend loop;\r\n\r\n\r\nend;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>set serveroutput on declare cursor c1 is select version from v$instance; cursor c2 is \u00a0\u00a0\u00a0 select \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 host_name \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 instance_name \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 to_char(sysdate, &#8216;HH24:MI:SS DD-MON-YY&#8217;) currtime \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,\u00a0 to_char(startup_time, &#8216;HH24:MI:SS DD-MON-YY&#8217;) starttime \u00a0\u00a0\u00a0\u00a0 from v$instance; cursor c4 is select * from (SELECT count(*) cnt, substr(event,1,50) event FROM v$session_wait WHERE wait_time = 0 AND event [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-231","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=231"}],"version-history":[{"count":1,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/231\/revisions"}],"predecessor-version":[{"id":232,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/231\/revisions\/232"}],"wp:attachment":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}