Link to home
Start Free TrialLog in
Avatar of joe_echavarria
joe_echavarriaFlag for Dominican Republic

asked on

Oracle Blocking Session

Hello Guys,

   I am having this issue with the database.   This blocking session keeps  appearing in the database and never ends.  I have tried rebooting the server and also bouncing the database, the session appears again.   The same SQL Text running, with different sessions ID.

  But the strange thing is that is not causing performance issues or any other problem.

  I have open a ticket at the application support partner site, i am waiting for their response.

  Please any help on how to approach this problem.      Or any recommendation or tips handling blocking sessions.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>But the strange thing is that is not causing performance issues or any other problem.

OK then, why are you considering them a problem?  Just because something is suggested to be bad, doesn't make it bad.

In the big picture, you don't want sessions waiting on other sessions to complete their work but it might not be possible to avoid.  It all depends on the apps, the data and the users.

If all the users modify pretty much the same data throughout the day, blocking will occur.  No real good way around it.

If you don't think this should be happening given the data and users, then the application developers should look into their code.
>> The same SQL Text running, with different sessions ID.
Through the v$session view, you should be able to find who and what is causing these sessions.

Agree with the above comment, it may be just normal behaviour. If you never noticed this before: is this because you didn't look before or did you get notified in some way?

If this started to happen suddenly, consider that there were changes to the application(s) using the database or maybe some new employees that use applications in a different way.
how are you looking for blocking sessions?

what are the results you are seeing that suggest to you there is a problem?
Avatar of joe_echavarria

ASKER

I have notice it before.  After bouncing the database and restarting the server the SQL Text of the blocking sessions keeps the same.    I know it is normal to have short term blocking sessions, but this one seems to keep for ever.

Below the code.

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN pilgrim_sync_all_indexes; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

I will be waiting for the partner answer.
how are you looking for blocking sessions?

 what are the results you are seeing that suggest to you there is a problem?
What is pilgrim_sync_all_indexes doing?
--Sync the index. To be run as CTXSYS.
BEGIN

   CTX_DDL.SYNC_INDEX(pIndexName);


There is no problem.  I find strange to keep having that blocking sessions all the time in the database.
>>> I find strange to keep having that blocking sessions all the time in the database.

how are you looking for blocking sessions?

  what are the results you are seeing that suggest to you there is something strange?
I look to then in the   Cloud Control/Performance/Blocking Sessions.

No results, it is just that the session is always there in the blocking stage.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What does CTX_DDL.SYNC_INDEX(pIndexName);  do ?


This is a 2 TB database,  a big database.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial