DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: CPU Usage 100%

  1. #1
    Join Date
    Mar 2003
    Posts
    34

    CPU Usage 100%

    Dear Friend,

    I m generating a report in which there is a nested select query with union clause using 4 tables. when the report starts the CPU usage goes to 100% which remains for some time. Because this affects other work I have to then Kill the job. What shall I do to keep the CPU usage minimum. I m sendin the query.


    SELECT DISTINCT "GRN_DTL"."ITM_ITM_ID"
    FROM "GRN_DTL"
    WHERE "GRN_DTL"."BU_ID" = :a_store_id
    AND "GRN_DTL"."ITM_ITM_ID" NOT IN
    (SELECT "ITM_TRXN_DTL"."ITM_ITM_ID"
    FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
    WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
    "ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
    )
    AND ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
    AND ("ITM_TRXN_HDR"."ITM_TRXN_TYP" = 'ICN')
    AND TO_CHAR (
    "ITM_TRXN_HDR"."ITM_TRXN_DT",
    'RRRR/MM/DD HH24:MI:SS'
    ) >= TO_CHAR (
    :adt_start_date,
    'RRRR/MM/DD HH24:MI:SS'
    )
    AND TO_CHAR (
    "ITM_TRXN_HDR"."ITM_TRXN_DT",
    'RRRR/MM/DD HH24:MI:SS'
    ) <= TO_CHAR (
    :adt_end_date,
    'RRRR/MM/DD HH24:MI:SS'
    ))
    UNION
    SELECT DISTINCT "ITM_TRXN_DTL"."ITM_ITM_ID"
    FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
    WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
    "ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
    )
    AND ("ITM_TRXN_DTL"."ITM_ITM_ID" NOT IN
    (SELECT "ITM_TRXN_DTL"."ITM_ITM_ID"
    FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
    WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
    "ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
    )
    AND ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
    AND ("ITM_TRXN_HDR"."ITM_TRXN_TYP" = 'ICN')
    AND TO_CHAR (
    "ITM_TRXN_HDR"."ITM_TRXN_DT",
    'RRRR/MM/DD HH24:MI:SS'
    ) >= TO_CHAR (
    :adt_start_date,
    'RRRR/MM/DD HH24:MI:SS'
    )
    AND TO_CHAR (
    "ITM_TRXN_HDR"."ITM_TRXN_DT",
    'RRRR/MM/DD HH24:MI:SS'
    ) <= TO_CHAR (
    :adt_end_date,
    'RRRR/MM/DD HH24:MI:SS'
    ))
    )
    AND ( ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
    OR ("ITM_TRXN_HDR"."TO_STORE_ID" = :a_store_id)
    )

  2. #2
    Join Date
    Mar 2003
    Posts
    34

    CPU usage 100%

    Dear Friend,

    I would like to provide u with more info. I m using Oracle 8 on Win NT

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    post the execution plan

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    AND TO_CHAR ("ITM_TRXN_HDR"."ITM_TRXN_DT",'RRRR/MM/DD HH24:MI:SS') <= TO_CHAR(:adt_end_date,'RRRR/MM/DD HH24:MI:SS')
    Why are you going this? Why not ...

    Code:
     AND "ITM_TRXN_HDR"."ITM_TRXN_DT" <= :adt_end_date
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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