25. August 2008

Der (meist unterschätzte) Effekt von "Bulk Binding"

English title: The (most often underestimated) effect of bulk binding

Bereits vor einiger Zeit hatte ich in einem Blog Posting über Mengenorientierung vs. Einzelsatzzugriffe mal das Thema Bulk Binds angerissen. Mit Bulk Binds kann man von PL/SQL aus mehrere DML-Operationen auf einmal ausführen.
In a previous blog posting I mentioned bulk binds in order to improve SQL performance from within PL/SQL. Bulk Binds allow to execute multiple SELECT or DML commands from PL/SQL at once.
Hintergrund der Bulk Binds (siehe auch: Oracle Dokumentation) ist die Tatsache, das SQL und PL/SQL zwar sehr eng miteinander integriert sind (bspw. gleiche Datentypen), dennoch bleiben es unterschiedliche "Engines". Wenn also ein SQL-Kommando aus PL/SQL heraus abgesetzt wird, findet ein Context Switch statt - der ist zwar (für sich betrachtet) nicht sehr teuer, macht aber was aus - besonders wenn er durch ein Schleifenkonstrukt viele Male ausgeführt werden muss.
The reason (documentation!) for the performance gains with bulk binding are the context switches between SQL and PL/SQL. Although both engines are very tightly integrated (using same data types), they are different engines. And there is some cost when invoking a SQL command from PL/SQL the "context switch". One context switch doesn't cost much - but if they're multiplied (in a loop construct) the cost gets significant.
Das folgende Skript illustriert das ein wenig. Es kommt sicherlich öfter vor, dass man mal eine Tabelle befüllen muss - wir werden nun eine Tabelle mit 50.000 Werten füllen. Zunächst mal der "konventionelle" Weg: fast jeder von uns würde das Skript wahrscheinlich zunächst mal so schreiben ...
The following script illustrates this: We're going to populate a table with a PL/SQL script. The script loops 50.000 times and inserts a sequence number and some randomly generated values into the table POPULATE. The first approach to such a requirement is as follows (I assume that nearly everybody would write it that way in the first step) ...
drop table populate
/

drop sequence seq_populate
/

create sequence seq_populate
/

create table populate(
  id     number(10),
  numval number,
  strval varchar2(200)
)
/

set timing on

prompt **********************************************************
prompt * Conventional INSERT
prompt **********************************************************
prompt 

begin
  for i in 1..50000 loop
    insert into populate values (seq_populate.nextval, dbms_random.value(1,1000), dbms_random.string('X', 10));
  end loop;
end;
/

commit
/
Sieht bei mir so aus ...
Looks as follows on my system ...
:

**********************************************************
* Convential INSERT
**********************************************************


PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:11.13
Das ganze machen wir nun als Bulk Bind in "einem Rutsch". Das bedeutet, dass alle Werte zunächst als Array aufbereitet werden - und dieses Array wird dann mit einem Context Switch "von PL/SQL an SQL" gereicht. Also statt 50.000 Context Switches nur noch einer ...
Now we do the same with bulk binding - all at once. So we firstly need to populate an in-memory array - this array is then passed to the SQL engine - "at once". Instead of 50.000 context switches there is now only one.
truncate table populate
/

prompt **********************************************************
prompt * Bulk Bind: Do it all at once
prompt **********************************************************
prompt 

declare
  type num_array is table of number index by binary_integer;
  type str_array is table of varchar2(200) index by binary_integer;
  v_na num_array;
  v_sa str_array;
begin
  for i in 1..50000 loop
    v_na(i) := dbms_random.value(1,1000);
    v_sa(i) := dbms_random.string('X', 10);
  end loop;
  forall i in v_na.first..v_na.last
    insert into populate values (seq_populate.nextval, v_na(i), v_sa(i));
end;
/
Das geht schon schneller ...
This is much faster ...
:

**********************************************************
* Bulk Bind: Do it all at once
**********************************************************


PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:03.39
Nun ist diese reine Lehre vielleicht nicht immer das richtige - denn das gesamte Array, welches hier zusammengestellt wird, braucht ja auch Speicherplatz. Und wenn wir nun anstatt 50.000 10 Millionen Zeilen zu verarbeiten haben, könnten wir hier in Probleme laufen. Also gehen wir einen Kompromiß ein. Wir arbeiten mit Schleife und Bulk Bind, nehmen aber kleinere Arrays ...
Now - although this was very much faster ... there might be an issue here. The array is in-memory - so it needs some space "in memory". Assume that there are 10 million rows instead of 50.000 - then the memory requirement for preparing the arrays might lead to problems. So we have to make a deal here: We keep the ordinary loop and the bulk binding, but we'll use smaller arrays ...

truncate table populate
/

prompt **********************************************************
prompt * Bulk Bind: Split the bulk bind into 100 small units
prompt **********************************************************
prompt 

declare
  type num_array is table of number index by binary_integer;
  type str_array is table of varchar2(200) index by binary_integer;
  v_na num_array;
  v_sa str_array;
begin
  for j in 1..100 loop
    for i in 1..500 loop
      v_na(i) := dbms_random.value(1,1000);
      v_sa(i) := dbms_random.string('X', 10);
    end loop;
    forall i in v_na.first..v_na.last
      insert into populate values (seq_populate.nextval, v_na(i), v_sa(i));
  end loop;
end;
/
Also fügen wir 100 Mal ein Array von 500 Zeilen ein - anstelle von 50.000 Context Switches also 100. Dass Ergebnis kann sich sehen lassen:
So we have 100 context switches - each inserts 500 rows. The result looks pretty well:
:
**********************************************************
* Bulk Bind: Split the bulk bind into 100 small units
**********************************************************


PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:03.21
Gut - wenn die Arrays vorher komplett zusammengestellt werden, kann man das ja einsehen - wie ist es aber, wenn die Informationen nicht generiert werden, sondern auf Tabellen basieren?
Wir wandeln das Skript nun also ein wenig um: Die 50.000 Zeilen werden nicht einfach per DBMS_RANDOM erstellt, sondern wir nehmen uns "Basisdaten" aus der View ALL_OBJECTS. Wir nehmen die Objekt-ID und den Objektnamen - die Objekt-ID wird mit Zwei multipliziert und der Objektname wird in zufällige Zeichen umgewandelt (gleiche Länge). Die Ergebnisse werden dann in die Tabelle POPULATE eingetragen. Das folgende Skript tut dies zunächst Zeile für Zeile (also ohne Bulk Binds) und dann mit dem oben beschriebenen "Kompromiß".
OK ... so one might argue that this is obvious for just populating tables with generated values. But what if the generated values are based on information in other tables? Is this still true when we do SELECTs in one table and insert values - based on this information - into another table?
We give it a try: We take information from the dictionary view ALL_OBJECTS. The Object ID is multiplied by two and we generate a random string with the length of the OBJECT_NAME column. Both values - and the sequence number are inserted into the table POPULATE. The following script firstly does it row-by-row (without bulk binding) and then with the same using bulk binds (array size 500).
drop table populate
/

drop sequence seq_populate
/

create sequence seq_populate
/

create table populate(
  id     number(10),
  numval number,
  strval varchar2(200)
)
/

set timing on

prompt **********************************************************
prompt * Conventional INSERT
prompt **********************************************************
prompt 

begin
  for i in (select object_id, object_name from all_objects where rownum <= 50000) loop
    insert into populate values (
     seq_populate.nextval, i.object_id * 2, dbms_random.string('X', length(i.object_name))
    );
  end loop;
end;
/

commit
/

truncate table populate
/

prompt **********************************************************
prompt * Bulk Bind: Split the bulk bind into 100 small units
prompt **********************************************************
prompt 

declare
  type num_array is table of number index by binary_integer;
  type str_array is table of varchar2(200) index by binary_integer;
  v_na num_array;
  v_sa str_array;

  CURSOR c IS SELECT object_id, object_name from all_objects where rownum <= 50000;
begin
  open c;
  loop
    fetch c bulk collect into v_na, v_sa limit 500;
    exit when v_na.count = 0;
    for i in v_na.first..v_na.last loop
      v_na(i) := v_na(i) * 2;
      v_sa(i) := dbms_random.string('X', length(v_sa(i)));
    end loop;
    forall i in v_na.first..v_na.last
      insert into populate values (seq_populate.nextval, v_na(i), v_sa(i));
  end loop;
  close c;
end;
/
Und gerade hier würde man zunächst vermuten, dass die "konventionelle" Verarbeitung schneller ist - eine Zeile aus ALL_OBJECTS lesen, verarbeiten und sofort in die Tabelle POPULATE schreiben - ohne irgendwelche in-memory Strukturen. Aber die Bulk Binds sind auch hier schneller ...
And this is a case where one might think that the "conventional" approach is faster - read one row from ALL_OBJECTS and write the depending information instantly without building any in-memory structures. But bulk binds are -again- faster ...
**********************************************************
* Conventional INSERT
**********************************************************


PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:27.20

:

**********************************************************
* Bulk Bind: Split the bulk bind into 100 small units
**********************************************************


PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:18.29
Man sieht also, dass sich die "Mehrarbeit" hier durchaus lohnt; die Context Switches machen im "konvetionellen" Teil ein Drittel der Zeit aus - das ist schon signifikant.
So using bulk binds is worth the efforts - one third of the time is spent for the context switches are (in this case).

1. August 2008

Mit SQL*Plus auf eine APEX Session "aufsetzen"

English title: Joining an Oracle Application Express session: from SQL*Plus

Heute habe ich im Rahmen der deutschen APEX Community einen Tipp veröffentlicht, wie man von SQL*Plus aus "auf eine APEX Session aufsetzen" kann und dann Zugriff auf den Session State hat. Da dies vielleicht auch für manche von euch interessant ist, hier einfach nochmal ...
Today I released a SQL script for attaching to an Oracle Application Express session from SQL*Plus on the website of the German Application Express Community. Since this might be also of interest to some of you here's the script also as a blog posting.
Also ... zunächst der Session-State in der normalen "APEX Ansicht" (Willkommensseite der "Sample Application"). Wenn Ihr also eine APEX Session am Laufen habt, startet SQL*Plus als der Datenbankuser, dem der APEX Workspace gehört (Parsing Schema der Applikation) und lasst dann dieses Skript hier laufen ...
So ... this image shows the browser's view on the state of the current APEX session (the welcome page of the "Sample Application"). Now start SQL*Plus, log on as the database user which owns the APEX Workspace (Application Parsing Schema) and start the following script ...
set verify off

prompt ***********************************************************************
prompt * Verbindung zu einer APEX-Session ...
prompt ***********************************************************************
prompt 

accept WSNAME                 prompt '>> APEX-Workspace (Name):          '
accept SESSID                 prompt '>> APEX Session-ID:                '
accept APP                    prompt '>> APEX Anwendungs-ID:             '
accept APUSER default 'ADMIN' prompt '>> APEX User:              [ADMIN] '

set timing off

declare
  l_cgivar_name owa.vc_arr;
  l_cgivar_val  owa.vc_arr;
  l_wsid        number;
begin
  htp.init;

  l_cgivar_name(1) := 'REQUEST_PROTOCOL';
  l_cgivar_val(1)  := 'HTTP';
  owa.init_cgi_env(
      num_params => 1,
      param_name => l_cgivar_name,
      param_val  => l_cgivar_val );
 
  select workspace_id into l_wsid from apex_workspaces where workspace='&WSNAME.';
  wwv_flow_api.set_security_group_id(
    p_security_group_id=>l_wsid
  );

  apex_application.g_flow_id      := &APP.;
  apex_application.g_instance     := &SESSID.;
  apex_application.g_flow_step_id := 1;

  apex_custom_auth.define_user_session('&APUSER.','&SESSID.');
  wwv_flow_custom_auth_std.post_login(
    p_uname => '&APUSER.',
    p_session_id => apex_application.g_instance,
    p_flow_page => apex_application.g_flow_id||':'||apex_application.g_flow_step_id
  );
  wwv_flow.show (
    p_flow_id      => apex_application.g_flow_id,
    p_flow_step_id => apex_application.g_flow_step_id,
    p_instance     => apex_application.g_instance
  );
end;
/

accept ITEM                   prompt '>> Inhalt von APEX Element zeigen: '

select v('&ITEM.') "&ITEM." from dual
/
Die Ausgabe sollte dann in etwa so aussehen ...
The output should then look like the following ...
C:\>sqlplus [username]/[password]

SQL*Plus: Release 10.2.0.1.0 - Production on Fr Aug 1 12:29:51 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Verbunden mit:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @attach_session
***********************************************************************
* Verbindung zu einer APEX-Session ...
***********************************************************************

>> APEX-Workspace (Name):          PARTNER
>> APEX Session-ID:                3934214310093418
>> APEX Anwendungs-ID:             120
>> APEX User:              [ADMIN] DEMO

PL/SQL procedure successfully completed.

>> Inhalt von APEX Element zeigen: P1_QUOTA

P1_QUOTA
--------------------------------------------------------------------------------
  $15,000.00

1 row selected.

SQL>
Viel Spaß damit ...
Have fun!

Beliebte Postings