This discussion is archived
6 Replies Latest reply: Mar 11, 2013 7:47 AM by Mark Beelen RSS

retrieve XMLTYPE based on registered schema very slow

Mark Beelen Newbie
Currently Being Moderated
Hi,

When i retrieve a record with a XMLType column based on a registered XML schema this is very slow in SQL Developer (version 3.2.20.09), both in a worksheet and the data tab of a table. The content of the XMLType is also not showing, only showing "(XMLTYPE)" in the column field.

Doing the same on a XMLType column not based on a registered XML schema it works as you suspect, fast and showing the content of the XML.

When I do the select in SQL Plus both are fast.

Any idea's what is causing this?

Regards,
Mark
  • 1. Re: retrieve XMLTYPE based on registered schema very slow
    Gary Graham Expert
    Currently Being Moderated
    Hi Mark,

    Have you tried something like Tools > Monitor Sessions > Trace Session in order to collect SQL trace data for analysis and find out specifically what is slow? That would be most helpful, especially as I have no working test case available and you do.

    I do not see any XMLType performance bugs logged against SQL Developer but, looking at XDK bugs, it appears over time there have been more bug titles containing SLOW, PERF, and HANG logged against XDK for Java than XDK for C.

    So, just guessing, maybe...
    1. SQL*Plus uses XDK for C while SQL Developer uses XDK for Java
    2. XML schema validation is expensive, generally. Perhaps SQL Developer does more with validation than SQL*Plus.
    3. There could be some JVM memory issues SQL Developer encounters that do not apply to SQL*Plus.

    Please post back here if you are able to pursue the Trace Session idea.

    Thanks,
    Gary
    SQL Developer Team
  • 2. Re: retrieve XMLTYPE based on registered schema very slow
    Mark Beelen Newbie
    Currently Being Moderated
    Hi Gary,

    I'm able to grep the following 3 statements from the monitor session window during the retrieving of the XMLType with a registered schema.

    SELECT VALUE(S).GETCLOBVAL()
    FROM XDB.XDB$SCHEMA S
    WHERE S.XMLDATA.SCHEMA_URL = :B1
    AND S.XMLDATA.SCHEMA_OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')

    begin :1 := dbms_csx_int.getCompiledSchema(:2 ); end;

    begin :1 := dbms_csx_int.getVocabulary(:2 , :3 ); end;



    The probleem does not occur with every XMLType with a registered schema. A small test case was performing OK and also showing the content of the XML instead of "(XMLTYPE)" in de column field. I gues it has to do with the complexity of the XSD and SQL Developer not able to show the content of the XML and thus showing "(XMLTYPE)" instead.

    Regards,
    Mark
  • 3. Re: retrieve XMLTYPE based on registered schema very slow
    Gary Graham Expert
    Currently Being Moderated
    Hi Mark,

    It looks like SQL Developer only calls those statements indirectly, probably via one of the ALL_XML_SCHEMAS views. So, in terms of performance, this is more a matter of...
    1. Can the DBA tune this?
    2. Is performance poor enough to justify logging a bug against XDB?

    Regarding the display of XMLTYPE column contents, the only thing that might make a difference is use of the OCI/Thick driver. Have you checked off the box for Tools > Preferences > Database > Advanced > Use OCI/Thick driver? And, of course, you must have an Oracle client installed. It may also depend on the specific version of your client oci driver.

    Hope this helps,
    Gary
  • 4. Re: retrieve XMLTYPE based on registered schema very slow
    Mark Beelen Newbie
    Currently Being Moderated
    Hi Gary,

    The OCI/Thick driver is unchecked, when enabled I'm getting the following error which I'm not yet able to solve:

    java.lang.Error: Incompatible version of libocijdbc[Jdbc:112030, Jdbc-OCI:112020
         at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3557)
         at java.security.AccessController.doPrivileged(Native Method)
         at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3550)
         at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:266)
         at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:552)
         at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:162)
         at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:526)
         at oracle.jdeveloper.db.adapter.AbstractConnectionCreator.getConnection(AbstractConnectionCreator.java:153)
         at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:195)
         at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:67)
         at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:321)
         at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:254)
         at oracle.jdevimpl.db.adapter.CADatabaseFactory.createConnectionImpl(CADatabaseFactory.java:60)
         at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:534)
         at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:520)
         at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:510)
         at oracle.javatools.db.AbstractDatabase.reconnect(AbstractDatabase.java:583)
         at oracle.javatools.db.AbstractDatabase.getConnection(AbstractDatabase.java:210)
         at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:180)
         at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1064)
         at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1045)
         at oracle.dbtools.raptor.navigator.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:115)
         at oracle.dbtools.raptor.navigator.AbstractConnectionNode.getConnection(AbstractConnectionNode.java:30)
         at oracle.dbtools.raptor.navigator.ConnectionFilter.getFactory(ConnectionFilter.java:94)
         at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:119)
         at oracle.dbtools.raptor.navigator.ConnectionFilter$1.doWork(ConnectionFilter.java:104)
         at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
         at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
         at java.util.concurrent.FutureTask.run(FutureTask.java:138)
         at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:515)
         at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
         at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
         at java.util.concurrent.FutureTask.run(FutureTask.java:138)
         at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
         at java.lang.Thread.run(Thread.java:662)

    Thanks for your time.

    Regards,
    Mark                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 5. Re: retrieve XMLTYPE based on registered schema very slow
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    It looks like you have a conflict between 11.2.0.3 and 11.2.0.2

    What version is your database and what version if your Oracle client?
  • 6. Re: retrieve XMLTYPE based on registered schema very slow
    Mark Beelen Newbie
    Currently Being Moderated
    I was still using a Oracle 10g Client, after upgrade to a 11g Client I'm able to use OCI/thick driver.

    Using the OCI thick driver solved both the problems, the column is now showing the actual content of the XML instead of (XMLTYPE) and all the records are retrieved fast.

    Thanks for the help!

    Regards,
    Mark

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points