Memory Leaks, part 2

While keeping up on the OTN forums, I saw one post that was working with DOMDocuments and used .freeNode. Having a system in production that doesn't use .freeNode, I wondered whether this was part of the known small memory leak that an OS session has over time. So I went back to my previous post of Memory Leaks and revisited and expanded it to see whether not using .freeNode was causing memory leaks.

Systems:
The following was tested on two different machines. Both were running the same version of Redhat Linux. The older machine was 10.2.0.4 and 4 Gigs of memory. The newer machine has 11.1.0.6 and 8 Gigs of memory. The processor is better of course too.

Test procedure:
I ran the following updated script three times on both systems. On the older system, I ran it 300,000 times. On the newer system, I ran it 600,000 times (which still ran faster than the older system). You can see by the comments what two lines of code I added in run #2 and the one line of code I added in run #3. After doing each run, I would close that window/session and start a new one. I'm using PL/SQL Developer and have it setup so each window runs as a separate database session.

DECLARE 
   l_dom_doc   dbms_xmldom.DOMDocument;
   l_node      dbms_xmldom.domnode;  -- added run 2

   PROCEDURE p_local_open_close IS
   BEGIN
      l_dom_doc := dbms_xmldom.newDOMDocument;
      l_node   := dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc, 'root'));  -- added run 2
      dbms_xmldom.freeNode(l_node);  -- added run 3
      dbms_xmldom.FreeDocument(l_dom_doc);
   END p_local_open_close;

BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',
                                    action_name => 'SLEEPING');
   dbms_lock.sleep(20);
   /* Sleeps so have time to find OS PID via
   select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid
   from gv$session s,
        gv$process p
   where s.module = 'user_domDocument_mem_leak_test'
     and s.paddr = p.addr;
   */
   dbms_application_info.set_action('RUNNING');
   FOR i IN 1..300000  -- adjust as needed for your system
   LOOP
     p_local_open_close;
   END LOOP;
   dbms_application_info.set_action('COMPLETE');
  
END;

To determine memory usage, I was using top to see what the VIRT (total virtual memory) for the OS session was. The SQL in the script shows how to get the OS session based on the Oracle session that was running. If your version of top doesn't support top -p , another way as my coworker turned up is ps -opid,vsz -p . This shows the PID and VSZ (Virtual Size) columns. I'm sure there are other ways too.

Run results:
Runs #2 and #3 were identical in memory usage to run #1. For the 10.2.0.4 system, VIRT would start out at/around 438m and increase to 470m after the run. For the 11.1.0.6 system, memory would start at 1181m and stay there.

Conclusion:
It would appear that using dbms_xmldom.freeNode is completely optional as Oracle handles the memory from this correctly. This is determined by the fact that memory usage from the first run, which had no dbms_xmldom.domnode in it, was exactly the same as the second and third runs.

Additional observation:
I was expecting that memory usage on the 10.2.0.4 system to remain a constant like it did on the 11.1.0.6 system. As my previous post said, the fix was applied to a 10.2.0.3 system and has since been upgraded to 10.2.0.4. As 10.2.0.4 was released sometime in early 2008 (or so it appears) it is highly possible the patch applied to 10.2.0.3 was not part of the 10.2.0.4 release. Looks like I will have to verify this on the client's system and see about getting this fixed if it still occurs on their system as well.

Methods to parse XML per Oracle version

One of the items I've noted while hanging out in the XML DB forum or the general XML forum is that many people are still using the old extract and extractValue methods to parse XML via SQL statements.

Starting with 11.2, Oracle has deprecated extract and extractValue. As you can see from the Oracle documentation, Oracle suggest you use XMLQuery in place of extract and either XMLTable or XMLCast/XMLQeury in place of extractValue.

So what method should you use in SQL to parse XML? It depends upon your version of Oracle of course.

Oracle version: 8i - 9.0.x.x
There was no option that I can recall or could find. All the parsing of XML that I've done in 8i was via the xmldom package.

Oracle version: 9.2.x.x - 10.1.x.x
This is were Oracle introduced extract, extractValue and TABLE(XMLSequence(extract())) for dealing with repeating nodes.

Oracle version: 10.2.x.x
Oracle introduced XMLTable as a replacement for the previous methods since it could handle all three methods for extracting data from XML. At that point, Oracle stopped enhancing extract/extractValue in terms of performance and focused on XMLTable. In 10.2.0.1 and .2, XMLTable was implemented via Java and in .3 it was moved into the kernel so performance from .3 onwards should be better than the older 9.2 / 10.1 methods. If not, feel free to open a ticket with Oracle support. Apparently Oracle also introduced XMLQuery as well but I've never heard of many using that in 10.2

Oracle version: 11.1.x.x - 11.2.x.x
Oracle still has XMLTable and XMLQuery as I pointed out above, but also added in XMLCast as a way to cast the output of XMLQuery into a desired datatype.

So when coding, please try to pick the parsing approach that works with the version of Oracle being used. It's good for your job skills and Oracle provides better support for current functionality than deprecated functionality.

Text Literal Structure

In a previous post on XMLType/XMLTransform and parameters, I'd discovered the q'{}' structure from the OTN forum post, but I didn't know much more about it. I wasn't sure what to go searching for so I left it as an interesting tidbit to figure out later.

Just about two months after that post (on Nov 4, 2010 to be exact), I encountered this syntax structure in a PL/SQL Challenge quiz. To see the actual quiz, you will need to register (free) on the site. If you are into learning more about the database from the SQL and PL/SQL side of things in a simple quiz that takes a few minutes of time each day, I strongly suggest signing up and participating. You can find his blog discussion of this particular quiz at The quote character (q) and the 4 November quiz.

