Unique Index Result Error with Ref Cursor on Oracle 11g but Not on 10g

6 06 2011

June 6, 2011

You might expect to see some differences when upgrading from one release version of Oracle Database to another release version (edit June 6, 2011: see this article for expected changes when upgrading from 10g to 11g).  For instance, you might find that Oracle Database 11.1.0.6 deadlock when Oracle Database 10.2.0.5 and below do not.  You might find that Oracle Database 11.1.0.6 and above throw error messages when ROWNUM is used when Oracle Database 10.2.0.5 and below do not throw error messages.  You might find that the following SQL statement (based on the contents of this article) returns different values for the SYS user depending on the Oracle Database release version:

SELECT SYS_CONTEXT('USERENV', 'SESSIONID') USERENV FROM DUAL; 

For example, on Oracle Database 11.2.0.2 when SYS connects as SYSDBA, you will see something like this for the output of that SQL statement:

USERENV
----------
4294967295

If the O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE on 11.2.0.2, and the SYS user connects without specifying AS SYSDBA, you might see something like this for the output of that SQL statement:

USERENV
-------
344841

If the SYS user connects on Oracle Database 9i, you will probably see something like this for the output of that SQL statement:

USERENV
-------
      0 

The above are all minor changes in bahavior.  If you are brave, go ahead and blindly upgrade to a new version of Oracle Database.  :-)

While browsing a couple of web forums I found another example of changed results caused by upgrading from Oracle Database 10g (possibly 10.2.0.4 or lower) to 11g (not stated, but possibly 11.2.0.2).  To be kind, my PL/SQL coding abilities are rusty, but I think that I follow the logic in that forum thread.  Let’s see if we are able to reproduce the problem with a couple of slight modifications.  First, let’s create a table with three rows:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  ORDER_ID VARCHAR2(20),
  PROCESSED NUMBER);

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Now create a PL/SQL package with an embedded COMMIT (as stated in the forum thread linked to above, this embedded COMMIT is included because this example is from a stateless web application):

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR; 

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Hopefully, I haven’t lost anyone yet, in the above: select a row, return the row as a ref cursor, and then update the row to show that the row has been processed.  Now let’s process the three orders using three anonymous PL/SQL blocks (it is not necessary to use three anonymous PL/SQL blocks, but this was done to verify that the problem happens on multiple executions also):

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/ 

The output from Oracle Database 11.2.0.2 looks like this:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.
 

The above worked as expected, the anonymous PL/SQL blocks output TESTING, TESTING2, and TESTING3.  Not particularly useful as written, but it executes and outputs the expected information.

Let’s create a non-unique index on the ORDER_ID column and repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE INDEX IND_T1 ON T1(ORDER_ID); 

The output from Oracle Database 11.2.0.2 showing the anonymous PL/SQL:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

The above test seemed to work as expected… TESTING, TESTING2, TESTING3.  Let’s create a primary key constraint on the ORDER_ID column, which will use the existing IND_T1 index to enforce the primary key constraint, then we will repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 ADD PRIMARY KEY(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Nothing unusual yet – you are probably wondering about the point of this article by now, almost there.  Let’s create a unique function based index on UPPER(ORDER_ID) and then repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID));

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion of the output:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1
IND_T1_FB

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Still nothing unusual yet.  Let’s try one more time, removing the primary key constraint, dropping the two indexes, and creating a single unique index on the ORDER_ID column (in case you are wondering, with just the function-based unique index on the table there were no problems):

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 DROP PRIMARY KEY;
DROP INDEX IND_T1;
DROP INDEX IND_T1_FB;
CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 (and 10.2.0.5):

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

Note that this time the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Oracle Database 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 all exhibit this unexpected behavior when a unique index is present on table T1’s ORDER_ID column. 

Oracle Database 10.2.0.4 does not exhibit this problem.  Below is the output from Oracle Database 10.2.0.4 for the last portion of the script:

SQL> CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

Index created.

SQL>
SQL> SELECT
  2    BANNER
  3  FROM
  4    V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Well, that is a little interesting.  Let’s try another test – remember that we had no trouble with the unique function-based index.  Let’s quickly revisit that example.  First, reset the T1 test table, drop the unique index, and then recreate the unique function-based index:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

DROP INDEX IND_T1_UNIQUE;
CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID)); 

Now let’s modify the package so that it will likely use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Let’s retry the anonymous PL/SQL blocks:

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

