The CURSOR_SHARING Parameter is a Silver Bullet – What is Wrong with this Quote?

3 07 2011

July 3, 2011 (Modified July 5, 2011)

Define Yah-but: Almost like yeah but, but with one missing letter.

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, specifically recipe 5-19, I found a couple of interesting comments about the CURSOR_SHARING parameter, specifically the FORCE and SIMILAR parameter values.  The quotes (as usual, trying to minimize the amount of material that is quoted without losing the context of the quotes):

“The CURSOR_SHARING parameter is one of the few Oracle Silver bullets that’ll improve database performance immediately by eliminating latch contention. Use it with confidence when dealing with library cache latch contention.”

“Thus, the SIMILAR setting seems a safer way to go about forcing the database to use bind variables instead of literals. Although there are some concerns about the safety of setting of the CURSOR_SHARING parameter to FORCE, we haven’t seen any real issues with using this setting.”

I would have expected to find the phrase silver bullet in another book, such as the book “Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition“, specifically page 48:

“Note: There are no silver bullets, none. If there were any, they would be the default behavior and you would never hear about them.”

Or possibly in a presentation titled “Playing Russian Roulette with Silver Bullets”, specifically Page 14 (slide 27)

The phrase might also appear in the book “Oracle Tuning the Definitive Reference, Second Edition” specifically page 105 (the phrase silver bullet actually appears on page 102, this quote is related to the second half of the quote at the start of this article):

“Note that in Oracle 11g, cursor_sharing=similar has been debugged, and it is now possible to use cursor sharing with bind variable peeking.”

Strangely, the phrase silver bullet might also appear on a page found in the toadworld.com domain (Edit: July 5, 2011: I suspect that the same advice might also be found in that author’s recent book Per the book author’s comment attached below, this assumption is incorrect):

“The CURSOR_SHARING is one of the few Oracle parameters that can have a ‘silver bullet’ effect – instantly and dramatically increasing throughput on a parse-constrained database.”

I still have fond memories of the problems caused by the October 2006 patch for Oracle Database 10.2.0.2 that were related to setting the CURSOR_SHARING parameter to FORCE.  That problem seems to be related to this article: Metalink (MOS) Doc ID 7272297.8, Bug 7272297 – “Memory corruption / OERI[17114] / OERI[17125] with literal replacement”.

Of course there are plenty of other resources that suggest utilizing the CURSOR_SHARING parameter to tune performance, including this article that suggests changing that parameter value to SIMILAR. 

Is changing the CURSOR_SHARING parameter from EXACT to either FORCE or SIMILAR a good idea, much less a silver bullet?  Was it only a problem with Oracle Database release versions prior to 11.1? 

A couple of additonal resources to help you decide:

  • Carol Dacko reports that the SIMILAR parameter value for the CURSOR_SHARING parameter is deprecated (obsolete) as of Oracle Database 11.1 (and it will be removed in 12.1) per Metalink (MOS) Doc ID: 1169017.1
  • The Oracle Optimizer Group answers the question: “Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10g”
  • The Oracle Optimizer Group explains adaptive cursor sharing behavior with cursor_sharing = similar and force
  • Kyle Hailey and Randolf Geist discuss the CURSOR_SHARING parameter in a blog article titled “Cursor_sharing: a picture is worth a 1000 words




Find Objects with the Greatest Wait Time – What is Wrong with this Quote?

28 06 2011

June 28, 2011

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a couple of interesting SQL statements in Recipe 5-17 that just did not look right.  One of those SQL statements, slightly reformatted, appears below:

SELECT
  A.CURRENT_OBJ#,
  O.OBJECT_NAME,
  O.OBJECT_TYPE,
  A.EVENT,
  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
FROM
  V$ACTIVE_SESSION_HISTORY A,
  DBA_OBJECTS D
WHERE
  A.SAMPLE_TIME BETWEEN SYSDATE – 30/2880 AND SYSDATE
  AND A.CURRENT_OBJ# = D.OBJECT_ID
GROUP BY
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT
ORDER BY
  TOTAL_WAIT_TIME; 

