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.)
Oracle text related internal procedure taking a lot of time in our Prod. database [message #600145] Fri, 01 November 2013 05:42 Go to next message
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,
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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
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 Go to previous message
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!
Previous Topic: CONTAIN function syntax usage
Next Topic: USER_DATASTORE procedure and markup
Goto Forum:
  


Current Time: Thu Mar 28 05:28:03 CDT 2024