Improve performance of Oracle OGR driver

[avautour]

Opening an OGR data source without specifying the tables to open was extremely slow with our in house server.

I did some performance profiling and found that most of the time was spent in OGROCIDataSource::OpenTable(). More specifically, the time was spent waiting on the database queries in OGROCITableLayer::ReadTableDefinition() and OGROCILayer::LookupTableSRID().

I decided to trace an Oracle session to see why the queries were taking so long, I discovered that most of the time was spent in the parse stage of the query, not in the execution.

Oracle has a shared pool to cache to execution plans of queries, but it usually gets hit if the queries are exactly the same.

This improvement is to update the functions above to use bind variables so that the queries can be parsed for the first table, and just executed for the following tables, resulting in much better performance.

[avautour]

Proposed changes

[avautour]

Oracle trace file of the initial parse when the shared pool was empty

[avautour]

Oracle trace file when the parse stage hit the shared pool

[Even Rouault]

In 38150:

OCI: use prepared statements to speed up opening (fixes #6874, patch by Andre Vautour)

[Even Rouault]

I've comitted the patch in trunk. Not there was a spurious ; in the below string that removed the where clause. I've fixed that

        352      const char* pszDimCmd =
 	353	            "SELECT COUNT(*)\n"
 	354	            "FROM ALL_SDO_GEOM_METADATA u, TABLE(u.diminfo) t\n";
 	355	            "WHERE u.table_name = :table_name\n"
 	356	            "  AND u.column_name = :geometry_name";

[avautour]

Interesting, I'm not sure how that got in there. Regardless, thanks Even.