Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query occasionally returning empty results in 8i

Re: Query occasionally returning empty results in 8i

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 27 Jun 2006 09:25:36 +0200
Message-ID: <l6n1a2ttog1rkk58n31bgg104ammvtpbut@4ax.com>


On 26 Jun 2006 23:38:44 -0700, "BoukeB" <bbruinsma_at_ortec.nl> wrote:

>The problem occured and we have a trace. First a query without WHERE
>clause (executed 3 times), second a query with where clause (executed
>2244 time). There absolutely is data in the table and no inserts and
>deletes are done on the table. Can this be an ORACLE bug in 8.1.7.4?
>This is what I really want to know or rule out.
>
>SELECT ID, NAME, DESCRIPTION, RQL_TYPE, OWNER_NAME, DEPARTMENT,
> DEFAULT_EMPLOYEE_IND, DEFAULT_CALCULATION_IND
>FROM
> ROS_REQUIREMENT_LISTS order by NAME
>
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 3 0.00 0.00 0 0 0
> 0
>Execute 3 0.02 0.06 1 0 9
> 0
>Fetch 3 0.01 0.32 0 0 0
> 0
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 9 0.03 0.38 1 0 9
> 0
>
>Misses in library cache during parse: 0
>Optimizer mode: CHOOSE
>Parsing user id: 789
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 0 SORT ORDER BY
> 0 TABLE ACCESS FULL ROS_REQUIREMENT_LISTS
>
>
>SELECT ID, NAME, DESCRIPTION, RQL_TYPE, OWNER_NAME, DEPARTMENT,
> DEFAULT_EMPLOYEE_IND, DEFAULT_CALCULATION_IND
>FROM
> ROS_REQUIREMENT_LISTS WHERE ID = :1
>
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 2 0.00 0.00 0 0 0
> 0
>Execute 2244 0.43 0.38 0 0 0
> 0
>Fetch 2244 0.18 0.39 13 6732 0
> 2244
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 4490 0.61 0.77 13 6732 0
> 2244
>
>Misses in library cache during parse: 1
>Misses in library cache during execute: 1
>Parsing user id: 789

Insufficient info and too easy shouting 'Bug'

A full table scan shows you have 3 records in that table. Yet you don't show what the value of the bind variable in the second query is.
So how can anyone know you aren't executed a query for 2244 times that simply should return no rows as you are submitting an ID that isn't there?

--
Sybrand Bakker, Senior Oracle DBA
Received on Tue Jun 27 2006 - 02:25:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US