Tuesday, June 23, 2015

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
 

Monday, June 08, 2015

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>

Friday, June 05, 2015

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…)

Wednesday, June 03, 2015

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>