ITPub博客

首页 > 应用开发 > IT综合 > How to update aggregate history Info(zt from asktom)

How to update aggregate history Info(zt from asktom)

原创 IT综合 作者:jametong 时间:2005-03-22 23:29:59 0 删除 编辑
You Asked (Jump to Tom's latest followup)
Hi Tom,

We have a busy OLTP database that shares many schemas for many applications. A
workflow application has a dbms job that runs every 5 minutes. Here is what it
does:

1. delete from ;
2. insert into
select *
from
,
<
inline view with sum(case when..) as period1,...
... sum(case when ...) as period10,
count(*), from ( select,,from tranxn tables..)
group by, outer join
> order by ..; <-- complex datawarehouse type query

Inserts ~150,000 rows.

Online operators query the summary table actively. This works good for them.

I see two issues. A single execution generates
1) 150M redo
2) 5 million buffers gets

This is not a problem yet. Future needs would increase the number of rows 2 or 3
times.

For the excess redo, I cannot use NOLOGGING/APPEND as the database is set to
FORCE LOGGING mode (for Standby database). I cannot use the session specific
GLOBAL TEMPORARY TABLE -- tried it, generates only 900K redo - nice!

I'd appreciate if you could give suggestions to tune/redesign for both issues.

*** Additional Information ***
1. The table being inserted has just one Primary Key index on a ID
varchar2(160)!? column.

2. The select statement in the insert uses 2 indexes with a range scan; I ran
the select alone seperately, this generates ~2500 buffer gets and ~6000 physical
reads.

3. The delete generates redo of ~93M; insert does a ~52M of redo.

4. Statistics are below for the delete and insert:

Delete:
Statistics
----------------------------------------------------------
295 recursive calls
644084 db block gets
3580 consistent gets
188 physical reads
97846596 redo size
795 bytes sent via SQL*Net to client
687 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
153720 rows processed

Insert:
Statistics
----------------------------------------------------------
79 recursive calls
3309832 db block gets
2338020 consistent gets
6306 physical reads
54814692 redo size
799 bytes sent via SQL*Net to client
6902 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
153720 rows processed

Thank you!
and we said...
Well, we can use partitions to reduce to a tiny fraction the redo (and insert 
/*+ append */ would "help" you in reducing redo for it skips undo generation on
the table insert -- but since you don't truncate, you'd always be writing above
the HWM....)


So, how can we reduce the redo?

by reducing significantly the undo generated. We can in fact skip undo on both
the TABLE and the INDEX by using a partitioned table with a single partition.
Consider this small example:


ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 OWNER,
4 OBJECT_NAME,
5 SUBOBJECT_NAME,
6 OBJECT_ID,
7 DATA_OBJECT_ID,
8 OBJECT_TYPE,
9 CREATED,
10 LAST_DDL_TIME,
11 TIMESTAMP,
12 STATUS,
13 TEMPORARY,
14 GENERATED,
15 SECONDARY
16 )
17 PARTITION BY RANGE (object_id)
18 (
19 PARTITION junk VALUES LESS THAN (MAXVALUE)
20 )
21 as
22 select * from all_objects where rownum <= 5
23 /

Table created.

where object_id < maxvalue, single partition -- starts with just 5 rows in
this case



ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(object_id)
using index local;

Table altered.

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true
);
PL/SQL procedure successfully completed.

so, that is your table you want to refresh from time to time... We'll set up a
one time "mirror" of that table:



ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table new_t
2 as
3 select *
4 from all_objects
5 where 1=0;

Table created.

it'll start empty. We create a view and a procedure on table T to see what
effect this technique will have on them "invalidation wise" (none as we'll
see)



ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v as select * from t;

View created.

ops$tkyte@ORA9IR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t ) loop null; end loop;
5 end;
6 /

Procedure created.

ok, here is the refresh routine, I'll inject comments in bold in it:


ops$tkyte@ORA9IR2> create or replace procedure refresh_t
2 as
3 no_constraint exception;
4 pragma exception_init( no_constraint, -2443 );
5 l_numrows number;
6 l_numblks number;
7 l_numlblks number;
8 l_avgrlen number;
9 l_numdist number;
10 l_avglblk number;
11 l_avgdblk number;
12 l_clstfct number;
13 l_indlevel number;
14 begin

