Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: help me in this query

    Hi all,

    I was running this query on one particular database in Oracle 10g and it works fine.Later when we test this on the same schema on a production DB in same Oralce 10g doesnot work.The CPU util goes very high.

    The query is just a join between 2 tables which returns less than 40 records.It still works fine on the test env,but still giving probs on the prod.

    I have run the explanation plan and found a new recommendation,but am not sure how to implement this.
    I have given the table schema,sql and old and recommended exp plans in the attachement.It would be great if any one could help me out of this.

    THanks
    micky
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Any number of "details" can result in performance changes against the same SQL
    a) the amount of data in the tables
    b) are the table & index statistics both current in the two DBs?
    c) Do the tables have exactly the same indexes on them?

    The absolutely the BEST way to determine what is happening is to
    set event 10046 to a value of 8 or 12, run the SQL query, and then
    analyze the resultant trace file with TKPROF to actually see where the time is being spent.

    Almost any other approach is just blindly shooting in the dark & hoping to get lucky.
    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 2004
    Location
    France
    Posts
    754
    Hello,

    From the recommended plan, it seems you have two indexes (SEADEV.GEOM_LONGLAT_SPT_IDX and SEADEV.SF_ATRID_IDX) that should be used instead of your two Full table scans (a full table scan should always be avoided for it is the slowest way to retrieve data, especially when your table is big). I think that if you update your stats, Oracle should use the recommended plan. Anyway, you can try to force Oracle to use these two indexes by telling the optimizer to do so :

    select /*+ INDEX(table SEADEV.GEOM_LONGLAT_SPT_IDX SEADEV.SF_ATRID_IDX) */
    a.longlat.sdo_point.x Longitude,a.longlat.sdo_point.y Latitude,timestepid,water_level,current_speed,curr ent_direction
    from sf_geom a,sf_attr b
    where b.attr_id=a.geom_id
    and sdo_within_distance(a.longlat,sdo_geometry(2001,nu ll,sdo_point_type(103.118,1.0505,null),null,null), 'distance=0.001')='TRUE'
    order by b.timestepid;

    Replace table with the name of the table the two indexes are on.

    Even if you do that, update your stats by running a command like :

    EXECUTE dbms_stats.gather_database_stats();

    I hope it will have helped you.

    Regards,

    RBARAER

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I think it more has to do with the function in the where clause.
    Wouldn't that force a FTS?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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