Jurij, Deleting stats help instead gathering stats
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Jurij, Deleting stats help instead gathering stats

  1. #1
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Jurij,

    I have been thinking about it for a while. We have couple of tables of size each 10+ million records. Whenever we get bad query performance or too much cost I will try gathering the stats for all indexed columns. But none of the times it helped me. When I get frustated and delete the stats as gathering stats screwed my query performance further... Query comes back quick...

    I don't know whats going onn really. May be you can help me understand whats exactly happening with optimizer in this specific scenario...
    Reddy,Sam

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi;
    Probably your Query runs better in RULE-Based Mode;
    So access the table with /*+RULE*/-Hint
    In our production-Environment very often the hint increases performance due to the CBO likes to scan tables full in some situations for ecample with the OR or NOT -Operator or <>-Operator.

    Orca

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Nope. When I use hint RULE I don't get much performance for the same queries... Let me investigate further on Optimizer intelligence for diffrent scenarios...
    Reddy,Sam

  4. #4
    Join Date
    Jun 2002
    Posts
    4
    Now this is an interesting thread, reading some of the other problems i am shocked that the companies had trusted these guys with the databases...

    My two pence worth on the subject.

    We have several tables with 20 Million + rows...the thing is that with stats the optimizer makes a calculated guess to determine the correct path for execution, do note that i said a calculated guess. Its not entirely correct. It takes into account several things like for eg:

    sort_area_size
    db_file_multiblock_read_count
    optimizer_index_cost_adj
    optimizer_max_permutations

    What sort of values do u have for these? The third one is specially useful if the indexes are not used in your queries. Set it to a lower value to force index scan rather than full table scans.

    Hope this helps and get back to me on your optimizer behaviour findings.

    Cheers,
    Ali

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