Optimizer Costing 1 – What is Wrong with this Quote?

December 5, 2010 (Modified December 6, 2010)

(Forward to the Next Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve

With that said, what is wrong with the following quote from page 498 of the book (you might need to view the page directly in the book to fully determine the context of the quote)?

“CPU Based Optimizer Costing

The recently added CPU costing feature, controlled by the _optimizer_cost_model = hidden parameter, enhances the CBO’s capabilities by allowing it to estimate the number of machine cycles necessary for an operation…  Generally, CPU costs are not considered significant unless the entire Oracle instance is using excessive CPU resources.

I/O Costing

… The I/O cost is proportional to the number of physical data blocks read by the operation.  However, the CBO has no prior information on the data buffer contents and cannot distinguish between a logical read (in-buffer) and a physical read.  Due to this shortcoming, the CBO cannot know if the data blocks are already in the RAM data buffers.  The best environment for using CPU costing is for all_rows execution plans, where cost is more noteworthy than with first_rows optimization.”

What, if anything, is wrong with the above quote from 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.

A Google book search indicates that a slightly modified version of the statement appeared in one other book:

• The previous edition of the book

Other pages found during a Google search of the phrase:

• rampant-books.com/t_oracle_cpu_costing.htm
• dba-oracle.com/art_builder_cpu_io.htm
• dba-oracle.com/art_otn_cbo_p6.htm
• Using Explain Plan from the Oracle documentation library

I am reminded of a quote from page 7 of the book, quite literally the first sentence in the book:

“Oracle tuning is a complex endeavor, and it does not help that Oracle databases are changing constantly.”

Edit: December 6, 2010: Added a couple of more quoted sentences under the heading “CPU Based Optimizer Costing” to reduce the risk of misunderstanding the context of the quote.

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 4 – What is Wrong with this Quote?

Decmber 5, 2010

(Back to the Previous Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve

With that said, what is wrong with the following quote from page 530 of the book (please excuse the length of this quote, I tried to make it as short as possible without losing the context of the material, and without destroying the sentence structure)?

“Remember, the db_file_multiblock_read_count parameter is used to tell Oracle how many blocks to retrieve in the single I/O operation and the setting is platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

The ‘automatically tuned’ db_file_multiblock_read_count in 10gr2 and beyond uses external disk workload statistics that are gathered via the dbms_stats.gather_system_stats package to determine the optimal setting.

A sub-optimal setting for db_file_multiblock_read_count can running SQL performance because it can cause the optimizer to favor full-scan access. This would cause some beginners to adjust for this by turning the wrong knob, lowering the setting for optimizer_index_cost_adj instead of using dbms_stats.gather_system_stats.

10gr2 and beyond, the db_file_multiblock_read_count is not used to estimate the average number of blocks read and a separate metric for the estimated number of actual block reads. Instead, the optimizer computes two new values, one for optimizer costing and another for the number of I/O requests.”

What, if anything, is wrong with the above quote from 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.

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 3 – What is Wrong with this Quote?

December 4, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve

With that said, what is wrong with the following quote from page 719 of the book (please ignore the error in the SQL statement found on that page for the moment)?

“Four factors synchronize to help the CBO choose whether or use an index or a full-table scan:

7.  The selectivity of a column value
8.  The db_block_size
9.  The avg_row_len
10. The cardinality

An index scan is usually faster if a data column has high selectivity and a low clustering_factor as shown in Figure 15.8.”

What, if anything, is wrong with the above quote from 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.

A Google book search indicates that a slightly modified version of the statement appeared in a couple of other books:

• “Oracle High-Performance SQL Tuning” (page 130)
• “Oracle SQL Tuning & CBO Internals” (page 312)
• “Oracle Silver Bullets: Real-World Oracle Performance Secrets” (page 103)

Other pages found during a Google search of the phrase:

• http://www.articles.freemegazone.com/optimize-oracle-sql-query.php
• articles.techrepublic.com.com/5100-10878_11-5090163.html
• ravivedala.blogspot.com/2008/11/how-oracle-decides-whether-to-use-index.html
• http://www.dba-oracle.com/art_index_clustering_factor.htm
• http://www.dba-oracle.com/art_otn_cbo_p5.htm
• http://www.remote-dba.net/oracle_10g_tuning/t_oracle_clustering_factor.htm

—————–

For some reason, the following quote seems to come to mind:

“Fool me once, shame on me.  Fool me twice, shame on you.” (reference)

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 2 – What is Wrong with this Quote?

December 3, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve.

With that said, what is wrong with the following quote from page 749 of the book?

“Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.”

“… The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.”

What, if anything, is wrong with the above quotes from 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.

A Google book search indicates that the first statement appeared in a couple of other books:

• “Oracle High-Performance SQL Tuning”
• “Oracle 9i High-Performance Tuning with Statspack”
• The previous edition of this book

Other pages found during a Google search of the first phrase:

• http://www.remote-dba.net/t_tuning_sql_optimizers.htm
• http://www.dba-oracle.com/t_fix_unnecessary_full_table_scans.htm
• deepthinking99.wordpress.com/2010/03/20/fix-unnecessary-full-table-scans/
• http://www.dba-oracle.com/art_dbazine_expert_tuning.htm

DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT – What is Wrong with this Quote?

December 2, 2010

(Forward to the Next Post in the Series)

It has been a couple of months since the last blog article that asked “What is Wrong with this Quote”, so I thought that I would try to add a couple of more blog articles to this series.  I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, but my review stopped when it extended to 24 pages (12 point Times New Roman font, 1 inch margins).   Why 24 pages?  It was probably just coincidence.

Page 531 of the book states the following:

“When multiple blocksizes are implemented, the db_block_size should be set based on the size of the tablespace where the large object full scans will be occurring.  The db_file_multiblock_read_count parameter is only applicable for tables/indexes that are full scanned.”

“With the implementation of multiple blocksizes, Oracle MetaLink notes that the db_file_multiblock_read_count should always be set to a value that sums to the largest supported blocksize of 32k.”

Examples are provided in the book that show that the value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter multiplied by the value for the DB_BLOCK_SIZE parameter should always equal 32KB.  Thus, with a database block size of 16KB, the DB_FILE_MULTIBLOCK_READ_COUNT parameter value should be set to 2.

—

What, if anything, is wrong with the above quotes from 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.

A Google search found similar information here:

• “Oracle Silver Bullets: Real-World Performance Secrets”
• Page 405 of the previous edition of this book
• http://www.rampant-books.com/t_db_block_size_multiple.htm
• http://www.dba-oracle.com/oracle_tips_multiple_blocksizes.htm (Setting your DB_BLOCK_SIZE with multiple block sizes heading)

An Invalid, or Do You Just Not Want to Work

December 1, 2010

Another recent blog article forced me to Stop, Think, … and just about understand (in case you are wondering about the blog title, the definition of invalid).

Consider the following table definition:

CREATE TABLE T3(
  V1 VARCHAR2(10),
  D2 DATE,
  N3 NUMBER);

INSERT INTO T3 VALUES(
  CHR(65),
  TRUNC(SYSDATE)-65,
  65);

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

SELECT
  *
FROM
  T3;

V1         D2                N3
---------- --------- ----------
A          27-SEP-10         65 

—

Which of the following SQL statements are valid SQL statements for the above table (E is not an option)?

A:

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1; 

B:

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A'); 

C:

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2; 

D:

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2; 

E:

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2; 

F:

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010'; 

G:

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010'; 

H:

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2; 

I:

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3; 

J:

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2'; 

K:

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

L:

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A'; 

M:

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010'; 

N:

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010'; 

——————-

Stop and think about it for a moment, which of the above are valid SQL statements?

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

We could use the AUTOTRACE feature in SQL*Plus to tell which are valid, and which are not:

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A');

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A';

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010'; 

The results might look like this:

A:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("V1")=1) 

B:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=TO_NUMBER('A');

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("V1")=TO_NUMBER('A'))

