-
Tracing session
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
forgot to mention my environment. Here it is ...
OS - SUN SOL 5.10
Oracle - 10.2.0.3
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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.
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|