awr snapshot report awr snapshot matrix report snap info !!!!! Author : Daniel W. Fink awr_snap_wkly_matrix.sql COLUMN sort_ord NOPRINT COLUMN snap_week NOPRINT DEFINE awr_dbid = &1 DEFINE awr_instid = &2 SET HEADING off PAGESIZE 0 TTITLE off PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name PROMPT *** Weekly by hour (intermediate snapshots not listed) SELECT DISTINCT 001 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , 'Sunday' sunday_snapid , 'Monday' monday_snapid , 'Tuesday' tuesday_snapid , 'Wednesday' wednesday_snapid , 'Thursday' thursday_snapid , 'Friday' friday_snapid , 'Saturday' saturday_snapid FROM sys.wrm$_snapshot UNION ALL SELECT 010 sort_ord , TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week , NULL hour_of_day , TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid , TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid , TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid , TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid , TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid , TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid , TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday FROM sys.wrm$_snapshot WHERE dbid = &awr_dbid AND instance_number = &awr_instid ) s UNION ALL SELECT 011 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week FROM sys.wrm$_snapshot WHERE dbid = &awr_dbid AND instance_number = &awr_instid GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL UNION ALL SELECT DISTINCT 020 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , '---------' hour_of_day , '---------' sunday_snapid , '---------' monday_snapid , '---------' tuesday_snapid , '---------' wednesday_snapid , '---------' thursday_snapid , '---------' friday_snapid , '---------' saturday_snapid FROM sys.wrm$_snapshot WHERE dbid = &awr_dbid AND instance_number = &awr_instid UNION ALL SELECT 030 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week FROM sys.wrm$_snapshot WHERE dbid = &awr_dbid AND instance_number = &awr_instid GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') , TO_CHAR(end_interval_time, 'hh24')||':00' UNION ALL SELECT DISTINCT 999 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , NULL sunday_snapid , NULL monday_snapid , NULL tuesday_snapid , NULL wednesday_snapid , NULL thursday_snapid , NULL friday_snapid , NULL saturday_snapid FROM sys.wrm$_snapshot WHERE dbid = &awr_dbid AND instance_number = &awr_instid ORDER BY snap_week , sort_ord , hour_of_day / SET HEADING ON apt-rdbms-01.aptus.co.uk:APTDB\sys> @awr_snap_wkly_matrix.sql *** Snapshots in AWR repository for Database APTDB Instance aptdb *** Weekly by hour (intermediate snapshots not listed) Sunday Monday Tuesday Wednesday Thursday Friday Saturday 08/21/11 08/22/11 08/23/11 08/24/11 08/25/11 08/26/11 08/27/11 --------- --------- --------- --------- --------- --------- --------- --------- 00:00 1920 1944 1968 01:00 1897 1921 1945 1969 02:00 1898 1922 1946 1970 03:00 1899 1923 1947 1971 04:00 1900 1924 1948 1972 05:00 1901 1925 1949 1973 06:00 1902 1926 1950 1974 07:00 1903 1927 1951 1975 08:00 1904 1928 1952 1976 09:00 1905 1929 1953 1977 10:00 1906 1930 1954 1978 11:00 1907 1931 1955 1979 12:00 1908 1932 1956 1980 13:00 1909 1933 1957 1981 14:00 1910 1934 1958 1982 15:00 1911 1935 1959 1983 16:00 1912 1936 1960 1984 17:00 1913 1937 1961 1985 18:00 1914 1938 1962 1986 19:00 1915 1939 1963 1987 20:00 1916 1940 1964 1988 21:00 1917 1941 1965 1989 22:00 1918 1942 1966 1990 23:00 1919 1943 1967 1991 ========= SET HEADING off PAGESIZE 0 linesize 200 COLUMN sort_ord NOPRINT SELECT DISTINCT 001 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , 'Sunday' sunday_snapid , 'Monday' monday_snapid , 'Tuesday' tuesday_snapid , 'Wednesday' wednesday_snapid , 'Thursday' thursday_snapid , 'Friday' friday_snapid , 'Saturday' saturday_snapid FROM sys.wrm$_snapshot UNION ALL SELECT 010 sort_ord , TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week , NULL hour_of_day , TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid , TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid , TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid , TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid , TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid , TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid , TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday FROM sys.wrm$_snapshot WHERE dbid = (select DBID from v$database) AND instance_number = sys_context ('userenv','INSTANCE') ) s UNION ALL SELECT 011 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week FROM sys.wrm$_snapshot WHERE dbid = (select DBID from v$database) AND instance_number = sys_context ('userenv','INSTANCE') GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL UNION ALL SELECT DISTINCT 020 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , '---------' hour_of_day , '---------' sunday_snapid , '---------' monday_snapid , '---------' tuesday_snapid , '---------' wednesday_snapid , '---------' thursday_snapid , '---------' friday_snapid , '---------' saturday_snapid FROM sys.wrm$_snapshot WHERE dbid = (select DBID from v$database) AND instance_number = sys_context ('userenv','INSTANCE') UNION ALL SELECT 030 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week FROM sys.wrm$_snapshot WHERE dbid = (select DBID from v$database) AND instance_number = sys_context ('userenv','INSTANCE') GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00' UNION ALL SELECT DISTINCT 999 sort_ord , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week , NULL hour_of_day , NULL sunday_snapid , NULL monday_snapid , NULL tuesday_snapid , NULL wednesday_snapid , NULL thursday_snapid , NULL friday_snapid , NULL saturday_snapid FROM sys.wrm$_snapshot WHERE dbid = (select DBID from v$database) AND instance_number = sys_context ('userenv','INSTANCE') ORDER BY snap_week, sort_ord , hour_of_day / 20211003 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 20211003 10/03/21 10/04/21 10/05/21 10/06/21 10/07/21 10/08/21 10/09/21 20211003 --------- --------- --------- --------- --------- --------- --------- --------- 20211003 00:00 36504 36528 36552 36576 20211003 01:00 36505 36529 36553 36577 20211003 02:00 36506 36530 36554 36578
Search This Blog
Total Pageviews
Thursday, 1 September 2011
Oracle awr matrix report
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
No comments:
Post a Comment