Blocking session and procedures running.

Hi,

  Every time i bounce the database this blocking sessions occurs and it never ends.   When checking the contents of the SQL ID Text, is about one procedure running.

  I want to know how to stop this from running automatically ?   and how to stop it from starting every time i start the database ? , also how to stop it now.

  This is a 2TB database, is a big one.

CREATE OR REPLACE PROCEDURE SMARTSOLVE.pilgrim_sync_all_indexes IS

CURSOR c1 IS
SELECT pnd_index_name,
       COUNT(*) num_rows
  FROM ctxsys.ctx_user_pending
GROUP BY pnd_index_name;

dStartTime    DATE;
nErrorCount   NUMBER;

BEGIN
   FOR r1 IN c1 LOOP

      dStartTime := SYSDATE;
      pilgrim_synchronize_index (r1.pnd_index_name);

   END LOOP;
END;


CREATE OR REPLACE PROCEDURE SMARTSOLVE.pilgrim_synchronize_index
              (pIndexName IN VARCHAR2) IS
--Sync the index. To be run as CTXSYS.
BEGIN

   CTX_DDL.SYNC_INDEX(pIndexName);

END;
SQL-TEXT-01.jpg
SQL-TEXT-02.jpg
blocking_session01.jpg
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?
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.

joe_echavarriaDatabase AdministratorAuthor Commented:
How to know if it is a job ? , or where to find any logs of the job ?
0
slightwv (䄆 Netminder) Commented:
From your previous question:
What makes you think blocking is bad?

>>   I want to know how to stop this from running automatically ?

Why do you want to do this?  If the rows aren't sync'd they will not be searchable.  This can cause missing results when searched.

The rows must be sync'd eventually.

>>How to know if it is a job ?

Look in DBA_JOBS or DBA_SCHEDULER_JOBS to see if it is running as a job.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
I am agree with you.    I know it has to run.

What happens is that it should end at some point.   It never ends.

Also i want to know how is being kicking off to run.

It appears in the dba_jobs also in the dba_jobs_running, but not in the dba_scheduler_jobs.

Is there any configuration to start a job when the database first start ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>Is there any configuration to start a job when the database first start ?

I'm going from memory here but I believe Oracle "remembers" jobs that were running when the database shut down and restarts them when it comes back up.

>>What happens is that it should end at some point.   It never ends.

There may be a LOT of rows to synchronize.  How many rows are in ctxsys.ctx_user_pending?

I once had a Text index that took an entire week to rebuild.

Constantly bouncing the database will likely cause everything to start over.

>>Also i want to know how is being kicking off to run.

Likely a scheduled job.  You will just need to go looking.

Weren't you waiting on an answer from the software vendor?  They should be able to give you specific answers to your questions since they wrote the code.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
select * from ctxsys.ctx_user_pending    , returns no rows at all.

If the database "remembers" the jobs that were running previous the shutdown, maybe this could be the case.

I have even killed  the job, and then it appears running again.

The software vendor has no answer yet.
0
slightwv (䄆 Netminder) Commented:
You still haven't commented on why you think this is a bad thing?

>>I have even killed  the job, and then it appears running again.

Jobs are funny that way.  Oracle knows it hasn't "completed" so it can and will start it again.

If it is a DBMS_JOB, you "can" set the job to broken:
http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_job.htm#ARPLS66569

But I wouldn't recommend it.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
Well, is not a bad thing, it is a strange behavior on the first glance.
0
slightwv (䄆 Netminder) Commented:
>>it is a strange behavior on the first glance.

This is why you need to know what is considered "normal behaviour" for your database.

The blocking you are seeing may be bad for some but normal for others.

Now if the sync job in fact, never stops running, then I might dig deeper into that.

I also hope the vendor has jobs that optimize the Text index.  If not, performance will degrade over time with the sync frequency.

From what you've posted in the last couple of questions, I really don't think they understand Oracle Text.

I mentioned in your other question, I moved to a sync on commit over a batch sync at a given time period.  I then do a full optimize every night for a specific number of hours.  The nice thing about the optimize is it remembers where it left off if it cannot complete in the time allotted.

However, since this is a vendor product, you are limited in what you can probably do and not void any support contracts.
0

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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.