PGA dump

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:


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 SETORAPID or SETOSPID subcommands.
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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: