How to tune something doing nothing?

To end this year in glory, I tried to replace a row-level trigger (LSC_TR1) into a statement level trigger (LSC_TR2)


SQL> create global temporary table lsc_t1(x number primary key) 
  2  on commit delete rows;
Table created.
SQL> create table lsc_t2(x number primary key);
Table created.
SQL> create table lsc_t3(x number primary key);
Table created.
SQL> create or replace function f return number deterministic is 
  2  begin sys.dbms_lock.sleep(1); return 10; end;
  3  /
Function created.
SQL> create or replace trigger lsc_tr1 after insert on lsc_t1 for each row
  2  begin
  3    if (:new.x > f)
  4    then
  5     insert into lsc_t2(x) values (:new.x);
  6    end if;
  7  end;
  8  /
Trigger created.
SQL> create or replace trigger lsc_tr2 after insert on lsc_t1
  2  begin
  3    insert into lsc_t2(x) select x from lsc_t1 where x > f;
  4  end;
  5  /
Trigger created.
SQL> insert into lsc_t3 values(1);
1 row created.
SQL> insert into lsc_t3 values(5);
1 row created.
SQL> insert into lsc_t3 values(20);
1 row created.
SQL> insert into lsc_t3 values(50);
1 row created.
SQL> insert into lsc_t3 values(100);
1 row created.
SQL> insert into lsc_t3 values(200);
1 row created.
SQL> insert into lsc_t3 values(1000);
1 row created.
SQL> commit;
Commit complete.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:07.05

The current trigger LSC_TR1 is very slow, it is doing some processing after each row. Since it is a temporary staging table, it makes no sense to process each row, I have written the trigger LSC_TR2 to boost the performance.

Have a look.


SQL> truncate table lsc_t2;
Table truncated.
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:01.03

Much faster ! LSC_TR1 just takes ages. What a glorious year 2008 :)

Well, the table LSC_T3 is empty most of the time, and the insert is running again and again…


SQL> truncate table lsc_t3;
Table truncated.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:00.01
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:01.03

Suddenly my tuning exercice turned to be a disaster, in this example it is about 63 times slower :(

I could possibly count the rows in the LSC_TR2 trigger, but then it will still be a bit slower when processing very few rows (in this example with 1 row).

So I will keep this AFTER EACH ROW trigger until next year ;)

old-fashion listagg

Yesterday I had my first session about XML, today I have one about SQL Model

Ok, it was the first time I spoke about XML so I did not really now where to focus. XML is so big, you have XQUERY, XPATH, dozens of XML functions in the database.

One of the XML function is called XMLTRANSFORM and transforms XML according to XSLT

I had a fun demo about XSLT to create a semi-column separated list :


select
   deptno,
   xmltransform
   (
      sys_xmlagg
      (
         sys_xmlgen(ename)
      ),
     xmltype
     (
       '<?xml version="1.0"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/ENAME">
              <xsl:value-of select="text()"/>;</xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>'
     )
  ).getstringval() listagg
from emp
group by deptno;


 DEPTNO LISTAGG
------- --------------------------------------
     10 CLARK;KING;MILLER;
     20 SMITH;FORD;ADAMS;SCOTT;JONES;
     30 ALLEN;BLAKE;MARTIN;TURNER;JAMES;WARD;

HOT


CREATE TABLE T(X NUMBER PRIMARY KEY) ORGANIZATION HEAP;

I have read the SQL reference more than once -believe me- but I did not notice this syntax until today… an HOT table is a simple table and this clause is optional. An IOT, or INDEX ORGANIZED TABLE, is a table where you mostly select thru an index. Good candidates for IOT are tables where you mostly access rows thru a full index scan.

Compare :


SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES (EMPNO NUMBER PRIMARY KEY)
  2    ORGANIZATION HEAP;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
  2    join LSC_SELECTED_EMPLOYEES using (EMPNO);

     EMPNO ENAME
---------- ----------
      7839 KING
      7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 609992009

--------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     2 |    46 |     3   (0)| 0
|   1 |  NESTED LOOPS      |              |     2 |    46 |     3   (0)| 0
|   2 |   TABLE ACCESS FULL| LSC_EMP      |    14 |   140 |     3   (0)| 0
|*  3 |   INDEX UNIQUE SCAN| SYS_C0010139 |     1 |    13 |     0   (0)| 0
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES"."EMPNO")

Note
-----
   - dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
  2  where segment_name in (
  3    select object_name from user_objects t where t.CREATED >
  4    sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------
LSC_SELECTED_EMPLOYEES         TABLE
SYS_C0010139                   INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES;

Table dropped.

with

SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES2 (EMPNO NUMBER PRIMARY KEY)
  2    ORGANIZATION INDEX;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
  2    join LSC_SELECTED_EMPLOYEES2 using (EMPNO);

     EMPNO ENAME
---------- ----------
      7839 KING
      7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 3539129569

--------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     2 |    46 |     3   (
|   1 |  NESTED LOOPS      |                   |     2 |    46 |     3   (
|   2 |   TABLE ACCESS FULL| LSC_EMP           |    14 |   140 |     3   (
|*  3 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_23608 |     1 |    13 |     0   (
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES2"."EMPNO")

Note
-----
   - dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
  2  where segment_name in (
  3    select object_name from user_objects t where t.CREATED >
  4    sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------
SYS_IOT_TOP_23608              INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES2;

Table dropped.

The plan is the same, in IOT, you have only one segment, the INDEX, and in HOT, you have two segments, the INDEX and the TABLE.

Surely a good candidate for IOT :)