The output of the anonymous PL/SQL blocks:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed. 

Note that the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Let’s try fixing the package so that it will not use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT /*+ NO_INDEX(T1 IND_T1_FB) */
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Now, reset the table’s rows:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Finally, the output from the anonymous PL/SQL blocks on Oracle Database 11.2.0.2:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed. 

Testing 1, 2, 3.  I think that I just fixed a problem by using a hint… although we could have avoided the problem by creating a non-unique normal index with a primary key constraint, or by creating a non-unique index with a second unique function-based index to enforce the uniqueness in the column.

Added June 6, 2011:

The linked forum thread suggests that the problem might be identified in Metalink (MOS) Bug 10425196: “PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5″.  I was able to reproduce the problem in Oracle Database 10.2.0.5 on the Windows platform.  Also, note in the above tests that it is not necessarily the presence of a primary key index on the table that could cause the problem – as shown above, the problem is apparently caused by cases where a unique index is used to retrieve the rows for the ref cursor.





Insert Error on Oracle 11g but Not on 10g

2 06 2011

June 2, 2011

The most popular article on this blog includes a script that demonstrates generating a deadlock on Oracle Database 11g R1 and R2 when the exact same script on Oracle Database 10g R2 does not trigger a deadlock.  I came across another interesting change between Oracle Database 10.2.0.5 and 11.2.0.2 (the change also appears in 11.1.0.7) that affects the results of Insert statements that seemingly worked fine for years.

We will use the following simple script for this blog article:

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

First, a pop quiz:

What is the expected output of the select from table T2?

 Answer A:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

10 rows selected.

Answer B:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

11 rows selected.

Answer C:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0

12 rows selected.

Answer D:

no rows selected

Answer E:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10
02-JUN-11         11
02-JUN-11         12

12 rows selected.

Answer F:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          1

2 rows selected.

—————————————————–

Think about your answer for a minute… you might even want to try the experiment on an Oracle Database.  Scroll down when you think that you have the answer.

Script Output from Oracle Database 10.2.0.5:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

SQL> SELECT * FROM T2;

C1                C2
--------- ----------
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0

12 rows selected.

Script Output from Oracle Database 11.2.0.2:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);
INSERT INTO T1 VALUES(SYSDATE-10,-10)
            *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;
  T1
  *
ERROR at line 2:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);
INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM)
                              *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.

SQL> SELECT * FROM T2;

no rows selected

The results for Oracle Database 11.1.0.7 are identical to those of Oracle Database 11.2.0.2.

The answer to the quiz question is therefore… C AND D, which is of course logically impossible.  Disappointed?  Maybe there is a bug ID in Metalink (MOS) for this particular problem.  :-)  If you search the web, you can find similar SQL statements in various Internet forums, for example on the OTN forums.





Reading Material On Order 2

29 05 2011

May 29, 2011

A year ago I wrote an article that had a couple of topics, one of which described three books that I put on order – ordered with the intention of writing reviews for all three books.  Two of the books arrived in a timely fashion, while the third book has yet to arrive despite being ordered twice from Amazon (I had to rely on the Google Books viewer for the review).

I just recently purchased three electronic formatted books (otherwise known as eBooks), and I plan to put the Motorola Xoom to use while reading those books.  The books include: “Troubleshooting Oracle Performance“, “Beginning Oracle SQL“, and “Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach”.  I will likely review the second and third of the above books, and if I find time I will try to improve the level of detail found in the review of the first of the above books so that the review matches the level of detail found in the book reviews that I wrote a couple of months later.  That said, here are a couple of quick observations about the books:

Troubleshooting Oracle Performance

I tested the PDF and ePUB (apparently used by the Nook) versions of this book.  While the PDF version of the book accurately reproduces the book contents, I recommend staying away from the ePUB version, if possible.  You can read about my adventures with the PDF version and ePUB version of that book at the end of one of my previous articles.  It has been almost three years since my first read through of this book, and while I found a couple of minor errors (yet somewhat obvious errors that the author quickly addressed before I had a chance to read those sections of the book), I did not find anything negative worth mentioning in the original review.  About a year ago I started re-reading the book, but only made it about 100 to 120 pages into the book before I had to break-away to something else.  I do not recall taking any notes in the first 100 to 120 pages, but I do recall taking fairly extensive notes in later parts of the book during the initial read.

Beginning Oracle SQL

