2

I am having trouble connecting to an Oracle Spatial data with QGIS client. I use QGIS Version 2.2 (Valmiera) to connect to a database schema in Oracle Spatial. I asked our DBA access to our database stored in Oracle Spatial. He granted me some privileges on some tables to perform Select statements. However, once I set my access I experienced difficulties with slow connection to the Oracle Spatial database. To connect to the schema tables, the connection can last 5-8 minutes. The geometric tables to which I connect have more than 3600 polygons.

Could you confirm me if this is due to a configuration problem or a bad customer optimization QGIS Oracle Spatial.

  • I have found QGIS 2.10 is much faster loading spatial oracle schema's – Mapperz Aug 24 '15 at 13:59
1

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.

  • Hi Albert,Thank you for your feed-back. For your first question the answer is yes. It's take me definitely 5-7 minutes to load my geometry table using QGIS as client after connecting to server. Also I access directely geometry and non geometries tables, no views. I'll try accessing the tables using the SQL queries you posted me and I'll be back to you. Since yesterday I got 2 days of training and I'm out of office. I'm back next monday. I'll talk this issue with our DBA and give you a feed-back. Thank Albert I'll appreciate your help. Regards! – user24401 Oct 3 '14 at 6:47
0

I have the same issue. The Problem is, that QGIS scans every table containing a geom Attribut. In presence of millions of geometries per table, that process takes up to 20 minutes in my Office. We definetively Need an updating of the ORACLE Plugin of QGIS to: - avoid These scans - fetch only needed records of explicitly specified tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.