Urgent: this oracle query is taking too much time for running, how to optimize
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Urgent: this oracle query is taking too much time for running, how to optimize

  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Urgent: this oracle query is taking too much time for running, how to optimize

    Plz help me tune this query and optimize it..it is taking too much time for running...

    thnx,


    SELECT wfraud_data.id,wfraud_data.account_number,wfraud_data_extra.data,
    wfraud_data.score,wfraud_data.time_created
    FROM wfraud_data,wfraud_data_extra
    WHERE wfraud_data.id=wfraud_data_extra.fraud_data_id AND wfraud_data.id
    IN (SELECT * FROM
    (SELECT MAX(wfraud_data.id) FROM wfraud_data WHERE wfraud_data.fraud_suspect_reason_id=1761
    AND wfraud_data.time_created >= 1160463600
    AND wfraud_data.status ='N'
    AND (wfraud_data.expiration is null OR wfraud_data.expiration=0 OR wfraud_data.expiration>1161003844
    OR wfraud_data.status != 'N' OR BITAND(wfraud_data.flags, 10) > 0)
    GROUP BY wfraud_data.account_number )
    WHERE rownum < 2800 )
    ORDER by wfraud_data.score;

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    whats the explain plan

  3. #3
    Join Date
    Feb 2007
    Posts
    20

    expain plan for above query is

    Quote Originally Posted by davey23uk
    whats the explain plan
    QUERY_PLAN OTHER_TAG OTHER OBJECT_NAME

    ------------------------------------------------------- -------------------- ------------------------------ ------------------------------

    SELECT STATEMENT

    SORT ORDER BY

    NESTED LOOPS

    NESTED LOOPS

    VIEW VW_NSO_1 VW_NSO_1

    SORT UNIQUE

    COUNT STOPKEY

    VIEW

    SORT GROUP BY STOPKEY

    TABLE ACCESS BY INDEX ROWID WFRAUD_ WFRAUD_DATA

    DATA



    INDEX RANGE SCAN WFRAUD_DATA_TIME WFRAUD_DATA_TIME_CREATED

    _CREATED



    TABLE ACCESS BY INDEX ROWID WFRAUD_DATA WFRAUD_DATA

    INDEX UNIQUE SCAN WFRAUD_DATA_NEW_PRIME WFRAUD_DATA_NEW_PRIME

    TABLE ACCESS BY INDEX ROWID WFRAUD_DATA_EXTRA WFRAUD_DATA_EXTRA

    INDEX RANGE SCAN WFRAUD_DATA_EXTRA_PK WFRAUD_DATA_EXTRA_PK



    15 rows selected

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    that doesnt help, hwt does it say the cardinalities are, how many rows are coming back from each operation?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Your query is executing in-line view for each row in main query.

    I would seriously consider creating a temp table with the query you are using to populate in-line view, query is creating once and again the same list no matter row selected on main query.

    Once you have your temp table you can take out in-line view and replace is for a single additional where clause.

    Depending on temp table size an index on temp.wfraud_data.id wouldn't hurt.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Feb 2007
    Posts
    20

    Sorry didnt get it...

    Quote Originally Posted by PAVB
    Your query is executing in-line view for each row in main query.

    I would seriously consider creating a temp table with the query you are using to populate in-line view, query is creating once and again the same list no matter row selected on main query.

    Once you have your temp table you can take out in-line view and replace is for a single additional where clause.

    Depending on temp table size an index on temp.wfraud_data.id wouldn't hurt.
    Sorry didnt get it ...

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    -- Create TEMP_FRAUD table
    -- For PROD deployment you might want to create
    -- a true temp table
    --
    create table TEMP_FRAUD as
    SELECT MAX(wfraud_data.id)
    FROM wfraud_data
    WHERE wfraud_data.fraud_suspect_reason_id=1761
    AND wfraud_data.time_created >= 1160463600
    AND wfraud_data.status ='N'
    AND (wfraud_data.expiration is null OR wfraud_data.expiration=0 OR wfraud_data.expiration>1161003844
    OR wfraud_data.status != 'N' OR BITAND(wfraud_data.flags, 10) > 0)
    GROUP BY wfraud_data.account_number )
    WHERE rownum < 2800 ) ;
    --
    -- CREATE PK on TEMP_FRAUD.ID if needed
    --
    ...
    --
    -- NOW RUN YOUR QUERY
    --
    SELECT fraud_data.id,wfraud_data.account_number,wfraud_data_extra.data,
    wfraud_data.score,wfraud_data.time_created
    FROM wfraud_data,wfraud_data_extra
    WHERE wfraud_data.id=wfraud_data_extra.fraud_data_id
    AND wfraud_data.id = TEMP_FRAUD.ID
    ORDER by wfraud_data.score;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    try this

    Code:
    SELECT
    	wfraud_data.id,
    	wfraud_data.account_number,
    	wfraud_data_extra.data,
    	wfraud_data.score,
    	wfraud_data.time_created
      FROM	wfraud_data,
    	wfraud_data_extra,
    	(
    	SELECT
    		MAX(x.id) MAX_ID_PER_ACC
    	   FROM wfraud_data x
    	  WHERE x.fraud_suspect_reason_id = 1761
    	    AND x.time_created >= 1160463600
    	    AND x.status ='N'
    	    AND rownum < 2800
    	    AND (x.expiration is null
    	         OR x.expiration = 0
    	         OR x.expiration > 1161003844
    	         OR x.status != 'N'
    	         OR BITAND(x.flags, 10) > 0)
    	GROUP BY x.account_number
    	) C
     WHERE wfraud_data.id = wfraud_data_extra.fraud_data_id
       AND wfraud_data.id = c.max_id_per_acc

  9. #9
    Join Date
    Feb 2007
    Posts
    20

    thnx a lot, can u explain me how this query will save time then query i wrote

    Quote Originally Posted by pando
    try this

    Code:
    SELECT
    	wfraud_data.id,
    	wfraud_data.account_number,
    	wfraud_data_extra.data,
    	wfraud_data.score,
    	wfraud_data.time_created
      FROM	wfraud_data,
    	wfraud_data_extra,
    	(
    	SELECT
    		MAX(x.id) MAX_ID_PER_ACC
    	   FROM wfraud_data x
    	  WHERE x.fraud_suspect_reason_id = 1761
    	    AND x.time_created >= 1160463600
    	    AND x.status ='N'
    	    AND rownum < 2800
    	    AND (x.expiration is null
    	         OR x.expiration = 0
    	         OR x.expiration > 1161003844
    	         OR x.status != 'N'
    	         OR BITAND(x.flags, 10) > 0)
    	GROUP BY x.account_number
    	) C
     WHERE wfraud_data.id = wfraud_data_extra.fraud_data_id
       AND wfraud_data.id = c.max_id_per_acc




    thnx a lot, can u explain me how this query will save time then query i wrote??

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    (
    	SELECT
    		MAX(x.id) MAX_ID_PER_ACC
    	   FROM wfraud_data x
    	  WHERE x.fraud_suspect_reason_id = 1761
    	    AND x.time_created >= 1160463600
    	    AND x.status ='N'
    	    AND rownum < 2800
    	    AND (x.expiration is null
    	         OR x.expiration = 0
    	         OR x.expiration > 1161003844
    	         OR x.status != 'N'
    	         OR BITAND(x.flags, 10) > 0)
    	GROUP BY x.account_number
    	) C
    First check, would oracle materialize the in-line view?
    Or post the execution plan.

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