C:

SQL> SELECT
  2    *
  3  FROM
  4    T3 WHERE TO_NUMBER(V1)=1
  5    AND ROWNUM=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1538339754

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  COUNT              |      |       |       |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=2)
   3 - filter(TO_NUMBER("V1")=1) 

D:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1
  7    AND 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter(TO_NUMBER("V1")=1)  

F:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '27-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))=TO_DATE(' 2010-09-27
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 

G:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '32-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))='32-SEP-2010') 

H:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    D2 = '32-SEP-2010'
  7    AND 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter("D2"='32-SEP-2010') 

I:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

J:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND TO_CHAR(1)='2';
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

K:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND 1=2;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

L:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = 'A';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('A')) 

M:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '27-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('27-SEP-2010')) 

N:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '32-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('32-SEP-2010')) 

So, which SQL statements are valid, and which are not?  Did you guess correctly?  Are you surprised, or still not sure?

How about if we do this just to confirm:

SET AUTOTRACE OFF
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_SQL_OK';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A');

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A';

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; 

The output looks like this:

A:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1;
  TO_NUMBER(V1)=1
  *
ERROR at line 6:
ORA-01722: invalid number 

B:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=TO_NUMBER('A');
  TO_NUMBER(V1)=TO_NUMBER('A')
  *