This book is intended as an update to the “Mastering Oracle SQL and SQL*Plus” book that was originally written by Lex DeHaan, and was apparently intended both and as an introduction to SQL as well as a brief demonstration of more advanced techniques.  I was impressed with the quality and accuracy of the original book, and I was excited to see a follow-up book titled “Pro Oracle SQL“.  The original “Mastering Oracle SQL and SQL*Plus” book did have a couple of problems: most of the pages lacked page numbers, various formatting problems that resulted in dropped characters, and a missing Oracle Database 10.1.0.2 CD that was promised on the book’s cover.  I have not had a lot of time to examine the “Beginning Oracle SQL” book.  However, I did notice that every page that should have a page number now has a page number, I have not noticed any formatting problems that resulted in dropped characters, and the front cover no longer advertises that the book includes Oracle Database on CD.  I initially thought that all mention of analytic queries had been dropped from the “Beginning Oracle SQL” book due to the extended discussion of this topic in the “Pro Oracle SQL” book, and a search for the word analytic finds only a single page containing that word.  Thankfully, it does appear that a couple of the analytic functions are mentioned in the updated book.  It does not appear that this is just a simple reprint of the original book – the new book includes descriptions of Oracle Database 11.1 and possibly 11.2 features.  More information will follow if I have a chance to read the entire book.  The Google Books viewer application crashes when attempting to show book pages 33 and 34 side-by-side (pages 56 and 57 as displayed in Google Books), but the problem is not present when the Xoom is held in portrait orientation.

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

