what else can I do here?

Hi gang. Any help on this will be welcomed!!!

I have a very dynamic application, and am trying to tune a procedure that takes about 30-minutes to run (after doing dbms_stats.gather_blah)

Am using 9.2.0.1 on Windows 2000 Server.

Within the same process, I'm deleting from another table about 2 mln rows... (simple delete table_name where indexkey=value)

The main query that seems to take the most time to run, because of about 2 mln rows that it needs to process is this (in a cursor): SELECT LEVEL, object_id, member_id, member_object_id, member_name

    FROM sol_object_member
    START WITH solution_id = :b2

          AND object_id = :b1
          AND member_id != 0
    CONNECT BY PRIOR solution_id = solution_id
           AND PRIOR member_object_id = object_id;

Here's the xplan for it:

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=10 Bytes=46
       0)

1    0   CONNECT BY (WITH FILTERING)
2    1     NESTED LOOPS
3    2       INDEX (SKIP SCAN) OF 'PK_XHQ_SOL_OBJECT_MEMBER' (UNIQU
       E) (Cost=12 Card=1 Bytes=26)

4    2       TABLE ACCESS (BY USER ROWID) OF 'XHQ_SOL_OBJECT_MEMBER
       '

5    1     NESTED LOOPS
6    5       BUFFER (SORT)
7    6         CONNECT BY PUMP
8    5       TABLE ACCESS (BY INDEX ROWID) OF 'XHQ_SOL_OBJECT_MEMBE
       R' (Cost=5 Card=10 Bytes=460)

9    8         INDEX (RANGE SCAN) OF 'PK_XHQ_SOL_OBJECT_MEMBER' (UN
       IQUE) (Cost=3 Card=10)