Oracle LogMiner PGA consumption

[Chris Cranford]

Andrey -

I was doing some research tonight about using a single mining session without ending a mining session.  I noticed one of your comments about ending the mining session to avoid PGA leaks.  How did you monitor the PGA memory consumption?  From what I've gathered from looking at the LogMiner documentation is that the PGA consumption is directly related to the number of logs, so removing logs that are no longer needed should keep PGA consumption in-check.  I want to proof this out because I think this could have a very positive impact on overall performance but wanted to explicitly understand your reasoning for the call to stop the mining session & restart when log switches happen.

Thanks
Chris

[Ignatenko, Andrey]

Hi, Chris.

When we started testing a year and a half ago, we hit an Oracle memory exception in about 20 minutes of activity.

We started monitoring PGA memory using OEM memory graph and observed a gradual increase on each building of v$logminer_contents.

As a mitigation we are setting a switch every 20 minutes (at least): alter system set archive_lag_target=1200 scope=both; and ending session on a switch.

I'm afraid PGA is consumed not just by REDOs, but did not analyse by what objects yet. Please share your findings.

If I were you, I would rather invest in replacing ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

with a table level supplemental log. This should unload REDO a lot and make switches less frequent.

I started with this approach at the beginning, but it was a chicken and egg problem, we should do it prior to the snapshotting.

I tested with "end session" and without it last month and did not notice a significant difference. Maybe I was missing something.

Thank you 

Andrey

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/aa52c2a8-9925-7916-38ce-e8e5fcc2f382%40gmail.com.

--

Andrey Ignatenko
Staff Engineer
tel: +15102675105 /// mobile: /// email: andrey.i...@navis.com
Navis LLC /// 55 Harrison Street, Suite 600 Oakland CA 94607 UNITED STATES
www.navis.com




CONFIDENTIAL – Information in this email, including any attachments, is confidential, may be legally privileged and may contain proprietary information. If you are not the intended recipient, please immediately notify the sender by reply email and then delete this email message and any attachments. You should not copy, use or disclose to any other person this message or any attachments. Thank you.

[Chris Cranford]

Hi Andrey -

We've already adjusted the connector to support both configurations, full database supplemental logging or per-table (preferring the latter for obvious reasons).  The most I've seen it grow was to something around 90MB and the least has been around 6MB so I'm not seeing any issues with memory consumption; hence why I wanted to ask if perhaps there was something I was missing.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAObaRxovydEma9dB4%2B0Sx-pVBXcmg%2BsC0nUuBKiv3eozgmJrGA%40mail.gmail.com.