I am on Oracle 22.214.171.124 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|
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.
This is the internal call which drives the SYNC call for a text index. Effectively all the indexing of new and updated data in your text-indexed table is contained within this call.
If you're using parallel SYNC you will see this call contained with a SELECT query - that select is executed as a parallel query on a table function, which is the way we divide up the work between parallel slaves.