Tracing session

[vnktummala]

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,

[vnktummala]

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

OS - SUN SOL 5.10
Oracle - 10.2.0.3

Thanks,

[malay_biswal]

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.

[vnktummala]

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.

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,

[malay_biswal]

did you see anything in v$session_longops?

[vnktummala]

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,

[malay_biswal]

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

[vnktummala]

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,

[PAVB]

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.

[vnktummala]

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,