Using his wording as a starting point to search the Oracle documentation, I soon came across Text Literals. Somewhere along the way I saw that this was a 10g (10.1 I believe) addition. I went from 8.1 to 10.2, so it explains why I missed it.

It's pretty simple to use. Ignoring the national character version, you start with
q''
Within that, you put your delimiters. As the documentation says,
If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.
so we can setup structures such as

• q'[]'
• q'<>'
• q'##'

and within our quote delimiter, we simply put the desired text. Some examples:

BEGIN
  dbms_output.put_line(q'{Here be the text with a ' in it}');
  dbms_output.put_line(q'*Here be the text with a ' in it*');
  dbms_output.put_line(q'#Random words put here#');
  dbms_output.put_line(q'^Here be the text with a ' in it^');
END;

which produces

Here be the text with a ' in it
Here be the text with a ' in it
Random words put here
Here be the text with a ' in it

Makes it a lot easier to put a single quote into a string than using double single quotes.

* Caveat *
Apparently the Text Literal process has issues with strings that contain 'n', where n' is at the end of the string.  By that I mean the following will all run successfully

select q'[help'n ']'
from dual;
select q'['na']'
from dual;
select q'['a']'
from dual;

but the following all return an ORA-01756: quoted string not properly terminated

select q'[help'n']'
from dual;
select q'['an']'
from dual;
select q'['n']'
from dual;

I've seen this error on both 10.2.0.4 and 11.1.0.6. I can't find anything in MOS yet on it.

XMLTable with a Parameter

I found this question on the OTN forums, Problem with XMLTABLE with parameters, interesting because
a) I knew there was a way to accomplish that
b) I couldn't quickly turn it up via Google till I got the right keywords.

After seeing the solution that I ended up borrowing, I first checked the Oracle documentation on XMLTable for 10.2 to verify this was valid. As you can see by the documentation, the XML_passing_clause does allow for multiple expressions.

Curious as to see how this worked for different data types and on 10g and 11g, I created the following setup.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

SQL>
SQL> CREATE TABLE XMLT
 2  (xmlcol  XMLType)
 3   XMLTYPE column xmlcol store AS BINARY XML;

Table created

SQL>
SQL> INSERT INTO XMLT
 2  VALUES
 3  ('<Root>
 4    <Object>
 5      <ObjectID>ID_1</ObjectID>
 6      <ObjectName>Name 1</ObjectName>
 7      <ObjectValue>1</ObjectValue>
 8      <ObjectDate>1980-01-01</ObjectDate>
 9    </Object>
10    <Object>
11      <ObjectID>ID_2</ObjectID>
12      <ObjectName>Name 2</ObjectName>
13      <ObjectValue>2</ObjectValue>
14    </Object>
15  </Root>');

1 row inserted

SQL> commit;

Commit complete

sys_xmlgen works on both 10g and 11g for these scenarios. With this, everything is treated as a simple string so you just need to ensure the string you pass in matches up to the string in the XML.

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectID=$objID]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('ID_1') as "objID"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectValue=$objVal]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('2') as "objVal"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_2   Name 2

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          sys_xmlgen('1980-01-01') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

Another way to do it in 11g is to use CAST to convert a string to the appropriate data type. I go overboard with the Date related queries. I will explain afterwards.

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectID=$objID]'
 4                  PASSING XMLT.xmlcol,
 5                          cast('ID_1' AS VARCHAR2(4)) as "objID"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectValue=$objVal]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST('2' AS NUMBER) as "objVal"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_2   Name 2

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST('01-JAN-1980' AS DATE) as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          CAST(DATE '1980-01-01' AS DATE) as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          DATE '1980-01-01' as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          '1980-01-01' as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

SQL> SELECT xt.ObjID, xt.ObjName
 2    FROM XMLT,
 3         XMLTABLE('/Root/Object[ObjectDate=$objDt]'
 4                  PASSING XMLT.xmlcol,
 5                          TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"
 6                  COLUMNS
 7                  ObjID   VARCHAR2(6)  PATH 'ObjectID',
 8                  ObjName VARCHAR2(10) PATH 'ObjectName') xt;

OBJID  OBJNAME
------ ----------
ID_1   Name 1

With my setup, Oracle knows nothing about the XML in terms of data types so 2 and 1980-01-01 are simply strings to it. So, even though I was CASTing a string to a DATE, Oracle is converting the value back to a string during the XPath evaluation. (Note: This is not confirmed but highly suspected).

So why is using CAST good? It comes in useful when you have a schema registered within Oracle and the XMLType is based off of the schema. This provides Oracle with data type information and is especially true for Object Relational Storage where it is used to create data types for columns. Oracle knows the data type of the node so you want to pass in the same data type using an explicit conversion. This is the basic reasoning why you don't want to compare a number to a string. Oracle will implicitly convert one to the other and can cause issues it if encounters a string with "a1" in it that fails to convert to a number.. That is the purpose of CAST, so you can tell Oracle that a given parameter is a number or a date to avoid the implicit conversion.

Gotchas

• Running the above XMLTable with CAST SQL Statements will result in an ORA000932: inconsistent datatypes: expected - got
  

Creating XML via the DB

Just some ways to produce XML from an Oracle DB using only SQL and PL/SQL. I'm sure this list is incomplete so I will expand it as I remember others. This is just what was coming to mind before writing this entry

• SQL/XML (XMLElement, XMLForest, XMLAgg)
• DBMS_XMLDOM
• XQUERY
• DBMS_XMLGEN
• DBMS_XMLQuery
• TRANSFORM/XMLTransform
• CreateXML/XMLType

Basically this serves as a reminder of what to cover in the future.