February 26, 2007 Leave a comment
In a number of previous blogs, I serialised some problems and thoughts on those problems that I had had creating an Oracle Text Context index where some five million rows took six days to index.
So, the basic process is that each index creation process (between 1 and n depending on the parallelism – in my case, 4 but just for index creation purposes) indexes a bunch of documents until it reaches the memory limit and then flushes those to disk.
The logs of the parallel index creation show that the in-memory indexing slows down over time but the flushing to disk remains fairly constant.
I originally posted a question on the OTN Oracle Text forum to try to get some ideas and one suggested possibility was that this might be hitting a memory leak bug which could be proven by taking PGA dumps and validating that some sub-heap is growing uncontrollably.
My immediate thoughts were that I hadn’t done this before (and I still haven’t done this against a renewed attempt at doing the index creation) and that I wasn’t sure of the mechanism to do this but that it might well involve the ORADEBUG command.
A search on a well-known search engine suggested a couple of helpful links:
So, it turns out that the command I need is probably:
ORADEBUG DUMP HEAPDUMP 1
which should dump the PGA.
Note that this command can be run via SQL*Plus but not via Toad, for example.
So, typically, I would need to logon to SQL*Plus as an appropriately privileged user, identify the process(es) into which I wish to “inject” the debug process using the
Then, I would use
ORADEBUG DUMP HEAPDUMP 1 to do a PGA dump and at a later interval do it again, probably several times, and then inspect the trace file.
Handily, the command
ORADEBUG TRACEFILE_NAME tells you the name of the trace file produced.
Here is an example PGA dump. Interesting stuff. And once I’ve tried to figure out how to read this, I might be in a position to do this against a renewed indexing attempt.