Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: what else can I do here?
Daniel, Thanks.
solution_id raw(16) not null, object_id number(10) not null, member_id number(10) not null,member_type_code_type varchar2(30 byte) default 'obj_mbr_type' not null,
member_type_code_key number(10) not null, member_name varchar2(80 byte) not null, member_desc varchar2(256 byte), member_cmpnt_catalog_id raw(16), member_cmpnt_id number(10), member_cmpnt_rev number(20), member_solution_id raw(16), member_object_id number(10), data_type_code_type varchar2(30 byte) default 'data_type', data_type_code_key number(10), alias_name varchar2(80 byte), proxy_flg number(1) default 0, proxy_object_id number(10), clctn_mbr_flg number(1) default 0, hidden_flg number(1) default 0not null,
tag_attribute_code_key number(10), tag_flg number(1) default 0, all_access_flg number(1), updt_xhquserid number(10), updt_timestamp date
initial 45m minextents 1 maxextents 2147483645 pctincrease 0 buffer_pool default )
alter table sol_object_member add (
constraint pk_sol_object_member primary key (solution_id, object_id,
member_id)
using index
tablespace indx1
pctfree 10
initrans 2
maxtrans 255
storage (
initial 64k minextents 1 maxextents 2147483645 pctincrease 0 ));
create unique index pk_sol_object_member on sol_object_member
(solution_id, object_id, member_id)
logging
tablespace indx1
pctfree 10
initrans 2
maxtrans 255
storage (
initial 64k minextents 1 maxextents 2147483645 pctincrease 0 buffer_pool default )
v_i_object_id innumber )
v_oid number(10); v_mid number(10); v_poid number(10); v_moid number(10); v_mname varchar2(80); v_oname varchar2(80); v_level number(3); v_alias varchar2(80); v_path varchar2(256); v_leaf_name varchar2(80); v_prev_level number(3); v_prev_oid number(10); v_root_name varchar(80); v_code_key number(10); v_m_code_key number(10); v_count integer:=0;
select level, alias_name, object_id, member_id, member_object_id, member_name, member_type_code_key from sol_object_member start with solution_id=v_i_solution_id and object_id = v_i_object_id and member_id != 0 connect by prior solution_id=solution_id and prior member_object_id=object_id;begin
select object_name, object_type_code_key into v_root_name, v_code_key from sol_object
where solution_id = v_i_solution_id and object_id = v_i_object_id; v_path:= '::'||v_root_name;
v_count:=v_count+1; if v_count>10000 then commit; v_count:=0; end if; fetch c1 into v_level, v_alias, v_oid, v_mid, v_moid, v_mname, v_m_code_key; exit when c1%NOTFOUND; --
"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message news:3722db.0404060615.70fe253a_at_posting.google.com...
> > Hi gang. Any help on this will be welcomed!!! > > > > I have a very dynamic application, and am trying to tune a procedurethat
> > 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 mlnrows...
> > (simple delete table_name where indexkey=value) > > > > The main query that seems to take the most time to run, because of about2
> > 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 'SOL_OBJECT_MEMBE > > R' (Cost=5 Card=10 Bytes=460) > > > > 9 8 INDEX (RANGE SCAN) OF 'PK_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 > > DanielReceived on Tue Apr 06 2004 - 11:57:27 CDT