Smells like oracle bug?

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Smells like oracle bug?

Smells like oracle bug?

2006-07-14 - By Niall Litchfield

Reply: 1 2 3 4 5 6 7 8 9 10 >>

I'll agree that you may get no result - though I'm not sure the halting
problem is relevant in most cases here, just reasonable timescales to wait
for bad plans. I'll agree with the first one only as so far as to say that
if you get a result it will be the same as the unhinted plan (anything else
is a bug). The third is just wrong

SQL> set autotrace on explain
SQL> create index idx1 on dept(deptno);
create index idx1 on dept(deptno)
*
ERROR at line 1:
ORA-01408 (See ORA-01408.ora-code.com): such column list already indexed

SQL> create index idx1 on dept(dname);

Index created.

SQL> select /*+ index dept(dname) */ empno,ename
2 from emp;

EMPNO ENAME
-- ---- -- -- ---- --
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
-- ---- -- -- ---- --
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

Execution Plan
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
Plan hash value: 3956160932

-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------

Note
-- --
- dynamic sampling used for this statement

SQL>
Hints are not designed to allow users to hint access paths that don't
satisfy the query correctly. They are designed to say either "set this
strategic goal for optimisation (RULE,FIRST_ROWS_N ....) or else "I know
that the best access path for this query (or part of it) is the following,
please don't bother second guessing me. Can you imagine how unreliable
Oracle based systems would be in general if the hints that developers have
added to various code around the planet for the last decade weren't checked
to make sure they were valid. Come to think of it there are more than enough
hints in various versions of the data dictionary for that possibility to be
worrying.
On 7/13/06, jaromir nemec <jaromir@(protected)> wrote:
>
> Hi,
>
>
> > You cannot (or at least shouldn't be able to) provide a hint to an
> access path that
>
> > gives wrong results.
>
>
>
> Conscious or unconscious abuse of hints in a SQL statement may lead only
> to three kind of results:
> - you get some result
> - you get no result (see halting problem of Alan Tuning:)
> - you get an error
> Other behavior (i.e. receiving two different results) is in my opinion a
> bug.
>
>
>
> Regards
>
> Jaromir
>
> -- -- Original Message -- --
> *From:* Niall Litchfield <niall.litchfield@(protected)>
> *To:* jkstill@(protected)
> *Cc:* gints.plivna@(protected) ; oracle-l <oracle-l@(protected)>
> *Sent:* Thursday, July 13, 2006 10:31 PM
> *Subject:* Re: Smells like oracle bug?
>
>

--
Niall Litchfield
Oracle DBA
http://www.orawin.info

I'll agree that you may get no result - though I'm not sure the halting problem
is relevant in most cases here, just reasonable timescales to wait for bad
plans. I'll agree with the first one only as so far as to say that if you get a
result it will be the same as the unhinted plan (anything else is a bug). The
third is just wrong
 SQL> set autotrace
on explain SQL> create index idx1 on dept(deptno);
 create index idx1 on dept(deptno) 
             
             * ERROR at line 1: 
ORA-01408 (See ORA-01408.ora-code.com): such column list already indexed 
SQL> create index idx1 on dept(dname); Index created. 
 SQL> select /*+ index dept(dname) */ empno,ename 
  2  from emp; 
      EMPNO ENAME 
-- ---- -- -- ---- --
       7369 SMITH 
    &nbsp
; 7499 ALLEN       7521 WARD 
    &nbsp
; 7566 JONES       7654 MARTIN
 
    &nbsp
; 7698 BLAKE       7782 CLARK 
    &nbsp
; 7788 SCOTT       7839 KING 
    &nbsp
; 7844 TURNER       7876 ADAMS 
      EMPNO ENAME -- ---- -- -- ---- --
       7900 JAMES 
      7902 FORD   
    7934 MILLER 
14 rows selected. 
Execution Plan -- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ----- 
Plan hash value: 3956160932 
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ | Id  | Operation     
    | Name | Rows  | Bytes | Cost (%CPU)| Time  
   |
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- -- 
|   0 | SELECT
STATEMENT  |      |    14 | 
  280 |     3   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS FULL| EMP  |    14 |
   280 |     3   (0)| 00:00:01 |
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ------
 Note 
-- --    - dynamic sampling used for this
statement 
SQL>   
       Hints are not designed to allow
users to hint access paths that don't satisfy the query correctly. They are
designed to say either "set this strategic goal for optimisation (RULE
,FIRST_ROWS_N ....) or else "I know that the best access path for this
query (or part of it) is the following, please don't bother second guessing me.
Can you imagine how unreliable Oracle based systems would be in general if the
hints that developers have added to various code around the planet for the last
decade weren't checked to make sure they were valid. Come to think of it there
are more than enough hints in various versions of the data dictionary for that
possibility to be worrying. 
     
             
             
             
             
             On 7/13/06, jaromir nemec <<a href=
"mailto:jaromir@(protected)">
jaromir@(protected)</a>> wrote:<div>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204)
; margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div>

<div bgcolor="#ffffff">
<div>Hi,</div>
<div> </div>
<div></div><div>
> You cannot (or at least
shouldn't be able
to) provide a hint to an access path that 
> gives wrong results.

 </div><div>
Conscious or unconscious
abuse of hints in a
SQL statement may lead only to three kind of results: - you get some
result - you get no result (see halting problem of Alan Tuning:) - you
get
an error Other behavior (i.e. receiving two different results) is in my
opinion a bug.
 
Regards</div><div
>
Jaromir<
/div><div></div></div><div>
<blockquote style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px;
padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div style="font-family: arial; font-style: normal; font-variant: normal;
font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust:
none; font-stretch: normal;">-- -- Original Message -- -- </div>
<div style="background: rgb(228, 228, 228) none repeat scroll 0% 50%; -moz
-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz
-background-inline-policy: -moz-initial; font-family: arial; font-style: normal;
font-variant: normal; font-weight: normal; font-size: 10pt; line-height: normal
; font-size-adjust: none; font-stretch: normal;">
From:
<a title="niall.litchfield@(protected) (mailto:niall.litchfield@(protected))"
href="mailto:niall.litchfield@(protected)" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">Niall Litchfield</a> </div>
<div style="font-family: arial; font-style: normal; font-variant: normal;
font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust:
none; font-stretch: normal;">To: <a title="jkstill@(protected) (mailto
:jkstill@(protected))" href="mailto:jkstill@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">
jkstill@(protected)</a> </div>
<div style="font-family: arial; font-style: normal; font-variant: normal;
font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust:
none; font-stretch: normal;">Cc: <a title="gints.plivna@(protected)
(mailto:gints.plivna@(protected))" href="mailto:gints.plivna@(protected)" target="
_blank" onclick="return top.js.OpenExtLink(window,event,this)">
gints.plivna@(protected)</a> ; <a title="oracle-l@(protected) (mailto:oracle-l
@(protected))" href="mailto:oracle-l@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">oracle-l</a>
</div>
<div style="font-family: arial; font-style: normal; font-variant: normal;
font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust:
none; font-stretch: normal;">Sent: Thursday, July 13, 2006 10:31
PM</div>
<div style="font-family: arial; font-style: normal; font-variant: normal;
font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust:
none; font-stretch: normal;">Subject: Re: Smells like oracle
bug?</div>
<div> </div></blockquote></div><div></div>

</div></blockquote></div> -- Niall Litchfield Oracle DBA <a href="http://www.orawin.info">http://www.orawin.info</a>