Results 1 to 6 of 6

Thread: SQL tuning

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: SQL tuning

    Hi all,

    I'm running an ETL on a table with 13 mill records. I created the necessary indexes and gathered the stats using
    exec dbms_stats.gather_table_stats(ownname=>'XYZ',tabna me=>'ABC',estimate_percent=>15);

    And then ran the exec plan on it. But the table ABC is still making a full table scan. What else can I do to enhance the performace?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL & EXPLAIN_PLAN

    I recently learned about DBMS_SQLTUNE, but have not had time to experiment with it.

    http://download-west.oracle.com/docs...8/d_sqltun.htm
    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
    Jul 2005
    Posts
    276
    There are 89 columns in the script but I deleted them here to save space.
    SELECT
    ps.id
    ,bu_unit
    ,ps.CONVERSION_ID
    ....
    .....
    ,ps.INDEX_NAME
    ,ps.INDEX_DETAIL_NAME
    ,ps.COMPOSITE_SW
    ,ps.COMPOSITE_ID
    ,ps.IN_SERVICE_DT
    ,fs.business_unit||'_'||fs.descr--bus_unit_descr
    ,dwal.LOCATION
    ,dwct.DESCR50
    FROM ASSET ps,
    LOCATION dwal,
    CLASS_TBL dwct,
    BUS_UNIT_TBL fs
    WHERE
    ps.ast_id=dwal.ast_id(+) and
    ps.bu_unit = dwal.bu_unit(+) and
    ps.class=dwct.class(+) and
    ps.BU_UNIT=fs.BU_UNIT(+)
    Table ASSET : 13.7mil records
    location: 13.2mil
    class_tbl: 440
    bu_unit: 560
    Exec plan:

    OPERATION OPTIONS OBJECT_NAME ID POS
    -------------------------- ------------ --------------------------- --- ---
    SELECT STATEMENT 0 ###
    NESTED LOOPS OUTER 1 1
    HASH JOIN RIGHT OUTER 2 1
    TABLE ACCESS FULL CLASS_TBL 3 1
    HASH JOIN RIGHT OUTER 4 2
    TABLE ACCESS FULL LOCATION 5 1
    TABLE ACCESS FULL ASSET 6 2
    TABLE ACCESS BY INDEX ROW BUS_UNIT_TBL 7 2
    ID

    INDEX UNIQUE SCAN PS_BUS_UNIT_TBL_FS_IDX1 8 1

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Nothing in the query seems to indicate you want anything less than the entire contents of each table. Full table scans is not shocking here. Is there some reason you believe only a (small) subset will be returned?

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    If I dont use the outer join, then it might result in a subset. I need all the records from the ASSET(ps) table
    A full table scan on table ASSET is fine as this is the driving table but what about a full table scan on LOCATION. I have an Index on ast_id, bus_unit but its not using the index. I feel if I can make it use the Index, perhaps the load will be faster. Will forcing the table to use the index help?
    Last edited by nandinir; 04-17-07 at 16:51.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Seeing as you have no criteria on location the hash join with the full table scan on location may well be the quickest way. Try using an index hint and use autotrace to confirm wether or not this is true.

    Alan

Posting Permissions

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