V$SESSION’s ROW_WAIT_OBJ# Column Shows the DATA_OBJECT_ID – Verify the Documentation Challenge

7 06 2011

June 7, 2011

While reading through the alpha copy of a performance tuning book, I noticed a set of SQL statements that were identical to those found in the Oracle Database 11.2 Performance Tuning Guide.  The set of SQL statements from the Performance Tuning Guide follow:

10.3.1.1 Causes
To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#
  FROM V$SESSION
 WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

In an earlier article we found that V$SESSION's ROW_WAIT_OBJ# column indicated the OBJECT_ID, not the DATA_OBJECT_ID of the object (as found in DBA_OBJECTS) and the same was true for the obj# entry found in a 10046 trace file.  When you first create an index the OBJECT_ID and the DATA_OBJECT_ID values will be the same for the index, as displayed in DBA_OBJECTS.  However, when you rebuild the index the DATA_OBJECT_ID value for the index will change while the OBJECT_ID value remains unchanged.  Likewise, when you first create a table the OBJECT_ID and the DATA_OBJECT_ID values will be the same, as displayed in DBA_OBJECTS.  If you TRUNCATE a table, the OBJECT_ID value will remain the same while the DATA_OBJECT_ID value for the table will change.

The challenge is to build a test case that either confirms or refutes the claim made by the Oracle Database Performance Tuning Guide.  Possible references:

  • AskTom thread
  • Metalink (MOS) ID 15476.1 “FAQ about Detecting and Resolving Locking Conflicts”
  • Metalink (MOS) Doc ID 603661.1 “ORA-01410: Invalid Rowid When Using rowid Generated from V$Session and Dba_objects”
  • Another article found on this blog (take a look at the Other Resources links)




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.





Disabling AUTOTRACE Causes “SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level” on 11.2.0.1

2 10 2010

October 2, 2010

I encountered a fun problem today while performing a couple of tests with the AUTOTRACE feature in SQL*Plus.  I am using a 32 bit 11.2.0.1 Oracle client connected to a 64 bit 11.2.0.1 Oracle database.  It seems that 50% of the time when I issue “SET AUTOTRACE OFF” or “set autotrace off” I receive the SP2-0575 error and my session is disconnected from the database, although it seems to happen randomly when disabling AUTOTRACE.  Interesting problem I think.  Here is a partial output from the script:

SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ=10 SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> HOST sleep 10
SQL>
SQL>SPOOL testresults11201CostAdj10.txt
SQL>
SQL> SHOW PARAMETER OPTIMIZER

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     10
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
(several select statements similar to the following were executed, see yesterday's article for the complete list)
SQL> SELECT
  2    T1.COL1,
  3    T1.COL2,
  4    T1.COL3,
  5    T2.COL1,
  6    T2.COL2,
  7    T2.COL3
  8  FROM
  9    T1,
 10    T2
 11  WHERE
 12    T1.COL1=T2.COL1
 13    AND T1.COL1 BETWEEN 1 AND 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945774515

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |     9 |  3816 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1           |    10 |  2120 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_C0018298 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0018300 |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2           |     1 |   212 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   4 - access("T1"."COL1">=1 AND "T1"."COL1"<=10)
   5 - access("T1"."COL1"="T2"."COL1")
       filter("T2"."COL1"<=10 AND "T2"."COL1">=1)

SQL>
SQL> SPOOL OFF
SQL> set autotrace off
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ=1 SCOPE=MEMORY;
ERROR:
ORA-03114: not connected to ORACLE

The problem is not specific to the modification of the OPTIMIZER_INDEX_COST_ADJ parameter, and the error does not always happen after changing a specific parameter. 

Metalink (MOS) Doc ID 563269.1 does not apply:

SQL> select owner, object_type from all_objects where object_name='DUAL';

OWNER      OBJECT_TYPE
---------- -------------------
SYS        TABLE
PUBLIC     SYNONYM

FIPS compliance checking (FLAGGER) (reference 2) should not apply:

SQL> show flagger
flagger OFF

As I mentioned, an interesting problem - yet a bit of a headache inducer when trying to use AUTOTRACE.





Building Oracle Database Test Case Scripts – Is there an Easy Method?

27 09 2010

September 27, 2010

It is far too easy to invest many hours into building a test case to demonstrate or explore a simple concept, or model a particular problem that is being encountered.  I recently had the opportunity to attend a session by Jonathan Lewis that was titled “The Beginner’s Guide to Becoming an Expert”, a session which emphasized the use of test scripts to investigate various types of unexpected behavior, when that unexpected behavior is found.  Using examples from Jonathan’s session, that unexpected behavior might include noticing a reduction in the redo generated when an update statement is driven through an index access path, for instance.  Another example from Jonathan’s session included the unexpected behavior where a significant amount of redo is generated when a bitmap index is updated, compared to the amount of redo generated when a b*tree index on the same column is updated, and then finding that the amount of redo generated for the bitmap index drops significantly when the Oracle Database is upgraded from 9.2 to 11.1.  Creating a test script that models a particular situation allows that problem to be investigated in isolation, and then repeatedly analyzed as one variable is changed at a time – the change might include modifying an initialization parameter, submission of different initial bind variables, execution of the script against a different Oracle Database release version, or any number of other small scale changes.

One of the key concepts in the creation of test cases is to compare the intended end result of the test case with the starting point, and then identify logical transformation stages between the two end points.  At each transformation stage confirm that the preceding step logically leads to the next step, and then determine if there is any chance that a different result might be obtained given the same input.  Work backward from the last transformation stage to the first, and ask yourself if there is any other possible input that might have caused the outcome.  It takes practice, a fair amount of research, and sometimes trial and error to discover the multiple potential cause and effect relationships.

I recently used this technique to build a test case for an OTN thread to discover why an index was not used to retrieve three rows from an 8 million row table.  The “given” information was that the index was used if an always false comparison of two literal VARCHAR values was removed from an OR condition in the WHERE clause.  The first step in building a test case, assuming that the production environment where the problem was found is not available for use, involves building a model of the source data.  If you are familiar with the real data that causes the unexpected behavior, build a model that simulates the real data, otherwise assume that the data to be returned is scattered among the other rows in the table data.  In my test case, to simplify the calculations a bit, the number of rows to be inserted into the test table was increased to 10 million.  Before building the data model, determine what might cause a full table scan to be performed rather than using an index access path: high clustering factor for the index, poor cardinality estimates (might be caused by out of date object statistics), incorrectly gathered system statistics, poorly specified optimizer parameters, sub-optimal index definition (might require an index skip scan, or return a large number of rows that are later eliminated at the table level), optimizer bugs, maybe the full table scan really is faster than an index access path, etc.
 
Now that we have identified some of the causes for a full table scan, our test case may be used to experiment “what if” and “how would I know if that was the cause?”  To determine if the clustering factor was the problem, I might first need to know how the optimizer uses the clustering factor, and am I able to artificially adjust it to see if a change in the value makes a difference in performance.  To determine if the cardinality estimates are a problem I might use DBMS_XPLAN.DISPLAY_CURSOR to retrieve the estimated cardinality and actual number of rows returned by every operation in the execution plan.  I might remove the comparison of the two literal VARCHAR values to see how that change affects the cardinality estimates, or see if changing one of the literal values causes a change – one of those changes just might also allow an index access path to be used.  I might try to use a hint to force the use the index (hints are, after all, directives and not suggestions), and if that does not work I might analyze why the hint was invalid.  If the hint worked, after verifying that a performance improvement was achieved, I would likely consult a 10053 trace file to determine why the index access path was not automatically used – was the cost too high, or was the index access path cost not even calculated?

Once the cause of the full table scan is determined, additional what-if statements should be considered.  What if the index hint worked, am I able to permanently add that hint to the SQL statement – are there any cases were adding that hint might lead to other performance problems later?  If the cardinality estimates are incorrect, is there anything that I am able to do to fix the cardinality estimates?  If the optimizer parameters are poorly specified, am I able to adjust those parameters – and if I do what might be the impact on the other SQL statements that depend on the current initialization parameter values for optimal performance and costing calculations?  Are we able to modify the application that submitted the SQL statement to remove the unnecessary literal comparison?

Once the desired outcome is reached, work backward through the steps and determine where this approach might go wrong.  Is it reasonable to conclude that the previous step always results in the next step?  In short, test cases are not terribly difficult to construct once you learn what might cause a transformation step to simply lead to the wrong next transformation step.

Oracle Database behavior changes from one release version to another, and sometimes from one patch level to the next.  Save the test case scripts once generated, because the behavior that is demonstrated by the test script might only be true for a small number of Oracle Database releases.  My test case script for generating a deadlock on Oracle Database 11.1 and 11.2, while the same test case script simply generates an expected enqueue on Oracle Database 10.2, is just one example of the usefulness of test cases to demonstrate Oracle behavior.

Do we need a test case to determine why the OTN forums, if working at all, are so slow this morning?  I believe that it required more than two minutes just to display the forum home – that was roughly an hour after I saw the message stating that the OTN forums were down (someone must have tripped over the power cord again).

Any other ideas for easily creating useful test cases?





SQL Challenge – Submit Update Statements, Updated Values are Reversed on Successful Commit

21 09 2010

September 21, 2010

Is it possible to construct an Oracle Database test case such that:

  1. An UPDATE statement is issued to change a DATE column value from NULL to the current date (SYSDATE) for two rows.
  2. The data source is queried and the result set returned by the database shows that the DATE column is set to the current date for the two rows.
  3. A COMMIT is successfully executed.
  4. The data source is queried and the result set returned by the database shows that the DATE column is again NULL for the two rows.

—————–

For example, something like this:

CREATE TABLE T1 (
  C1 NUMBER,
  C2 DATE,
  C3 DATE,
  CONSTRAINT "CHECK_DATE" CHECK (
    NVL(C2,TO_DATE('01-JAN-2000','DD-MON-YYYY')) < NVL(C3,TO_DATE('01-JAN-2000','DD-MON-YYYY')))
    INITIALLY DEFERRED DEFERRABLE);

INSERT INTO T1 VALUES (1,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
INSERT INTO T1 VALUES (2,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
INSERT INTO T1 VALUES (3,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));

COMMIT;

There are now three rows in the table T1:

SELECT
  *
FROM
  T1;

 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

We are able to update the column C2 to the current date (SYSDATE) with the following SQL statement:

UPDATE
  T1
SET
  C2=SYSDATE
WHERE
  C1 IN (1,2);

2 rows updated.

The update was successful, and we are able to confirm that the update was successful:

SELECT
  *
FROM
  T1;

C1 C2        C3
-- --------- ---------
 1 21-SEP-10 31-DEC-00
 2 21-SEP-10 31-DEC-00
 3           31-DEC-00

Now we will issue a COMMIT:

COMMIT;

Then query the table again to find the original value of column C2 was restored:

SELECT
  *
FROM
  T1;

 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

The only catch is that "Commit complete." must appear after the COMMIT, rather than something like the following:

COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TESTUSER.CHECK_DATE) violated

Be creative.  How is it possible to update a set of rows, select the rows to prove that the rows were updated, COMMIT, receive a confirmation that the COMMIT was successful, and then find that the original values were restored?  In case you are wondering, this OTN thread was the inspiration for this blog article (but don't look at forum thread yet).

Is the data source that makes this possible a table, or is it something else?  Is there possibly a corrupt index involved?  Is there an ON COMMIT trigger involved (as far as I am aware, there is no such trigger available on Oracle Database).  VPD (virtual private database) tricks?  View tricks?  Magic?





Finding a New Home for a Client on Windows

30 08 2010

August 30, 2010

As I am sure that you are aware, there are compatibility problems between Oracle Databases and Oracle Clients that are from different releases.  The Oracle Database 11.1 documentation states the following:

“Upgrade Clients to the Current Release
You can upgrade any or all of your Oracle8i, Oracle9i, or Oracle Database 10g clients to Oracle Database 11g Release 1 (11.1). The Oracle Database 11g Release 1 (11.1) client can be used to access your Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Database 11g Release 1 (11.1) databases.”

So, apparently the Oracle 11.1 client has no trouble communicating with a very old Oracle Database 8.1 release (I did not realize that the backward compatibility extended that far back).  But, what do you do if you are running a Windows client computer and need to able to access an Oracle 11.2.0.1 database and an Oracle 8.0.5 database?  Are you out of luck?  A recent thread on the OTN forums asked a question that was very similar.  In the thread I provided three possible solutions:

  • If you have either the Professional or Ultimate editions of Windows 7. Download Microsoft’s free Windows XP Mode virtual machine and install the Oracle 9i client in that virtual machine. You should be able to create an icon that automatically launches the Windows XP mode virtual machine and then runs a specific program inside the virtual machine (I have not yet tested the auto-launch feature).
  • If the programs will use an ODBC connection, install one of the Oracle clients, create the ODBC connection that will use that client, install the other Oracle client, and create the ODBC connection that will use that client.
  • Modify the environment and start the program that will access the database using that modified environment.

Let’s take a closer look at the last option.  Let’s assume that we have an Oracle 11.1.0.7 home set up on the computer, with the bin folder located here:

C:\oracle\product\11.1.0\db_1\bin

When a program starts on Windows, typically the current folder (directory) is searched for required support files, and then the folders in the system path (defined by the PATH environment variable) are checked in the specified order for the support files.  So, to change to this Oracle home we just need to place this location of our home's bin folder at the start of the PATH environment variable.  To change that variable, open a Windows command prompt window (run cmd.exe) and type the following commands (note that the last command will cause the command prompt window to close):

SET PATH=C:\oracle\product\11.1.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The first line appends the selected Oracle home to the beginning of the PATH variable.  The second line starts SQL*Plus in a new window, with Windows automatically finding it in the modified search path; what is interesting is that SQL*Plus (or any program for that matter) remembers the PATH variable that was modified by the previous command (we could have modified several environment variables).  The end result might look like the following (assuming that you did not press the Enter key after typing EXIT - the background window will disappear if you pressed the Enter key):

Interesting, but how do we know that SQL*Plus is remembering the modified PATH variable?  We are able to confirm that the modified path is remembered using Microsoft's Process Explorer utility by double-clicking on the sqlplus.exe entry in the program and then switching to the Environment tab:

As you are probably able to determine, I probably also should have changed the PERL5LIB location.  Now, let's try a 8.1.7 client on the same computer:

SET PATH=C:\orant\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The resulting window will look like this, if you did not press the Enter key after typing EXIT  - the background window will disappear if you pressed the Enter key:

And the Process Explorer window might look like this:

Why stop at two examples?  We could do this in a command prompt window assuming that there are four Oracle homes):

SET PATH=C:\oracle\product\10.2.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\oracle\product\10.2.0\client_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\oracle\product\11.1.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\orant\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The end result, after manually aligning the windows, might look like this:

It does not need to be SQL*Plus that is started - it could be any program.  OK, but I don't want to type a bunch of commands to launch the program.  No problem, just create a text file with a .bat extension:

If the default icon is not fancy enough, just right-click the file and create a shortcut.  You can then view the properties of the shortcut to change the icon:

Just about any EXE file will contain a couple of icons, as will some DLL files.  The result looks like this:

If we were using this approach to start an application program that uses a specific Oracle home, it probably would make sense to change the properties of the shortcut to run using a minimized window to prevent a brief screen flash to black when the batch file executes.





Buffer Cache Hit Ratio, Optimizer Index Cost Adj, Clustering Factor, Performance Testing – How to Break a Test Computer

14 08 2010

August 14, 2010

My replacement laptop arrived this week to fix an occasional problem that I had with the old laptop:

I thought that I would perform a test on the new laptop, so I thought that I would try a slightly modified version of the script that appeared in the “Expert Oracle Practices” book in the Buffer Cache Hit Ratio section of chapter 8.  The table creation part of the script tends to be very CPU intensive for an hour or two, so if you want to try the script, set aside plenty of time.

SET LINESIZE 150
SET PAGESIZE 2000
SET TRIMSPOOL ON
SET TIMING ON
SET ARRAYSIZE 100
SPOOL C:\BCHRTestShortAsync11Gr2.txt

CREATE TABLE T1 (
  ID NUMBER,
  DESCRIPTION VARCHAR2(80));

INSERT INTO T1
SELECT
  CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),
  'This is the long description for this number '|| TO_CHAR(CEIL(ABS
(SIN(ROWNUM/9.9999)*10000)))
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000),
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000);

