Using bulk binds for fewer redo records

Redo logs are made of redo records; each redo record is composed by redo change vectors, each of which describes a DB block level change.

Insert with bulk binds, aka bulk insert, can boost your program’s performance by processing many rows from a cursor in one call instead of one row at a time. It also generates fewer redo change vectors therefore less amount of redo logs records.

create table t1 (
a number,
b varchar2(10)
);

[Case 1] Insert one row at a time.

DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
WHILE v_Counter <= 10 LOOP
INSERT INTO t1 (a, b) VALUES (v_Counter, v_counter);
v_Counter := v_Counter + 1;
END LOOP;
END;
/
commit;

I dump the redo logs about the changes, after finding redo log name and DBA information.
ALTER SYSTEM DUMP LOGFILE '/…/redolog-1554-1-1232324.arc' DBA MIN 1 39898 DBA MAX 1 39898;

What I see is 10 redo change records. 1 redo record per insert. I paste 1 of 10 here.

REDO RECORD – Thread:1 RBA: 0×000612.0000014d.0140 LEN: 0x016c VLD: 0×01
…
CHANGE #1 TYP:0 CLS:19 AFN:2 DBA:0×00800099 OBJ:4294967295 SCN:0×0000.00d3ccef SEQ: 1 OP:5.2 ENC:0
…
CHANGE #2 TYP:0 CLS:20 AFN:2 DBA:0x0081b92d OBJ:4294967295 SCN:0×0000.00d3ccee SEQ: 3 OP:5.1 ENC:0
…
Tablespace Undo: No
0×00000000 prev ctl uba: 0x0081b92d.03bb.07
..
CHANGE #3 TYP:0 CLS: 1 AFN:1 DBA:0x00409bda OBJ:21674 SCN:0×0000.00d3cd01 SEQ: 3 OP:11.2 ENC:0
KTB Redo
…
null: –
col 0: [ 2] c1 02
col 1: [ 1] 31

[Case 2] Bulk insert with 10 rows at a time. To get an individual redo log, I switch logfile first.

declare
type testnumberarray is table of number index by binary_integer;
type testchararray is table of varchar2(10) index by binary_integer;

v_a testnumberarray;
v_b testchararray;

cursor cu_cursor is select a,b from t1_copy; — (t1_copy has 10 records.)
begin
open cu_cursor;

loop
fetch cu_cursor bulk collect into v_a, v_b LIMIT 10;

forall i in 1 .. v_a.count
insert into t1
values
( v_a(i), v_b(i) );

exit when cu_cursor%notfound;
end loop;
close cu_cursor;
end;
/

Then I dump the new redo log for the change to blocks.

REDO RECORD – Thread:1 RBA: 0x00061b.00000043.0140 LEN: 0x01dc VLD: 0×01
…
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x008000b9 OBJ:4294967295 SCN:0×0000.00d41e1a SEQ: 1 OP:5.2 ENC:0
…
CHANGE #2 TYP:0 CLS:24 AFN:2 DBA:0x0080c891 OBJ:4294967295 SCN:0×0000.00d41e19 SEQ: 1 OP:5.1 ENC:0
…
KTB Redo
…
tabn: 0 lock: 0 nrow: 10
slot[0]: 0
slot[1]: 1
slot[2]: 2
slot[3]: 3
slot[4]: 4
slot[5]: 5
slot[6]: 6
slot[7]: 7
slot[8]: 8
slot[9]: 9
CHANGE #3 TYP:0 CLS: 1 AFN:1 DBA:0x00409bda OBJ:21691 SCN:0×0000.00d41e38 SEQ: 3 OP:11.11 ENC:0
KTB Redo
…
tabn: 0 lock: 1 nrow: 10
slot[0]: 0
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 31
slot[1]: 1
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 32
slot[2]: 2
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 33
slot[3]: 3
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 05
col 1: [ 1] 34
slot[4]: 4
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 06
col 1: [ 1] 35
slot[5]: 5
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 07
col 1: [ 1] 36
slot[6]: 6
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 08
col 1: [ 1] 37
slot[7]: 7
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 09
col 1: [ 1] 38
slot[8]: 8
tl: 8 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 0a
col 1: [ 1] 39
slot[9]: 9
tl: 9 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 2] c1 0b
col 1: [ 2] 31 30
END OF REDO DUMP

