This content has been marked as final. Show 3 replies
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.
The sql sent to the gateway is straight forward.
select col1, col2, col3
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 ).
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.