點選(此處)摺疊或開啟

--初始化建表
declare
v_count number;
begin
select count(1) into v_count from dba_tables where owner='SCOTT' and table_name='T_STAT_TEMP';
if v_count=1 then
execute immediate 'truncate table scott.t_stat_temp';
else
execute immediate 'create table scott.t_stat_temp(snap_id integer,name varchar2(100),value int)';
end if;
end;
--Run1執行前收集
--可從v$mystat中得到當前會話id,或從v$session中得到某會話id
insert into scott.t_stat_temp
select 1,a.name,b.value
from v$statname a,v$sesstat b
where a.statistic#=b.statistic# and b.sid=46
and a.name in ('table scans (long tables)','bytes received via SQL*Net from client',
'bytes received via SQL*Net from dblink','consistent changes','consistent gets',
'CPU used by this session','physical reads','physical writes','session pga memory','session pga memory max',
'session uga memory','session logical reads','session uga memory max','sorts (disk)','sorts (memory)','table fetch continued row',
'opened cursors cumulative','opened cursors current','DDL statements parallelized','DML statements parallelized','queries parallelized',
'buffer is not pinned count','parse time cpu','redo blocks written','redo buffer allocation retries','redo entries',
'redo log space requests','redo size','redo synch time','redo synch writes','redo wastage','redo write time',
'redo writer latching time','redo writes');
commit;
/*執行語句或等待某會話執行*/
--Run1執行後收集
insert into scott.t_stat_temp
select 2,a.name,b.value
from v$statname a,v$sesstat b
where a.statistic#=b.statistic# and b.sid=46
and a.name in ('table scans (long tables)','bytes received via SQL*Net from client',
'bytes received via SQL*Net from dblink','consistent changes','consistent gets',
'CPU used by this session','physical reads','physical writes','session pga memory','session pga memory max',
'session uga memory','session logical reads','session uga memory max','sorts (disk)','sorts (memory)','table fetch continued row',
'opened cursors cumulative','opened cursors current','DDL statements parallelized','DML statements parallelized','queries parallelized',
'buffer is not pinned count','parse time cpu','redo blocks written','redo buffer allocation retries','redo entries',
'redo log space requests','redo size','redo synch time','redo synch writes','redo wastage','redo write time',
'redo writer latching time','redo writes');
commit;
--檢視Run1的statistics
select a.name,a.value begin_value,b.value end_value,b.value-a.value diff
from scott.t_stat_temp a, scott.t_stat_temp b
where a.name=b.name and a.snap_id=1 and b.snap_id=2
order by 1 ;
--若要對比Run2,繼續
--Run2執行前收集
--可從v$mystat中得到當前會話id,或從v$session中得到某會話id
insert into scott.t_stat_temp
select 3,a.name,b.value
from v$statname a,v$sesstat b
where a.statistic#=b.statistic# and b.sid=46
and a.name in ('table scans (long tables)','bytes received via SQL*Net from client',
'bytes received via SQL*Net from dblink','consistent changes','consistent gets',
'CPU used by this session','physical reads','physical writes','session pga memory','session pga memory max',
'session uga memory','session logical reads','session uga memory max','sorts (disk)','sorts (memory)','table fetch continued row',
'opened cursors cumulative','opened cursors current','DDL statements parallelized','DML statements parallelized','queries parallelized',
'buffer is not pinned count','parse time cpu','redo blocks written','redo buffer allocation retries','redo entries',
'redo log space requests','redo size','redo synch time','redo synch writes','redo wastage','redo write time',
'redo writer latching time','redo writes');
commit;
/*執行語句或等待某會話執行*/
--Run2執行後收集
insert into scott.t_stat_temp
select 4,a.name,b.value
from v$statname a,v$sesstat b
where a.statistic#=b.statistic# and b.sid=46
and a.name in ('table scans (long tables)','bytes received via SQL*Net from client',
'bytes received via SQL*Net from dblink','consistent changes','consistent gets',
'CPU used by this session','physical reads','physical writes','session pga memory','session pga memory max',
'session uga memory','session logical reads','session uga memory max','sorts (disk)','sorts (memory)','table fetch continued row',
'opened cursors cumulative','opened cursors current','DDL statements parallelized','DML statements parallelized','queries parallelized',
'buffer is not pinned count','parse time cpu','redo blocks written','redo buffer allocation retries','redo entries',
'redo log space requests','redo size','redo synch time','redo synch writes','redo wastage','redo write time',
'redo writer latching time','redo writes');
commit;
--檢視Run2的statistics
select a.name,a.value begin_value,b.value end_value,b.value-a.value diff
from scott.t_stat_temp a, scott.t_stat_temp b
where a.name=b.name and a.snap_id=3 and b.snap_id=4
order by 1 ;
--Run1,Run2 statistics對比
select c.name,c.begin_value run1_begin_value,c.end_value run2_end_value,
d.begin_value run2_begin_value,d.end_value run2_end_value,
c.end_value-c.begin_value run1_diff,d.end_value-d.begin_value run2_diff,
(d.end_value-d.begin_value)-(c.end_value-c.begin_value) run1_run2_diff
from ( select a.name,a.value begin_value,b.value end_value,b.value-a.value diff
from scott.t_stat_temp a, scott.t_stat_temp b
where a.name=b.name and a.snap_id=1 and b.snap_id=2) c,
( select a.name,a.value begin_value,b.value end_value,b.value-a.value diff
from scott.t_stat_temp a, scott.t_stat_temp b
where a.name=b.name and a.snap_id=3 and b.snap_id=4) d
where c.name=d.name
order by 1;

oracle實用sql(7)--單個會話或會話間statistics對比

相關文章