COMMIT;

CREATE INDEX IND_T1 ON T1(ID);

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE, METHOD_OPT=>'FOR ALL COLUMNS SIZE 1')

ANALYZE INDEX IND_T1 VALIDATE STRUCTURE;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

SET AUTOTRACE TRACEONLY EXPLAIN
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400;

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5;

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400;

SET ARRAYSIZE 100
SET AUTOTRACE TRACEONLY STATISTICS

SET TIMING ON

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='IND_COST_LARGE100';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='IND_COST_LARGE5';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5;

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400;

SPOOL OFF

The script builds a table that has 100,000,000 rows, displays the execution plan for a SQL statement that selects a small percent of the table when the OPTIMIZER_INDEX_COST_ADJ is set to 100 and again at 5, and finally excutes the SQL statements with a 10046 trace enabled.

A screen capture during the table creation with Oracle Database 11.2.0.1 (SQL*Plus Windows 7 Task Manager, Windows 7 Resource Monitor):

Note that the laptop only managed to achieve 3MB per second while creating the table - that was a fairly consistent average during the table creation that required 40 minutes and 29 seconds. 

The execution plans generated are as follows:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4000K|   206M|   222K  (1)| 00:44:29 |
|*  1 |  TABLE ACCESS FULL| T1   |  4000K|   206M|   222K  (1)| 00:44:29 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"<=400 AND "ID">=1)

--------

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  4000K|   206M|   202K  (1)| 00:40:25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  4000K|   206M|   202K  (1)| 00:40:25 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |  4067K|       |   427   (1)| 00:00:06 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=1 AND "ID"<=400)

So, the optimizer is predicting that an execution using a full table scan will complete in 44 minutes and 29 seconds, while an execution using an index range scan will complete in 40 minutes and 25 seconds.   Let's see how the different execution plans performed.

Full Table Scan:

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> SELECT
  2    ID,
  3    DESCRIPTION
  4  FROM
  5    T1
  6  WHERE
  7    ID BETWEEN 1 AND 400;

2547158 rows selected.

Elapsed: 00:00:14.39

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     838515  consistent gets
     813246  physical reads
          0  redo size
  140941880  bytes sent via SQL*Net to client
     280701  bytes received via SQL*Net from client
      25473  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2547158  rows processed

----

Index Range Scan:

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=5;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> SELECT
  2    ID,
  3    DESCRIPTION
  4  FROM
  5    T1
  6  WHERE
  7    ID BETWEEN 1 AND 400;

2547158 rows selected.

Elapsed: 00:01:24.36

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2573595  consistent gets
     818246  physical reads
          0  redo size
   17418653  bytes sent via SQL*Net to client
     280701  bytes received via SQL*Net from client
      25473  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    2547158  rows processed

The full table scan when selecting 2.55% of the rows in the table required 14.39 seconds while the index range scan required 1 minute 24.36 seconds.  I guess that is not a bad improvement for leaving the OPTIMIZER_INDEX_COST_ADJ parameter at the default value of 100.  But wait, the full table scan was CPU bound, not IO bound!  What if we allow the SQL statement to use more than 1 CPU?

SET ARRAYSIZE 100
SET AUTOTRACE TRACEONLY STATISTICS

SET TIMING ON

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='IND_COST_PARALLEL100';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;

SELECT /*+ PARALLEL(T1, 8 ) */
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400;

Here is a screen capture that was captured as the full table scan ended (I had to execute the above several times so that the screen capture showed the very end of the execution):