This book is set to be released in the middle of August 2011, so I bought the alpha copy of the book that currently includes chapters 3, 5, 6, 7, and 14.  I bought this book because I was curious to see how the recipe format works for a book written on the topic of performance tuning.  That format worked very well for one of the books that I reviewed, and mostly/almost worked for another book that I reviewed (with the notable exception of the SQL statements that were Oracle Database performance related).  How well will the format work for a book that is specifically written about Oracle Database 11g performance tuning?  Let’s just say that I hope that there is a good technical reviewer involved in this book project, that the authors listen to the technical reviewer, and that the alpha copy of the chapters were captured before the technical reviewer had a chance to examine the chapters.  If the alpha copy of the book chapters actually shows the results after the technical reviewers provided recommendations, this will very likely be the first Apress title that I have read which will receive a 3 star, or very likely lower, rating on a 5 star scale when I write a review of the book.  I do not want to go into a lot of specifics after a very quick examination of a couple of alpha chapters of a book, but I will mention a couple of examples of problems that I identified:

  • The introduction to chapter 3 states that the results of SQL queries and PL/SQL functions may be stored in the shared pool when a results cache is used.  It might be a better idea to be much more specific, and state that Oracle Database is using the Result Cache Memory in the shared pool as indicated in the Oracle Database documentation, but maybe this is explained later.
  • In recipe 3-1 the authors mention that they would set the MEMORY_MAX_TARGET to 1500M, and then show a SQL statement that sets that parameter to 2G.  This particular inconsistency will likely be caught in a later review of the book material.  This recipe seems to be mirroring a page from the Oracle documentation library, only that the order of a couple commands were specified incorrectly in the book.  The comment regarding the _TARGET parameters does not seem to apply to the SGA_TARGET parameter.  This recipe states that “automatic memory management is the recommended approach to managing Oracle’s memory allocation,” but does not describe any of the potential pitfalls or operating system specific problem areas/limitations (reference1 reference2 reference3 reference4 reference5 reference6).  Beyond telling the reader to simply add the current PGA_AGGREGATE_TARGET and SGA_TARGET values to determine a starting point for MEMORY_MAX_TARGET, the recipe gives little insight into the “best” value for this parameter.  For now I will ignore the numerous spelling mistakes, because the book editors will likely find and address those issues.
  • In recipe 3-2 the authors state, “if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool [when creating the segment].”  Something just doesn’t seem to be right (or complete) about this statement – for one, I wonder what the CACHE and  NOCACHE clauses might affect?  Secondly, this statement might be implying that blocks belonging to an object will only remain in the buffer cache if those objects are assigned to the KEEP buffer cache.  The recipe states that the approximate size of the KEEP buffer pool can be determined by “summing up the size of all candidate objects for this pool” – great, now what happens when Oracle Database creates up to six (and potentially many more) consistent read versions of the objects’ blocks (reference)?  The last two paragraphs of this recipe seem to indicate that it is the segments themselves (apparently all blocks) that are aged out of the buffer cache when space is needed to cache other blocks, rather than the segments’ blocks that are aged out of the cache – additional clarity is needed in these two paragraphs.  The second to last paragraph also makes a claim that I am not sure how to interpret: “Oracle figures that if a single segment takes up more than 10% of (nonsequential) physical reads, it’s a large segment, for the purpose of deciding if it needs to use the KEEP or RECYCLE buffer pools.”
  • (Skipping around a bit) In recipe 3-13 we learn how to tune the redo log buffer.  The book states, “since there’s no cost whatsoever to increasing the LOG_BUFFER size, feel free to set it to higher than the suggested maximum of 1MB for this parameter.”  Earlier the book showed how to set the LOG_BUFFER parameter to a value of 4,096,000.  I think that I recall that Oracle Database 11.1 was released after Oracle Database 10.2 ( :-) ), where the LOG_BUFFER parameter started being auto-set to a value slightly smaller than the granule size – the most common granule size for Oracle Database 10.2 databases was likely 16MB, while only databases with a SGA size less then about 1GB saw granule sizes of 4MB.  The granule size rules changed again with the release of Oracle Database 11.1 and again with the release of Oracle Database 11.2.  If you check the comments section of this blog article, one reader commented about the LOG_BUFFER being auto-tuned to nearly 512MB in size.  I wonder how useful the ratio of ‘redo entries’ statistic value divided by the the ‘redo log space requests’ statistic value might be when trying to find the ideal value for the LOG_BUFFER parameter?
  • In recipe 5-1, how does one see a “latch on a log file”?  The recipe also describes a view that has a name that ends with the suffix _HISTORY, but the book failed to mention the licensing requirements to access this view.  When describing the V$SESSION_EVENT view, the book states, “The data in this view are available only so long as a session is active.” – considering that one of the columns in V$SESSION is named ACTIVE, I can see where a statement like this one could lead to confusion.  The last paragraph of the recipe states, “Note that you can query the V$WAITSTAT view for the same information as well.” – that view was not previously introduced and in actuality, the V$WAITSTAT view does not produce information similar to the V$SYSTEM_EVENT view that was described a sentence or two earlier.
  • Recipe 5-2 queries V$ACTIVE_SESSION_HISTORY without first providing a warning regarding the licensing requirements to access this view.  I have not experimented enough with ASH data, but I wonder if SUM(WAIT_TIME + TIME_WAITED) when grouped by SQL_ID and USER_ID could lead to incorrect assumptions regarding the wait time per SQL statement for the last 30 minutes.  Is there a risk of double-counting, or counting incidental CPU usage immediately after a wait event completes, as part of the time spent in wait events for a specific SQL statement?  I am left wondering why the authors did not suggest checking the APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME columns of V$SQL (or V$SQLAREA) periodically, and calculating the delta (change) values for specific SQL statements – those columns have existed as long as ASH (since the release of Oracle Database 10.1).

I guess that the above is a bit more lengthy than I had originally intended.  Best of luck to the technical reviewers of the book.





How to Eliminate Wait Events in Oracle Database?

13 05 2011

May 13, 2011

In the last couple of days I have been busy with a couple of non-Oracle Database specific tasks, and I started wondering what types of wait events I might be posting during this time period if I had to report what I was doing.  Waiting on the server to reboot for the 20th time during a software deployment, waiting for replacement hard drives to be shipped, waiting for the darn technology gadgets to download a file or recover from the latest force close (a nice way of saying CRASH!), waiting for a software vendor to return a call, waiting for thoughts to magically appear on PowerPoint slides, etc.

While I was processing all of these wait events, I noticed a couple of recent search keywords being used to find pages on my blog.  One of the sets of search keywords included:
How to Eliminate Wait Events Oracle

I know that I covered that topic in an earlier blog article, but I thought that I would open this topic again for discussion.

While we are at it, let’s try to answer a couple of additional sets of search keywords that lead people to blog articles on this site:
How to Determine Why a CPU is Busy
How to Stop Dead Locks in Oracle

Just a fair warning – the first result returned by a search engine is not necessarily the result that you want in a production environment.





How Do You Find Information in the Oracle Documentation Library?

22 02 2011

February 22, 2011

