6 Replies Latest reply: Mar 11, 2013 9:47 AM by Mark Beelen RSS

    retrieve XMLTYPE based on registered schema very slow

    Mark Beelen
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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