{"id":266,"date":"2017-12-12T10:42:29","date_gmt":"2017-12-12T09:42:29","guid":{"rendered":"https:\/\/blog.kurschies.de\/?p=266"},"modified":"2019-10-15T14:47:01","modified_gmt":"2019-10-15T12:47:01","slug":"performance-drilldown","status":"publish","type":"post","link":"https:\/\/blog.kurschies.de\/index.php\/2017\/12\/12\/performance-drilldown\/","title":{"rendered":"Performance drilldown"},"content":{"rendered":"

well, every dba might now these kind of phone calls, where a user is complaining about poor performance of the database… but if you ask questions like when or what part of the application is slow, you don’t get useful answers.
\nso you have to play “detective” and find the guy who murdered the performance \ud83d\ude09
\nBut where to start? You might generated some AWR reports and try to find something nasty – but in my opinion this is like finding a needle in a haystack, you need something covering a metric over time… my advise: use DBA_HIST_SYSMETRIC_SUMMARY<\/strong> (as long as you have the proper license!)<\/p>\n

if you are in a hurry and need something handy now, you may use this:<\/p>\n

\r\nset linesize 200\r\ncol "CPU USAGE %" for a12 heading "CPU|USAGE %"\r\ncol "LOGICAL READ IO\/s" for a12 heading "LOGICAL|READ IO\/s"\r\ncol "PHYSICAL READ IO\/s" for a12 heading "PHYSICAL|READ IO\/s"\r\ncol "PHYSICAL READ MB\/s" for a12 heading "PHYSICAL|READ MB\/s"\r\ncol "PHYSICAL WRITE IO\/s" for a12 heading "PHYSICAL|WRITE IO\/s"\r\ncol "PHYSICAL WRITE MB\/s" for a12 heading "PHYSICAL|WRITE MB\/s"\r\ncol "REDO MB\/s" for a12 heading "REDO|MB\/s"\r\ncol "BUFFER HIT RATIO" for a12 heading "BUFFER|HIT RATIO"\r\nselect \r\n    snap_id\r\n   ,to_char(begin_interval_time, 'YYYY-MM-DD HH24:MI') "DATE"\r\n   ,round(cpu_value,2) "CPU USAGE %"\r\n   ,round(logical_read_value) "LOGICAL READ IO\/s"\r\n   ,round(physical_read_value) "PHYSICAL READ IO\/s"\r\n   ,round(physical_read_byte_value\/1024\/1024) "PHYSICAL READ MB\/s"\r\n   ,round(physical_write_value) "PHYSICAL WRITE IO\/s"\r\n   ,round(physical_write_byte_value\/1024\/1024) "PHYSICAL WRITE MB\/s"\r\n   ,round(redo_byte_value\/1024\/1024,2) "REDO MB\/s"\r\n   ,round(100-(physical_read_value\/logical_read_value*100),2) "BUFFER HIT RATIO"\r\nfrom (\r\n   select \r\n       a.snap_id\r\n      ,b.begin_interval_time\r\n      ,a.metric_name\r\n      ,a.average \r\n   from dba_hist_sysmetric_summary a\r\n   inner join dba_hist_snapshot b on a.snap_id = b.snap_id\r\n   )\r\npivot (sum(average) as value for (metric_name) in (\r\n          'Logical Reads Per Sec' as logical_read\r\n         ,'Physical Reads Per Sec' as physical_read\r\n         ,'Physical Writes Per Sec' as physical_write\r\n         ,'Physical Read Total Bytes Per Sec' as physical_read_byte\r\n         ,'Physical Write Total Bytes Per Sec' as physical_write_byte\r\n         ,'Redo Generated Per Sec' as redo_byte\r\n         ,'Host CPU Utilization (%)' as cpu))\r\norder by snap_id;\r\n<\/pre>\n
Keep in Mind: this SQL gives you the average values between two snapshots, the dba_hist_sysmetric_summary view is also able to provide you the peak values…<\/p>\n
so far
\nBenjamin<\/p>\n","protected":false},"excerpt":{"rendered":"
well, every dba might now these kind of phone calls, where a user is complaining about poor performance of the database… but if you ask questions like when or what part of the application is slow, you don’t get useful … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[7,9],"class_list":["post-266","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-oracle","tag-performance"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8m6YC-4i","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/comments?post=266"}],"version-history":[{"count":8,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/266\/revisions"}],"predecessor-version":[{"id":274,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/266\/revisions\/274"}],"wp:attachment":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/media?parent=266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/categories?post=266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/tags?post=266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}