The above SQL statement is supposed to retrieve a list of object names that experienced the most wait time in the last 15 minutes.  What is wrong with the query?  Consider that a slight variation of the above query is found in the following locations:

So, what is wrong with the query?  OK, the table aliases are inconsistent, but that is too easy.  Let’s refer to the Oracle Database 11.2 documentation for the V$ACTIVE_SESSION_HISTORY view.

“V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn’t belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes.

This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.”

Still not seeing the problem?  If we believe the documentation, assume that we are only interested in a 10 second time interval, and one of the sessions started waiting on a wait event at the start of the time interval, and was still waiting in the same wait event at the end of the 10 second time interval.  SUM(A.WAIT_TIME + A.TIME_WAITED) for the session (assuming that rounding does not happen due to view update frequency) would show 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = 55 seconds of total wait time for the result of the SUM function.  Not too bad, right?  Now, assume that the session had already waited 240 seconds and continued to wait for an additional 10 seconds – the SUM(A.WAIT_TIME + A.TIME_WAITED) for the session would show that the session waited 240 + 241 + 242 + 243 + 244 + 245 + 246 + 247 + 248 + 249 + 250 = 2695 seconds (44.92 minutes) in that 10 second time interval.

A fun little test that will require two sessions.  In Session 1:

CREATE TABLE T1 (
  C1 NUMBER,
  PRIMARY KEY(C1));

INSERT INTO
  T1
VALUES (
  1); 

Now, in Session 1, determine the SID:

COLUMN SID FORMAT 999

SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;

 SID
----
 189 

In Session 2, determine the SID:

COLUMN SID FORMAT 999

SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;

 SID
----
   4 

Back in Session 1, insert a row into the table:

INSERT INTO
  T1
VALUES (
  1); 

In Session 2, attempt to insert the same row into the table (a primary key violation if the first session commits):

INSERT INTO
  T1
VALUES (
  1); 

Session 2 is now hung.  Back in Session 1, wait 20 minutes and check AWR ASH using a slightly fixed version of the query (mainly the table aliases are corrected, and adjusted to examine only the last 10 seconds of entries in V$ACTIVE_SESSION_HISTORY for the two sessions of interest):

SELECT
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT,
  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
FROM
  V$ACTIVE_SESSION_HISTORY A,
  DBA_OBJECTS D
WHERE
  A.SAMPLE_TIME BETWEEN SYSDATE – 10/(24*60*60) AND SYSDATE
  AND A.CURRENT_OBJ# = D.OBJECT_ID
  AND A.SESSION_ID IN (4,189)
GROUP BY
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT
ORDER BY
  TOTAL_WAIT_TIME; 

no rows selected

That can’t be the expected result.  Let’s try running the following query in Microsoft Excel:

SELECT
  SESSION_ID,
  EVENT,
  CURRENT_OBJ#,
  SAMPLE_TIME,
  WAIT_TIME,
  TIME_WAITED
FROM
  V$ACTIVE_SESSION_HISTORY
WHERE
  SESSION_ID IN (4,189)
ORDER BY
  SESSION_ID,
  SAMPLE_TIME; 

After scrolling down a couple of thousand lines in the spreadsheet:

Well, that throws off the results of the query found in the book a bit.  The CURRENT_OBJ# is -1 and the session has not waited any time (SUM(A.WAIT_TIME + A.TIME_WAITED) = 0 for SID 4, but would be 116,972,415 for SID 189 for the same CURRENT_OBJ# = -1). 

Maybe the query found in the book just does not work for potential primary key violations?  What about a case where one session updates a row and another session attempts to update the same row?  Issue a COMMIT in Session 1 and Session 2.  In Session 1:

COMMIT;

UPDATE
  T1
SET
  C1=C1; 

In Session 2, attempt to update the same row:

UPDATE
  T1
SET
  C1=C1; 

Session 2 is hung while waiting for Session 1 to COMMIT or ROLLBACK.  Now wait for a while.  While you are waiting, let’s take a quick look at the documentation for the V$ACTIVE_SESSION_HISTORY view from the 11.2 Oracle Documentation library:

“TIME_WAITED: If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that event (in hundredths of a second). This column is set for waits that were in progress at the time the sample was taken.

If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows.”

OK, must check the SESSION_STATE column, only the last of those sample rows in a string of rows for the same wait event, and the time is in 1/100th (0.01) of a second.  Let’s re-run the query in Excel to see the progress:

That 2,471,728,088 number in the TIME_WAITED column appears to be in conflict with the description in the Oracle Documentation library – the time appears to be reported in 1/1,000,000 (0.000001) of a second, not 1/100 of a second.

Back to the original topic of this article.  I have yet to determine why the query subtracts 30/2880 from the SYSDATE to retrieve the activity in the last 15 minutes – wouldn’t it be more clear to subtract 15 / (24 * 60) or 15 / 1440?  Considering that the SAMPLE_TIME column in V$ACTIVE_SESSION_HISTORY is a TIMESTAMP datatype, would it make more sense to use SYSTIMESTAMP rather than SYSDATE?

Finally, What is Wrong with the Quote Found in the Book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Addressing TM Enqueue Contention – What is Wrong with this Quote?

19 06 2011

June 19, 2011

Currently, the most viewed article in the past 90 days on this blog demonstrates a deadlock situation that is present in Oracle Database 11.1.0.6 and above that is not present in Oracle Database 10.2.0.1 through 10.2.0.5 (someone else might be able to check the earlier releases).  This particular deadlock was the result of a missing foreign key index, which resulted in TM enqueues and an eventual deadlock when the first of three sessions issued a COMMIT.  From this bit of information, hopefully it is clear that foreign key columns should usually be indexed (especially if the primary/unique column in in the parent table is subject to UPDATE statements).

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a test case in Recipe 5-15 that is intended to demonstrate how an index on a foreign key’s column(s) helps to resolve the most common cause of TM enqueues.  While the test case included the DDL to create a parent and child table, it did not include the DML to create test data.  The DDL for the two tables looks something like the following:

CREATE TABLE STORES (
  STORE_ID NUMBER(10) NOT NULL,
  SUPPLIER_NAME VARCHAR2(40) NOT NULL,
  CONSTRAINT STORES_PK PRIMARY KEY (STORE_ID));

CREATE TABLE PRODUCTS (
  PRODUCT_ID NUMBER(10) NOT NULL,
  PRODUCT_NAME VARCHAR2(30) NOT NULL,
  SUPPLIER_ID NUMBER(10) NOT NULL,
  STORE_ID NUMBER(10) NOT NULL,
  CONSTRAINT FK_STORES FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) ON DELETE CASCADE); 

Immediately after the above DDL statements appears the following claim is made by the alpha copy of the book:

“If you now delete any rows in the STORES table, you’ll notice waits due to locking.”

Interesting observation… I hope that the final copy of the book includes the DML to populate those tables.  In the mean time, let’s create our own test data to see if the claim made in the quote is correct:

INSERT INTO
  STORES
