7 Replies Latest reply: Jul 30, 2012 4:13 PM by John O'Toole RSS

    Oracle 11.2.0.2 spatial recommendations

    AB007
      Hi,

      I am running a 4-node 11gR2 RAC & RDBMS with spatial. Off late with increasing traffic, the performance of the database is degrading. I have already done all possible healthchecks and tunings from a db perspective (isolate load processes to 1 node, remove parallel clauses from SQLs, gather stats, etc) but nothing has helped. After some more investigation, it seems like its the spatial index that's at the root of all problem. Someone suggested me, that I should keep spatial indexes in a separate tablespace with local extent mgmt etc instead of dictionary.

      Is there any good practice for Oracle Spatial Indexes (like separate tablespace, init settings etc)???

      Thanks,
      aBBy
        • 1. Re: Oracle 11.2.0.2 spatial recommendations
          John O'Toole
          aBBy,

          It would be useful if you could provide some more information. E.g.
          - How large are your spatial tables?
          - Are they partitioned?
          - What type of data do they contain (points, line, polygons, complex geometries?)
          - What is the level of traffic?
          - What application(s) is accessing the data (make sure it is using bind variables)?

          Here are some links to useful information on Oracle Spatial performance tuning:
          Oracle Spatial Performance with 10-20.000 users
          http://download.oracle.com/otndocs/products/spatial/pdf/spatial_wp09_bestprac.pdf
          http://download.oracle.com/otndocs/products/spatial/pdf/oow2010/spatial/spatialoow10_bestpractices.pdf
          http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/51/oracle-spatial-mapping-and-map-rendering-performance-tips
          Someone suggested me, that I should keep spatial indexes in a separate tablespace ...
          With modern storage systems, I don't think there is a whole lot of benefit to putting data and indexes in separate tablespaces.
          ...with local extent mgmt etc instead of dictionary.
          Its 2012, why on earth would you consider using Dictionary Extent Management? Local Extent Management all the way.
          Is there any good practice for Oracle Spatial Indexes (like separate tablespace, init settings etc)???
          And I'm not aware of any init parameters that would be of particular benefit to spatial queries.

          Note that if you have large spatial tables, lets say >20m, then you may see benefit from partitioning the data. Also, clustering of the spatial data can really help with reducing I/O.

          John
          • 2. Re: Oracle 11.2.0.2 spatial recommendations
            AB007
            Hi John,

            Before I get into the answers, let me give you a small insight into the app layout...

            The app caters to geographic regions narrowed to the counties in US and each county has its own schema. For now the db has around 150 schemas (representing the counties). And each schema has around 100+ tables and the apprx schema size is 60GB. So to answer your questions now -
            - How large are your spatial tables?
            - Are they partitioned?
            Largest table in a schema is ~1GB and non-partitioned
            - What type of data do they contain (points, line, polygons, complex geometries?
            Complex geometries (not 100% sure, but will check with the developer)
            - What is the level of traffic?
            AAS is ~20/sec (guess this doesn't qualify as traffic)
            - What application(s) is accessing the data (make sure it is using bind variables)?
            AutoCAD


            Thanks
            aBBy
            • 3. Re: Oracle 11.2.0.2 spatial recommendations
              John O'Toole
              The app caters to geographic regions narrowed to the counties in US and each county has its own schema. For now the db has around 150 schemas (representing the counties). And each schema has around 100+ tables and the apprx schema size is 60GB. So to answer your questions now
              So each of the 150 schemas is ~60GB is size? That's pretty big - I make that 9TB. Are you sure you've got that much vector data?
              You probably mean that combined size of the 150 schemas is 60GB.
              Largest table in a schema is ~1GB and non-partitioned
              Based on a finger in the air assessment, I'd say there is little value in partitioning a table of that size (but of course there are so many variables in that decision...).
              AAS is ~20/sec (guess this doesn't qualify as traffic)
              I guess I was more thinking of:
              - How many concurrent sessions are running spatial queries when you are seeing the performance problems?
              - How frequent are the queries?
              AutoCAD
              You're being pretty frugal with information here. I'm guessing you mean AutoCAD Map 3D - or do you mean Autodesk MapGuide Enterprise? What version of the product? Are you using TopoBase?
              In the past I have had significant performance problems with connecting AutoCAD Map 3D 2006 sessions to Oracle using OSE. It works better with FDO. What are you using?
              The issues I had were always in getting the initial connection from Autodesk Map at it seemed to read all of the data in the table regardless of how little you wanted. Are you finding that the problem occurs on initial connection or does it persist with every subsequent request from the same Autodesk Map session?

              John
              • 4. Re: Oracle 11.2.0.2 spatial recommendations
                741357
                AB007 wrote:
                The app caters to geographic regions narrowed to the counties in US and each county has its own schema. For now the db has around 150 schemas (representing the counties). And each schema has around 100+ tables and the apprx schema size is 60GB.
                Abby - some additional questions:

                Why RAC? Do you have a HA requirement that requires RAC? I'm trying to understand why you are using it.

                How much RAM do the servers have? If it is a total of 60GB, that's easily loadable on a single small server. Since it is getting slower - possibly you are memory starved and disk IO is now killing your performance.

                Is this possibly just a network bottleneck instead of a server bottleneck?

                I guess what we are asking in a round-about way is - what is the bottleneck in general?

                Bryan
                • 5. Re: Oracle 11.2.0.2 spatial recommendations
                  AB007
                  Hi John,
                  So each of the 150 schemas is ~60GB is size? That's pretty big - I make that 9TB. Are you sure you've got that much vector data?
                  Yes, that is the supposed size of the db when all the schemas go live. Right now only 6 of the 150 schemas are being used and the rest just have the metadata.
                  I guess I was more thinking of:
                  - How many concurrent sessions are running spatial queries when you are seeing the performance problems?
                  - How frequent are the queries?
                  ET 8AM - 4PM we see ~20 AAS. After that, there is hardly any traffic.
                  You're being pretty frugal with information here. I'm guessing you mean AutoCAD Map 3D - or do you mean Autodesk MapGuide Enterprise? What version of the product? Are you using TopoBase?
                  Team uses AutoCAD Map 3D latest version. In addition to the ACAD, they install an additional app provided by the spatial vendor. Most of the coordinates, vertices etc originate from this addon. (As the dev team explained to me, the coordinates, vertices etc are buttons that appear as tools on ACAD).
                  Are you finding that the problem occurs on initial connection or does it persist with every subsequent request from the same Autodesk Map session?
                  Well, we initially thought that it had something to do with the users's pc, then we tested it on a Windows 2008 server (in the same data center) with 64G RAM, but still the same problem. We ran the same query from a SQL prompt and the response time was very much acceptable (I don't have those data as the developers tested those).


                  Thoughtss????

                  aBBy.
                  • 6. Re: Oracle 11.2.0.2 spatial recommendations
                    AB007
                    Hi Bryan,

                    As I just explained in my response to John above - the expected size of the db is supposed to grow to ~9T when all the schemas go live. With increased schemas and increased traffic, the app architect concluded that this would need a RAC setup.
                    How much RAM do the servers have? If it is a total of 60GB, that's easily loadable on a single small server. Since it is getting slower - possibly you are memory starved and disk IO is now killing your performance.
                    Each server in the cluster has 128G (so a total of 512G across the cluster).

                    I guess what we are asking in a round-about way is - what is the bottleneck in general?
                    The bottleneck currently seems like translation of index values to actual coordinates. When we run the query with actual values from SQL, we get a response in a timely fashion. But when we try to plot the same using ACAD, it takes more than 2 times the avg time. As the Devs tell me, the apprx response time is almost 10s+ for each vertice change.

                    I don't know much (or rather anything) about how spatial works, so I might be mis-stating something, but if you can let me know, I can ask the dev team and get you the response.

                    Thanks,
                    aBBy.
                    • 7. Re: Oracle 11.2.0.2 spatial recommendations
                      John O'Toole
                      Hi aBBy,
                      Team uses AutoCAD Map 3D latest version. In addition to the ACAD, they install an additional app provided by the spatial vendor. Most of the coordinates, vertices etc originate from this addon. (As the dev team explained to me, the coordinates, vertices etc are buttons that appear as tools on ACAD).
                      It sounds like you need to research the "additional app provided by the spatial vendor". I'd suggest running a trace on one of the sessions from that mystery application. So connect that application to the database, then gets its session_id and serial_num from v$session, then start the trace using:
                      execute dbms_monitor.session_trace_enable(session_id=>?, serial_num=>?, binds=>true, waits=>true);
                      Then in as few steps as possible, carry out the task that reproduces the problem, then stop the trace using
                      execute dbms_monitor.session_trace_disable(session_id=>?, serial_num=>?);
                      Then run tkprof to format the trace file and go looking for the problem queries.
                      Note that you'll need to ensure you run dbms_monitor.session_trace_enable() from the same instance that the mystery application is connected to.
                      The bottleneck currently seems like translation of index values to actual coordinates. When we run the query with actual values from SQL, we get a response in a timely fashion.
                      Can you post the query you ran and the execution plan with timings?
                      But when we try to plot the same using ACAD, it takes more than 2 times the avg time. As the Devs tell me, the apprx response time is almost 10s+ for each vertice change.
                      How long did the query take when run from SQL*Plus? How long does it take to draw the data using the mystery application?
                      What do you mean by vertice change? Do you mean for each refresh of the data - i.e. each time the user zooms or pans?

                      John