Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: what else can I do here?

Re: what else can I do here?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 6 Apr 2004 07:15:40 -0700
Message-ID: <3722db.0404060615.70fe253a@posting.google.com>


> 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)

It would be helpful for us to know the definition of the indexes used (in particular "XHQ_SOL_OBJECT_MEMBER" and "'PK_XHQ_SOL_OBJECT_MEMBER", and also a bit about the data in these tables (in particular for the columns used by the query). I also notice that you use a SKIP SCAN index search of 'PK_XHQ_SOL_OBJECT_MEMBER', which is always slower than a straight index RANGE SCAN. One way to avoid this would be to create a new proper index. If this query is included in a PL/SQL procedure, run DBMS_PROFILER to be sure that it's slow where you think. If you could post the procedure, we could also possibly give some hints on how to speed the PL/SQL part (collections, bulk collects, ...).

HTH Daniel Received on Tue Apr 06 2004 - 09:15:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US