Viewing oracle XML table in Toad data grid generates ORA-04036 memory error - Toad memory leak?

Toad for Oracle
1

I have an oracle table SY_History with the structure shown below that includes an XML column and contains 17,591 rows. Each row has a small amount of character data. The entire table consumes 22.4MB When opening the table in the Toad for Oracle data grid without any filters (simply displaying it) it generates oracle error ORA-0436 out of PGA memory. If the PGA memory is expanded the toad query will run, but it consumes 6GB and it will display after a long delay. If I filter the table to show only one row it comes up without any problem. I can query against the table and list it in Oracle SQL command window without a problem. The result of an Oracle SR is Oracle feels there is not an oracle issue. Appears to be a TOAD memory leak. Toad version is 12.9.0.71

How does this get corrected?

Thanks

Joe

ALTER TABLE VPAORA.SY_HISTORY
DROP PRIMARY KEY CASCADE;

DROP TABLE VPAORA.SY_HISTORY CASCADE CONSTRAINTS;

CREATE TABLE VPAORA.SY_HISTORY
(
SY_HISTORY_ID NUMBER(20) DEFAULT “VPAORA”.“SY_HISTORY_ID”.“NEXTVAL”,
SY_USER_ID NUMBER(20) CONSTRAINT SY_HISTORY_SU_NN NOT NULL,
SY_CALL_STACK VARCHAR2(4000 BYTE),
SY_ACTION VARCHAR2(20 BYTE) CONSTRAINT SY_HISTORY_AC_NN NOT NULL,
SY_COMMENT VARCHAR2(100 BYTE),
SY_PRIMARY_ID NUMBER(20) CONSTRAINT SY_HISTORY_PO_NN NOT NULL,
SY_CALL_PROGRAM VARCHAR2(100 BYTE),
SY_TABLE VARCHAR2(500 BYTE) CONSTRAINT SY_HISTORY_TB_NN NOT NULL,
SY_COLUMN1 VARCHAR2(500 BYTE),
SY_VALUE1_OLD VARCHAR2(500 BYTE),
SY_VALUE1_NEW VARCHAR2(500 BYTE),
SY_COLUMN2 VARCHAR2(500 BYTE),
SY_VALUE2_OLD VARCHAR2(500 BYTE),
SY_VALUE2_NEW VARCHAR2(500 BYTE),
SY_COLUMN3 VARCHAR2(500 BYTE),
SY_VALUE3_OLD VARCHAR2(500 BYTE),
SY_VALUE3_NEW VARCHAR2(500 BYTE),
SY_USER_ID_ADD NUMBER(20) CONSTRAINT SY_HISTORY_UA_NN NOT NULL,
SY_DATE_ADD DATE CONSTRAINT SY_HISTORY_DA_NN NOT NULL,
SY_USER_ID_MOD NUMBER(20),
SY_DATE_MOD DATE,
SY_UPDATES_XML SYS.XMLTYPE
)
XMLTYPE SY_UPDATES_XML STORE AS SECUREFILE BINARY XML (
TABLESPACE VPAORA
ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE
LOGGING
STORAGE (
INITIAL 104K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
ALLOW NONSCHEMA
DISALLOW ANYSCHEMA
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

COMMENT ON TABLE VPAORA.SY_HISTORY IS ‘History of transactions.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_HISTORY_ID IS ‘Unique identifier for this history row.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_USER_ID IS ‘SY_User_ID of individual initiating the add/change transaction that is generating this history.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_CALL_STACK IS ‘Oracle call stack that led to the transaction.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_ACTION IS ‘Transaction that caused the change.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_COMMENT IS ‘Comment regarding the change, if applicable.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_PRIMARY_ID IS ‘Primary Key of row being changed.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_CALL_PROGRAM IS ‘Last calling program as recorded through context variable, so not verifiable.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_TABLE IS ‘Table whose data is changing’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_COLUMN1 IS ‘Name of Column 1 being tracked.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE1_OLD IS ‘Old Value of 1 element before change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE1_NEW IS ‘New Value of 1 element after change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_COLUMN2 IS ‘Name of Column 2 being tracked.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE2_OLD IS ‘Old Value of 2 element before change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE2_NEW IS ‘New Value of 2 element after change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_COLUMN3 IS ‘Name of Column 3 being tracked.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE3_OLD IS ‘Old Value of 3 element before change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_VALUE3_NEW IS ‘New Value of 3 element after change’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_USER_ID_ADD IS ‘User VPA_Person_ID who originally added this row.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_DATE_ADD IS ‘Date row originally added.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_USER_ID_MOD IS ‘User VPA_Person_ID who last modified this row.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_DATE_MOD IS ‘Date row last updated.’;

COMMENT ON COLUMN VPAORA.SY_HISTORY.SY_UPDATES_XML IS ‘History of changes to table data values by table and column in XML format.’;

CREATE INDEX VPAORA.SY_HISTORY_PI ON VPAORA.SY_HISTORY
(SY_PRIMARY_ID)
LOGGING;

CREATE UNIQUE INDEX VPAORA.SY_HISTORY_PK ON VPAORA.SY_HISTORY
(SY_HISTORY_ID)
LOGGING;

CREATE INDEX VPAORA.SY_HISTORY_TB ON VPAORA.SY_HISTORY
(SY_TABLE)
LOGGING;

CREATE INDEX VPAORA.SY_HISTORY_UI ON VPAORA.SY_HISTORY
(SY_USER_ID)
LOGGING;

ALTER TABLE VPAORA.SY_HISTORY ADD (
CONSTRAINT SY_HISTORY_PK
PRIMARY KEY
(SY_HISTORY_ID)
USING INDEX VPAORA.SY_HISTORY_PK
ENABLE VALIDATE);

2

I’ve never heard of this exact symptom, but Toad doesn’t do well with BINARY XML. It’s been a long-standing problem.

You can work around it with GetClobVal, like this:

SELECT SY_UPDATES_XML.GetClobVal() as SY_UPDATES_XML,

FROM SY_HISTORY SY

You must include a table alias, or GetClobVal with throw an error. This workaround will be automatic in schema browser.
You’ll still have to code it manually in the editor though.

John

3

As suggested, I tried this in the TOAD editor and it returned the results quickly without error. I have asked my DBA to return the PGA memory setting to a reasonable number and will try again to fully test the suggestion.

If it does work, then the next question is if TOAD will fix the bug in TOAD.

Thanks, and will post my results once I am able to run the test.

Joe

4

I’d love to fix it, but the vendor of our third party library that connects to the database says it’s an OCI problem, and Oracle says that it’s not a documented feature, so we’re kind of stuck on this one. I’ve gone back and forth on this with both of them. It only happens when the OCI is used in Unicode-enabled mode, which Toad uses, which I believe is why you don’t see the problem in SQL*Plus.

If you go to SB-Tables-Data, we automatically put the GetClobVal() in the query there, so that’s a little help.

5

Thanks, you have been very helpful. I just tested your suggested getclobval() query with a reasonable PGA setting and it worked.

I don’t understand the last sentence of your last reply though. If SB-tables-Data is the schema browser and getclobval() is automatically a part of the query that fills the data grid, I don’t understand why it errors in the grid, but not in the Toad editor when I run the query there. Unless, SB-Tables-Data is something else?

Thanks

JOe

6

SB-Tables-Data automatically puts GetClobVal in the query because we know about the table’s columns before you go to that tab.

The editor runs whatever you type in.

7

Are you saying that SB-Tables-Data throws the error? If so, click the 2nd toolbar button (view/edit query) there to see what query is being run. Maybe it’s not putting in GetClobVal as it should for some reason…

8

I found the issue. It is throwing the error in SB-tables-data, and I checked the query. It is including the getclobval(). The query also throws the error in the editor. The problem is that I had the results sorted by the primary key. When I removed the sort by primary key in the data grid and in the test query in the editor they both run without issues.

So, the real problem was it choked on the sort. (17,591 rows). Does that make sense though?

Thanks

Joe

9

That surprises me that a sort on 17K rows would cause such a problem but if the sort is performed with an “ORDER BY” in the query, then it doesn’t sound like it has anything to do with Toad (Oracle does the sorts, not Toad)

You can probably reproduce it in SQL*Plus, and maybe with a query that doesn’t even return any rows, like:

create table blah

as select …

order by …;

10

Oddly, the suggested create table runs without error and reasonably quick:

Create table test_xml as select sy_history_ID, sy.sy_Updates_XML.getclobval() as sy_xml from sy_history sy order by sy_history_ID;

11

The full query in Toad included “order by rowid desc nulls last;” so I tried the last create table with “order by sy_history_ID desc” and it worked fine. So, I tried “order by sy_History_ID desc nulls last;” and then the query failed in the oracle sql command window.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

ORA-06512 at “SYS.XMLTYPE”, line 138

Joe

12

That’s good (from a Toad perspective, anyway). Open a case with Oracle and they’ll help you I’m out of ideas anyway. :slight_smile: Be sure to tell them that you can reproduce the problem in SQL*Plus. They tend to kick them back if you tell them you have problems in Toad!