tigormanmsft
commented
3 years ago Naresh,
Thanks so much for the correction. I updated the script in Github, added your name to the modification comments.
Thanks again!
Tim Gorman | +1 303-885-4526 | @.**@.> Principal Cloud Solution Architect - Data & AI Customer Success Architecture & Engineering [http://mysignature/Templates/Logos/L_Microsoft.png]
From: nareshgb1 @.> Sent: Thursday, May 13, 2021 8:37 PM To: tigormanmsft/az-oracle-sizing @.> Cc: Subscribed @.***> Subject: [tigormanmsft/az-oracle-sizing] The script may not work for the case where there is an instance restart. (#1)
Add dba_hisT_snapshot in the innermost queries and use startup_time in the partition by to prevent the anomaly:
select x.instance_number, x.snap_id, to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') begin_tm, to_char(s.end_interval_time, 'DD-MON-YYYY HH24:MI:SS') end_tm, x.pio, x.cpu from (select instance_number, snap_id, pio, cpu, row_number() over (partition by instance_number order by sortby desc) rn from (select instance_number, snap_id, sum(pio) pio, sum(cpu) cpu, sum(sortby) sortby from (select instance_number, snap_id, pio, cpu, sortby from (select instance_number, snap_id, value pio, 0 cpu, (value(&&V_PIO_FACTOR)) sortby, ntile(&&V_BUCKETS) over (partition by instance_number order by value) bucket from (select s.instance_number, s.snap_id, nvl(decode(greatest(value, nvl(lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id),0)), value, value - lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id), value), 0) value from dba_hist_sysstat s, dba_hist_snapshot h where stat_name = 'physical reads' and s.dbid = (select dbid from v$database) and h.dbid = s.dbid and h.instance_number = s.instance_number and h.snap_id = s.snap_id) union all select instance_number, snap_id, 0 pio, value cpu, (value(&&V_CPU_FACTOR)) sortby, ntile(&&V_BUCKETS) over (partition by instance_number order by value) bucket from (select s.instance_number, s.snap_id, nvl(decode(greatest(value, nvl(lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id),0)), value, value - lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id), value), 0) value from dba_hist_sysstat s, dba_hist_snapshot h where stat_name = 'CPU used by this session' and s.dbid = (select dbid from v$database) and h.dbid = s.dbid and h.instance_number = s.instance_number and h.snap_id = s.snap_id)) where bucket = &&V_BUCKETS) group by instance_number, snap_id)) x, dba_hist_snapshot s where s.snap_id = x.snap_id and s.instance_number = x.instance_number and rn <= 5 order by instance_number, rn;
- You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ftigormanmsft%2Faz-oracle-sizing%2Fissues%2F1&data=04%7C01%7CTimothy.Gorman%40microsoft.com%7C24a12e69db014a229e0608d916897dd8%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637565602042364185%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=RXIWCEJOp7b6Zq5qqrZKmrmBojgNtg9egA7Lf6RQ51A%3D&reserved=0, or unsubscribehttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAOEEG3KSDUCKRXSZ2FS4SUTTNSLEVANCNFSM443VR3RQ&data=04%7C01%7CTimothy.Gorman%40microsoft.com%7C24a12e69db014a229e0608d916897dd8%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637565602042374148%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=jPH8%2FQQeWKxSDvdmIp16S6zTGQx5EwJG7tupU4X%2BMAs%3D&reserved=0.
Add dba_hisT_snapshot in the innermost queries and use startup_time in the partition by to prevent the anomaly:
select x.instance_number, x.snap_id, to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') begin_tm, to_char(s.end_interval_time, 'DD-MON-YYYY HH24:MI:SS') end_tm, x.pio, x.cpu from (select instance_number, snap_id, pio, cpu, row_number() over (partition by instance_number order by sortby desc) rn from (select instance_number, snap_id, sum(pio) pio, sum(cpu) cpu, sum(sortby) sortby from (select instance_number, snap_id, pio, cpu, sortby from (select instance_number, snap_id, value pio, 0 cpu, (value(&&V_PIO_FACTOR)) sortby, ntile(&&V_BUCKETS) over (partition by instance_number order by value) bucket from (select s.instance_number, s.snap_id, nvl(decode(greatest(value, nvl(lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id),0)), value, value - lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id), value), 0) value from dba_hist_sysstat s, dba_hist_snapshot h where stat_name = 'physical reads' and s.dbid = (select dbid from v$database) and h.dbid = s.dbid and h.instance_number = s.instance_number and h.snap_id = s.snap_id) union all select instance_number, snap_id, 0 pio, value cpu, (value(&&V_CPU_FACTOR)) sortby, ntile(&&V_BUCKETS) over (partition by instance_number order by value) bucket from (select s.instance_number, s.snap_id, nvl(decode(greatest(value, nvl(lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id),0)), value, value - lag(value) over (partition by h.startup_time, s.instance_number order by s.snap_id), value), 0) value from dba_hist_sysstat s, dba_hist_snapshot h where stat_name = 'CPU used by this session' and s.dbid = (select dbid from v$database) and h.dbid = s.dbid and h.instance_number = s.instance_number and h.snap_id = s.snap_id)) where bucket = &&V_BUCKETS) group by instance_number, snap_id)) x, dba_hist_snapshot s where s.snap_id = x.snap_id and s.instance_number = x.instance_number and rn <= 5 order by instance_number, rn;