INSERT /*+ APPEND */

This example demonstrates that an INSERT /*+ APPEND */ does not put rows into free space within a table, it adds them at the end instead. It also shows that Oracle inserts these rows using a DIRECT PATH mechanism. I tested it on Oracle 11.2. First I created an empty table:

SQL> conn /

Connected.

SQL> create table tab1

  2  as select * from dba_tables

  3  where 1 = 2

  4  /

 

Table created.

 

SQL>

Then I added the contents of DBA_TABLES 50 times. I did this using a normal INSERT statement:

SQL> begin

  2  for x in 1..50 loop

  3  insert into tab1

  4  select * from dba_tables;

  5  commit;

  6  end loop;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL>

I deleted all the rows so the table would contain nothing but free space. Then I checked how big it was:

SQL> delete tab1

  2  /

 

158150 rows deleted.

 

SQL> select bytes from user_segments

  2  where segment_name = 'TAB1'

  3  /

 

     BYTES

----------

  46137344

 

SQL>

I started a new session to zeroise my statistics then I reinserted the same data. This time I used an INSERT /*+ APPEND */ so that Oracle would add the rows to the end of the table, instead of putting them in the free space I had just created:

SQL> conn /

Connected.

SQL> begin

  2  for x in 1..50 loop

  3  insert /*+ append */ into tab1

  4  select * from dba_tables;

  5  commit;

  6  end loop;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL>

I checked the session’s wait events and they showed that a DIRECT PATH WRITE had been chosen:

SQL> select event, time_waited/100

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  order by 1

  5  /

 

EVENT                               TIME_WAITED/100

----------------------------------- ---------------

Disk file operations I/O                        .02

SQL*Net message from client                     .01

SQL*Net message to client                         0

db file sequential read                         .67

direct path sync                                  0

direct path write                                .4

events in waitclass Other                       .11

log file sync                                     0

 

8 rows selected.

 

SQL>

Finally, I rechecked the size of the table. If the INSERT /*+ APPEND */ had put the rows in the free space, the size would have been unchanged. However, the table was twice as big, proving that the data had been added at the end of the table:

SQL> select bytes from user_segments

  2  where segment_name = 'TAB1'

  3  /

 

     BYTES

----------

100663296

 

SQL>

ORA-12838

If you do an INSERT /* APPEND */, you cannot query the table afterwards until you have done a COMMIT. If you try to do so, Oracle gives you an ORA-12838. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table tab1

  2  as select * from dba_tables

  3  where 1 = 2

  4  /

 

Table created.

 

SQL> insert /*+ append */ into tab1

  2  select * from dba_tables

  3  /

 

3166 rows created.

 

SQL> select count(*) from tab1

  2  /

select count(*) from tab1

                     *

ERROR at line 1:

ORA-12838: cannot read/modify an object after

modifying it in parallel

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

      3166

 

SQL>

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA Hangs

This post demonstrates that if a database has an unfinished transaction and you try to run the above-mentioned command, it will hang. I logged into an Oracle 11 database, started a transaction but did not COMMIT it in the red session below:

PQEDPT1 /export/home/oracle > sqlplus /

 

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create table andrew

  2  as select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew

  2  select * from dba_tables

  3  /

 

15251 rows created.

 

I started the blue session below and tried to run an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA command but it did not appear to be doing anything:

PQEDPT1 /export/home/oracle > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter database add supplemental log data

  2  /

I started the green session below and saw that the blue session was waiting for events in waitclass Other:

PQEDPT1 /export/home/oracle > sqlplus /

 

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:23:52 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'events in waitclass Other'

  4  and sid =

  5  (select sid from v$session where username = 'SYS')

  6  /

 

TIME_WAITED/100

---------------

        1312.97

 

SQL> /

 

TIME_WAITED/100

---------------

        1321.79

 

SQL> /

 

TIME_WAITED/100

---------------

        1330.59

 

SQL> 

This was not particularly helpful but, fortunately for me, I knew that the blue session was waiting for the red session’s transaction to finish. I returned to it and did a COMMIT:

PQEDPT1 /export/home/oracle > sqlplus /

 

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 14:55:54 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create table andrew

  2  as select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew

  2  select * from dba_tables

  3  /

 

15251 rows created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

… and the blue session’s command completed:

PQEDPT1 /export/home/oracle > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 8 15:01:47 2015

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter database add supplemental log data

  2  /

 

Database altered.

 

SQL>

Oracle Pipes (Part 1)

Oracle pipes use the DBMS_PIPE package to allow one session to communicate with another. I decided to try this out in an Oracle 11.1 database. Pipes can be public or private. This example only looks at public pipes. First I created a user called ANDREW. I gave it SELECT ANY DICTIONARY so that it could look at V$DB_PIPES and I allowed it to execute DBMS_PIPE so that it would be able to create a pipe: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL> grant select any dictionary to andrew

  2  /

 

Grant succeeded.

 

SQL> grant execute on sys.dbms_pipe to andrew

  2  /

 

Grant succeeded.

 

SQL> 

Then I logged in as the user and queried V$DB_PIPES to ensure there were no pipes in the database to begin with: 

SQL> conn andrew/reid

Connected.

SQL> col name format a20

SQL> select * from v$db_pipes

  2  /

 

no rows selected

 

SQL>

 

I used DBMS_PIPE.PACK_MESSAGE to create a message then I used DBMS_PIPE.SEND_MESSAGE to send it down a pipe, which I called ANDREWS_PIPE. If you do it this way, Oracle creates the pipe implicitly with the name you provide: 

SQL> declare

  2   result number;

  3  begin

  4   dbms_pipe.pack_message('Andrew was here');

  5   result := dbms_pipe.send_message('andrews_pipe');

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> 

I queried V$DB_PIPES again and saw that Oracle had created the pipe: 

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL>

I decided that I did not want the pipe any more so I flushed the shared pool:

SQL> conn / as sysdba

Connected.

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

I just did this to show that flushing the shared pool by itself does not remove a pipe. To prove this, I queried V$DB_PIPES again and saw that the pipe was still there:

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL>

Then I used DBMS_PIPE.PURGE to clear the contents of the pipe:

SQL> exec dbms_pipe.purge('andrews_pipe');

 

PL/SQL procedure successfully completed.

 

SQL>

Once you have done this, an implicitly created pipe can be aged out of the SGA. I queried V$DB_PIPES and saw that the pipe was still there:

SQL> select * from v$db_pipes

  2  /

 

   OWNERID NAME                 TYPE     PIPE_SIZE

---------- -------------------- ------- ----------

           ANDREWS_PIPE         PUBLIC        1621

 

SQL> 

I wanted to finish this test but did not have time for the pipe to be aged out of the SGA so I flushed the shared pool to speed things along: 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

… and when I queried V$DB_PIPES again, the pipe had gone:

SQL> select * from v$db_pipes

  2  /

 

no rows selected

 

SQL>

 

(More to follow…)

PL/SQL lock timer

I was looking through V$SYSTEM_EVENT on an Oracle 11.2 production database (as you do) and I noticed that it had waited for exactly 1 second on the PL/SQL lock timer event. Apparently this is the amount of time a database has been waiting for sessions which have been told to sleep by their application. I decided to check this out so I started a new session, slept for 6.7 seconds then looked to see how long my session had been waiting on this event:

SQL> conn /

Connected.

SQL> exec dbms_lock.sleep(6.7);

 

PL/SQL procedure successfully completed.

 

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'PL/SQL lock timer'

  4  and sid = (select distinct sid from v$mystat)

  5  /

 

TIME_WAITED/100

---------------

           6.71

 

SQL>