A recent thread in the OTN forums mentioned Metalink (MOS) Doc ID 1203353.1 – “How to find Oracle Database Documentation on OTN Web Site” and lists four easy steps for finding information in the Oracle documentation library, with the starting point being this link.  I think that it is great having the list of steps in such as easily read form.  There is very useful thread in another OTN forum, and that thread is titled “SQL and PL/SQL FAQ” (I really like this thread, and hope that a similar thread makes its way into the Database – General forum also).  Among other helpful topics mentioned in the thread is how to access the Oracle Database Documentation using another website (this is the site that I have seen specified most often as the starting point for accessing the Oracle documentation). 

As I mentioned in the first of the above OTN threads, I use a slightly different method to access the Oracle documentation that seems to be a bit more efficient for my needs – Google searches. For example, if I am looking for the definition of V$SESSION_EVENT in a specific Oracle Database release version, I will use the following search keywords:

For Oracle Database 8i (8.1.5 through 8.1.7): site:download.oracle.com 815 V$SESSION_EVENT
For Oracle Database 9.0.1: site:download.oracle.com 901 V$SESSION_EVENT
For Oracle Database 9.2.0: site:download.oracle.com 920 V$SESSION_EVENT
For Oracle Database 10.1.0: site:download.oracle.com 101 V$SESSION_EVENT
For Oracle Database 10.2.0: site:download.oracle.com 102 V$SESSION_EVENT
For Oracle Database 11.1.0: site:download.oracle.com 111 V$SESSION_EVENT
For Oracle Database 11.2.0: site:download.oracle.com 112 V$SESSION_EVENT

Once in a while, it might be necessary to add the term database to the search keywords to eliminate search results that are not Oracle Database specific.

The custom search engine on the Miracle Finland site also seems to do a good job of taking me directly to the Oracle documentation library.





Single Sign-On Security Issue in 11.2.0.2 Patchset

18 11 2010

November 18, 2010

I just browsed though Metalink (MOS) to see which operating system platforms are currently supported for Oracle Database 11.2.0.2.  I noticed that the 11.2.0.2 patchset for 64 bit Linux was updated yesterday, and more digging revealed that all of the 11.2.0.2 patches for the other supported operating systems were also recently updated.

Take a look at Metalink Doc ID 1179474.1 and 1266978.1 for more information to see if you should re-download the 4.7GB patchset.

————–

Edit November 18, 2010:

If you have some time, and you are planning to upgrade to Oracle Database 11.2.0.2, spend a little time reading on Metalink – it is interesting to see what is fixed in 12.1.0.1.  A couple of interesting articles that I found include:

  • Doc ID 10142788.8, Bug 10142788 – Natively compiled PL/SQL in 11.2.0.2 can result in ora-04030: out of process memory
  • Bug 10274128 – Compressing and moving a partition fails in 11.2.0.2
  • Bug 10297087 – NFS mount warning in 11.2.0.2
  • Bug 10294904 – Selecting the COUNT(*) from ALL_SYNONYMS requires an hour to complete in 11.2.0.2, but just a couple seconds in earlier releases
  • Bug 10297041 – Create synonym invalidates objects in 11.2.0.2
  • Bug 10188727 – Some SQL failing with ORA-3113 or ORA-7445 after upgrade to 11.2.0.2
  • Doc ID 1264550.1, Bug:8352378 – Wrong Results on 11.2.0.2 with Function-Based Index and OR Expansion
  • Doc ID 285586.1 – ORA-600 [kdsgrp1]
  • Doc ID 138554.1 – ORA-600 [17059]
  • Doc ID 284433.1 – ORA-600 [25027]
  • Doc ID 138428.1 – ORA-600 [15160]
  • Doc ID 300581.1 – ORA-600 [kddummy_blkchk]
  • Doc ID 300637.1 – ORA-600 [qertbfetchbyrowid]
  • Doc ID 567171.1 – _FIX_CONTROL
  • Doc ID 94036.1 – CURSOR_SHARING
  • Doc ID 62338.1 – _COMPLEX_VIEW_MERGING




11.2.0.1 ODBC Update Problem

2 11 2010

November 2, 2010 (Updated November 3, 2010)

An interesting ODBC problem was brought to my attention yesterday regarding the Microsoft Access 2010 program and the Oracle Database 11.2.0.1 ODBC client.  What is the problem?  The person reported that they were trying to update a table in a 10.2.0.4 Oracle Database using values from a database table that resides in an Microsoft Access 2010 database – Microsoft Access was supposed to magically update the Oracle table when the user issued a simple UPDATE statement.

