New tablespace performs slowly
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: New tablespace performs slowly

  1. #1
    Join Date
    Feb 2001
    Posts
    16

    Unhappy

    I recently learned that new tables shouldn't be created in the SYSTEM tablespace, so I copied them to a new tablespace called CAT_DAT. Also, I transfered the corresponding indexes (some generated by Oracle and others custom made) to the tablespace CAT_IDX. Since this time, the performance of the DB is very poor.

    The user specified to draw from CAT_DAT and CAT_IDX is configured the same as the old user who drew from the SYSTEM tablespace. Is there something I can do to get the performance back up?

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    try analyzing the tables.

    with each table in the tablespace, do this :

    analyze table x compute statistics;
    (it will do the indexes automatically when you do the tables)

  3. #3
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    Check the status of the indexes. They might have become invalid and hence your applications might be running slow. If that is the case, rebuild the indexes.

  4. #4
    Join Date
    Feb 2001
    Posts
    16
    No results with your responses. Anyone else?

  5. #5
    Join Date
    Oct 2000
    Posts
    80
    What exactly is the performance problem you're having? Are we talking about the entire database being slower or just queries against the tables you moved?
    In the absence of this information try the following:
    run explain plan for problem queries. Look for full table scans where index searches should be done.
    run utlbstat/utlestat and analyze the results (report.txt)
    John Doyle

  6. #6
    Join Date
    Jul 2000
    Posts
    53
    Have you looked at the i/o against this new tablespace?
    What device have you put these tablespaces on? is it the same as the system tablespace?

    Have you put the files on the same device as the redo logs.

    have a look at utlbstat/estat and see what the hit ratios are like.

    Have a look at the extents of the tables you have moved


    Hope this helps
    Steve

  7. #7
    Join Date
    Feb 2001
    Posts
    16
    The response time in querying the moved tables is where the performance problem lies. The rest of the DB is fine. Everything is on the same disk (redo logs, indexes, Oracle binaries, etc.).

  8. #8
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    Why don't you try this. Reorganize the new tablespace contents once by EXPORT and IMPORT, and then check the Execution plan for your queries whether the indexes are properly used or not.

  9. #9
    Join Date
    Nov 2000
    Posts
    212
    why do not look into optimizer_mode parameter.

    If it is CHOOSE, FIRST_ROWS, ALL_ROWS then most probably CBO is employed (unless querie's hint overrides it)

    If CBO is used, then statistics are important and must be checked if still available after moving table to new tablespace -> best of all if they are exactly the same as were before reorganization.
    Creating new/modifying existing statistics can even make things worth.

    Just for fun, if indexes are available and non CBO specific features are used(like partition pruning), try to set optimizer_mode=RULE






  10. #10
    Join Date
    Oct 2002
    Posts
    391
    [QUOTE][i]Originally posted by jdorlon [/i]
    [B]

    with each table in the tablespace, do this :

    analyze table x compute statistics;
    (it will do the indexes automatically when you do the tables) [/B][/QUOTE]

    is this the same as running
    1) -checkopt
    2) -analyze

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