SELECT
  ROWNUM STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM) PRODUCT_NAME,
  TRUNC((ROWNUM-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT; 

We now have 10 rows in the parent table and 100 rows in the child table (side note: is there something strange about the names of the columns in the tables?).  Let’s connect to the database using two sessions.  In Session 1, execute the following to delete a row from the STORES table:

DELETE FROM
  STORES
WHERE
  STORE_ID=1; 

Here is the challenge, what happens if we attempt to execute the following script in Session 2 (the values to be inserted should not conflict with existing rows in the tables):

INSERT INTO
  STORES
SELECT
  ROWNUM+10 STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100; 

Which of the following will happen?

a. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the first of the two SQL statements.

b. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the second of the two SQL statements.

c. Session 2 will receive the following error message “ERROR at line 1: ORA-02291: integrity constraint (TESTUSER.FK_STORES) violated – parent key not found”.

d. Session 2 will corrupt the database due to the missed TM enqueue.

e. Session 2 will be waiting in the “SQL*Net message from client” wait event after it attempts to execute the second of the two SQL statements.

—–

Think about the above for a moment before scrolling down.  Does the Oracle release version make a difference?

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Now that you have thought about the question for a minute or two, take a look at the output I received on Oracle Database 11.2.0.2:

SQL> INSERT INTO
  2    STORES
  3  SELECT
  4    ROWNUM+10 STORE_ID,
  5    'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO
  2    PRODUCTS
  3  SELECT
  4    ROWNUM PRODUCT_ID,
  5    'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  6    TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  7    TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
  8  FROM
  9    DUAL
 10  CONNECT BY
 11    LEVEL<=100;

100 rows created. 

So, the answer to the multiple choice question is…

Wait… we are not done yet.  Back in Session 2 execute the following which will delete a previously committed row (Session 1 previously deleted the row with STORE_ID=1, but has yet to COMMIT):

DELETE FROM
  STORES
WHERE
  STORE_ID=10; 

The output I received on Oracle Database 11.2.0.2:

SQL> DELETE FROM
  2    STORES
  3  WHERE
  4    STORE_ID=10;

(session is hung - reporting wait event "enq: TM - contention") 

So, the answer to the multiple choice question is…  Wait, does the Oracle Database release version matter?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Row Lock is Synonymous with TX Lock, and is a Lock on a Single Row of a Table – Verify the Documentation Challenge

10 06 2011

June 10, 2011

I found an interesting quote in the Oracle Database documentation library:

Row Locks (TX)
A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock for the table in which the row resides. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM)
A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. You can explicitly obtain a table lock using the LOCK TABLE statement, as described in ‘Manual Data Locking‘.”

It appears that the above quote was changed a bit and included in section 5-11 of the alpha copy of the book that I am reading:

“Oracle uses two types of locks to prevent destructive behavior: exclusive and share locks… Row locks, indicated by the symbol TX, lock just a single row of table for each row that’ll be modified by a DML statement such as INSERT, UPDATE, and DELETE. This is true also for a MERGE or a SELECT … FOR UPDATE statement. The transaction that includes one of these statements grabs an exclusive row lock as well as a row share table lock.”

Something to think about: If a row lock is also called a TX lock, and it is a lock on a single row in a table, does that mean that one would expect to see 1,000 of these TX locks for a single session if that session modifies 1,000 rows in a single table?  Or, might we expect to see only a single transaction lock (TX) for the session that modified 1,000 rows in a single table – that would indicate that a row lock is not synonymous with a TX lock.  For that matter, what about the other types of TX enqueues?

Maybe it works as Tom Kyte states here:

“the TX is not a row lock, TM is the ‘row lock’ actually.”

The above is further explained in this exchange on the OTN forums between Tom Kyte and Mark Bobak.

Some additional clarification from the latest version of Tom Kyte’s book (also found in his much older “Expert One-On-One Oracle” book):

“Whereas we get only one TX lock per transaction, we can get as many TM locks as the objects we modify.”

Combining the above quote with the quote from the documentation, does that mean that a session can only lock a single row per transaction?  :-)

So, is the documentation correct?  If not, how would you re-word what appears in the documentation?  Keep in mind the sub-title of this blog:

Stop, Think, … Understand





NOLOGGING Option and log file sync Waits – What is Wrong with this Quote?

8 06 2011

June 8, 2011

I have not had published a “What is Wrong with this Quote” blog article for a couple of months, so I thought that I would publish a few of those types of articles.

The first quote is from the Alpha copy of the book “Oracle 11g Performance Tuning Recipes”, recipe 5-7 Resolving Log File Sync Waits.  As always, I try to minimize the amount of text that I post for a quote, but a sufficient amount so that the context of the quote is not completely lost.  The quote:

“As the log file sync wait event is caused by contention caused by the LGWR process, see if you can use the NOLOGGING option to get rid of these waits. Of course, in a production system, you can’t use the NOLOGGING option when the database is processing user requests, so this option is of limited use in most cases.”

“Although the database automatically calculates the value of the log_io_size parameter, you can specify a value for it, by issuing a command such as the following:

SQL> alter system set "_log_io_size"=1024000 scope=spfile;

System altered.                                 "

What, if anything, is wrong with the above quote?

Hint for the first half of the quote: AskTom, Documentation
Hint for the second half of the quote: The scale for the _log_io_size parameter is NOT bytes.  This article may also be helpful.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





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)




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.








Follow

Get every new post delivered to your Inbox.

Join 142 other followers