That helped a little, the full table scan completed in 12.01 seconds and used about 24% of the CPU capacity.  Interesting that the screen capture shows that the network utilization was 207Mb/s - the laptop was connected to a wireless connection with Oracle Database 11.2.0.1 running locally.  The full table scan might be IO bound now (note that there were only two datafiles, so I supposed that we could still be CPU bound if only two parallel processes could be used).

Let's see the storage requirements for the table and index (ASSM AUTO tablespace):

SET AUTOTRACE OFF

SELECT
  SEGMENT_NAME SEGMENT,
  COUNT(*) EXTENTS,
  BYTES/1024 EXT_SIZE_KB,
  (COUNT(*) * BYTES)/1048576 TOTAL_MB
FROM
  DBA_EXTENTS
WHERE
  OWNER=USER
  AND SEGMENT_NAME IN ('IND_T1','T1')
GROUP BY
  SEGMENT_NAME,
  BYTES
ORDER BY
  SEGMENT_NAME,
  BYTES;

SEGMENT       EXTENTS EXT_SIZE_KB   TOTAL_MB
---------- ---------- ----------- ----------
IND_T1             16          64          1
IND_T1             63       1,024         63
IND_T1            120       8,192        960
IND_T1             10      65,536        640
T1                 16          64          1
T1                 63       1,024         63
T1                120       8,192        960
T1                  1      60,416         59
T1                 83      65,536      5,312

It might be somewhat interesting to mention that somehow we ended up with one odd 59MB extent in the table, otherwise the extent allocation followed the typical pattern on 16 extents at 64KB, 63 extents at 1MB, 120 extents at 8MB, and then 64MB extents.

Let's take a quick peek at a couple of the index statistics:

SELECT
  HEIGHT,
  BLOCKS,
  LF_BLKS,
  LF_ROWS,
  DISTINCT_KEYS,
  MOST_REPEATED_KEY,
  PCT_USED
FROM
  INDEX_STATS
WHERE
  NAME='IND_T1';

HEIGHT     BLOCKS    LF_BLKS     LF_ROWS DISTINCT_KEYS MOST_REPEATED_KEY   PCT_USED
------ ---------- ---------- ----------- ------------- ----------------- ----------
     3     212992     208854 100,000,000        10,000           900,324         90

-

SELECT
  INDEX_TYPE,
  BLEVEL,
  LEAF_BLOCKS,
  DISTINCT_KEYS,
  AVG_LEAF_BLOCKS_PER_KEY,
  AVG_DATA_BLOCKS_PER_KEY,
  CLUSTERING_FACTOR,
  SAMPLE_SIZE
FROM
  USER_INDEXES
WHERE
  INDEX_NAME='IND_T1';

INDEX_TYPE     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
---------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -----------
NORMAL              2     212,350        10,000                      21                  10,074       100,749,838     556,356

Interesting - the clustering factor is higher than the number of rows in the table - maybe we should have sampled 100% of the table and index when collecting statistics.

A quick look at the table statistics:

SELECT
  NUM_ROWS,
  BLOCKS,
  AVG_ROW_LEN,
  SAMPLE_SIZE
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

   NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE
----------- ---------- ----------- -----------
100,000,000    817,275          54 100,000,000

It might be a good idea to take a look at the TKPROF summaries for the executions.  Just as a reminder, the normal (non-parallel, serial) table scan required 14.39 seconds, the parallel table scan required 12.01 seconds, and the index range scan required 1 minute 24.36 seconds.

TKPROF output full table scan: elapsed time 6.18 seconds (8.21 seconds less than what was reported by SQL*Plus) with 5.28 seconds of CPU time:

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    25473      5.28       6.18     813246     838515          0     2547158
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25475      5.28       6.18     813246     838515          0     2547158

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 185 

Rows     Row Source Operation
-------  ---------------------------------------------------
2547158  TABLE ACCESS FULL T1 (cr=838515 pr=813246 pw=0 time=6130508 us cost=222369 size=216021546 card=4000399)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   25473        0.00          0.02
  db file sequential read                         3        0.00          0.00
  direct path read                             4080        0.00          0.91
  SQL*Net message from client                 25473        0.00          7.00
  db file scattered read                          1        0.00          0.00

TKPROF output parallel full table scan: elapsed time 3.42 seconds (8.59 seconds less than what was reported by SQL*Plus) with 1.63 seconds of CPU time:

SELECT /*+ PARALLEL(T1, 8 ) */
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.04         11         25          0           0
Fetch    25473      1.63       3.42          0          0          0     2547158
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25475      1.63       3.47         11         25          0     2547158

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 185 

Rows     Row Source Operation
-------  ---------------------------------------------------
2547158  PX COORDINATOR  (cr=25 pr=11 pw=0 time=2345038 us)
      0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=30853 size=216021546 card=4000399)
      0    PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=30853 size=216021546 card=4000399)
      0     TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us cost=30853 size=216021546 card=4000399)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         3        0.00          0.00
  db file scattered read                          1        0.00          0.00
  os thread startup                               8        0.00          0.02
  PX Deq: Join ACK                                8        0.00          0.00
  PX Deq: Parse Reply                             8        0.00          0.01
  SQL*Net message to client                   25473        0.00          0.02
  PX Deq: Execute Reply                        5495        0.00          1.92
  SQL*Net message from client                 25473        0.00          7.32
  latch free                                      1        0.00          0.00
  PX qref latch                                   1        0.00          0.00
  PX Deq: Signal ACK RSG                          1        0.00          0.00
  PX Deq: Signal ACK EXT                          7        0.00          0.00
  PX Deq: Slave Session Stats                     3        0.00          0.00
  enq: PS - contention                            1        0.00          0.00

TKPROF output index range scan: elapsed time 76.85 seconds (16.51 seconds less than what was reported by SQL*Plus) with 29.28 seconds of CPU time:

SELECT
  ID,
  DESCRIPTION
FROM
  T1
WHERE
  ID BETWEEN 1 AND 400

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    25473     29.28      76.85     818246    2573595          0     2547158
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25475     29.28      76.85     818246    2573595          0     2547158

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 185 

Rows     Row Source Operation
-------  ---------------------------------------------------
2547158  TABLE ACCESS BY INDEX ROWID T1 (cr=2573595 pr=818246 pw=0 time=31008522 us cost=202004 size=216021546 card=4000399)
2547158   INDEX RANGE SCAN IND_T1 (cr=30646 pr=5238 pw=0 time=2313805 us cost=427 size=0 card=4067270)(object id 77111)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   25473        0.00          0.03
  db file sequential read                     81689        0.10         32.96
  SQL*Net message from client                 25473        0.00          6.11
  db file parallel read                       36586        0.87         26.09

Numbers from the twilight zone?  But wait, there is more.  The numbers were so illogical that I took the database offline, rebooted, and was immediately greeted with a warning that the RAID 0 array in the new laptop failed!  Try this experiement at your own risk.

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

So, what  have we learned from this blog article?  To get you started, what was the buffer cache hit ratio for each of the executions?  How can you explain the decrease in CPU time when parallel execution was used?

----

Edit August 14, 2010: In case you are wondering, the Oracle database was running on the laptop, not a remote server.  The 11.2.0.1 database was configured with Oracle's default parameters, with the exception of SGA_TARGET which was set to 12G and PGA_AGGREGATE_TARGET which was set to 1800M.  Oracle Database on the Windows platform supports only direct, asynchronous I/O.  Windows 7 Ultimate running on the laptop does not offer large memory page support, unlike the Server type Windows operating systems.





Determine the Oracle Client Release Version – Remotely

6 08 2010

August 6, 2010

An ERP mailing list email asked if there was a way to determine which Oracle clients had not been upgraded to the Oracle 11.2.0.1 client - I also recently encountered a request to determine similar information about the Oracle client versions of potential sessions, so I thought it might be fun to develop a solution.  I might be overlooking something, but it does not appear that the Oracle Database, by default, maintains the connected client’s release version.  Is there a solution?  Assume that the following is true:

  • The client computers are running Windows 2000 (client or server) or above
  • The client computers are members of ActiveDirectory
  • WMI (Windows Management Instrumentation) has not been disabled on the client computers
  • VBS (wscript or cscript) may be run from a computer that is logged in with domain administrator permissions

First of all, I would prefer not to visit each computer to determine which Oracle client is installed.  I also do not want to type all of the computer names – so we need a way to retrieve a list of the computers that are members of the ActiveDirectory domain.  A partial code fragment might look something like this (replace oracle and com as necessary for your domain):

Dim dbActiveDirectory
Dim comData
Dim snpData
Dim strDomain

Set dbActiveDirectory = CreateObject("ADODB.Connection")
Set comData = CreateObject("ADODB.Command")
Set snpData = CreateObject("ADODB.Recordset")

dbActiveDirectory.Provider = "ADsDSOObject"
dbActiveDirectory.Open "Active Directory Provider"
comData.ActiveConnection = dbActiveDirectory
strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
With comData
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  NAME" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  NAME"

    .CommandText = strSQL          
    .Properties("Page Size") = 1000
    .Properties("Searchscope") = ADS_SCOPE_SUBTREE
End With

Set snpData = comData.Execute
If snpData.State = 1 Then
    Do While Not (snpData.EOF)
        'Do something
        snpData.MoveNext
    Loop
End If

Now that we have a method for retrieving the computer names in the domain (note that the above code might only work for the domain administrators), we need an easy method to determine which Oracle client is installed - or at least which client is listed first in the system path.  Something like this might work:

C:\> tnsping /?

TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 05-AUG-2010 20:54:43

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

There is the Oracle client release version on the second line of the output, along with the client's operating system (just ignore the error message).  Now we just need a way to record the output of that command - let's redirect the output to a text file:

C:\> tnsping /? >C:\OracleClientversion.txt

Next, we need to find a way to tell the client computers on the network to execute the above command.  WMI will help, as long as our network user account is a member of the  Administrators group on the remote computer (or our network user account is a member of the Domain Adminitrators group).  If we are somehow able to create a batch file named C:\OracleClientVersion.bat on the remote client computer, we could execute a command like this in our script:

lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

The next task is to read the resulting text files that are created on each of the client computers, looking for the line that contains "TNS Ping Utility". Obviously, we need to write the results to another text file.  We end up with something like this:

Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

Do While Not (objFile.AtEndOfStream)
    strLine = objFile.ReadLine
    If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
        Exit Do
    Else
        'Do nothing
    End If
Loop

objFile.Close

One problem remains.  If we attempt to access the WMI objects on a computer that is offline our script will lock up for a period of time.  To work around that problem we should probably try to ping the remote computer first.  The ping routine looks like this:

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

A simple, straight-forward solution, right?  Because the columns in the combined log file are tab delimited, we are able to easily open the file using Microsoft Excel.  Partial output might look something like this:

Obviously, the client computers need to be turned on for the script to work correctly.  :-)  COMP19 certainly is running an old version of the Oracle client.

Notes:
* For every computer that responds to a ping, the script pauses for two seconds to allow enough time for the tnsping command on the remote computers to execute. 
* The script execution speed may be improved by modifying the script to remember each computer name that responded to a ping, and remotely launch the tnsping command on those computers.  Once the tnsping command has been started on each computer, make a loop through the list of computers that previously reponded to a ping and collect the OracleClientversion.txt files from those computers.

The full script may be downloaded here: DetOrclClient.vbs (save as DetermineOracleClientReleaseVersion.vbs).  The full script is displayed below (the script download from WordPress seems to fail):

'Revision 1.0 Created by Charles Hooper August 6, 2010

ProbeAllComputers

Sub ProbeAllComputers()
    'For modifying the INI file
    Const ForReading = 1
    Const ForWriting = 2
    Dim objFSO
    Dim objFile
    Dim objFileLog
    Dim objFileBatch
    Dim strLine
    Dim strOracleClientFile
    Dim strOracleClientFileF
    Dim lngResult

    'For querying active directory
    Const ADS_SCOPE_SUBTREE = 2
    Dim dbActiveDirectory
    Dim comData
    Dim snpData
    Dim strSQL
    Dim strDomain
    Dim strCommand

    On Error Resume Next

    strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
    strOracleClientFile = "C:\OracleClientVersion.txt"  'The location of the output file on the computers
    strCommand = "tnsping /? >C:\OracleClientVersion.txt"

    'Make the file location compatible with a UNC name
    strOracleClientFileF = Replace(strOracleClientFile, ":", "$")
    strOracleClientFileF = "\" & strOracleClientFileF

    Set dbActiveDirectory = CreateObject("ADODB.Connection")
    Set comData = CreateObject("ADODB.Command")
    Set snpData = CreateObject("ADODB.Recordset")

    dbActiveDirectory.Provider = "ADsDSOObject"
    dbActiveDirectory.Open "Active Directory Provider"

    comData.ActiveConnection = dbActiveDirectory

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  NAME"

        .CommandText = strSQL

        .Properties("Page Size") = 1000
        .Properties("Searchscope") = ADS_SCOPE_SUBTREE
    End With

    Set snpData = comData.Execute

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while reading the computer list from Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    If snpData.State = 1 Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFileLog = objFSO.CreateTextFile("C:\Oracle Remote Client Log.txt", True)

        Do While Not (snpData.EOF)
            If PingTest(CStr(snpData.Fields("Name").Value)) = True Then
                'Write the command file to the remote computer
                Set objFileBatch = objFSO.CreateTextFile("\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat", True)
                objFileBatch.Write "tnsping /? >C:\OracleClientversion.txt" & vbCrLf
                objFileBatch.Close
                Set objFileBatch = Nothing

                lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

                If lngResult = 0 Then
                    'Wait 2 seconds for the command to execute
                    WScript.Sleep 2 * 1000
                End If

                'Erase the batch file from the remote computer
                objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat"

                Err = 0  'Reset the error indicator
                Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

                If Err = 0 Then
                    Do While Not (objFile.AtEndOfStream)
                        strLine = objFile.ReadLine
                        If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
                            objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
                            Exit Do
                        Else
                            'Do nothing
                        End If
                    Loop

                    objFile.Close

                    If Err = 0 Then
                        'lngResult = MsgBox("No Errors accessing the file on " & CStr(snpData.Fields("Name").Value), 64, "Good")
                    Else
                        'lngResult = MsgBox("An error happened while accessing the output file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        Err = 0
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf
                    End If

                    'Erase the log file from the remote computer
                    objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF
                    Err = 0
                Else
                    If Err <> 0 Then
                        'lngResult = MsgBox("An error happened while accessing the INI file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf

                        If lngResult = 7 Then
                            dbActiveDirectory.Close
                            Set snpData = Nothing
                            Set dbActiveDirectory = Nothing
                            Exit Sub
                        End If
                        Err = 0
                    End If
                End If

            Else
                'lngResult = MsgBox("The computer " & CStr(snpData.Fields("Name").Value) & " could not be pinged" & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "No ping response" & vbCrLf
                If lngResult = 7 Then
                    dbActiveDirectory.Close
                    Set snpData = Nothing
                    Set dbActiveDirectory = Nothing
                    Exit Sub
                End If
            End If

            snpData.MoveNext
            Set objFile = Nothing
        Loop

        snpData.Close
        objFileLog.Close
    Else
        If Err <> 0 Then
            lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        End If
    End If

    dbActiveDirectory.Close
    Set snpData = Nothing
    Set dbActiveDirectory = Nothing
End Sub

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

.





SQL Injection – Getting a Date with Bobby Tables

2 08 2010

August 2, 2010

Last Friday I received my copy of the new book “Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions“.  There is an interesting demonstration of SQL injection on pages 17 through 21 of the book, where a clever use of SQL injection permits a significant amount of information to be returned when the only supplied value for a SQL statement is declared as a DATE data type.  If you bought the book, take a close look at the example on those pages (I do not recall this example from the first edition of this book or his “Expert One On One” book).  Certainly, you have heard of Bobby Tables?

I thought that it might be interesting to create a slight twist (an alternate ending) for the example shown in the book.  Let’s create a simple test table with a list of employees, divisions, and series of dates:

CREATE TABLE T1 (
  EMPLOYEE_ID VARCHAR2(30),
  DIVISION VARCHAR2(30),
  SALES_DATE DATE);

INSERT INTO
  T1
SELECT
  DECODE(MOD(ROWNUM,10),
    1,'JIM',
    2,'JACK',
    3,'MIKE',
    4,'BOB',
    5,'JOE',
    6,'SUE',
    7,'MARY',
    8,'JOAN',
    9,'CAROL',
    0,'JAN'),
  DECODE(MOD(ROWNUM,6),
    0,'DIVISION 6',
    'DIVISION '||TO_CHAR(MOD(ROWNUM,6))),
  TRUNC(SYSDATE)-1000+ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

COMMIT;

Now assume that we have a SQL statement like this in a stored procedure:

SELECT
  *
FROM
  T1
WHERE
  EMPLOYEE_ID='JIM'
  AND SALES_DATE>='01-JUL-2010'
ORDER BY
  SALES_DATE;

EMPLOYEE_ID DIVISION    SALES_DAT
----------- ----------- ---------
JIM         DIVISION 5  04-JUL-10
JIM         DIVISION 3  14-JUL-10
JIM         DIVISION 1  24-JUL-10

So, for the employee JIM there were three sales dates, one each in DIVISION 5, DIVISION 3, and DIVISION 1.  The stored procedure might look something like this (adapted from the example in the book):

 CREATE OR REPLACE PROCEDURE T1_RETRIEVE (strEmployeeID IN VARCHAR,dteSalesDate IN DATE) AS
  strSQL VARCHAR(1000);
  rowT1  T1%ROWTYPE;
  curT1  SYS_REFCURSOR;
begin
  strSQL := '
    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID=''' || strEmployeeID ||'''
      AND SALES_DATE>=''' || dteSalesDate ||'''
    ORDER BY 3';

  dbms_output.put_line( strSQL );

  open curT1 for strSQL;

  loop
    fetch curT1 into rowT1;
    exit when curT1%notfound;
    dbms_output.put_line (
      rowT1.EMPLOYEE_ID || ',' || rowT1.DIVISION || ',' || TO_CHAR(rowT1.SALES_DATE,'MM/DD/YYYY'));
  end loop;

  close curT1;
end;
/

We are able to verify that the stored procedure works like this:

SET SERVEROUTPUT ON SIZE UNLIMITED

EXEC T1_RETRIEVE('JIM','01-JUL-2010')

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'
AND SALES_DATE>='01-JUL-2010'
    ORDER BY 3
JIM,DIVISION 5,07/04/2010
JIM,DIVISION 3,07/14/2010
JIM,DIVISION 1,07/24/2010

It appears that the stored procedure worked.  Maybe I was too harsh in the book review of "Oracle SQL Recipes: A Problem - Solution Approach"?  It certainly appears as though we are relying on implicit data type conversion.

Let's assume that someone is interested in bypassing the date and employee specification for this stored procedure, so that all rows are returned.  Of course it is important to hide what is changed.  Maybe we would want the result of the SQL injection to look something like this:

SELECT
  *
FROM
  T1
WHERE
  EMPLOYEE_ID='JIM'
  AND SALES_DATE>=''
UNION ALL
SELECT
  *
FROM
  T1;

EMPLOYEE_ID DIVISION    SALES_DAT
----------- ----------- ---------
JIM         DIVISION 1  07-NOV-07
JACK        DIVISION 2  08-NOV-07
MIKE        DIVISION 3  09-NOV-07
BOB         DIVISION 4  10-NOV-07
JOE         DIVISION 5  11-NOV-07
SUE         DIVISION 6  12-NOV-07
MARY        DIVISION 1  13-NOV-07
JOAN        DIVISION 2  14-NOV-07
CAROL       DIVISION 3  15-NOV-07
JAN         DIVISION 4  16-NOV-07
JIM         DIVISION 5  17-NOV-07
...

For instance, if we do this:

ALTER SESSION SET NLS_DATE_FORMAT = '"''UNION ALL SELECT * FROM T1 --"';

Now if we execute the stored procedure like before:

EXEC T1_RETRIEVE('JIM','01-JUL-2010')

Output:
-----------------------
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 1
-----------------------

An error?  I did provide a warning in the review of the SQL Recipes book.  Let's do it the correct way this time to avoid the implicit data type conversion:

EXEC T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY'))

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=''UNION ALL SELECT * FROM T1 --'
    ORDER BY 3
JIM,DIVISION 1,11/07/2007
JACK,DIVISION 2,11/08/2007
MIKE,DIVISION 3,11/09/2007
BOB,DIVISION 4,11/10/2007
JOE,DIVISION 5,11/11/2007
SUE,DIVISION 6,11/12/2007
MARY,DIVISION 1,11/13/2007
JOAN,DIVISION 2,11/14/2007
CAROL,DIVISION 3,11/15/2007
JAN,DIVISION 4,11/16/2007
JIM,DIVISION 5,11/17/2007
JACK,DIVISION 6,11/18/2007
MIKE,DIVISION 1,11/19/2007
BOB,DIVISION 2,11/20/2007
JOE,DIVISION 3,11/21/2007
SUE,DIVISION 4,11/22/2007
MARY,DIVISION 5,11/23/2007
JOAN,DIVISION 6,11/24/2007
CAROL,DIVISION 1,11/25/2007
JAN,DIVISION 2,11/26/2007
JIM,DIVISION 3,11/27/2007
JACK,DIVISION 4,11/28/2007
MIKE,DIVISION 5,11/29/2007
...
MARY,DIVISION 1,07/30/2010
JOAN,DIVISION 2,07/31/2010
CAROL,DIVISION 3,08/01/2010
JAN,DIVISION 4,08/02/2010

That certainly is a different result than what we received the first time the procedure was executed (what the programmer probably intended).  We could have also tried this:

EXEC T1_RETRIEVE('JIM',SYSDATE)

I suppose that things might have been worse if Bobby Tables had a chance to experiment with this example.  We really should be using bind variables in this example, but what if you still want to use literals rather than bind variables - is there a work-around (one might be wondering if it is not easier to just do things the right way from the start)?  Maybe if we remove the source of the implicit data type conversion in the procedure like this:

CREATE OR REPLACE PROCEDURE T1_RETRIEVE (strEmployeeID IN VARCHAR,dteSalesDate IN DATE) AS
  strSQL VARCHAR(1000);
  rowT1  T1%ROWTYPE;
  curT1  SYS_REFCURSOR;
begin
  strSQL := '
    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID=''' || strEmployeeID ||'''
      AND SALES_DATE>=TO_DATE(''' || dteSalesDate ||''', ''DD-MON-YYYY'')
    ORDER BY 3';

  dbms_output.put_line( strSQL );

  open curT1 for strSQL;

  loop
    fetch curT1 into rowT1;
    exit when curT1%notfound;
    dbms_output.put_line (
      rowT1.EMPLOYEE_ID || ',' || rowT1.DIVISION || ',' || TO_CHAR(rowT1.SALES_DATE,'MM/DD/YYYY'));
  end loop;

  close curT1;
end;
/

Now testing the procedure:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
EXEC T1_RETRIEVE('JIM','01-JUL-2010')

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=TO_DATE('01-JUL-2010', 'DD-MON-YYYY')
    ORDER BY 3
JIM,DIVISION 5,07/04/2010
JIM,DIVISION 3,07/14/2010
JIM,DIVISION 1,07/24/2010

PL/SQL procedure successfully completed.

OK, it works.  Now what happens when the attempted SQL injection happens:

ALTER SESSION SET NLS_DATE_FORMAT = '"''UNION ALL SELECT * FROM T1 --"';
EXEC T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY'))

    SELECT
      *
    FROM
      T1
    WHERE
      EMPLOYEE_ID='JIM'