With bulk binds, Oracle generates 1 redo change records for all 10 rows, since only 1 insert sql is fired. As an result, given the same data to insert, with bulk binds generates much less amount of redo logs.

My bloginality – INTP

As an INTP, you are Introverted, iNtuitive, Thinking, Perceiving.
This makes your primary focus on Introverted Thinking with an Extraverted Intution.

This is defined as a NT personality, which is part of Carl Jung’s Rational (Knowledge Seeking) type, and more specifically the Architect or Thinker.

As a weblogger, you might not be as concerned about popularity, but more with the ideas and theories that you strive to understand. Because routines aren’t your strong point, you might be more likely to work on the concept of how to do a blog, but not be as excited to keep it up.

http://bloginality.love-productions.com/index2.php

So it is said. …

Netflix sharing “Availabile-Network Partition Tolerant” Storage practice

In May, I read the news that Netflix had been using Amazon Web Service for a year. Now, in October, Sid Anand from Netflix revealed “Netflix’s transition to high-availability storage system” practice. The paper has 2 attractive sections to me.
1. eBay’s best practice to achieve “Availabile-Network Partition Tolerant” (AP) with Oracle.
2. Netflix’s Best practice of “leaving RDBMS behind” and on SimpleDB.

Good stuff.

Load hinted SQL execution plan into SQL Plan Baselines – 11g

In 11g, I like 11g’s SQL plan management feature a lot. It comes handy when I need a good execution plan to correct CBO misbehavior. I can just load existing better SQL execution plans from cache, awr, SQL profile into SQL Plan Baseline if they exist, accept them, fix them if needed, instruct Oracle sessions to use them.

It is not always the case that an ideal execution plan already exists somewhere in database. During a production firefight, I like to tune the problematic query with SQL hints, get a good execution plan, and then use this good plan for the problematic query. It is tricky because original query and tuned query with hints are different SQL, having different SQL ID e.g. In 9i and 10g, we tweak stored outlines to achieve; in 11g, it is actually more straightforward using dbms_spm. This is how to do it.

1. Get the sql_handle for original SQL.
If no baseline exists before, you create one for the original SQL from cache.

var v_out number ;
exec :v_out:= dbms_spm.load_plans_from_cursor_cache(
sql_id => ‘&original_sql_id’,
plan_hash_value => ‘&original_plan_hash_value’ );

select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

2. Tune the original query using right hints. Execute hinted SQL. Find SQL_ID and PLAN_HASH_VALUE for the hinted SQL statements.
Validate the plan as an efficient plan.

3. Associate the hinted execution plan to the original sql_handle.

var v_out number
exec :v_out := dbms_spm.load_plans_from_cursor_cache(
sql_id => ‘&HINTED_sql_id’,
plan_hash_value => ‘&HINTED_plan_hash_value’,
sql_handle => ‘&sql_handle_for_ORIGINAL_sql’);

select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

4. Check now, you can see 2 execution plans for the same SQL. Drop the old, inefficient plans.

var v_out number
exec :v_out :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (‘&original_sql_handle’,'&original_plan_name’);

Done. Please check out a demo in the comments.

As I know, people still tweak stored outlines with hints to get a sql execution plan needed. Here is a good post about tweaking stored outline from Paul Tabashov. In 11g release 2, you can keep playing with stored outline, at the same time, use sql plan baseline in action. You just need to load tweaked stored outlines into SQL baselines using dbms_spm.migrate_stored_outline.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_spm.htm#ARPLS72437