Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002

    Unanswered: database optimisation

    My tables(indexed) are overflowing - Data is growing at a very fast rate. This slows down my queries from MS Access front end (ODBC route). What can I do to manage this? My users frequently need to access old data.

  2. #2
    Join Date
    Apr 2002
    Here are some things to check:


    Are you using rule or cost based optimizer?
    If cost then are your tables/indexes being analyzed regularly?

    You can analyze a schema using the dbms_utility package e.g.

    dbms_utility.analyze_schema(<SCHEMA_NAME>, 'COMPUTE', NULL, NULL, NULL);

    If this doesn't help then your indexes might not be getting used.
    First I'd identify the sql statements that are causing problems and generate an explain plan for them to see if you are actually using the indexes. If you're not then you either need to modify your sql or change your indexes.

    Once you have the explain plan check for the following:

    Table scans on large tables.

    Unnecessary sort operations (distinct and order by clauses that the application doesn't need).

    Non selective index scans, if an index is not selective e.g. not many unique values, you might want to use a bitmap index.

    Although I hate to say it (I like command lines) you might like to try to use the Enterprise Manager GUI tools for evaluating the sql as they're very good and make the whole process very intuative.

    If your tables really are VERY big then you might want to partition them and allow parallel query plans but this is quite a complex option to implement properly and you'll need to spend a long time with the tuning manual before you do it.

    Hope this is helpful,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts