oracle实用sql(7)--单个会话或会话间statistics对比

最新推荐文章于 2025-06-17 10:05:04 发布

转载最新推荐文章于 2025-06-17 10:05:04 发布 · 132 阅读

本文提供了一套完整的Oracle性能对比脚本，通过收集特定会话的前后统计数据来对比不同操作的影响，并展示了如何创建用于存储这些数据的表以及如何进行数据收集和对比。

点击(此处)折叠或打开

--初始化建表
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;