文章目录
语句基本来自tom的书。
- 按照个人习惯做了一些小修改
- 所有测试全部在PL/SQL Developer中进行
runstats 比较两个方法的优劣
通过几个监视动态性能的表来建立一个新的视图以及一个存储中间过程的表
create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.STATISTIC# = b.STATISTIC#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer;
create global temporary table run_stats(
runid varchar2(15),
name varchar2(80),
value int
)
on commit preserve rows;
创建一套存储过程来监控运行状态
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_end;
procedure rs_report(p_difference_threshold in number default 0);
end;
;
create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;
procedure rs_start
is
begin
execute immediate 'truncate table run_stats';
insert into run_stats
select 'before', stats.* from stats;
commit;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_cpu_time - g_start);
delete from run_stats t1 where t1.runid = 'after 1';
insert into run_stats
select 'after 1', stats.* from stats;
commit;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_end
is
begin
g_run2 := (dbms_utility.get_cpu_time - g_start);
delete from run_stats t where t.runid = 'after 2';
insert into run_stats
select 'after 2', stats.* from stats;
commit;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_report(p_difference_threshold in number default 0)
is
begin
dbms_output.put_line
('Run1 ran in ' || g_run1 || ' cpu hsecs');
dbms_output.put_line
('Run2 ran in ' || g_run2 || ' cpu hsecs');
if(g_run2 <> 0) then
dbms_output.put_line
('run 1 ran in ' || round(g_run1 / g_run2 * 100, 2) || '% of the time');
end if;
dbms_output.put_line(chr(9));
dbms_output.put_line
(rpad('Name', 30) || lpad('Run1', 12) || lpad('Run2', 12) || lpad('Diff', 12) || lpad('Per', 12));
for x in (
select rpad(a.name, 30) ||
to_char(b.value - a.value, '999,999,999') ||
to_char(c.value - b.value, '999,999,999') ||
to_char(((c.value - b.value) - (b.value - a.value)), '999,999,999') ||
to_char(round(decode((c.value - b.value), 0, 0, (b.value - a.value) / (c.value - b.value)) * 100, 2), '999,990.99') || '%' data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and abs((c.value - b.value) - (b.value - a.value)) > p_difference_threshold
order by abs((c.value - b.value) - (b.value - a.value))
) loop
dbms_output.put_line(x.data);
end loop;
dbms_output.put_line(chr(9));
dbms_output.put_line
('Run1 latches total versus runs -- difference and pct');
dbms_output.put_line
(lpad('Run1', 12) || lpad('Run2', 12) || lpad('Diff', 12) || lpad('Pct', 10));
for x in (
select to_char(run1, '999,999,999') ||
to_char(run2, '999,999,999') ||
to_char(diff, '999,999,999') ||
to_char(round(run1 / decode(run2, 0, to_number(0), run2) * 100, 2), '99,999.99') || '%' data
from (
select sum(b.value - a.value) run1,
sum(c.value - b.value) run2,
sum((c.value - b.value) - (b.value - a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
)
) loop
dbms_output.put_line(x.data);
end loop;
end;
end;
使用示例
按顺序逐条执行下列语句
create table test_big1 as select*from all_objects where 1 = 2;
create table test_big2 as select*from all_objects where 1 = 2;
begin
runstats_pkg.rs_start;
end;
insert into test_big1 select*from all_objects where rownum < 100000;
commit;
begin
runstats_pkg.rs_middle;
end;
begin
for x in (select*from all_objects where rownum < 100000) loop
insert into test_big2 values x;
end loop;
commit;
end;
begin
runstats_pkg.rs_end;
end;
begin
runstats_pkg.rs_report(100000);--只显示大于传入参数的差异指标,需要根据自身硬件配置与所执行语句进行调整
end;
最后会输出这样一个报告(具体信息会根据硬件的差异而有所不同)。
Run1 ran in 246 cpu hsecs
Run2 ran in 816 cpu hsecs
run 1 ran in 30.15% of the time
Name Run1 Run2 Diff Per
STAT...db block gets from cach 4,660 104,815 100,155 4.45%
STAT...db block gets 4,660 104,815 100,155 4.45%
STAT...recursive calls 7,648 108,648 101,000 7.04%
STAT...session logical reads 142,706 243,756 101,050 58.54%
LATCH.KJCT flow control latch 102,728 209,068 106,340 49.14%
STAT...session pga memory 65,536 -65,536 -131,072 -100.00%
LATCH.simulator hash latch 166,859 351,551 184,692 47.46%
STAT...db block changes 3,929 203,297 199,368 1.93%
LATCH.row cache objects 64,703 286,419 221,716 22.59%
LATCH.KCL gc element parent la 208,279 496,675 288,396 41.93%
STAT...table scan rows gotten 967,049 1,272,457 305,408 76.00%
LATCH.gcs remastering latch 70,303 400,166 329,863 17.57%
LATCH.gcs resource hash 145,950 519,769 373,819 28.08%
LATCH.cache buffers lru chain 383,463 907,621 524,158 42.25%
LATCH.object queue header oper 458,659 1,078,847 620,188 42.51%
LATCH.gcs partitioned table ha 355,457 2,275,525 1,920,068 15.62%
LATCH.process queue reference 2,219,518 4,352,024 2,132,506 51.00%
LATCH.cache buffers chains 4,934,294 11,119,628 6,185,334 44.37%
STAT...undo change vector size 274,832 6,818,300 6,543,468 4.03%
STAT...redo size 12,433,288 38,792,164 26,358,876 32.05%
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
10,046,208 23,515,003 13,468,795 42.72%
上述例子所得出的结论
将一个功能用一条语句执行与用多条语句执行相比,在效率上有压倒性的优势。参考block、redo、undo以及更关键的latch指标。
当然,这套功能的实际作用远不止于此。
修改记录
对比原书所进行的改造
- 添加了一些commit
- 添加了一些delete语句
- 将stop拆成了end与report
mystat
用于展示完成某些操作前后的某些统计结果的变化情况。功能上看,应该属于上面的runstats的子集。书中使用sqlplus写的,我这边改成了存储过程。
create or replace package mystat
as
procedure mystat1(item in varchar2);
procedure mystat2;
end;
create or replace package body mystat as
procedure mystat1(item in varchar2)
is
begin
delete from mystat_temp;
insert into mystat_temp
select t1.NAME, t2.VALUE
from v$statname t1, v$mystat t2
where t1.STATISTIC# = t2.STATISTIC#
and lower(t1.NAME) like '%' || lower(item) || '%'
;
commit;
end;
procedure mystat2
is
begin
dbms_output.put_line(rpad('name', 40) || lpad('value', 20));
for x in (
select t1.NAME, to_char(t2.VALUE - t3.value, '999,999,990.99') diff
from v$statname t1, v$mystat t2, mystat_temp t3
where t1.STATISTIC# = t2.STATISTIC#
and lower(t1.NAME) = lower(t3.name)
order by abs(t2.VALUE - t3.value) desc
) loop
dbms_output.put_line(rpad(x.NAME, 40) || lpad(x.diff, 20));
end loop;
end;
end mystat;
调用
begin
mystat.mystat1('redo');
end;
insert into test_big1 select*from all_objects where rownum < 100000;
commit;
begin
mystat.mystat2;
end;
show_space
又是一个NB的家伙,主要用于打印段的空间利用率信息。
create or replace procedure show_space
(
p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default null
) as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_lastUsedExtFileId number;
l_lastUsedExtBlockId number;
l_last_used_block number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
procedure p(p_label in varchar2, p_num in number)
is
begin
dbms_output.put_line(rpad(p_label, 40, '.') || to_char(p_num, '999,999,999,999'));
end;
begin
begin
--this query determines if the object is an ASSM or not
execute immediate '
select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segment
and (:p_partition is null or seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name
' into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line('this must be a partitioned table, use p_partition => ');
return;
end;
dbms_output.put_line('test: ASSM flag = ' || l_segment_space_mgmt);
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage(
p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes,
l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes,
l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes,
l_full_blocks, l_full_bytes,
p_partition
);
p('Unformatted Blocks', l_unformatted_blocks);
p('FS1 Blocks (0-25)', l_fs1_blocks);
p('FS1 Blocks (25-50)', l_fs2_blocks);
p('FS1 Blocks (50-75)', l_fs3_blocks);
p('FS1 Blocks (75-100)', l_fs4_blocks);
p('Full Blocks', l_full_blocks);
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks
);
p('Free Blocks', l_free_blks);
end if;
--and then the unused space API call to get the rest of the information
dbms_space.unused_space(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_lastUsedExtFileId,
last_used_extent_block_id => l_lastUsedExtBlockId,
last_used_block => l_last_used_block
);
p('Total Blocks', l_total_blocks);
p('Total Bytes', l_total_bytes);
p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
p('Unused Blocks', l_unused_blocks);
p('Unused Bytes', l_unused_bytes);
p('Last Used Ext FileId', l_lastUsedExtFileId);
p('Last Used Ext BlockId', l_lastUsedExtBlockId);
p('Last Used Block', l_last_used_block);
end show_space;
调用
begin
show_space('TEST_BIG1');
end;
输出
test: ASSM flag = AUTO
Unformatted Blocks...................... 0
FS1 Blocks (0-25)....................... 0
FS1 Blocks (25-50)...................... 0
FS1 Blocks (50-75)...................... 1
FS1 Blocks (75-100)..................... 8
Full Blocks............................. 397
Total Blocks............................ 416
Total Bytes............................. 13,631,488
Total MBytes............................ 13
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 3,334
Last Used Ext BlockId................... 4,101
Last Used Block......................... 32
big_table
创建大数据量表的一个方法,比较简单,直接自己写了一个存储过程版本的。
create or replace procedure p_insert_big_data(p_rows in number, p_table_name in varchar2) is
l_cnt number;
l_rows number := p_rows;
begin
execute immediate '
insert /*+ apppend*/
into ' || p_table_name || '
select rownum, t1.*
from all_objects t1
where rownum <= ' || p_rows || '
';
l_cnt := sql%rowcount;
commit;
while(l_cnt < l_rows)
loop
execute immediate '
insert /*+ apppend*/
into ' || p_table_name || '
select ' || l_cnt || ' + rownum, t1.*
from all_objects t1
where rownum <= ' || (l_rows - l_cnt) || '
';
dbms_output.put_line(sql%rowcount);
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end p_insert_big_data;
调用
create table test_big_table1 as
select rownum id, t1.* from all_objects t1 where 1 = 2;
begin
p_insert_big_data(321, 'TEST_BIG_TABLE1');
end;