Re: Query occasionally returning empty results in 8i

From: BoukeB <bbruinsma_at_ortec.nl>
Date: 26 Jun 2006 23:38:44 -0700
Message-ID: <1151390324.502746.323230@u72g2000cwu.googlegroups.com>

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 Received on Tue Jun 27 2006 - 01:38:44 CDT