Results 1 to 9 of 9

Thread: database tuning

  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: database tuning

    Hi Everybody

    I'm kind of new to database tuning and I was wondering if someone could help me.
    I'm trying to run a cost based optimizer however I'm not sure about the syntax.

    Here's what I have so far.
    analyze table well_data compute statistics;
    analyze table prod_data compute statistics;
    spool c:\problem1.txt
    SELECT /*+choose*/ --is this the correct syntax
    w.meridian,
    w.township,
    w.range,
    w.section,
    oil_prod
    FROM well_data w,
    prod_data p
    WHERE depth BETWEEN 1000 and 4000
    AND w.meridian=p.meridian
    AND w.township=p.township
    AND w.range=p.range
    AND w.section=p.section
    ORDER BY w.meridian, w.township, w.range, w.section;

    spool off;


    Also I tried using indexes to speed things up as well however it only sped up the query by 3 seconds. Not much of an improvement. I've seen an index used before and it did it in about 2 to 5 seconds. Could someone look at the following and tell me if i'm doing it right?

    set autotrace traceonly
    set timing on
    create index mer_well_data_idx
    on well_data (meridian);

    create index mer_prod_data_idx
    on prod_data (meridian);

    spool c:\problem1Bothindex.txt
    SELECT
    w.meridian,
    w.township,
    w.range,
    w.section,
    oil_prod
    FROM well_data w,
    prod_data p
    WHERE depth BETWEEN 1000 and 4000
    AND w.meridian=p.meridian
    AND w.township=p.township
    AND w.range=p.range
    AND w.section=p.section
    ORDER BY w.meridian, w.township, w.range, w.section;

    spool off;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    all columns in the WHERE clause should be indexed

    post EXPLAIN PLAN for this SQL statement

    make sure PLAN is formatted using < code tags>
    read the #1 STICKY post at TOP of postings list that explains how to use < code tags>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears you replied to the wrong/different/thread.
    After CREATE INDEX, statistics need to be gathered.

    Post complete results of
    SELECT * from v$version;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2010
    Posts
    6
    HTML Code:
    SQL> select * from v$version;
    
    BANNER                                                                          
    ----------------------------------------------------------------                
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product                
    PL/SQL Release 10.2.0.1.0 - Production                                          
    CORE	10.2.0.1.0	Production                                                      
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                         
    NLSRTL Version 10.2.0.1.0 - Production                                          
    
    Elapsed: 00:00:00.00
    SQL> spool off

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT COUNT(*)
    FROM well_data w,
    prod_data p
    WHERE depth BETWEEN 1000 and 4000
    AND w.meridian=p.meridian
    AND w.township=p.township
    AND w.range=p.range
    AND w.section=p.section
    ORDER BY w.meridian, w.township, w.range, w.section;
    SELECT COUNT(*) FROM well_data w;
    SELECT COUNT(*) FROM prod_data p;

    post results from all 3 SQL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2010
    Posts
    6

    results

    Hey

    here are the results.
    1st query: 4336847
    2nd: 1000
    3rd: 4980095

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Full Table Scan of both tables is reasonable
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2010
    Posts
    6
    how do i do a full table scan?

    also, how do i use the cost based optimizer

    should i use it as a hint or put it separately.

    select /*+choose*/

    or select statement = optimizer

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >also, how do i use the cost based optimizer
    Since V10, only Cost Based Optimizer (CBO) exists.

    When all else fails, Read The Fine Manual
    Contents
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

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