3 Replies Latest reply: Dec 17, 2012 6:55 AM by Mkirtley-Oracle RSS

    I/O bottleneck when using ODBC gateway

      Oracle XE 11gR2
      Oracle ODBC Gateway
      Linux CentOS 5.8
      Oracle APEX 4.1.1
      Glassfish 3.0.1

      APEX reports run fine when using materialized views but when trying to run same APEX reports across dblink and not using materialized views, processing is much slower.
      This is not entirely surprising, of course, but I did notice a lot of disk I/O when using the dblink (where the disk I/O was measured on the server housing both the Oracle XE database and gateway).
      Does anyone have an explanation for this behavior? Why would going across a dblink cause so much disk I/O? The reports are just doing selects across the dblink -- no sorts / order bys.

      sar -b with materialized views

      tps rtps wtps bread/s bwrtn/s
      20.00 0.00 20.00 0.00 192.00
      8.00 0.00 8.00 0.00 144.00
      3.00 0.00 3.00 0.00 48.00
      3.00 0.00 3.00 0.00 48.00
      5.00 0.00 5.00 0.00 72.00
      3.00 0.00 3.00 0.00 48.00

      sar -b with dblink

      tps rtps wtps bread/s bwrtn/s
      1012.50 0.00 1012.50 0.00 19260.00
      3103.00 0.00 3103.00 0.00 57672.00
      5061.50 0.00 5061.50 0.00 84620.00
      7356.22 0.00 7356.22 0.00 135570.15
      6029.00 0.00 6029.00 0.00 109912.00
      5740.50 0.00 5740.50 0.00 98916.00

      Also, when running the SQL for the reports from SQL*Plus, I get no disk I/O issues.
      But when running from APEX SQL Workshop, I get the same high disk I/O issue you see above.

      Thanks for any insights.
        • 1. Re: I/O bottleneck when using ODBC gateway
          Depending on exactly what query gets sent to the remote database, it is entirely possible that you end up requesting all the data from the remote database via ODBC and then do the joins, apply the filters, etc. locally (it depends on exactly how the query is structured on the source, whether the query involves Oracle-specific functions, etc.). If that happens (or if that happens to some extent), then depending on the data volume, it is entirely plausible that you need to use a decent amount of TEMP space in order to do the join or apply the predicates. That could generate a reasonable amount of disk I/O.

          If you can tell us exactly what query you're executing, what gets sent to the remote non-Oracle database, and what tablespace(s) the I/O occurs on, that would help us figure out whether what I'm speculating about above is plausible in your particular case.

          • 2. Re: I/O bottleneck when using ODBC gateway
            The sql sent to the gateway is straight forward.

            select col1, col2, col3
            from mytable@dbkink;

            where dblink is to the remote Sybase database via the ODBC Gateway.

            (1) How would you measure or monitor I/O a particular tablespace?

            (2) Also, the same SQL is run via SQL*Plus and I do not see the I/O issue?

            (3) Only when the SQL is run via APEX SQL Workshop, or the APEX Report Application itself, does the I/O problem arise?

            (4) Could APEX be generating more disk I/O in order to render the report page? (The images are stored on the glassfish server ).

            • 3. Re: I/O bottleneck when using ODBC gateway
              This problem has now come to the Gateway forum.
              To try and see if it is a gateway problem or something in APEX could you set up DG4ODBC 255 level tracing by adding the following to the DG4ODBC init<sid>.ora file -


              and run a select from a new SQLPLUS session then end the session then run the select from a new APEX session and end that session.
              Post the part of each trace that shows the select passed by the gateway to the non-Oracle gateway.
              It should be in the section that begins 'hgopars' and we can see if there is any difference in what the gateway is doing.