2 Replies Latest reply on Jun 26, 2013 8:49 AM by Stefan Jager

    Perfromance issue in Spatial database !


      Hi All


      There was a deployment done recently in a Spatial GIS Oracle database, after which server utilization just got doubled, and users started experiencing delayed response time wrt queries (Select/DML both).

      They did Model changes (specific to Spatial DB) and few update statements too.

      SGA of database is 22 GB.

      I noticed that ADDM gave some recommendations like host paging problem, suggestion to increase memory to 28 GB from 22 GB, sql tuning.

      AWR didn't show any alarming issue.


      1) Can you suggest me which things to look for getting cause of problem ? I have heard that Spatial DBs need more resources, so adding more RAM on host and SGA in DB, will help ?


      2) Can you tell me the scripts I can use for DB monitoring during peak hours ? I will appreciate more info about what to look via the DB monitoring.


      3) Also, how can I monitor (and what things to look for) the instance process on AIX ?



        • 1. Re: Perfromance issue in Spatial database !
          B Hall

          In general, without any details given, I'd say that sounds about right.



          Spatial will use more CPU that most other database operations, and if you are expericieng the full-table-scan index bug (not sure what version you are using) - that can definetly impact other users.



          As far as tools go, OEM can monitor the database if you have tuning/diag packs. Or you can use toad/spotlight/foglight, SQL developer, etc.



          What seems to the most problematic sql - from awr?




          • 2. Re: Perfromance issue in Spatial database !
            Stefan Jager

            BHall wrote:

            In general, without any details given, I'd say that sounds about right.

            Hmmm. I'd have to disagree with that. Depending on the changes, 25% increase, sure, but an increase of 100%?

            No, I would never accept that, not even for Spatial. Something must be wrong somewhere.


            Ankit, which version of DB are you using?

            select * from v$version;

            will give you the exact version info. Further, I would start my research by looking at the differences between before and after. You should have source control, so a difference between the two versions should point you at what is causing the tremendous difference in resource consumption. Improving performance on spatial queries can be tricky, but luckily there are plenty of experts around here to help you out if the culprit is a spatial query

            And I would probably use tracing, as Bryan suggested: if you are running into the FTS-bug, the tracing should give that away. Also make sure all your data is valid and correct, metadata is filled correctly, indexes are fine and usable, statistics are up-to-date, etc.


            For monitoring the database processes on AIX, I have no idea, I'm not really familiar with *nix type of OS'ses but I'm sure there must be tools to do so (Google is your friend here).