I put together a test case using a 11.2.0.1 Oracle Database to simulate the problem, picking a table named PART in the Oracle Database and creating a table named  UPDATE_TABLE in Microsoft Access 2010.  I then constructed the following SQL statement:

UPDATE
  PART
SET
  WHSALE_UNIT_COST = (
    SELECT
      UPDATE_TABLE.WHSALE_UNIT_COST
    FROM
      UPDATE_TABLE
    WHERE
      UPDATE_TABLE.PART_ID = PART.ID)
WHERE
  PART.ID IN (
    SELECT
      PART_ID
    FROM
      UPDATE_TABLE);

Nifty, except that the above SQL statement generated an error message:

Operation must use an updatable query (Error 3073)

OK, let’s try this crazy looking SQL statement instead:

UPDATE DISTINCTROW
  PART
INNER JOIN
  UPDATE_TABLE
ON
  PART.ID = UPDATE_TABLE.PART_ID
SET
  PART.WHSALE_UNIT_COST = UPDATE_TABLE.WHSALE_UNIT_COST; 

No error 3073 this time, so the above must be a valid SQL statement.  I put 4 test rows into the UPDATE_TABLE table and let the update run:

Great, its going to work…  Hey, why did I receive a warning that 3 records could not be updated due to lock violations?  Darn Microsoft bugs!

Let’s try to execute the SQL statement again:

LONGs?  The PART.ID column is defined as VARCHAR2(30), the PART.WHSALE_UNIT_COST is defined as NUMBER(20,6).  Maybe it is the Access table’s fault.  The UPDATE_TABLE.PART_ID column is defined as TEXT(30), and it does not matter if the UPDATE_TABLE.WHSALE_UNIT_COST column is defined as DECIMAL, SINGLE, or DOUBLE.

For fun, let’s create a trigger to enable a 10046 trace at level 12 whenever the Microsoft Access program connects to the database.  The trigger at the top of this blog article will work without modification.  Now let’s take a look at part of the 10046 trace file after trying the update SQL statement again:

PARSING IN CURSOR #4 len=64 dep=0 uid=287 oct=6 lid=287 tim=20065405443 hv=655341070 ad='4696fd8f0' sqlid='gwux0ysmhzdhf'
UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2
END OF STMT
PARSE #4:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065405443
BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f284498  bln=2000  avl=07  flg=05
  value="123.122"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f29f060  bln=4000  avl=09  flg=05
  value=".250-A572"
EXEC #4:c=0,e=264,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=20065405739

The above kind of works, even though the number value is passed in as a VARCHAR2 rather than as a NUMBER.  The WHSALE_UNIT_COST will be set to 123.122 for part ID .250-A572.
 
Now the next set of bind variables:

BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f284498  bln=2000  avl=07  flg=05
  value="4554.44"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f29f060  bln=4000  avl=00  flg=05
EXEC #4:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065407265

Notice in the above that the bind variable value for the PART.ID column was left as NULL – that can’t work if we are trying to update rows.
 
The next set of bind variables:

BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f284498  bln=2000  avl=07  flg=05
  value="45.1¿¿"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f29f060  bln=4000  avl=00  flg=05
EXEC #4:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065408596

Notice in the above that the bind variable value for the PART.ID column was left NULL, and the value passed in for the WHSALE_UNIT_COST is 45.1¿¿ – I am pretty sure that is an invalid number.
 
The final set of bind variables:

BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f284498  bln=2000  avl=07  flg=05
  value="665"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f29f060  bln=4000  avl=00  flg=05
EXEC #4:c=0,e=158,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065410013

Notice in the above that the bind variable value for the PART.ID column was left as NULL – that can’t work if we are trying to update rows.
 
So, I guess that explains why 3 of the 4 updates failed.  Darn Microsoft bugs!  But wait, here is an interesting thread on a Microsoft site that suggests the problem is with the 11.2.0.1 client’s ODBC dll named sqora32.dll.  Someone in the thread suggested just borrowing the sqora32.dll file from a 11.1.0.7 client install.  I was suspicious about the advice, so I borrowed the sqora32.dll file from a 10.2.0.1 client install.  I have seen this before, Yes.

And then… nothing.  The 10046 logon trigger was still enabled, let’s check the trace file:

PARSING IN CURSOR #4 len=64 dep=0 uid=287 oct=6 lid=287 tim=28794270809 hv=655341070 ad='4696fd8f0' sqlid='gwux0ysmhzdhf'
UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2
END OF STMT
PARSE #4:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=28794270808
BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f2af830  bln=2000  avl=10  flg=05
  value="123.122001"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f2ae850  bln=4000  avl=09  flg=05
  value=".250-A572"
EXEC #4:c=0,e=324,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794271179
...
BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f2abb40  bln=2000  avl=11  flg=05
  value="4554.443847"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f2af060  bln=4000  avl=09  flg=05
  value=".250X.750"
EXEC #4:c=0,e=181,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794292695
...
BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f2abb40  bln=2000  avl=09  flg=05
  value="45.099998"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f2af060  bln=4000  avl=07  flg=05
  value=".50-A36"
EXEC #4:c=0,e=198,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794312262
...
BINDS #4:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=1f2abb40  bln=2000  avl=03  flg=05
  value="665"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=1f2af060  bln=4000  avl=08  flg=05
  value="00045364"
EXEC #4:c=0,e=217,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794333188
WAIT #4: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=28794333220

*** 2010-11-02 14:02:01.661
WAIT #4: nam='SQL*Net message from client' ela= 2750115 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=28797083349
XCTEND rlbk=0, rd_only=0, tim=28797083480

It worked!  The last line indicates that the changes were committed, and all bind variables were passed in.  So, I tested again with the sqora32.dll file from a 11.1.0.7 client – it still worked.  I then went back to the sqora32.dll file from the 11.2.0.1 client, and the failure messages returned.  OK, maybe the problem is with the 11.2.0.1 Oracle Database?  Pointing Access 2010 at a 10.2.0.4 Oracle Database with the sqora32.dll file from the 11.2.0.1 client returned the same silly error messages that I saw earlier.

What do you know, it is not a Microsoft Bug 2010 after all.

——————-

Edit: November 3, 2010

A search of Metalink (MOS) revealed that Patch 7 for Oracle Database/Client 11.2.0.1 on the Windows platform included a fix for the ODBC problem – this fix is not included in Patch 6 for 11.2.0.1.  Metalink indicated that patch number 10155837 is Patch 7 for Oracle 11.2.0.1, but that is only for the 32 bit Oracle binaries on Windows.  A patch search found patch number 10155838 which appears to include the same fix for the 64 bit Oracle binaries on Windows.  Initial testing of the 32 bit patch (10155837) on a 32 bit client home residing on a 64 bit Windows client seems to show that the problem is fixed by patch 10155837.  From the 10046 trace file:

PARSING IN CURSOR #7 len=64 dep=0 uid=287 oct=6 lid=287 tim=7180612054 hv=655341070 ad='4697fba58' sqlid='gwux0ysmhzdhf'
UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2
END OF STMT
PARSE #7:c=0,e=85,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=7180612053
...
BINDS #7:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=203f0bc8  bln=2000  avl=10  flg=05
  value="123.122001"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=203f1ab0  bln=4000  avl=09  flg=05
  value=".250-A572"
...
EXEC #7:c=31200,e=45322,p=17,cr=628,cu=4,mis=1,r=1,dep=0,og=1,plh=1148805963,tim=7180657412
...
BINDS #7:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=203f0fe0  bln=2000  avl=11  flg=05
  value="4554.443847"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=203f84e8  bln=4000  avl=09  flg=05
  value=".250X.750"
EXEC #7:c=0,e=247,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180678947
...
BINDS #7:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=203f0fe0  bln=2000  avl=09  flg=05
  value="45.099998"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=203f1ab0  bln=4000  avl=07  flg=05
  value=".50-A36"
EXEC #7:c=0,e=173,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180699090
...

BINDS #7:
 Bind#0
  oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0
  kxsbbbfp=203f0fe0  bln=2000  avl=03  flg=05
  value="665"
 Bind#1
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0
  kxsbbbfp=203f84e8  bln=4000  avl=08  flg=05
  value="00045364"
EXEC #7:c=0,e=164,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180720112
WAIT #7: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=7180720145

*** 2010-11-03 08:03:08.775
WAIT #7: nam='SQL*Net message from client' ela= 2522305 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=7183242464
XCTEND rlbk=0, rd_only=0, tim=7183242564

The 10046 trace file show four complete sets of bind variables and a successful COMMIT at the end.








Follow

Get every new post delivered to your Inbox.

Join 143 other followers