Re: Query occasionally returning empty results in 8i

On 26 Jun 2006 23:38:44 -0700, "BoukeB" <bbruinsma@xxxxxxxx> 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
.

Relevant Pages

  • Re: Runtime error 2001
    ... the Where clause so that I could see as it was ... I can get the query to run with these criterion ... however I cannot seem to write the query via SQL and get it to run. ... you can execute that exact same SQL successfully then it's something else. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • RE: selecting a value based on a date
    ... I still get the message "you tried to execute a query that does not include ... SO i tried replacing the having clause by the WHERE clause in the Waitdays ... chart pertaining to that dive was received. ...
    (microsoft.public.access.queries)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)