remove pretty much the redo from the "delete" (your biggest part!), truncate
is standby safe 100% it just updates the dictionary:


15 execute immediate 'truncate table new_t';
16

Now, we don't want to maintain the index in real time, so, if one exists, drop
it, ignore "no constraint" errors:


17 begin
18 execute immediate 'alter table new_t drop constraint
new_t_pk';
19 exception
20 when no_constraint
21 then
22 null;
23 end;
24

Now, using insert append (skips undo generation on the table) load the new
table:


25 insert /*+ append */ into new_t
26 select *
27 from all_objects;
28

and index it, the create index doesn't have to generate undo (we just rollback
the dictionary change -- standby "safe"


29 execute immediate
30 'alter table new_t
31 add constraint new_t_pk
32 primary key(object_id)';
33

lets get the correct stats and save them, if you use column stats, you can add
that as well:


34 dbms_stats.gather_table_stats
35 ( user, 'NEW_T', cascade=>true );
36
37 dbms_stats.get_table_stats
38 ( user, 'NEW_T', numrows => l_numrows,
39 numblks => l_numblks, avgrlen => l_avgrlen );
40
41 dbms_stats.get_index_stats
42 ( user, 'NEW_T_PK',
43 numrows => l_numrows, numlblks => l_numlblks,
44 numdist => l_numdist, avglblk => l_avglblk,
45 avgdblk => l_avgdblk, clstfct => l_clstfct,
46 indlevel => l_indlevel );
47

now, do the swap -- make the newly loaded table become the partitioned table
and vice versa:


48 execute immediate
49 'alter table t
50 exchange partition junk
51 with table new_t
52 including indexes
53 without validation';
54

and update the table level stats, the swap will get the partition stats --
this just makes the table level stuff "correct" as well



55 dbms_stats.set_table_stats
56 ( user, 'T',
57 numrows => l_numrows, numblks => l_numblks,
58 avgrlen => l_avgrlen );
59
60 dbms_stats.set_index_stats
61 ( user, 'T_PK',
62 numrows => l_numrows, numlblks => l_numlblks,
63 numdist => l_numdist, avglblk => l_avglblk,
64 avgdblk => l_avgdblk, clstfct => l_clstfct,
65 indlevel => l_indlevel );
66
67 end;
68 /

Procedure created.

Ok, lets see what we see, remember table starts with 5 rows:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name
= 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 5

ops$tkyte@ORA9IR2> select index_name, num_rows from user_indexes where
index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 5

ops$tkyte@ORA9IR2> select table_name, num_rows from user_tab_partitions where
table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 5

ops$tkyte@ORA9IR2> select index_name, num_rows from user_ind_partitions where
index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 5


we do the swap:


ops$tkyte@ORA9IR2> exec refresh_t;

PL/SQL procedure successfully completed.


code and views stay "valid", they are safe

ops$tkyte@ORA9IR2> select object_name, status from user_objects where
object_name in ('V','P');

OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID


and the stats are all in place...


ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name
= 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 26334

ops$tkyte@ORA9IR2> select index_name, num_rows from user_indexes where
index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 26334

ops$tkyte@ORA9IR2> select table_name, num_rows from user_tab_partitions where
table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 26334

ops$tkyte@ORA9IR2> select index_name, num_rows from user_ind_partitions where
index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 26334


Now, how about redo, what effect will this have on it?

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off

NAME VALUE
------------------------------ ----------
redo size 29524876

ops$tkyte@ORA9IR2> exec refresh_t;

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off

NAME V DIFF
------------------------------ ---------- ----------------
redo size 30085276 560,400

that way took about 560k of redo, lets check out the delete+insert approach:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off

NAME VALUE
------------------------------ ----------
redo size 30085276

ops$tkyte@ORA9IR2> delete from new_t;

26334 rows deleted.

ops$tkyte@ORA9IR2> insert into new_t select * from all_objects;

27894 rows created.

ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off

NAME V DIFF
------------------------------ ---------- ----------------
redo size 48908380 18,823,104


18meg, very significant difference -- you'll find the consistent gets go way
down too (that was the index I think you'll discover)
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94317/viewspace-793672/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280612