【實驗】【Tom】runstats工具使用演示
Tom的這個runstats工具作用是比較多種執行方法之間的比較資訊,請看下面的演示,很好理解。
1.建立runstats所需的表和包等
sys@sec> @runstats
sys@sec> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
2.建立測試用表:
sys@sec> conn sec_test/sec_test
sec_test@sec> create table t1 as select * from dba_objects;
Table created.
sec_test@sec> create table t2 as select * from dba_objects where 1=2;
Table created.
sec_test@sec> create table t3 as select * from dba_objects where 1=2;
Table created.
建立後的測試表資料如下:
sec_test@sec> select count(*) from t1;
COUNT(*)
----------
11334
sec_test@sec> select count(*) from t2;
COUNT(*)
----------
0
sec_test@sec> select count(*) from t3;
COUNT(*)
----------
0
3.看一看使用這個工作比較的結果
使用方法:
sys@sec> /*
sys@sec> exec runStats_pkg.rs_start;
sys@sec> exec runStats_pkg.rs_middle;
sys@sec> exec runStats_pkg.rs_stop;
sys@sec> */
1)先實行runStats_pkg.rs_start
sys@sec> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
2)執行第一種插入方法:一次性插入
sys@sec> insert into sec_test.t2 select * from sec_test.t1;
11334 rows created.
sys@sec> commit;
Commit complete.
3)再執行runStats_pkg.rs_middle
sys@sec> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
4)執行第二種插入方法:逐行插入資料
sys@sec> begin
2 for x in (select * from sec_test.t1 )
3 loop
4 insert into sec_test.t3 values x;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
5)檢視最後的比較結果,這裡僅僅顯示出數值大於100000的資訊
sys@sec> exec runStats_pkg.rs_stop(100000);
Run1 ran in 1651 hsecs
Run2 ran in 3636 hsecs
run 1 ran in 45.41% of the time
Name Run1 Run2 Diff
STAT...session pga memory 0 131,072 131,072
STAT...undo change vector size 44,896 730,000 685,104
STAT...redo size 1,170,168 4,013,696 2,843,528
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
6,719 119,256 112,537 5.63%
PL/SQL procedure successfully completed.
【附錄】runstats.sql內容
set echo on
drop table run_stats;
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
grant select any table to ops$tkyte;
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;
delete from run_stats;
commit;
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( 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
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
dbms_output.put_line( chr(9) );
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 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' ) 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 (c.value-a.value) > 0
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/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;
/
/*
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;
*/
-- The End --
1.建立runstats所需的表和包等
sys@sec> @runstats
sys@sec> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
2.建立測試用表:
sys@sec> conn sec_test/sec_test
sec_test@sec> create table t1 as select * from dba_objects;
Table created.
sec_test@sec> create table t2 as select * from dba_objects where 1=2;
Table created.
sec_test@sec> create table t3 as select * from dba_objects where 1=2;
Table created.
建立後的測試表資料如下:
sec_test@sec> select count(*) from t1;
COUNT(*)
----------
11334
sec_test@sec> select count(*) from t2;
COUNT(*)
----------
0
sec_test@sec> select count(*) from t3;
COUNT(*)
----------
0
3.看一看使用這個工作比較的結果
使用方法:
sys@sec> /*
sys@sec> exec runStats_pkg.rs_start;
sys@sec> exec runStats_pkg.rs_middle;
sys@sec> exec runStats_pkg.rs_stop;
sys@sec> */
1)先實行runStats_pkg.rs_start
sys@sec> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
2)執行第一種插入方法:一次性插入
sys@sec> insert into sec_test.t2 select * from sec_test.t1;
11334 rows created.
sys@sec> commit;
Commit complete.
3)再執行runStats_pkg.rs_middle
sys@sec> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
4)執行第二種插入方法:逐行插入資料
sys@sec> begin
2 for x in (select * from sec_test.t1 )
3 loop
4 insert into sec_test.t3 values x;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
5)檢視最後的比較結果,這裡僅僅顯示出數值大於100000的資訊
sys@sec> exec runStats_pkg.rs_stop(100000);
Run1 ran in 1651 hsecs
Run2 ran in 3636 hsecs
run 1 ran in 45.41% of the time
Name Run1 Run2 Diff
STAT...session pga memory 0 131,072 131,072
STAT...undo change vector size 44,896 730,000 685,104
STAT...redo size 1,170,168 4,013,696 2,843,528
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
6,719 119,256 112,537 5.63%
PL/SQL procedure successfully completed.
【附錄】runstats.sql內容
set echo on
drop table run_stats;
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
grant select any table to ops$tkyte;
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;
delete from run_stats;
commit;
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( 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
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
dbms_output.put_line( chr(9) );
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line
( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 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' ) 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 (c.value-a.value) > 0
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/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;
/
/*
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;
*/
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-611269/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- jQuery Validate驗證規則使用演示jQuery
- runstats安裝方法
- db2 reorg,runstatsDB2
- IBM knative101演示實驗開源專案IBM
- Presentation Helper for Mac(桌面演示工具)1.8Mac
- 題目 1118: Tom數
- 使用NumPy演示實現神經網路過程神經網路
- 使用PyTorch演示實現神經網路過程PyTorch神經網路
- Caffeinated 6.828:實驗工具指南
- 直播預告丨開源SDN互通實戰演示與經驗分享
- Jeff Bezos Has Nothing On Tom Nook
- OB導數工具使用經驗分享
- 實驗一軟體開發文件與工具的安裝與使用
- .NET使用OllamaSharp實現大模型推理對話的簡單演示大模型
- 實驗37--使用neo4j工具匯入知識圖譜
- 可以在桌面演示的小工具Presentation Helper
- libSVM使用實驗報告
- iOS超級簽名系統演示(免費體驗)iOS
- TOM貓安卓市場整站程式安卓
- 工程數學 實驗5-MATLAB最最佳化工具箱的使用Matlab
- 綜合漏洞發現工具蜻蜓使用體驗
- 使用Kubernetes演示金絲雀釋出
- RocketMQ系列一:入門級使用演示MQ
- SpringMVC入門案例 & 常用API使用演示SpringMVCAPI
- Web Socket 效能對比——Spring Boot vs TomWebSpring Boot
- Oracle實驗(03):number的使用Oracle
- 實驗四 JavaBean及Servlet使用JavaBeanServlet
- FlinkCDC 2.0使用實踐體驗
- 實操演示:使用 NineData 修改來管理 ClickHouse 的資料庫表結構資料庫
- 騰訊效能測試工具——PerfDog使用初體驗
- PowerPoint LTSC 2021 for Mac(ppt演示工具) v16.80 beta版Mac
- git的基本命令使用演示視訊Git
- 實驗三 JSP內建物件使用JS物件
- 使用 Spring Validator 介面實現驗證Spring
- 現場分屏演示工具:ProPresenter for Mac v7.15漢化版Mac
- Linux下使用Kibana教程(詳細圖文演示)Linux
- 例項演示如何使用CCE XGPU虛擬化GPU
- 簡單演示Excel中VBA程式碼的使用Excel
- Nuxt Kit 實用工具的使用示例UX