Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Query optimization

    I'm running an ETL where I'm inserting to a table A by selecting from PS table
    The PS table has about 14 mil records. When I do an explain plan on it, it shows 55000cost approx and the response time is 3hours with a full table scan on dw_asset_location. I know that the cost and performance are not directly related but if the cost is less then all the resource are used properly and the query should perform well, right??
    Please find the query(only the select part) below:

    SELECT /*+ parallel(ps,8),
    parallel(dwal,8),
    index (dwal,ASSET_LOCATION_IDX1) */

    ps.BUSINESS_UNIT
    ,ps.ASSET_ID
    ...
    ....
    ...
    ,ps.ASSET_ASSIGNED_TO
    ,dwal.LOCATION
    ,dwct.DESCR50
    FROM PS_ASSET ps, --14mil records
    ASSET_LOCATION dwal, --13mil records
    ASSET_CLASS_TBL dwct, --500
    BUS_UNIT_TBL_FS fs --180
    WHERE
    ps.business_unit = dwal.business_unit(+) and
    ps.BUSINESS_UNIT=fs.BUSINESS_UNIT(+) and
    ps.asset_id=dwal.asset_id(+) and
    ps.asset_class=dwct.asset_class(+)
    When I made slight changes to the query, the cost was 4mil with index scan on dw_asset_location and the response time is 1.20min. Does anyone has any idea as to what is going wrong.

    SELECT /*+ index (dwal, ASSET_LOCATION_IDX1) */
    ps.BUSINESS_UNIT
    ,ps.ASSET_ID
    ...
    ....
    ...
    ,ps.ASSET_ASSIGNED_TO
    ,dwal.LOCATION
    ,dwct.DESCR50
    FROM PS_ASSET ps, --14mil records
    ASSET_LOCATION dwal, --13mil records
    ASSET_CLASS_TBL dwct, --500
    BUS_UNIT_TBL_FS fs --180
    WHERE
    ps.business_unit = dwal.business_unit(+) and
    ps.BUSINESS_UNIT=fs.BUSINESS_UNIT(+) and
    ps.asset_id=dwal.asset_id(+) and
    ps.asset_class=dwct.asset_class(+)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    Maybe you could have tried using the parallel_index_hint instead of the parallel hint on the tables.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget cost is just an estimate, use autotracing to confirm the amount of work the query actually does. Often the cost is wrong when the stats are out of date or the fact that the stats collection isnt very sophisticated, for example it doesnt pick up relationships between different columns.

    Alan

  4. #4
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    hm... have you enabled parallel dml ?
    ALTER SESSION ENABLE PARALLEL DML
    in big amount data is hash join more efective than nesteed loop (index scan)
    you can use hint "append " and no "logging" operation.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    are these the only two columns you are gathering in your select statement for these tables?

    ,dwal.LOCATION
    ,dwct.DESCR50

    what about this table? i don't see you using a column from this table
    BUS_UNIT_TBL_FS fs --180
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    I have enabled parallel DML and I'm using retrieve a column from this table too BUS_UNIT_TBL_FS(My mistake, I didnt paste it here)

    One of my friend suggested me to make the following changes and it made wonders. Queries which took 2-5 hours take 1 hour now.

    ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;

    ALTER SESSION SET SORT_AREA_SIZE=128000000;

    ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;

  7. #7
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    nandinir:
    - run pga advisor.
    - check execute plan. when you have hash_join, this is ok. when some table is scanned by index, use hint full for this table. .
    - try leading hint for table PS_ASSET
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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