DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: indexes problem

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    Some of my user's complain about slowleness of their retrieval. I do have indexes on columns they're using in a where clause. Those indexes used to work just fine. There was no deletes performed on the table. What can I do to fix the problem?

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    REBUILD your indexes and analyze the tables, Oracle updates statistics for Optimizer... which helps for fast retrieval of data.
    Reddy,Sam

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    periodically have a plan to analyze your tables and indices, such that this would provide your cost based optimizer to make a good decision.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Though, CBO will produce good execution (access) paths many times, the response time may not be acceptable by end users at some time.

    What you need to do :
    1 Capture the heavy SQL statements when the system is running slow.
    2 Do EXPLAIN PLAN.
    3 Add hints in the SQL statement that means over ride the CBO execution paths.
    4 If all blocks in a table is needed for a SQL statement, it is better not to use INDEX. Use HASH JOIN instead of Nested Loops. Allo full table scan.
    5 Configure Buffer Cache correctly. Allocate BUFFER POOL KEEP for all the look up tables.

    There are plenty of ways to improve SQL statements. What you need is analytical mind.


Posting Permissions

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


Click Here to Expand Forum to Full Width