{"action":"opened","issue":{"url":"https://api.github.com/repos/tuananh0788/Exadata/issues/9","repository_url":"https://api.github.com/repos/tuananh0788/Exadata","labels_url":"https://api.github.com/repos/tuananh0788/Exadata/issues/9/labels{/name}","comments_url":"https://api.github.com/repos/tuananh0788/Exadata/issues/9/comments","events_url":"https://api.github.com/repos/tuananh0788/Exadata/issues/9/events","html_url":"https://github.com/tuananh0788/Exadata/issues/9","id":382480057,"node_id":"MDU6SXNzdWUzODI0ODAwNTc=","number":9,"title":"capture the PGA allocation and usage for the past 60 days.","user":{"login":"tuananh0788","id":30109770,"node_id":"MDQ6VXNlcjMwMTA5Nzcw","avatar_url":"https://avatars3.githubusercontent.com/u/30109770?v=4","gravatar_id":"","url":"https://api.github.com/users/tuananh0788","html_url":"https://github.com/tuananh0788","followers_url":"https://api.github.com/users/tuananh0788/followers","following_url":"https://api.github.com/users/tuananh0788/following{/other_user}","gists_url":"https://api.github.com/users/tuananh0788/gists{/gist_id}","starred_url":"https://api.github.com/users/tuananh0788/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tuananh0788/subscriptions","organizations_url":"https://api.github.com/users/tuananh0788/orgs","repos_url":"https://api.github.com/users/tuananh0788/repos","events_url":"https://api.github.com/users/tuananh0788/events{/privacy}","received_events_url":"https://api.github.com/users/tuananh0788/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-11-20T03:04:04Z","updated_at":"2018-11-20T03:04:04Z","closed_at":null,"author_association":"OWNER","body":"WITH\r\npgastat_denorm_1 AS (\r\nSELECT /*+ MATERIALIZE NO_MERGE */\r\n       snap_id,\r\n       dbid,\r\n       instance_number,\r\n       SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,\r\n       SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,\r\n       SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,\r\n       SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,\r\n       SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,\r\n       SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,\r\n       SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,\r\n       SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,\r\n       SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,\r\n       SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,\r\n       SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,\r\n       SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,\r\n       SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,\r\n       SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem\r\n  FROM dba_hist_pgastat\r\n WHERE name IN\r\n('PGA memory freed back to OS'\r\n,'aggregate PGA auto target'\r\n,'aggregate PGA target parameter'\r\n,'bytes processed'\r\n,'extra bytes read/written'\r\n,'global memory bound'\r\n,'maximum PGA allocated'\r\n,'maximum PGA used for auto workareas'\r\n,'maximum PGA used for manual workareas'\r\n,'total PGA allocated'\r\n,'total PGA inuse'\r\n,'total PGA used for auto workareas'\r\n,'total PGA used for manual workareas'\r\n,'total freeable PGA memory'\r\n)\r\n   AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)\r\n GROUP BY\r\n       snap_id,\r\n       dbid,\r\n       instance_number\r\n),\r\npgastat_denorm_2 AS (\r\nSELECT /*+ MATERIALIZE NO_MERGE */\r\n       h.dbid,\r\n       h.instance_number,\r\n       s.startup_time,\r\n       MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,\r\n       MIN(h.bytes_processed) bytes_processed,\r\n       MIN(h.extra_bytes_rw) extra_bytes_rw\r\n  FROM pgastat_denorm_1 h,\r\n       dba_hist_snapshot s\r\n WHERE s.snap_id = h.snap_id\r\n   AND s.dbid = h.dbid\r\n   AND s.instance_number = h.instance_number\r\n GROUP BY\r\n       h.dbid,\r\n       h.instance_number,\r\n       s.startup_time\r\n),\r\npgastat_delta AS (\r\nSELECT /*+ MATERIALIZE NO_MERGE */\r\n       h1.snap_id,\r\n       h1.dbid,\r\n       h1.instance_number,\r\n       s1.begin_interval_time,\r\n       s1.end_interval_time,\r\n       ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,\r\n       (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,\r\n       h1.aggr_pga_auto_target,\r\n       h1.aggr_pga_target_param,\r\n       (h1.bytes_processed - h0.bytes_processed) bytes_processed,\r\n       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,\r\n       h1.global_memory_bound,\r\n       h1.max_pga_allocated,\r\n       h1.max_pga_used_aut_wa,\r\n       h1.max_pga_used_man_wa,\r\n       h1.tot_pga_allocated,\r\n       h1.tot_pga_inuse,\r\n       h1.tot_pga_used_aut_wa,\r\n       h1.tot_pga_used_man_wa,\r\n       h1.tot_freeable_pga_mem\r\n  FROM pgastat_denorm_1 h0,\r\n       pgastat_denorm_1 h1,\r\n       dba_hist_snapshot s0,\r\n       dba_hist_snapshot s1,\r\n       pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */\r\n WHERE h1.snap_id = h0.snap_id + 1\r\n   AND h1.dbid = h0.dbid\r\n   AND h1.instance_number = h0.instance_number\r\n   AND s0.snap_id = h0.snap_id\r\n   AND s0.dbid = h0.dbid\r\n   AND s0.instance_number = h0.instance_number\r\n   AND s1.snap_id = h1.snap_id\r\n   AND s1.dbid = h1.dbid\r\n   AND s1.instance_number = h1.instance_number\r\n   AND s1.snap_id = s0.snap_id + 1\r\n   AND s1.startup_time = s0.startup_time\r\n   AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */\r\n   AND min.dbid = s1.dbid\r\n   AND min.instance_number = s1.instance_number\r\n   AND min.startup_time = s1.startup_time\r\n)\r\nSELECT snap_id,\r\n       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,\r\n       TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,\r\n       ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,\r\n       ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,\r\n       ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,\r\n       ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,\r\n       ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,\r\n       ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,\r\n       ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,\r\n       ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,\r\n       ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,\r\n       ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,\r\n       ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,\r\n       ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,\r\n       ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,\r\n       ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,\r\n       0 dummy_15\r\n  FROM pgastat_delta\r\n GROUP BY\r\n       snap_id\r\n ORDER BY\r\n       snap_id;"}}
{"actor":{"display_login":"tuananh0788"}}