Oracle Text URL indexing performance

Question

I have an oracle table that has two columns - id and url.

The URL is simply http://somemachinename/mypage.php?id=

I then create an oracle text index using the URL datastore on the url column.

If I then do:

BEGIN
ctx_ddl.sync_index(idx_name        => 'MY_INDEX',
                   memory          => '50M',
                   parallel_degree => 4);
END;
/

Then if I look at the apache logs on somemachinename I can see oracle requesting all of the URLs in turn.

The problem is that oracle requests about 60 urls in turn and then stops for 15s, before requesting another 60-ish urls.

The amount of data in the html page is small - less than 3k, so 60 pages shouldnt be filling any buffers - and even if it were it shouldnt take 15s to clear them.

Running wireshark shows that the delay is definitely in the requests arriving (rather than a problem with the webserver), so I dont know what Oracle is doing in those 15s.

The indexing is a big job (the table has about 2m rows), and it currently takes a week, wheras without the del;ays I think it would be more like hours....

Any thoughts?

Answer 1

"so I dont know what Oracle is doing in those 15s." You could look at the Wait / Event on v$session. Could be chewing CPU parsing the HTML, or it maybe that it needs to write the data somewhere (temp tablespace) first.

Don't suppose there is anything on the network side treating this as some sort of attack ?