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.
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>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.
Gerwin Jansen, EE MVETopic Advisor Commented:
>> 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.
sdstuberCommented:
how are you looking for blocking sessions?

what are the results you are seeing that suggest to you there is a problem?
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

joe_echavarriaDatabase AdministratorAuthor Commented:
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.
sdstuberCommented:
how are you looking for blocking sessions?

 what are the results you are seeing that suggest to you there is a problem?
Gerwin Jansen, EE MVETopic Advisor Commented:
What is pilgrim_sync_all_indexes doing?
joe_echavarriaDatabase AdministratorAuthor Commented:
--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.
sdstuberCommented:
>>> 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?
joe_echavarriaDatabase AdministratorAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>CTX_DDL.SYNC_INDEX(pIndexName);

That involves Oracle Text indexes.  The SYNC_INDEX does a lot of things under the scenes that touches all the base tables that makes up a Text index.  I've never checked to see if it 'blocks' for me but I wouldn't worry about it.

Well you can 'worry' but there probably isn't anything you can do about it.

How often does that job run?  I used to SYNC my text indexes every few minutes but since abandoned that in favor of syncing on commit and let my nightly optimize take care of things.

They should be doing an optimize on a regular basis anyway or search performance will degrade quite a bit over time!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joe_echavarriaDatabase AdministratorAuthor Commented:
What does CTX_DDL.SYNC_INDEX(pIndexName);  do ?


This is a 2 TB database,  a big database.
slightwv (䄆 Netminder) Commented:
The online docs are your friend:
http://docs.oracle.com/cd/E11882_01/text.112/e16593/cddlpkg.htm#CCREF0652

It synchronizes an Oracle Text index.  The index synced is whatever name is passed into pIndexName.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.