{"id":10,"date":"2008-09-24T15:43:42","date_gmt":"2008-09-24T13:43:42","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=10"},"modified":"2011-03-08T18:55:44","modified_gmt":"2011-03-08T16:55:44","slug":"plot-oracle-historical-statistics-within-sqlplus","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/plot-oracle-historical-statistics-within-sqlplus\/","title":{"rendered":"Plot Oracle historical statistics within SQL*Plus"},"content":{"rendered":"

More than often I’m asked to investigate “what happened yesterday when performance problems appeared”.<\/p>\n

Sometimes I have the Enterprise Manager DB Console licensed, sometimes not. Sometimes I have direct SQL*Net access to the database that I may use to produce custom reports with my LAMP self-developed application. But it may happen that only an ssh access is granted to the db server.<\/p>\n

That’s why I started to develop some little scripts to plot the trends of database timed statistics.<\/p>\n

Let’s see this one:
\n
\nSQL> @sysstat.sql
\nEnter a sysstat to search for: physical reads<\/strong><\/code>
\n
\nSTAT_ID\u00a0\u00a0\u00a0\u00a0 STAT_NAME
\n----------- ------------------------------------------<\/code>
\n2263124246<\/strong> physical reads
\n4171507801 physical reads cache
\n297908839 physical reads cache prefetch
\n2589616721 physical reads direct
\n2564935310 physical reads direct (lob)
\n2663793346 physical reads direct temporary tablespace
\n473165409 physical reads for flashback new
\n3102888545 physical reads prefetch warmup
\n531193461 physical reads retry corrupt<\/code><\/p>\n

9 rows selected.<\/p>\n

Enter the desired stat_id: 2263124246<\/strong>
\nEnter the start date (YYYYMMDD) [defaults today] :<\/code> 20080922<\/strong>
\nEnter the end date date (YYYYMMDD) [defaults today] :<\/code> 20080922<\/strong>
\n
\nSTAT_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 START \u00a0\u00a0 END
\n---------------- -------- --------
\nphysical reads\u00a0\u00a0\u00a020080922 20080922<\/code><\/p>\n

BEGIN_INTERVAL_TIME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALORE PLOTTED_VALUE
\n------------------------- ---------- -------------------------
\n22-SEP-08 12.00.12.122 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0
\n22-SEP-08 01.00.28.253 AM\u00a0\u00a0\u00a0\u00a0 120092
\n22-SEP-08 02.00.05.039 AM\u00a0\u00a0\u00a0\u00a0\u00a0 35780
\n22-SEP-08 03.00.55.595 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4792
\n22-SEP-08 04.00.43.725 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4905
\n22-SEP-08 05.00.31.855 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7300
\n22-SEP-08 06.00.17.017 AM\u00a0\u00a0\u00a0\u00a0 234596
\n22-SEP-08 07.00.08.132 AM\u00a0\u00a0\u00a0\u00a0\u00a0 24651
\n22-SEP-08 08.00.50.936 AM\u00a0\u00a0\u00a0\u00a0 481884
\n22-SEP-08 09.00.33.488 AM\u00a0\u00a0\u00a0\u00a0 130201
\n22-SEP-08 10.00.03.805 AM\u00a0\u00a0\u00a0 1300306 **
\n22-SEP-08 11.00.07.764 AM\u00a0\u00a0\u00a0\u00a0 491857
\n22-SEP-08 12.00.31.548 PM\u00a0\u00a0\u00a0\u00a0 304702
\n22-SEP-08 01.01.04.880 PM\u00a0\u00a0\u00a0 1023664 *
\n22-SEP-08 02.00.17.822 PM\u00a0\u00a0\u00a0 8588180 ************
\n22-SEP-08 03.00.36.969 PM\u00a0\u00a0\u00a0 2201615 ***
\n22-SEP-08 04.01.01.397 PM\u00a0\u00a0 17237098 *************************
\n22-SEP-08 05.00.39.262 PM\u00a0\u00a0\u00a0 1606300 **
\n22-SEP-08 06.00.03.829 PM\u00a0\u00a0\u00a0\u00a0 451568
\n22-SEP-08 07.00.31.461 PM\u00a0\u00a0\u00a0\u00a0 137684
\n22-SEP-08 08.00.05.966 PM\u00a0\u00a0\u00a0\u00a0 203803
\n22-SEP-08 09.00.24.829 PM\u00a0\u00a0\u00a0\u00a0 536394
\n22-SEP-08 10.00.12.945 PM\u00a0\u00a0 10209783 **************
\n22-SEP-08 11.00.35.123 PM\u00a0\u00a0\u00a0 6151663 *********<\/code><\/p>\n

24 rows selected.<\/p>\n

Oh! At 4.00 PM we had a lot of physical reads. Nice.<\/p>\n

This is the code:<\/p>\n

-- display given statistics from DBA_HIST_SYSSTAT\r\ncol BEGIN_INTERVAL_TIME for a25\r\nset pages 100 lines 130\r\nset verify off term on\r\n\r\naccept sysstat prompt 'Enter a sysstat to search for: '\r\nselect STAT_ID, STAT_NAME\r\n  from DBA_HIST_STAT_NAME\r\n   where lower(STAT_NAME) like lower('%&sysstat%')\r\n  order by stat_name;\r\n\r\naccept stat_id prompt 'Enter the desired stat_id: '\r\naccept startdate prompt 'Start date (YYYYMMDD) [today] : '\r\naccept enddate prompt 'End date date (YYYYMMDD) [today] : '\r\n\r\nselect STAT_NAME,\r\n  nvl('&startdate',to_char(sysdate,'YYYYMMDD')) as \"START\",\r\n  nvl('&enddate',to_char(sysdate,'YYYYMMDD')) as \"END\"\r\n from DBA_HIST_STAT_NAME\r\nwhere STAT_ID = &stat_id;\r\n\r\nselect BEGIN_INTERVAL_TIME, VALORE,\r\n  substr( rpad('*',40*round( VALORE\/max(VALORE)over(),2),'*'),1,40) PLOTTED_VALORE\r\n  from (\r\n  select s.BEGIN_INTERVAL_TIME BEGIN_INTERVAL_TIME,\r\n    nvl(decode(greatest(VALUE, nvl(lag(VALUE) over\r\n      (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id),0)),\r\n    VALUE,\r\n    VALUE - lag(VALUE) over\r\n      (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id),VALUE), 0) VALORE\r\n  from DBA_HIST_SNAPSHOT s,\r\n          DBA_HIST_SYSSTAT g,\r\n          v$instance i\r\n  where s.SNAP_ID=g.SNAP_ID\r\n  and g.STAT_ID='&stat_id'\r\n  and s.BEGIN_INTERVAL_TIME >=\r\n    trunc(to_timestamp(nvl('&startdate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD'))\r\n  and s.BEGIN_INTERVAL_TIME < =\r\n   trunc(to_timestamp(nvl('&enddate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')+1)\r\n  and s.instance_number=i.instance_number\r\n  and s.instance_number=g.instance_number\r\n  order by 1\r\n);<\/pre>\n
Ciao
\n--
\nLudovico<\/p>\n","protected":false},"excerpt":{"rendered":"
More than often I’m asked to investigate “what happened yesterday when performance problems appeared”. Sometimes I have the Enterprise Manager DB Console licensed, sometimes not. Sometimes I have direct SQL*Net access to the database that I may use to produce … 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":{"footnotes":""},"categories":[3,6],"tags":[12,11,15,9,14,10,13],"class_list":["post-10","post","type-post","status-publish","format-standard","hentry","category-oracledb","category-perf","tag-awr","tag-dba_hist_sysstat","tag-graph","tag-oracle","tag-repository","tag-sqlplus","tag-workload"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/10","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=10"}],"version-history":[{"count":11,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/10\/revisions"}],"predecessor-version":[{"id":34,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/10\/revisions\/34"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=10"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=10"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=10"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}