Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Unanswered: Query Not Responding - Hanging

    Hi All

    I am facing a problem with one of the Query. When I run this Sqlplus hangs. This may be bcos these tables are very large. If I change the inputs in the where clause to something which does not exist in database, query comes out fast telling "no rows selected"

    Query :

    select sum( event_total_t.amount )
    from
    event_t,
    event_total_t,
    event_bal_impacts_t
    where (event_t.service_obj_ID0 = 33305 and
    event_t.service_obj_DB = 1 ) and
    event_t.end_t > 1049238000 and
    event_t.end_t <= 1050444000 and
    event_bal_impacts_t.resource_id = 100002 and
    event_t.rum_name in ( 'Occurrence','Duration') and
    event_t.poid_id0 = event_total_t.obj_id0 and
    event_t.poid_id0 = event_bal_impacts_t.obj_id0;

    Optmizer Mode is Rule
    Explain Plan is below :
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 NESTED LOOPS
    3 2 NESTED LOOPS
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_BAL_IMPACTS_T'

    5 4 INDEX (RANGE SCAN) OF 'I_EVENT_BAL_IMPACTS_RES__ID
    ' (NON-UNIQUE)

    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_T'
    7 6 INDEX (UNIQUE SCAN) OF 'I_EVENT__ID' (UNIQUE)
    8 2 TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_TOTAL_T'
    9 8 INDEX (RANGE SCAN) OF 'I_EVENT_TOTAL__ID' (NON-UNIQUE)

    Can anybody throw some light on what could be causing the query to hang.

  2. #2
    Join Date
    Apr 2003
    Posts
    9
    Hi

    If these tables have had a lot of deletes I would recommend rebuilding the indexes.

    That will reduce the time spend in doing a RANGE scan.

    This could be one reason for apparent HANGING issue.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Rajesh,

    As you've probably guessed, the query hasn't hung it's just working hard. Maybe this will give you some pointers....

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    It *still* has some mods and suggestions [as per AndrewsT suggestions] but should point you in the right direction.

    HTH
    Bill

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi Rajesh,

    You can try building and using Bitmap Indexes on required columns.

    Thanks.

    Hings

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

    Still Learning...

Posting Permissions

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