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

Thread: sql query tuning

  1. #1
    Join Date
    May 2001
    Posts
    39

    Red face sql query tuning

    Hi,

    My materialized view mv_bank_stats has four columns and has 80 lakh
    records in it.

    mv_bank_stats
    population_group varchar2(20),
    bank varchar2(30),
    bank_id number(5),
    region varchar2(20).

    If I do select distinct population_group from the view it takes
    8.0 seconds .Can I have something which makes my DISTINCT work
    faster.

    Thanx in advance.

    SANCTUS
    sanctus

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Increase sort_multiblock_read_count (in 8i), sort_area_size and sort_area_retained_size.

    Also, to improve disk sort, configure initial and next in multiples of sort_area_size value in the temp tablespace.

    Are you using 9i?

    Tamil
    Last edited by tamilselvan; 07-21-2004 at 09:01 AM.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    sanctus, have you done an explain plan on the query? Did you turn on sql_trace and timed_statistics and run tkprof on the trace file? Also, can you post the entire query?

    These are usually the first steps for any tuning.

  4. #4
    Join Date
    Oct 2002
    Posts
    182
    why not create a bitmap index on population_group?
    - Cookies

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