[fdo][FDO198][New] DescribeSchema slow after Oracle Cache flush performed

[Brent Robinson]

You can view the artifact detail at the following URL:

    https://fdo.osgeo.org/servlets/Scarab/id/FDO198

Type
 Defect

Artifact ID
 FDO198 (DescribeSchema slow after Oracle Cache flush performed)

Reported by
 Brent Robinson
 brentrobinson (brent.robinson at autodesk.com)

New artifact details:
---------------------------------------------------------

- Version set to new value
  V2
- Defect Severity set to new value
  High
- Subcomponent set to new value
  PERFORMANCE
- Operating system set to new value
  All
- Target milestone set to new value
  3.2.1
- Component set to new value
  ORACLE
- Summary set to new value
  DescribeSchema slow after Oracle Cache flush performed
- Description set to new value
  There are a couple of queries that take a long time when first executed. However, once they get into Oracle's cache, they are much faster. These include:

    - an all_sdo_geom_metadata query that is executed once per
      geometric column. It takes about 30 seconds (1second 
      after it gets cached).

    - a query to retrieve foreign keys takes 22 seconds ( 0.2          
       seconds after it is cached) 

Although these queries are fast after they are cached, they still contribute to very slow "first run" performance. 

The above queries are defined in the Autodesk Oracle Provider, which is not open-source. However, open-source changes are required to fix this defect. 
- Steps to Reproduce set to new value
  - connected to arc r10103 and executed the following to clear out Oracle's cache:

   alter system flush buffer_cache.

- connected, via FDO, to fdo_br_wamig_nm (a foreign datastore ) 
  on arc r10103
- performed a DescribeSchema command

The describeSchema took 65 seconds. If the above is repeated (without doing the "alter system" ) DescribeSchema takes 4.5 seconds 
- Artifact Status set to new value
  New
- Platform set to new value
  All
- Artifact created

---------------------------------------------------------
This message was automatically generated by Project Tracker.