Softwareentwicklung, ETL im Data Warehouse, Reporting, Dashboards, usw. Irgendwann kommen wir immer an einer Stelle an, wo alle sagen: „Das dauert zu lange!“<\/p>\n
Damit wir ausschlie\u00dfen k\u00f6nnen, dass es nicht die Datenbank ist die den Flaschenhals bildet m\u00fcssen wir \u00fcberpr\u00fcfen, mit welchen Aufgaben die Datenbank zu diesem Zeitpunkt besch\u00e4ftigt ist. Unter einigen anderen Methoden gibt es das Auslesen der View V$SQL. Mit einer Abfrage k\u00f6nnen wir sehen, welche Statements gerade abgeschlossen sind und welche noch arbeiten.<\/p>\n
<\/p>\n
select module module,\r\n\u00a0 round(cpu_time\/1000000,2) cpu_ts,\r\n\u00a0 last_load_time ,\r\n\u00a0 round(elapsed_time \/1000000,2) elaps_ts,\r\n\u00a0 rows_processed row_cnt ,\r\n\u00a0 sql_fulltext fulltext ,\r\n\u00a0 sql_text sqltxt \r\nfrom v$sql\r\nwhere 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- just a dummy\r\n--and module\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 'pmdtm.exe'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- limit your analysis to certain software product\r\nand parsing_schema_name != 'SYS'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- exclude internal entrances\r\nand module\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 is not null\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- exclude nulls\r\nand last_load_time\u00a0\u00a0\u00a0\u00a0\u00a0 > '2017-12-29\/07:54:52'\u00a0 --\u00a0 limitation to time\r\n--and ( upper(sql_text )like '%XYZ%')\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- searching a sp\r\norder by last_load_time desc,\u00a0 elapsed_time desc;<\/pre>\n","protected":false},"excerpt":{"rendered":"Softwareentwicklung, ETL im Data Warehouse, Reporting, Dashboards, usw. Irgendwann kommen wir immer an einer Stelle an, wo alle sagen: „Das dauert zu lange!“ Damit wir ausschlie\u00dfen k\u00f6nnen, dass es nicht die Datenbank ist die den Flaschenhals bildet m\u00fcssen wir \u00fcberpr\u00fcfen, mit welchen Aufgaben die Datenbank zu diesem Zeitpunkt besch\u00e4ftigt ist. Unter einigen anderen Methoden gibt … <\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,10,11],"tags":[],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-informatica","category-oracle-database","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12"}],"version-history":[{"count":3,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":28,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions\/28"}],"wp:attachment":[{"href":"https:\/\/www.pastscho.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pastscho.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}