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: Alex Ivascu <alexdivascu_at_hotmail.com>
Date: Tue, 6 Apr 2004 09:57:27 -0700
Message-ID: <BjBcc.2183$tA1.1745@fed1read05>


Daniel, Thanks.



Here's the table DDL:

create table sol_object_member
(
  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 0
not null,
  tag_attribute_code_type varchar2(30 byte) default 'tag_attribute_type',
  tag_attribute_code_key   number(10),
  tag_flg                  number(1)            default 0,
  all_access_flg           number(1),
  updt_xhquserid           number(10),
  updt_timestamp           date

)
tablespace xhq_data1
pctused 0
pctfree 35
initrans 1
maxtrans 255
storage (
            initial          45m
            minextents       1
            maxextents       2147483645
            pctincrease      0
            buffer_pool      default
           )

logging
nocache
noparallel
monitoring;

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
           )

noparallel;

This is the procedure in question. It is called by the another one:

CREATE OR REPLACE PROCEDURE "SOL_BUILD_NAMESPACE_FWD" (v_i_solution_id in raw,
                                                         v_i_object_id   in
number )
is
   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;

   type pathlist is table of varchar2(2000)     index by binary_integer;
   v_pathholder pathlist;
   cursor c1 is
        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;

   insert into sol_static_namespace
   (solution_id, object_id, member_id, path)     values
   (v_i_solution_id, v_i_object_id, 0, v_path);    v_pathholder(0):=v_path;
   v_prev_level:=0;
   open c1;
   loop
       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;
       -- 

dbms_output.put_line('v_level='||to_char(v_level)||',v_oid='||to_char(v_oid) ||

"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 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 '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
>
> Daniel
Received on Tue Apr 06 2004 - 11:57:27 CDT

Original text of this message

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