AND SALES_DATE>=TO_DATE(''UNION ALL SELECT * FROM T1 --', 'DD-MON-YYYY')

ORDER BY 3
BEGIN T1_RETRIEVE('JIM',TO_DATE('01-JUL-2010','DD-MON-YYYY')); END;

*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "TESTUSER.T1_RETRIEVE", line 18
ORA-06512: at line 1

That's better, but now we created a new problem... time for another change to the procedure?





SQL_TYPE_MISMATCH in V$SQL_SHARED_CURSOR

23 07 2010

July 23, 2010

While reading the OTN forums a couple of days ago I found a simple request regarding the meaning of the SQL_TYPE_MISMATCH column in V$SQL_SHARED_CURSOR.  The Oracle Database 8i documentation and the Oracle Database 11.2 documentation both state the following very limited description for that column in V$SQL_SHARED_CURSOR:

“The SQL type does not match the existing child cursor”

Just what does the above mean?  Analysis indicates that the SQL_TYPE_MISMATCH column indicates that something caused a setting change originating from the client (possibly Oracle client version from one client computer to the next or a change from DAO, ADO, RDO, OO4O, etc.), and that change triggered a hard parse resulting in the generation of a new child cursor with a different value in the SQLTYPE column of V$SQL.  I have not found the triggering element that causes apparently the same client computer with the same username and with the same ERP package to generate multiple child cursors for the same SQL statement, with just different values displayed in the SQLTYPE column.

For example (do not execute this SQL statement in a busy database):

SELECT
  SS1.SQL_TEXT,
  SS1.SQL_ID,
  SS1.CHILD_NUMBER,
  SS2.CHILD_NUMBER,
  SS1.SQLTYPE,
  SS2.SQLTYPE,
  SS1.PARSING_SCHEMA_NAME,
  SS2.PARSING_SCHEMA_NAME
FROM
  V$SQL SS1,
  V$SQL SS2
WHERE
  SS1.SQL_ID=SS2.SQL_ID
  AND SS1.SQLTYPE < SS2.SQLTYPE
ORDER BY
  SS1.SQL_TEXT,
  SS1.CHILD_NUMBER,
  SS2.CHILD_NUMBER;

A sampling of the output from a production database: 

SQL_ID        CHILD_NUMBER CHILD_NUMBER    SQLTYPE    SQLTYPE PARSING_SCHEMA_NAME            PARSING_SCHEMA_NAME
------------- ------------ ------------ ---------- ---------- ------------------------------ ------------------------------
SELECT BANNER FROM V$VERSION
39tw34mramfdv            0            1          2          6 USER3                          USER4  

  select SYSDATE from APPLICATION_GLOBAL
crmxa1g4nrc89            0            2          2          6 USER1                          USER1

SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"',  decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#),
  decode (rl.ectx#, 0, decode(rm.ectx#, 0,    (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1,    obj$ o1 where
o1.obj# = rs.ectx# and o1.owner# = u1.user#),   (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2,    obj$ o2 where
 o2.obj# = rm.ectx# and o2.owner# = u2.user#)),   (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3,    obj$ o3 where
 o3.obj# = rl.ectx# and o3.owner# = u3.user#))   from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl,   rule_set$ rs
   where su.name = :1 and so.name = :2 and so.owner# = su.user# and   so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# =
ru.user#   and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj#
3hgxzypxz2xpg            3            1          0          2 SYS                            SYS

SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"',  decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#),
  decode (rl.ectx#, 0, decode(rm.ectx#, 0,    (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1,    obj$ o1 where
o1.obj# = rs.ectx# and o1.owner# = u1.user#),   (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2,    obj$ o2 where
 o2.obj# = rm.ectx# and o2.owner# = u2.user#)),   (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3,    obj$ o3 where
 o3.obj# = rl.ectx# and o3.owner# = u3.user#))   from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl,   rule_set$ rs
   where su.name = :1 and so.name = :2 and so.owner# = su.user# and   so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# =
ru.user#   and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj#
3hgxzypxz2xpg            3            2          0          2 SYS                            SYS

alter session set events 'immediate trace name krb_options level 5'
fw1b57tbvz13j            1            0          0          6 SYS                            SYS

delete from source$ where obj#=:1
5t480bb4uh8hw            1            0          0          2 SYS                            SYS

delete from source$ where obj#=:1
5t480bb4uh8hw            3            0          0          2 SYS                            SYS

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            0          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            1          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            2          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            3          2          6 USER5                          USER9

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            4          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            5          2          6 USER5                          USER10

...

---

Checking V$SQL_SHARED_CURSOR for several of the above SQL statements to determine why the additional child cursors were created:

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='39tw34mramfdv'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
39tw34mramfdv 00000001847D4CA0 00000001847D3D70            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
39tw34mramfdv 00000001847D4CA0 00000001655BB248            1 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

(only SQL_TYPE_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='crmxa1g4nrc89'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
crmxa1g4nrc89 000000018460A3F8 00000001846094C8            0 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
crmxa1g4nrc89 000000018460A3F8 0000000161263120            2 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N

(only SQL_TYPE_MISMATCH and ROLL_INVALID_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='3hgxzypxz2xpg'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
3hgxzypxz2xpg 000000018CFE1950 0000000185222478            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
3hgxzypxz2xpg 000000018CFE1950 0000000184C8AFE0            2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
3hgxzypxz2xpg 000000018CFE1950 0000000160FA5908            3 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

(only SQL_TYPE_MISMATCH and OPTIMIZER_MODE_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='0jw74fwfdkv1x'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0jw74fwfdkv1x 0000000167748680 000000017CC367C0            0 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000016F344C28            1 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000162D93820            2 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0jw74fwfdkv1x 0000000167748680 0000000154E037E0            3 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000176A5B698            4 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000016B9DEA38            5 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000018D8A5370            6 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000187D7A1C8            7 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000171C634F0            8 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N

(SQL_TYPE_MISMATCH, BIND_MISMATCH and ROLL_INVALID_MISMATCH)

The last of the above SQL statements which was investigated included bind variables, and V$SQL_SHARED_CURSOR indicated that the additional child cursors were created in part due to a bind mismatch.  Let's take a look at the bind variable definitions for the SQL statement:

SELECT
  S.CHILD_NUMBER,
  SBM.POSITION,
  SBM.DATATYPE,
  SBM.MAX_LENGTH,
  SBM.BIND_NAME
FROM
  V$SQL S,
  V$SQL_BIND_METADATA SBM
WHERE
  S.SQL_ID='0jw74fwfdkv1x'
  AND S.CHILD_ADDRESS=SBM.ADDRESS
ORDER BY
  S.CHILD_NUMBER,
  SBM.POSITION;

CHILD_NUMBER   POSITION   DATATYPE MAX_LENGTH BIND_NAME
------------ ---------- ---------- ---------- ---------
           0          1          1         32 1
           0          2         96         32 2
           0          3         96         32 3
           1          1          1         32 1
           1          2         96         32 2
           1          3         96         32 3
           2          1          1         32 1
           2          2         96         32 2
           2          3         96         32 3
           3          1          1         32 1
           3          2         96         32 2
           3          3         96         32 3
           4          1          1         32 1
           4          2         96         32 2
           4          3         96         32 3
           5          1          1         32 1
           5          2         96         32 2
           5          3         96         32 3
           6          1          1         32 1
           6          2         96         32 2
           6          3         96         32 3
           7          1          1         32 1
           7          2         96         32 2
           7          3         96         32 3
           8          1          1         32 1
           8          2         96         32 2
           8          3         96         32 3

The bind variable definitions appear to be consistent from one child cursor to the next even though V$SQL_SHARED_CURSOR reported a difference in the bind variable definitions or data lengths.  The commercial ERP application, which is the primary source of SQL statements in this database, typically does not pre-initialize bind variable values during the initial parse call, which results in the MAX_LENGTH of VARCHAR2 bind variables being set to 2000.  The above shows that what should be a numeric bind variable is defined as a VARCHAR2 and what should be a VARCHAR2 is defined as CHAR - those child cursors with non-initialized bind variables might have aged out of the libary cache.

From the above analysis we see that the change in the SQLTYPE is not just confined to just SQL statements with bind variables, not confined to a specific username (in some cases the same username appears for both child cursors with different SQLTYPE values), and is not specific to just application level SQL.  It is still a bit of a mystery to me what causes the SQLTYPE column in V$SQL to change for internal SQL statements and SQL statements that are executed by the same client computer.

Any idea what triggers the change in the SQLTYPE column?  For this particular database, values of 0 and 2 appear to be used when SQL is parsed by SYS, while values of 2 and 6 appear to be used when SQL is parsed by application code.  This is likely more of a odd behavior than an actual problem.





Demonstration of Oracle “Ignoring” an Index Hint

19 07 2010

July 19, 2010 (Modified July 26, 2010, January 18, 2011)

In an earlier blog article I showed that adding an index hint is sufficient to allow Oracle to use a normal (non-function based) index on a column when that column is included in the WHERE clause inside a function.  Another blog article listed reasons why the Oracle optimizer may select not to use an index, with the help of the book “Expert One-On-One Oracle”.  I also wrote a blog article about the various hints that are available in 11g R1 and 11g R2.  I just stumbled across a forum post of mine from a year ago that included a test case regarding Oracle ignoring hints, so I thought that I would include that test case here (one of the initial justifications for setting up this blog is that I had difficulty with locating my previously created test cases).

First, hints are directives – Oracle’s optimizer cannot ignore hints unless:

  • The hint is invalid due to the wrong alias used in the hint
  • The hint is malformed
  • The hint is incompatible with another hint
  • The query was transformed by the optimizer into a form that is incompatible with the hint before the optimizer applied the hint
  • The hint, if followed, would cause the wrong results to be returned (see the forum thread for an explanation)

(Late Additions to the List, added July 26, 2010:)

  • Bugs in Oracle Database cause the hint to be lost (see Jonathan Lewis’ comment in the above forum post, and the examples on his blog – for example)
  • The hint, which appears like a comment, is removed before the query is sent to Oracle Database (see Mark W. Farnham’s comment below)
  • The hint specifies the use of a feature that is explicitly disabled by an initialization parameter
  • The _OPTIMIZER_IGNORE_HINTS initialization parameter is set to TRUE
  • An off-shoot of bullet point #5, an INSERT statement includes an APPEND hint and the table to be modified includes triggers and/or foreign key constraints (see Mohamed Houri’s comments below)

(Late Addition to the List, added January 18, 2011:)

Here is the setup for the test case from the forum thread:

CREATE TABLE T15(
  C1 NUMBER NOT NULL,
  C2 VARCHAR2(50) NOT NULL,
  C3 NUMBER,
  C4 VARCHAR2(300));

INSERT INTO T15
SELECT
  ROWNUM,
  TO_CHAR(ROWNUM,'0000000')||'A',
  DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM),
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 10000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 1000) V2;

CREATE INDEX IND_T15_C1_C2 ON T15(C1,C2);
CREATE INDEX IND_T15_C3 ON T15(C3);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T15',CASCADE=>TRUE) 

The above created the table T15, the composite index IND_T15_C1_C2 on the columns C1 and C2, and the index IND_T15_C3 on just the C3 column. The table is large enough due to column C4 that Oracle will probably select to use an index, when possible, rather than performing a full table scan. Now, let's see what happens when we try to determine the number of rows in table T15:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

The optimizer selected to use the composite index IND_T15_C1_C2 rather than a full table scan or the much smaller index IND_T15_C3 on just the column C3. Let's try a hint to use the index IND_T15_C3:

SELECT /*+ INDEX(T1 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

Oracle still used the IND_T15_C1_C2 index, even though I hinted to use the IND_T15_C3 index. But wait, there is a problem. The table is actually T15, not T1. I have included this example, as it is an easy mistake to make when typing SQL statements. Let's try again with a correctly formed hint in the SQL statement:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

SET AUTOTRACE OFF

Oracle still ignored the hint to use the IND_T15_C3 index, and selected to use the IND_T15_C1_C2 index instead (again). Oh, Oracle does not index NULL values in a non-composite B*Tree index (or when alll values are NULL in a composite index), so using that index may yield the wrong result. We can fix that problem:

UPDATE
  T15
SET
  C3=0
WHERE
  C3 IS NULL;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T15',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

Let's try again:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer did not select to use the IND_T15_C3 index, let's help it with a hint:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer still ignored the index hint and used the larger index. Let's see if we can help the optimizer by telling it that column C3 cannot hold a NULL value:

ALTER TABLE T15 MODIFY (C3 NUMBER NOT NULL);

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 877827156

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |  6700   (1)| 00:01:21 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |    10M|  6700   (1)| 00:01:21 |
----------------------------------------------------------------------------

Oracle finally used the correct smaller index, without needing a hint.

------

What else might cause Oracle's optimizer to "ignore" a hint?  Last year Jonathan Lewis posed this question, with a lot of interesting responses showing possible reasons for the FULL hint to be ignored.  I supplied two test cases in the comments of that blog article, so I thought that I would reproduce those test cases here.

Test Case #1:

CREATE TABLE T2 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T2(C1);
CREATE UNIQUE INDEX T1_N2 ON T2(C1,C2);

ALTER TABLE T2 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

CREATE OR REPLACE VIEW T1 AS
SELECT /*+ INDEX(T2) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T2) T2;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

select /*+ full(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 1213398864

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   101 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_PK | 48000 |   101   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_PK;

select /*+ full(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 824454759

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   134 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_N2 | 48000 |   134   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_N2;

select /*+ full(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

---

Test Case #2:

In schema 1:

CREATE TABLE T1 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T1(C1);
CREATE UNIQUE INDEX T1_N2 ON T1(C1,C2);

ALTER TABLE T1 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

CREATE OR REPLACE VIEW T1_VIEW AS
SELECT /*+ INDEX_FFS(T1_V) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T1) T1_V;

CREATE OR REPLACE PUBLIC SYNONYM T1 FOR T1_VIEW;

GRANT SELECT ON T1_VIEW TO PUBLIC;
GRANT SELECT ON T1 TO PUBLIC;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 1018460547

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    29 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_PK | 48000 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------- 

In schema 1:

DROP INDEX T1_PK;

In schema 2:

select /*+ full(t) no_index(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 177081169

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 48000 |    38   (0)| 00:00:01 |
-----------------------------------------------------------------------

In schema 1:

DROP INDEX T1_N2;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

---

Jonathan pointed out in his blog article that there is another very sensible reason for Oracle's optimizer to "ignore" the FULL hint... the table was defined as an index organized table.





Windows as an OS Platform for Oracle Database – Where Do I Start?

13 07 2010

July 13, 2010 (Modified July 14, 2010, July 22, 2010, February 22, 2011)

I try to follow the discussions on the Oracle-L mailing list, even though I do not participate in that mailing list.  Just a couple of days ago I saw an interesting discussion thread in the group that forced me to think for a moment… maybe one should use a shared server configuration rather than a dedicated server configuration when Oracle Database is running on Windows XP?  Think about it for 10 seconds – why should someone do this, and why not?

While you think about it, let’s chain together a couple of forum threads to compile a collection of notes about the combination of Windows and Oracle Database.

I have worked with the Windows operating system a bit, having read the Windows 95 Resource Kit book cover to cover roughly 15 years ago (and a book about the Windows 95 registry from the same time period), the Windows Vista Resource Kit book a couple of years ago, and several books that described Microsoft’s server operating systems.  I have also posted a couple of responses to various forums, answering questions about the sometimes unexpected behavior of the Windows operating systems.

Take, for example, this thread where the original poster asked what are the advantages of running Oracle Database on a server edition of Windows compared to a desktop edition of Windows.  This is the response that I provided (the italics section should have been included in the original post):

There are a couple of important differences between the two operating systems:

  • Windows XP is limited to 10 simultaneous external connections. It is easy to reach that limit even when other computers are not attempting to remotely connect to the Windows XP computer. The System event log will display a message “TCP/IP has reached the security limit imposed on the number of concurrent TCP connect attempts.” when the 10 connection limit is attempted to be exceeded. No such limit exists on Windows 2003.
  • Memory handling algorithms are different between the two operating systems. There are memory limits in the operating systems that are independent of the amount of physical memory installed. For example, what do you do if the following message appears in the System event log: “The server was unable to allocate a work item 2 times in the last 60 seconds.” On Windows 2003, the problem can be fixed by creating a MaxWorkItems entry in the Windows registry.
  • The Home and Home Starter (I am not sure if that is the correct name) versions of Windows XP do not fully support Oracle Database, because those versions of the operating system do not permit user controlled/configurable local user groups, user permissions (logon as batch), nor file level permissions (file permissions/file ownership are there – they just cannot be modified through the user interface).
  • [Server version supports large memory pages, while the desktop version does not.]
  • Yes, there are other differences [between the server and desktop operating systems], even though the two operating systems share the same kernel. The R2 version of Windows 2003 is preferred over the original release.

With the above bullet points in mind, how would you answer the original poster in the Oracle-L discussion thread (incidentally, Windows 95 did not impose the 10 simultaneous external connection limit)?

Let’s take a look at memory, since it was mentioned in the above quote.  From another OTN thread that I participated in:

> I have a vendor recommending shared server due to a windows thread limitation.  Upon reading up on windows threads, it seems to be more an issue of an application not properly managing memory withing the memory stack.
> Does anyone know of a way to monitor thread utilization on a per session basis?
> Has anyone ever seen an Oracle based application hit a windows thread limit?
> Thanks in advance.
—————-

A quick Google search finds this link that suggests that 500,000 threads are possible, but the author was only able to reach 13,000 threads:
http://blogs.msdn.com/oldnewthing/archive/2005/07/29/444912.aspx
32 bit Windows: “Because the default stack size assigned by the linker is 1MB, and 2000 stacks times 1MB per stack equals around 2GB, which is how much address space is available to user-mode programs.”

Metalink Note:46001.1: “Oracle Database and the Windows NT memory architecture, Technical Bulletin” confirms that by default Oracle allocates 1MB per thread. The number of possible threads is thus limited by either the maximum per process limit for memory, or the combination of system RAM and swap space, with each session requiring a minimum of 1MB just to connect.

The number of threads can be viewed in Task Manager on the server – it is necessary to manually add the “Thread Count” column to Task Manager to see the current number of threads in a process. There is typically one thread per session, plus several background threads.

Niall Litchfield, who authored the chapter Running Oracle on Windows in the “Expert Oracle Practices” book provided a brief follow-up to my post in that thread.

Continuing with the discussion of memory on Windows, another one of my posts on the OTN forums mentioned the following:

The Standard Edition of 64 bit Windows 2003/2008 Server is artifically limited to 32GB in a flat memory model. The Enterprise Edition of 64 bit Windows 2003/2008 Server supports more memory than you can physically connect to the server’s motherboard. 64 bit Oracle Database Standard Edition One and greater is able to take advantage all of the memory in the server that is provided within the flat memory model without any special configuration – other than enabling large page support to decrease the overhead of managing a lot of memory.

The problem that you are encountering with 32 bit Windows is a limitation of 32 bit software – it can only address 2^32 GB of memory – that is 4GB. To access more than 4GB, a memory region is set aside to act as a translation window to peek into the upper memory region which can contain only the Oracle buffer cache. Articles on Metalink describe how this works. The 64 bit flat memory model does not have this limitation. With 64GB in the server [on a 64 bit operating system], you could specify a 32GB SGA_TARGET and 20GB PGA_AGGREGATE_TARGET, if you so desire.

Incidentally, the desktop 64 bit Windows operating systems do not impose the 32GB limit like the 64 bit Standard Edition of the server operating systems.

If you have run Oracle Database Database 10.1 or higher on Windows you might have seen a warning in Enterprise Manager stating that significant virtual memory paging is happening in the server.  There have been several threads on the OTN and other forums that mentioned this particular error message.  One of the threads that I participated in that is found on the OTN forums mentioned the following and included a link to one of my Usenet posts on the same topic:

The message may be a false alarm – even checking task manager on Windows 2003 may be misleading. On Windows 2003, the “PF Usage” statistic in task manager is not page file usage, but instead total virtual memory usage, which includes RAM and page file. Oracle Enterprise Manager, I believe, is showing the “PF Usage” statistic, and is making the assumption that this is page file usage.
For additional analysis see the discussion in this link:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b62c4d761cace2a6

If I recall correctly, enabling large page support on Windows causes an incorrect amount of memory to be reported in Task Manager and by the TASKLIST command executed at the Windows command line.  However, enabling large page support may reduce kernel (system) mode CPU usage and the amount of memory needed for managing memory, so it might make sense to enable large page support.  I described the process in an OTN thread a couple of years ago:

Below are the steps that I used to enable large page support for 64 bit Windows.
Set up for Large Page Support (cannot use LOCK_SGA in init.ora/spfile):

  1. Choose Start > Settings > Control Panel
  2. Double-click Administrative Tools.
  3. Double-click Local Security Policy.
  4. Expand Local Policies and select User Rights Assignment.
  5. Select Lock pages in memory and choose Action > Security…
  6. Click Add…
  7. Select the username that was used to install Oracle and the ora_dba group from the Name list.
  8. Click Add.
  9. Click OK to close the Select Users or Groups dialog.
  10. Click OK to close the Local Security Policy Setting dialog.
  11. Open Regedit
  12. Navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
  13. Create a registry key (REG_SZ) named ORA_LPENABLE and set the value to 1
  14. Reboot the server.

Tuning Windows for Oracle… I might have replied to a couple of forum threads over the years in this topic.  One of those threads is this one, where I unfortunately had to correct a couple of suggestions that were quoted from another resource (just another reason why one should not blindly copy the work of others).

A couple notes about the above setting suggestions:

  • I believe that you will find that NtfsDisable8dot3NameCreation is already set to 1 on 64 bit Windows server operating systems. The reasoning is that only 16 bit programs need the 8.3 names, and 64 bit Windows cannot run 16 bit applications.
  • LargeSystemCache = 1 – this does not look correct, see:
    http://technet.microsoft.com/en-us/library/cc784562(WS.10).aspx

I suggest taking a look at the following document:
http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document)
Do not follow the directions related to TcpAckFrequency in the above document [think 45 minutes to transfer a file over a gigabit connection when the transfer should have completed in three to four seconds].

A couple other documents which you might find helpful (pick the version specific for your release of Oracle):
http://download.oracle.com/docs/html/B13831_01/tuning.htm (edit: the documentation site appears to be down at the moment, this link applies to Oracle Database 11.2.0.1 and Windows server operating systems and will be accessible when the documenation site comes back online – in the mean time the Google cache version)
http://download.oracle.com/docs/html/B13831_01/architec.htm

Tuning, of course, leads to questions, “Is Oracle Slower on Windows than on Linux”  as asked in another OTN thread.  I provided a couple of comments in that thread, with the following as the most relevant to this blog article:

There are a lot of factors which may have contributed to the slower than expected performance on Windows 2003 that you noticed. Those factors include:

  • Not collecting system (CPU) statistics on Oracle 9i or higher
  • Not collecting data dictionary statistics on Oracle 9i or higher
  • Not configuring large page support, if it is able to be used
  • Not taking advantage of a battery backed cache to improve write performance
  • Leaving the default db_file_multiblock_read_count set to 16 (I believe that is what the DBCA sets on 10g and above) rather than allowing Oracle to auto-tune the parameter. Limiting extent sizes to smaller than 1MB could also cause performance problems.
  • Installing a virus scanner on the server, especially if it is permitted to scan program and data files used by Oracle.
  • Using inappropriate parameters for memory allocations.
  • If a database release upgrade was involved, not directly attacking the specific performance problem which is the source of the problem – could just be just a couple bad execution plans, possibly caused by the upgraded query optimizer.
  • … (Niall Litchfield, or someone else might be able to provide additional causes)

A couple things to keep in mind about Windows:

  • Oracle on Windows uses direct, asynchronous I/O, which helps minimize concurrency problems. But, this also means that the operating system’s file cache probably is of little use to Oracle on Windows.
  • Oracle on Windows uses a thread model, while Oracle on Linux (and other Unix like operating systems) uses a process model. The thread model is less negatively impacted by context switches than is the process model, which should help improve performance in some cases, such as repeatedly calling a PL/SQL function from a SQL statement.
  • Windows uses a single CPU run queue, while the Linux 2.6 and later kernel uses a separate run queue for each CPU. That means that processes (and threads) tend to float between CPUs more frequently on Windows than on Linux, which may reduce the benefit of large L1 and L2 caches built into the CPUs on Windows. The Linux 2.6 kernel is also able to automatically throttle very CPU intensive processes, which might provide a little more headroom for multiple concurrent Oracle sessions.
  • There is a paper on Microsoft’s website titled “Performance Tuning Guidelines for Windows Server 2003″ http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document) – take a look at that document, but do not implement the suggested TcpAckFrequency Windows registry modification.
  • Try to minimize the amount of memory used by the operating system for file caching.
  • Properly configured, the same server running 64 bit Windows should be able to achieve roughly the same performance as the same server running 64 bit Linux (assuming direct, asynchronous I/O is enabled on Linux).
  • … (Niall Litchfield, or someone else might be able to provide additional things to keep in mind)

The general advice that you will likely receive is to use the operating system that you know best for Oracle.

Niall Litchfield as well as several others provided responses in the above OTN thread – it is an interesting read.

—–

Late addition July 22, 2010:

Another OTN forum thread that I recently participated in:

When I tested it [Windows XP Home running Oracle Database] a while ago (maybe a year or two in the past) certain features did not work.  Because there are certain Oracle Database features that require the ORA_DBA security group and the Local Security Policy control panel, neither of which exist in Windows XP Home.  If you are able to get Oracle Database installed and a database created (I was able to do both), you might encounter the limitations or you might not depending on which features you use.  This is what I recall from the testing:

  • “CONNECT / AS SYSDBA” will not work – the logged in user must be in the ORA_DBA group which does not exist
  • Datapump import/export does not work.
  • Cannot schedule jobs using Enterprise Manager Database Control – the OS user specfiied must have the LOGON AS BATCH permission which is assigned in the Local Security Policy control panel (I experimented with borrowing this control panel item from XP Pro but it refused to run on XP Home).
  • RMAN from the command line works to backup and restore databases, but of course it is not possible to schedule the RMAN backups using Enterprise Manager Database Control.
  • File level permissions cannot be adjusted without removing the hard drive and installing it as a secondary drive in a Windows XP Pro (or higher) computer, at which point the user logged into the Windows XP Pro computer will need to take ownership of the files before modifying file permissions.

I suspect that there might be problems with UTL_FILE, BFILE datatypes, and some of the other features.

I did not perform much testing.  When one considers the time involvement, the Windows Anytime upgrade that I mentioned earlier is the least expensive solution.

—–

Back to the Oracle-L thread… shared or dedicated?





Experimenting with Automated Emails

7 07 2010

July 7, 2010

I have not had much of a need to send emails on a scheduled basis using Oracle Database.  Roughly nine years ago I created a program that directly interacts with an SMTP server to send HTML formatted emails on a schedule.  If I recall correctly, we have over 200 such emails based on data contained in the database that are sent on schedule.  In my custom program there are several built-in custom reports, but I am also able to build a report directly from a SQL statement.  The custom program looks like this:

The {TODAY} keyword displayed in the Alternate Report Title box is one of several that may be specified in the subject line or in the Custom SQL Statement by right-clicking (click to see the large version of this menu):

—–

A request recently came in from an ERP mailing list that looked something like this (paraphrased):

I want an email sent when the expiry_date = trunc(sysdate) +30, so even if the vendor does not supply to us in a specific time frame, we can alert him, and the relevant staff, that his supplier certification is about to expire.

The original poster was looking for a way to automatically send an email to a supplier when some sort of expiration date was approaching, and of course a trigger could not send the email if there was no triggering event.  The original poster provided the following code sample:

DECLARE
msg varchar2(20000) := 'TESTING ONLY';
recipient varchar2(20000);
BEGIN
select EMAIL_id into recipient from vw_vendor_a_email;
UTL_MAIL.SEND (
sender => 'dontspamme@mycompany.com.au',
recipients => recipient,
subject => 'Documents have been attached to ',
message => msg||' Please update your documents');
END;
/

The OP was happy with the code sample, but was having trouble when the SQL statement returned more than one email address (SELECT ... INTO MY_VARIABLE only allows a single row to be returned).  There are a couple of methods to solve this problem/limitation - one of those methods is shown below.

First, we should probably make certain that the SMTP_OUT_SERVER parameter value is specified:

SHOW PARAMETER SMTP_OUT_SERVER

NAME               TYPE        VALUE
------------------ ----------- ------------------------
smtp_out_server    string      mailman.mycompany.com.au

Next, we should probably create a test table with a couple of email addresses:

CREATE TABLE T1(
  VENDOR_ID VARCHAR2(15),
  EMAIL_ADDRESS VARCHAR2(50));

INSERT INTO T1 VALUES(
  'INTERNAL',
  'tryme@mycompany.com.au');

INSERT INTO T1 VALUES(
  'INTERNAL',
  'headache@mycompany.com.au');

INSERT INTO T1 VALUES(
  'INTERNAL',
  'helpme@mycompany.com.au');

COMMIT;

Now we may construct a SQL statement to put the email addresses into a format that is suitable for SMTP email servers (only tested with Microsoft Exchange):

SELECT
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2))
FROM
  (SELECT
    EMAIL_ADDRESS,
    ROWNUM RN
  FROM
    T1
  WHERE
    VENDOR_ID='INTERNAL'
  ORDER BY
    EMAIL_ADDRESS)
CONNECT BY
  PRIOR RN=RN-1
START WITH
  RN=1;

MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2))
------------------------------------------------------------------------
tryme@mycompany.com.au;headache@mycompany.com.au;helpme@mycompany.com.au

Note that the email addresses are not in alphabetical order even though there is an ORDER BY clause in the inline view - that is because the ORDER BY clause is applied after the ROWNUM value is calculated.  We could fix that, if it were important.  The above email address list is in the correct format for Microsoft Exchange.

Now let's put that SQL statement into the PL/SQL script supplied by the OP:

DECLARE
msg varchar2(20000) := 'TESTING ONLY';
recipient varchar2(20000);
BEGIN
SELECT
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2)) into recipient
FROM
  (SELECT
    EMAIL_ADDRESS,
    ROWNUM RN
  FROM
    T1
  WHERE
    VENDOR_ID='INTERNAL'
  ORDER BY
    EMAIL_ADDRESS)
CONNECT BY
  PRIOR RN=RN-1
START WITH
  RN=1;
UTL_MAIL.SEND (
sender => 'dontspamme@mycompany.com.au',
recipients => recipient,
subject => 'Documents have been attached to ',
message => msg||' Please update your documents');
END;
/

Note that when the above anonymous PL/SQL script is executed, the email server may return an error message if we had inserted an email address into table T1 that is foreign (belonging to a different email address domain).  For instance, Microsoft Exchange might return a 550 5.7.1 Unable to relay error if we tried to send an email to Bill Gates:

ERROR at line 1:
ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 241
ORA-06512: at "SYS.UTL_MAIL", line 414
ORA-06512: at "SYS.UTL_MAIL", line 602
ORA-06512: at line 19

In such a case, the email administrator would need to adjust a couple of security settings.

Now what do we do?  We need the email to be sent on a scheduled basis.  We will use the DBA 2.0 approach to schedule the execution of our anonymous PL/SQL.  Fire up Enterprise Manager Database Control and connect as a user with permissions to create scheduled jobs.  Then we just walk through the steps and fill in the blanks:

---

And for the people who still want to do things the DBA 1.0 way, Enterprise Manager Database Control will show you the SQL statement that may be used in SQL*Plus:

-----

How would you answer the original poster's question?  What changes would you make to my suggestions?  Is there a much better way to send the emails?





Expectations of Oracle Technical Books – The Path to Positive Book Reviews

5 07 2010

July 5, 2010

It might appear that my last two, three, four or maybe even five Oracle Database book reviews were a bit harsh.  In some of the blog articles leading up to the last two book reviews I pulled out a sentence or two from the book and offered readers of this blog an opportunity to comment on the accuracy of the quote from the book.  Was I just nit-picking the book’s contents, or is there a reason for the analysis?

It is probably best to start by defining some of what I believe to be the most critical key targets for a technical book:

  • A technical book must be non-fiction - a technical book should describe technically accurate details of an event, action, or object.
  • A technical book is a permanent record of an event, action, or object.  With that in mind, the contents of a technical book should require a significant amount of research, testing, and retesting to verify the accuracy of the book’s contents not just in a Linux virtual machine, but also in physical hardware on Unix, Linux, and Windows.  If the book’s technical content only applies to a Linux virtual machine environment, readers will likely experience trouble implementing the suggestions in a production environment.
  • A technical book that describes a feature should correctly identify with which Oracle release version the feature works.
  • A technical book that describes additional cost options such as partitioning, AWR, Database Replay, etc. should identicate that the feature must be purchased and/or is not available for the Standard Edition.
  • A technical book that describes Oracle initialization parameters must avoid mentioning the hidden initialization parameters unless a warning is provided that such parameters should not be modified without first consulting Oracle support.  Mentioning that warning a hundred pages after the description of the first hidden parameter is of limited help.
  • A technical book that states feature X is 25% (or any percent) more efficient than feature Y should provide a reproducible test case so that the reader is able to test the efficiency improvement in their environment as well as adapt the test case to more accurately model a problem in the reader’s database configuration.
  • A technical book should provide forward and backward references to help readers locate detailed or advanced adaptations/implementations located elsewhere in the book.  Also helpful are references to external resources found in other books, Metalink/MOS, or specific websites.
  • A technical book that contains “recipes” or “lab projects” should provide all of the critical configuration elements within a couple of pages of the “recipes” or “lab projects” – forcing the reader to page through hundreds of pages to piece together the “lab project” is unexceptable.
  • A technical book that advertises “undocumented secrets” or “definitive” or “complete” on the front cover had better contain actual undocumented (not in the Oracle documentation and not in Metalink/MOS and not found easily through an Internet web search) facts and/or contain the complete description of the topic.
  • A technical book should look like a technical book: 10 point or smaller font size, small margins, no cartoon drawings, well organized topics and sub-topics, few if any spelling or grammar errors, helpful table of contents and index, meaningful chapter names, few unnecessary distractions, etc.
  • A technical book should draw the reader in – trick the reader into reading the book cover to cover even when the weather is better suited to outdoor activities.
  • A technical book’s author(s) should have a reputation for producing accurate information about the subject – a Google search is helpful.
  • A technical book should cover the current release of the product (Oracle Database), as well as older but still supported releases.
  • And last, but not least, a technical book should convey useful content that helps people just starting to learn Oracle Database, as well as people who have used the product for years and are just looking for new ideas or a way to help maintain their knowledge.   A technical book that introduces nothing new is of limited value.

I certainly do not know everything about Oracle Database – in actuality I probably know very little about everything that there is to know about Oracle Database.  To say that it bothers me when I find errors in technical books, especially about Oracle Database, is an understatement – especially when it is something as commonly understood as the description of the db file scattered read wait event.  Spelling errors, especially when an Oracle feature or  initialization parameter are involved, simply should not be present – odd synonyms for well known Oracle keywords also should be avoided to limit confusion.  A clear, professional writing style is appreciated.

If I buy a technical book and do not receive the above, I feel a bit cheated.  The above will hopefully explain the seemingly harsh tone found in a couple of my book reviews.

What are your thoughts?  What are your expectations when reading a technical book?

References – links to my last couple of book reviews:

—————-

Late edit/addition – links to five more of my Oracle Database technical book reviews (most recent listed first):








Follow

Get every new post delivered to your Inbox.

Join 139 other followers