1 Reply Latest reply on Nov 2, 2013 10:34 AM by Roger Ford-Oracle

    Oracle text related internal procedure taking a lot of time in our Production database




      I am on Oracle 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.

      SQL ordered by Elapsed Time



      • Resources reported for PL/SQL code includes the resources used by
        all SQL statements called by the code.
      • % Total DB Time is the Elapsed Time of the SQL statement divided
        into the Total Database Time multiplied by 100
      • %Total - Elapsed Time as a percentage of Total DB time
      • %CPU - CPU Time as a percentage of Elapsed Time
      • %IO - User I/O Time as a percentage of Elapsed Time
      • Captured SQL account for 59.3% of Total DB Time (s): 120,379
      • Captured PL/SQL account for 33.8% of Total DB Time (s): 120,379


      Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IOSQL IdSQL ModuleSQL Text
      23,476.22205,0950.1119.5016.217.88ddr8uck5s5kp3  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.