ERROR at line 6:
ORA-01722: invalid number 

C:

SQL> SELECT
  2    *
  3  FROM
  4    T3 WHERE TO_NUMBER(V1)=1
  5    AND ROWNUM=2;
  T3 WHERE TO_NUMBER(V1)=1
           *
ERROR at line 4:
ORA-01722: invalid number 

D:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1
  7    AND 1=2;

no rows selected 

F:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '27-SEP-2010';

V1         D2                N3
---------- --------- ----------
A          27-SEP-10         65 

G:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '32-SEP-2010';
  TO_DATE(D2) = '32-SEP-2010'
                *
ERROR at line 6:
ORA-01847: day of month must be between 1 and last day of month 

H:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    D2 = '32-SEP-2010'
  7    AND 1=2;

no rows selected 

I:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

J:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND TO_CHAR(1)='2';
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

K:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND 1=2;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 

L:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = 'A';
  N3 = 'A'
       *
ERROR at line 6:
ORA-01722: invalid number 

M:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '27-SEP-2010';
  N3 = '27-SEP-2010'
       *
ERROR at line 6:
ORA-01722: invalid number 

N:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '32-SEP-2010';
  N3 = '32-SEP-2010'
       *
ERROR at line 6:
ORA-01722: invalid number 

So, which SQL statements are invalid?  Last chance.

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

—-

Good idea, let’s take a look inside the 10046 trace file to see if it provides any critical clues:

WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208311587
WAIT #6: nam='SQL*Net message from client' ela= 409 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313312
CLOSE #6:c=0,e=0,dep=0,type=1,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313394
WAIT #0: nam='SQL*Net message from client' ela= 1137 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314551
=====================
PARSING IN CURSOR #2 len=44 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=2056319931 ad='1e367f68' sqlid='94uqsttx91wxv'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314830
FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #2: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208315213
WAIT #2: nam='SQL*Net break/reset to client' ela= 124 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208315356
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208315926
WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317175
=====================
PARSING IN CURSOR #4 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=4036914934 ad='1e3647e4' sqlid='b34gs4zs9wvrq'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A')
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317422
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208317617
WAIT #4: nam='SQL*Net break/reset to client' ela= 125 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208317761
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208318310
WAIT #0: nam='SQL*Net message from client' ela= 1084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319413
=====================
PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=3419871603 ad='1e3641f4' sqlid='2g02uyv5xf6bm'
SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319705
FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=3 cnt=0 pid=2 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208319939
WAIT #8: nam='SQL*Net break/reset to client' ela= 112 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208320070
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208320807
WAIT #0: nam='SQL*Net message from client' ela= 1240 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322066
=====================
PARSING IN CURSOR #3 len=54 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1521821591 ad='1e363c04' sqlid='3ty5saddba9wr'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322296
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 366 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322774
CLOSE #3:c=0,e=0,dep=0,type=1,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322830
WAIT #0: nam='SQL*Net message from client' ela= 1249 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208324098
=====================
PARSING IN CURSOR #2 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1963953631 ad='1e3634ec' sqlid='du7hngjuhz3fz'
SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010'
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326129
FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message from client' ela= 182 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326423
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=7 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326523
WAIT #2: nam='SQL*Net message from client' ela= 411 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327021
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327083
WAIT #0: nam='SQL*Net message from client' ela= 28084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355185
=====================
PARSING IN CURSOR #4 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1226802804 ad='1e362dd4' sqlid='1hqqd0x4jz1mn'
SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010'
END OF STMT
PARSE #4:c=15625,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355455
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208355669
WAIT #4: nam='SQL*Net break/reset to client' ela= 134 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208355822
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208356681
WAIT #0: nam='SQL*Net message from client' ela= 1259 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208357960
=====================
PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=2831431159 ad='1e3627e4' sqlid='3u7tx3knc8dgr'
SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358205
FETCH #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net message from client' ela= 439 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358756
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358815
WAIT #0: nam='SQL*Net message from client' ela= 1489 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208360322
WAIT #3: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208363231
WAIT #3: nam='SQL*Net break/reset to client' ela= 133 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208363398
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208363421
WAIT #3: nam='SQL*Net message from client' ela= 701 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364163
CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364233
WAIT #0: nam='SQL*Net message from client' ela= 1322 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208365574
WAIT #6: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208366129
WAIT #6: nam='SQL*Net break/reset to client' ela= 126 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208366285
WAIT #6: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208366308
WAIT #6: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367019
CLOSE #6:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367086
WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208368340
WAIT #2: nam='SQL*Net break/reset to client' ela= 4 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208368838
WAIT #2: nam='SQL*Net break/reset to client' ela= 122 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208368989
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369011
WAIT #2: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369721
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369786
WAIT #0: nam='SQL*Net message from client' ela= 1010 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208370816
=====================
PARSING IN CURSOR #4 len=37 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1569184524 ad='1e361d0c' sqlid='54544w5fsgqsc'
SELECT
  *
FROM
  T3
WHERE
  N3 = 'A'
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208371121
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208371337
WAIT #4: nam='SQL*Net break/reset to client' ela= 108 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208371463
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208372094
WAIT #0: nam='SQL*Net message from client' ela= 1072 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373186
=====================
PARSING IN CURSOR #8 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=2630877256 ad='1e36173c' sqlid='5xks2pufd0028'
SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010'
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #8: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373423
FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208373612
WAIT #8: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208373732
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208374372
WAIT #0: nam='SQL*Net message from client' ela= 1083 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375474
=====================
PARSING IN CURSOR #3 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=1567527007 ad='1e36116c' sqlid='c5w7qdxfqx42z'
SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010'
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375702
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #3: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208375886
WAIT #3: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208376006
CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208376714
WAIT #0: nam='SQL*Net message from client' ela= 719 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208377453
PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919
EXEC #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919 

It is a little painful that not even the PARSE call for some of the SQL statements completed, therefore the SQL statement is not written to the 10046 trace file.

Maybe I should have clarified what I was asking for?  When we say that a SQL statement is invalid, do we mean:

• It generates an error message when it is executed.
• It generates an error message when it is parsed.
• It generates no error message, yet is logically invalid due to impossible values: 1=2 ; September 32, 2010 ; TO_NUMBER(‘A’)
• Something else?