OraFAQ Forum: Text & interMedia » Oracle text related internal procedure taking a lot of time in our Prod. database

Home » Server Options » Text & interMedia » Oracle text related internal procedure taking a lot of time in our Prod. database (Oracle 11.2.0.2 on Linux.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fri, 01 November 2013 05:42

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

I am on Oracle 11.2.0.2 on Linux. I have Oracle Text implemented in all my databases for fuzzy search. I am seeing the following Oracle TExt specific internal procedure to be among the Top SQL in my AWR in production. This is during business time.

Elapsed Time (s) Executions  Elapsed Time per Exec (s)  %Total %CPU %IO SQL Id SQL Module SQL Text 
23,476.22 205,095 0.11 19.50 16.21 7.88 ddr8uck5s5kp3    begin ctxsys.drvdml.com_sync_i...

Note that the sql id ddr8uck5s5kp3 has this sql:

begin ctxsys.drvdml.com_sync_index(:idxname, :idxmem, :partname); end;

Also note that I have the procedure to optimize the indexes (ctx_ddl.optimize_index in FULL mode) set up every night to run at 3 am for all our Oracle Text indexes. Is there anything else needed. I don't know why the procedure I showed above in the AWR report takes so much time and why it is among our Top sql.

I will be very thankful for guidance in this regard.

Thanks,

Report message to a moderator

Re: Oracle text related internal procedure taking a lot of time in our Prod. database [message #600170 is a reply to message #600145]

Fri, 01 November 2013 14:11

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Apparently it is busy synchronizing your Oracle Text index. How often is your index synchronized? Do you SYNC(ON COMMIT) or do you synchronize at intervals or use TRANSACTIONAL or what? Is it slowing down your DML or queries? If not, then I wouldn't worry about it. If there is a problem, then you might benefit from changing the synchronization interval if that is practical. The more DML you have and the less frequently that you synchronize and the more complex your index is, the longer the synchronization will take.

[Updated on: Fri, 01 November 2013 14:12]

Report message to a moderator

Re: Oracle text related internal procedure taking a lot of time in our Prod. database [message #600171 is a reply to message #600170]

Fri, 01 November 2013 14:16

orausern
Messages: 826
Registered: December 2005

Senior Member

Thank you Barbara!! Yes I am synchronizing on commit. Now I have not heard that queries or DML are slower but I am certainly worried to see this being the first or second in the top 10 list of the AWR!! I am sure it is causing lot of impact and i don't know what i can do about it! we have sync on commit so that immediately we can see the changes being done. and we have optimnization procedure being run once at every night. Please suggest if we can improve something.

[Updated on: Fri, 01 November 2013 14:17]

Report message to a moderator

Re: Oracle text related internal procedure taking a lot of time in our Prod. database [message #600172 is a reply to message #600171]

Fri, 01 November 2013 14:24

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If you need your data to be immediately searchable, then usually you need the sync(on commit) that you are using. I don't know if you have so much DML triggering synchronization that you have some sort of overlapping never-ending synchronization gridlock or some such thing. Theoretically, the longer that you wait to synchronize, the longer it takes to do so. However, if you synchronize less frequently, then it reduces the fragmentation that accumulates between optimizing. If a five-minute synchronization interval is acceptable, then you might try that. You might find the following interesting reading:

http://martinmeyer.blogspot.com/2008/07/using-various-tools-to-solve-problem.html

Report message to a moderator

Re: Oracle text related internal procedure taking a lot of time in our Prod. database [message #600173 is a reply to message #600172]

Fri, 01 November 2013 14:27

orausern
Messages: 826
Registered: December 2005

Senior Member

Thanks a lot Barbara! You are the BEST when it comes to ANY issue on Oracle text! thanks a lot!

Report message to a moderator

Previous Topic:	CONTAIN function syntax usage
Next Topic:	USER_DATASTORE procedure and markup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 27 00:56:34 CST 2024