Direct mode operations on IOT’s

An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c.  We can see the difference by running a simple script in both v11 and v12


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

SQL> drop table t purge;

Table dropped.

SQL> create table t(x int primary key, y int) organization index;

Table created.

SQL> insert /*+ append */ into t select 1 x, 1 y from dual;

1 row created.

SQL> select * from t;  -- no error, so no direct mode was done

         X          Y
---------- ----------
         1          1

1 row selected.


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

SQL> drop table t purge;

Table dropped.

SQL> create table t(x int primary key, y int) organization index;

Table created.

SQL> insert /*+ append */ into t select 1 x, 1 y from dual;

1 row created.

SQL> select * from t;  -- now we do get the error, so append hint was respected
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

So that looks pretty cool. People may recall an older blog post of mine where it was not possible to do a large scale IOT population without a large underlying sort operation. So I thought I’d revisit that, and see what we can discover.

In this case, we are using table T_SOURCE as the source of our data. It was created simply as:


SQL> create table T_SOURCE ( n , r , constraint T_SOURCE_PK primary key( n ))
  2  organization index
  3  as select rownum, rpad(rownum,128)
  4  from dual
  5  connect by level <= 1000000
  6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','t_source')

PL/SQL procedure successfully completed.

So we are going to copy those 1million rows from T_SOURCE to a new table, and try to avoid having to sort the rows by asking the optimizer to “walk” the source data in index order.

So let’s try direct mode first. You’ll references to a script called “mystat”, that’s simply a query to v$mystat to pick up session level statistics that might be relevant.

We’ll run these before and after to see what deltas pop out.


SQL> truncate table t_tgt;

Table truncated.

SQL> alter table T_TGT nologging;

Table altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                              135736
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                            117
sorts (disk)                                                0
sorts (rows)                                              359

3 rows selected.

SQL> set timing on
SQL> insert /*+ APPEND */ into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1  ;

1000000 rows created.

Elapsed: 00:00:46.30

SQL> select count(*) from t_tgt where rownum < 10;
select count(*) from t_tgt where rownum < 10
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           497096676
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                            122
sorts (disk)                                                1
sorts (rows)                                          1000436

3 rows selected.

SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                   19231
physical writes direct                                  19231
physical reads direct temporary tablespace              19231
physical writes direct temporary tablespace             19231

 

So we can make the following deductions

  • Direct mode seems to have been activated, due to the ORA-12838 error.
  • I didn’t avoid the sorting, because I sorted 1,000,000 rows
  • The NOLOGGING attribute seems of no benefit here – still consumed 500megabytes of redo

So whether it was really a direct mode operation is perhaps up for debate, because all of the direct reads/writes recorded were all against temporary tablespace.

Let’s explore that last one a little.

I repeated the same population script, but before I did so, I set my workarea_size_policy to manual, and cranked up my sort_area_size to 1G.

I’ll save you all the output again, but here’s the stats output


SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                       0
physical writes direct                                      0
physical reads direct temporary tablespace                  0
physical writes direct temporary tablespace                 0

So…no direct operations here now. This would appear to confirm that the direct mode operations were simply sorting operations.

Now we’ll perform the same process without using the direct mode hint. We’ll set the table back to logging because we’ve seen that’s of no benefit, and use a fresh session to pick up fresh statistics


SQL> truncate table t_tgt;

Table truncated.

SQL> alter table T_TGT logging;

Table altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                              137236
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                             21
sorts (disk)                                                0
sorts (rows)                                               86

3 rows selected.

SQL> insert into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1  ;

1000000 rows created.

Elapsed: 00:00:13.96
SQL> set timing off
SQL> select count(*) from t_tgt where rownum < 10;

  COUNT(*)
----------
         9

1 row selected.

SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           500630220
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                             26
sorts (disk)                                                0
sorts (rows)                                              146

3 rows selected.

SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                       0
physical writes direct                                      0
physical reads direct temporary tablespace                  0
physical writes direct temporary tablespace                 0

So for the conventional mode we observe

  • I didn’t have to sort the rows
  • I consumed similar amount of redo
  • It was three times faster than the case where we had to so all that sorting to disk

This just goes to show, that (like everything in software), the advice of “Do ‘X’ and in every case it will be faster” is never a good thing to adhere to.

Direct mode insert is a very cool facility, but it doesn’t mean that it’s going to be the best option in every situation.

4 thoughts on “Direct mode operations on IOT’s

  1. The NOLOGGING attribute seems of no benefit here – still consumed 500megabytes of redo.

    but those REDO’s were not against the index, not against the table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s