Tracing session

[vnktummala]

After hours of investigation, I am able to fix the issue. Thanks to my application team who has given me some scope to R&D by giving false information.

Finally today, I have taken the script execution control from application team, started tracing my session, then executed the function which is in question. I found the below information from tkprof log file.

********************************************************************************

DELETE FROM TBL_SRQ_LOG
WHERE
TBL_SRQ_LOG.SRQ_ID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 242 1.09 1.48 3394 4774 8897 1730
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 243 1.09 1.48 3394 4774 8897 1730

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 2)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 166207 5.38 238.54
log buffer space 726 0.98 631.18
log file switch completion 146 0.98 76.41
log file switch (checkpoint incomplete) 34693 0.98 45.56
buffer busy waits 1 0.00 0.00
free buffer waits 216 0.01 4.15
********************************************************************************

As per application team this table should not have more than 4K rows but the statistics are showing some thing different.

When I quickly check the row count of the object, it is a surprise for me. It has almost 60M rows.

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
--------------- --------------- ---------- ---------- -----------------
TBL_SRQ_LOG SARM_DATA 59940640 674052 20091016 02:59:58

Application owners doesn't have any clue how this table has 60M rows. So, I simply rename the table / index / constraints and created the new table / index / constraints. Then the function has been executed in milli seconds.

Thanks,

[malay_biswal]

Good to know u solved it.
Bottom line never trust others at least for row count.