Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query occasionally returning empty results in 8i
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 DBAReceived on Tue Jun 27 2006 - 02:25:36 CDT