Bug report #13838

Oracle Connection takes too long (exactly listing db tables)

Added by Mehmet Selim BILGIN over 4 years ago. Updated over 3 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/Oracle
Affected QGIS version:2.12.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:worksforme
Crashes QGIS or corrupts data:No Copied to github as #:21862

Description

After clicking Connect button in the Add Oracle Table(s) dialog QGIS stucks. It can list db tables about 20 minutes maybe more than it. I have noticed this problem happening after adding big tables (>1.5 million) to Oracle. I think QGIS scans all db for something, not just looking it which tables are in there.


Related issues

Related to QGIS Application - Feature request #8689: Oracle provider - speed of listing Open 2013-09-26

History

#1 Updated by Giovanni Manghi over 4 years ago

  • Priority changed from Severe/Regression to Normal
  • Category set to Data Provider/Oracle
  • Status changed from Open to Feedback

Did it worked as expected in previous qgis releases?

#2 Updated by Médéric RIBREUX over 4 years ago

Hello,

the first time you make a connection to an Oracle database (and each time you click on the "connect" button), QGIS tries to list all the tables that are stored in the database. This scan can take a great amount of time depending on what options you have selected in the connection dialog box:

  • "Only look in metadata table": QGIS will only scan the tables that are listed in USER_SDO_GEOM_METADATA or ALL_SDO_GEOM_METADATA (Oracle metadata views).
  • "Only look for user's table": QGIS will only scan tables that belong to user schema.
  • "Also list tables with no geometry": QGIS will scan geographic and non geographic tables.
  • "Use estimated table metadata": QGIS will use the informations found in Oracle metadata views. Furthermore, it will scan only the first 100 lines of each table to determine the geometry type of the layer.
  • "Only existing geometric types": will look after the geographic tables that are not empty (have a geographic type).

You have to know that Oracle metadata views don't store geometry type. As QGIS needs this information to open the layer, QGIS "scans" the geometric type of all objects (or the first 100 if "Use estimated table metadata" is checked) to find the geometric types of the layer. And it do it on each table that is scanned.

When this first global scan is done, the list of the tables and their datatypes is stored in a local SQLite database (cache) to speed up the display of the tables in the connection dialog box. If you have lots of tables (> 3000) it can take about 10 seconds to QGIS to extract all of the lines of the SQLite cache but not 20 minutes.

If it takes too much time for the first connection:

  • Check option "Use estimated table metadata".
  • Only list the tables of the user (check option "Only look for user's table").
  • If you are confident in the declaration of your geographic tables into Oracle metadata views (should be the case), check connection option "Only look in metadata table".
  • Avoid non geographic tables (less tables to scan).

Furthermore, for each layer, you should declare the following metadata:

  • Be sure to use a non NULL SRID for each layer.
  • Name the extent dimensions as 'X' and 'Y', otherwise, QGIS will not be able to use them and each time you'd like to open the layer, QGIS will launch the extent calculation on the server (which implies to access to each index of each object on the layer).

I think that as long as we scan each table to determine geometric type, the listing of all tables will consume time.

#3 Updated by Mehmet Selim BILGIN over 4 years ago

  • % Done changed from 0 to 100

Thanks Meredic, your answer is really helpful. I checked connection settings disabled "Only look meta data table" option and activated "Use estimated table metadata". Now it lists tables in a few seconds.

#4 Updated by Jürgen Fischer over 4 years ago

  • Status changed from Feedback to Closed
  • Resolution set to worksforme

#5 Updated by chris Mr over 3 years ago

Hi,

I see this is closed, but I'm not only experiencing a long delay when adding a new oracle connection (in spite of following the above suggestions), QGIS also crashes every time, and I can't successfully add a connection at all. This is QGIS 2.18 on macos sierra using osgeo4mac homebrew installer

Thanks

Also available in: Atom PDF