Tracing session

:confused:Hi,

One of my application teams running a shell script which does some DML operations in the beginning then calls a function and does some DDL & DML inside the function.

The script is hanging when it call the function. I have traced the session using dbms_system.set_sql_trace_in_session, tkprof it but nothing is showing in the tkprof log.

This is the only session connected to DB. Log switch (50MB) is happening once in every two minutes but nothing is showing in sql trace.

Can some one shed some light?

Thanks,

forgot to mention my environment. Here it is ...

OS - SUN SOL 5.10
Oracle - 10.2.0.3

Thanks,

I assume total time for both recursive and non recursive is less.
Then problem is not in database. Ask them is it also connecting to some other server except this D/B?

BTW u traced for how long? if it's for a small time increase trace time and see what u got.

Thanks Malay.

It's not connecting to any other server except DB.

I have traced the session for more than 2 hours and find nothing in the trace file.

Some thing is happening in the DB which I am not able to figure out. Because, log switch is happening once in every 2 minutes and undo records used and undo blocks used are keep growing.

when i query dba_rollback_segs, Undo segments 12$ and 13$ are offline since long time but 11$ is missing and 10$ is growing steadily.

Quote:

---

SQL> select owner,tablespace_name,segment_name,status from
dba_rollback_segs;

OWNER TABLESPACE_NAME SEGMENT_NAME
STATUS
------ ------------------------------ ------------------------------
----------------
SYS SYSTEM SYSTEM
ONLINE
PUBLIC UNDOTBS1 _SYSSMU1$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU2$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU3$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU4$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU5$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU6$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU7$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU8$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU9$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU10$
ONLINE
PUBLIC UNDOTBS1 _SYSSMU12$
OFFLINE
PUBLIC UNDOTBS1 _SYSSMU13$
OFFLINE

13 rows selected.

---

I am suspecting some thing wrong with my undo table space. does it make sense if i switch the system to new undo table space?

Thanks,

did you see anything in v$session_longops?

No, i was querying v$session_longops for the sessions running more than 5 seconds but it didn't show any thing.

The tables involved in the script are very small; not even 10k rows in each table but undo records used crossed more than 15M at one time. Now, i killed the session and recovery is going on. Once recovery is complete then I am planning to create new undo table space and switch.

Thanks,

Seems like undo recreation should resolve this issue.
keep us posted the outcome.

Unfortunately, undo TS recreation also didn't work. Tomorrow, I need to look into each individual object in the function. I will update the forum if I find some thing interesting.

Thanks,

Couple of questions...

How does this script behaves on Test database?
Is this the first time this script runs against production?
... if not, has script changed since last succesful run?

Last but not least, have you taken a look at the function's code? ... what it does?

Check any piece of logic that may induce a never ending loop condition.

Thanks PAVB.

Same script and same function are running good in other 3 environments and it was good in the environment where is it problematic now. No changes have been made from DB side since quite a long time.

This script and function are the inbuilt code by Oracle (for ASAP application) and no one modify the code in it. More over the function has few delete blocks in it and returns some value. Not too complicated function.

I have just analyzed all the objects in the schema and I am going to drop / create the function with the code from the other environment.

Though it sounds silly, but i have very few options to fix this issue.

Thanks,

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.

Quote:

---

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

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.

Quote:

---

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,

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