I was having the same (or similar) issue with simply reading from a database. I noticed as well that a non-spatial table was being added to the SDO_GEOM_METADATA table. To resolve it, I removed the table name from the OCI connection string. Since I had the two tables (joining a non-spatial to the spatial for the query) in the SQL, it still worked and I no longer had the trigger going off. I should note I'm running from the C++ GDAL interface.
To connect to the schema tables, the connection can last 5-8 minutes.
Do you mean that this is the elapsed time between the moment you open a table and the moment you see the result on your screen as a map ? And does that include a view of all the 3600 polygons in that table ? Or does your view contain multiple tables ?
Obviously fetching many geometries from a database will take time, but it should not be that long. For example, fetching some 3200 fairly complex polygons is less than a second (say 800ms) on my database. This is not using QGIS - just a simple fetch. But the cost for reading from the database, passing to the client over the network and decoding the objects is the same irrespective of the client.
So there is definitely something wrong in your case.
Can you try accessing the tables using sqlplus ? Just do the following:
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> select * from <my_table>;
The first command tells sqlplus to not print out the results (that would be very long). Your DBA will need to grant you some additional privileges in order to to this.
The second command tells sqlplus to show you how long it took to execute the statement.
The third one reads the full content from that table. For example:
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> select * from us_counties;
3230 rows selected.
Elapsed: 00:00:00.75
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
1298 consistent gets
560 physical reads
0 redo size
4219623 bytes sent via SQL*Net to client
3930 bytes received via SQL*Net from client
224 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3230 rows processed
If this is also very slow, then the issue is probably around your connection to the server and you need to ask your DBA to solve this.
One thing that makes reading geometries expensive is their complexity, i.e. the number of vertices: the flattening of those vertices into messages, the transfer, the decoding of the shapes will all take time, and so will the time it takes QGIS to render them. But I would not expect timings of the order of magnitude you mention.
Best Answer
The ArcGIS JavaScript API only has built-in support for editing (and then writing the edited features) of ArcGIS Server Feature Services. (Or at least this was the case the last time I worked with it a few months ago.)
But the API does have a general purpose drawing and editing capability. If you can't get ArcGIS Server then you could potentially roll your own type of layer that knows how to talk to your back-end data store and persist any edits. The down side to this approach is that you'd need to be pretty darn good with HTML 5 and the dojo toolkit. And you'd also need a lot of patience since the documentation of the ArcGIS JavaScript API isn't geared toward people trying to extend it in this way. (I know this from experience.)
If you didn't have ArcGIS Server, you would need something on the server side to web-enable your Oracle Spatial data. This would make it accessible to the browser JavaScript. (GeoServer could be used in this way, for example.)
Have you considered using the OpenLayers client toolkit? It is a more general-purpose JavaScript library with no licensing cost. Combine that with GeoServer on the back end (to web-enable your Oracle Spatial data). You'd still have some glue code to write, but at least you wouldn't have the licensing cost of ArcGIS Server.
EDIT: If I understand your comment correctly, you have an existing Oracle Spatial instance. And you have (or will have) an existing ArcGIS Server install. And you want ArcGIS Server to view the data in Oracle without having to install ArcSDE (and its associated database objects) on your Oracle instance.
Yeah, I think you could get the data OUT of Oracle. That would go like this:
After that you could make ArcGIS Server map and feature services that view the data.
Those services would probably not be editable through ArcGIS, though. Geodatabase editing in ArcGIS seems to need those extra ArcSDE metadata tables. (But I'm not an expert on that corner of the ArcGIS software, so maybe someone else would say different.)