undo change vector size oracle,oracle 大师tom的性能分析常用包

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_cpu_time;

end;

procedure rs_middle

is

begin

g_run1 := (dbms_utility.get_cpu_time-g_start);

insert into run_stats

select 'after 1', stats.* from stats;

g_start := dbms_utility.get_cpu_time;

end;

procedure rs_stop(p_difference_threshold in number default

0)

is

begin

g_run2 := (dbms_utility.get_cpu_time-g_start);

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) );

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'

)

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 big_table

as

select *

all_objects;

create table t1

as

select * from big_table

where 1=0;

create table t2

as

select * from big_table

where 1=0;

exec runstats_pkg.rs_start;

insert into t1

select *

from big_table

where rownum <= 1000000;

commit;

exec runstats_pkg.rs_middle;

begin

for x in ( select *

from big_table

where rownum <= 1000000 )

loop

insert into t2 values X;

end loop;

commit;

end;

/

exec runstats_pkg.rs_stop(1000000);

Name  Run1  Run2

Diff

LATCH.shared pool simulator  123  1,000,147  1,000,024

STAT...session cursor cache hi  211

1,000,558  1,000,347

STAT...execute count  215  1,000,565

1,000,350

STAT...opened cursors cumulati  216

1,000,572  1,000,356

LATCH.shared pool  715

1,001,441  1,000,726

STAT...calls to get snapshot s  561

1,001,954  1,001,393

STAT...recursive calls  3,225  1,016,047  1,012,822

STAT...db block changes  134,668  2,076,454

1,941,786

LATCH.cache buffers chains  585,923

5,412,754  4,826,831

STAT...physical read bytes  115,646,464 132,284,416

16,637,952

STAT...physical read total byt 119,775,232 139,853,824

20,078,592

STAT...physical write total by 129,466,368 190,169,088

60,702,720

STAT...undo change vector size  3,863,312

67,952,600  64,089,288

STAT...redo size  120,898,108 380,951,096

260,052,988

Run1 latches total versus runs -- difference and pct

Run1  Run2  Diff  Pct

895,960  7,854,808  6,958,848  11.41%

结论：能在select中完成insert操作，尽量不用游标单行处理！

Tom大师的结论：

you should use a single SQL statement

instead of a bunch of procedural code when developing applications

whenever possible!

Mystat.sql

set echo off

set verify off

column value new_val V

define S="&1"

set autotrace off

select a.name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' ||

lower('&S')||'%'

/

set echo on

Mystat2.sql

set echo off

set verify off

select a.name, b.value V,

to_char(b.value-&V,'999,999,999,999') diff

from v$statname a, v$mystat b

where a.statistic# = b.statistic#

and lower(a.name) like '%' ||

lower('&S')||'%'

/

set echo on

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 )

-- this procedure uses authid current user so it can query

DBA_*

-- views using privileges from a ROLE and so it can be

installed

-- once per database, instead of once per user that wants to

use it

authid current_user

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;

-- inline procedure to print out numbers nicely

formatted

-- with a simple label

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

-- this query is executed dynamically in order to allow this

procedure

-- to be created by a user who has access to

DBA_SEGMENTS/TABLESPACES

-- via a role as is customary.

-- NOTE: at runtime, the invoker MUST have access to these

two

-- views!

-- this query determines if the object is an ASSM object or

not

begin

execute immediate

'select ts.segment_space_management

from dba_segments seg, dba_tablespaces ts

where seg.segment_name = :p_segname

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;

-- if the object is in an ASSM tablespace, we must use this

API

-- call to get space information, else we use the

FREE_BLOCKS

-- API for the user managed segments

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( 'FS2 Blocks (25-50) ', l_fs2_blocks );

p( 'FS3 Blocks (50-75) ', l_fs3_blocks );

p( 'FS4 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;

/