Board index Oracle

Anyway to speed up this pl/sql code?

Anyway to speed up this pl/sql code?

Post by G » Thu, 18 Apr 2002 05:39:04



Hi:

Do you think there is a way to optimize the following PL/SQL code (in
terms of performance)? An example of the parameter "idlist" could be
"1,2,3".

TIA.

Guang

------------
function getMethods(idlist in varchar2) return varchar2 is
  c             integer;
  dum           integer;
  retstr        varchar2(1024) := null;
  base_sql      varchar2(256) := 'select distinct method from XYZ '||
                                 'where method is not null and id in ';

begin
    c := dbms_sql.open_cursor;
    dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
    dbms_sql.define_column(c, 1, method, 128);
    dum := dbms_sql.execute(c);

    loop
      if dbms_sql.fetch_rows(c)>0 then
         dbms_sql.column_value(c, 1, method);
         -- doing something to set retstr here
      else
         dbms_sql.close_cursor(c);
         return retstr;
      end if;
    end loop;
end getMethods;
-------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by Daniel Morga » Thu, 18 Apr 2002 07:16:56


Yes. Though which of many possibilities depends on what version of Oracle.
Something you neglected to provide. Does the version you have contain
native dynamic SQL?

BTW: What is the point of 'retstr varchar2(1024) := null;'?

Daniel Morgan


> Hi:

> Do you think there is a way to optimize the following PL/SQL code (in
> terms of performance)? An example of the parameter "idlist" could be
> "1,2,3".

> TIA.

> Guang

> ------------
> function getMethods(idlist in varchar2) return varchar2 is
>   c             integer;
>   dum           integer;
>   retstr        varchar2(1024) := null;
>   base_sql      varchar2(256) := 'select distinct method from XYZ '||
>                                  'where method is not null and id in ';

> begin
>     c := dbms_sql.open_cursor;
>     dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
>     dbms_sql.define_column(c, 1, method, 128);
>     dum := dbms_sql.execute(c);

>     loop
>       if dbms_sql.fetch_rows(c)>0 then
>          dbms_sql.column_value(c, 1, method);
>          -- doing something to set retstr here
>       else
>          dbms_sql.close_cursor(c);
>          return retstr;
>       end if;
>     end loop;
> end getMethods;
> -------------


 
 
 
Top

Anyway to speed up this pl/sql code?

Post by G » Thu, 18 Apr 2002 11:09:41


Thanks for your reply.

1. The oracle version is 8.1.7.0.0 Enterprise Edition on Sun Solaris
2.8

2. If dbms_sql.fetch_rows(c) = 0 (that is, no rows in the cursor as
the result of "select"), then it will jump to

dbms_sql.close_cursor(c);
return retstr;

And with  'retstr varchar2(1024) := null;' we know that a null value
is returned in this case.

Guang


> Yes. Though which of many possibilities depends on what version of Oracle.
> Something you neglected to provide. Does the version you have contain
> native dynamic SQL?

> BTW: What is the point of 'retstr varchar2(1024) := null;'?

> Daniel Morgan


> > Hi:

> > Do you think there is a way to optimize the following PL/SQL code (in
> > terms of performance)? An example of the parameter "idlist" could be
> > "1,2,3".

> > TIA.

> > Guang

> > ------------
> > function getMethods(idlist in varchar2) return varchar2 is
> >   c             integer;
> >   dum           integer;
> >   retstr        varchar2(1024) := null;
> >   base_sql      varchar2(256) := 'select distinct method from XYZ '||
> >                                  'where method is not null and id in ';

> > begin
> >     c := dbms_sql.open_cursor;
> >     dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
> >     dbms_sql.define_column(c, 1, method, 128);
> >     dum := dbms_sql.execute(c);

> >     loop
> >       if dbms_sql.fetch_rows(c)>0 then
> >          dbms_sql.column_value(c, 1, method);
> >          -- doing something to set retstr here
> >       else
> >          dbms_sql.close_cursor(c);
> >          return retstr;
> >       end if;
> >     end loop;
> > end getMethods;
> > -------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by Suni » Thu, 18 Apr 2002 13:23:17


    I have not tried this but ,
    If you make the "idlist" an object type you can select from it using the
table cast operator. Then you can rewrite your dynamic sql as a static
cursor accepting a parameter. ( static sql being faster). Next you can do
bulk fetch from the opened cursor and operate on it.

    Additions/corrections from the experts are welcome.

Sunil.


Quote:> Hi:

> Do you think there is a way to optimize the following PL/SQL code (in
> terms of performance)? An example of the parameter "idlist" could be
> "1,2,3".

> TIA.

> Guang

> ------------
> function getMethods(idlist in varchar2) return varchar2 is
>   c integer;
>   dum           integer;
>   retstr varchar2(1024) := null;
>   base_sql varchar2(256) := 'select distinct method from XYZ '||
>          'where method is not null and id in ';

> begin
>     c := dbms_sql.open_cursor;
>     dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
>     dbms_sql.define_column(c, 1, method, 128);
>     dum := dbms_sql.execute(c);

>     loop
>       if dbms_sql.fetch_rows(c)>0 then
>          dbms_sql.column_value(c, 1, method);
>          -- doing something to set retstr here
>       else
>          dbms_sql.close_cursor(c);
>          return retstr;
>       end if;
>     end loop;
> end getMethods;
> -------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by G » Fri, 19 Apr 2002 02:51:12


The idea may work in some situation, but not in mine here. I did a
quick test and looked at the explain plan of

select distinct method
from XYZ
where method is not null
and id in ( select * from THE ( select cast( in_list ('1') as
                                    mytableType ) from dual ) a );

The cost is huge (3436410) and it is way too slow in my case.

Anyway, thanks for your input.

Guang


> I have not tried this but ,
>     If you make the "idlist" an object type you can select from it using the
> table cast operator. Then you can rewrite your dynamic sql as a static
> cursor accepting a parameter. ( static sql being faster). Next you can do
> bulk fetch from the opened cursor and operate on it.

>     Additions/corrections from the experts are welcome.

> Sunil.



> > Hi:

> > Do you think there is a way to optimize the following PL/SQL code (in
> > terms of performance)? An example of the parameter "idlist" could be
> > "1,2,3".

> > TIA.

> > Guang

> > ------------
> > function getMethods(idlist in varchar2) return varchar2 is
> >   c integer;
> >   dum           integer;
> >   retstr varchar2(1024) := null;
> >   base_sql varchar2(256) := 'select distinct method from XYZ '||
> >          'where method is not null and id in ';

> > begin
> >     c := dbms_sql.open_cursor;
> >     dbms_sql.parse(c, base_sql||'('||idlist||')', dbms_sql.native);
> >     dbms_sql.define_column(c, 1, method, 128);
> >     dum := dbms_sql.execute(c);

> >     loop
> >       if dbms_sql.fetch_rows(c)>0 then
> >          dbms_sql.column_value(c, 1, method);
> >          -- doing something to set retstr here
> >       else
> >          dbms_sql.close_cursor(c);
> >          return retstr;
> >       end if;
> >     end loop;
> > end getMethods;
> > -------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by Pablo Sanche » Fri, 19 Apr 2002 08:09:39


Hi Guang,

A quick question on your problem before I recommend anything ... how
much data are you planning typically returning?  Also, any reason why
we have a 'DISTINCT'?  Do we really need that?  If not, no biggie but
let me know on that too.

Thx!
--
Pablo Sanchez, High-Performance Database Engineering

Available for short-term and long-term contracts


> The idea may work in some situation, but not in mine here. I did a
> quick test and looked at the explain plan of

> select distinct method
> from XYZ
> where method is not null
> and id in ( select * from THE ( select cast( in_list ('1') as
>                                     mytableType ) from dual ) a );

> The cost is huge (3436410) and it is way too slow in my case.

> Anyway, thanks for your input.

> Guang




- Show quoted text -

> > I have not tried this but ,
> >     If you make the "idlist" an object type you can select from it
using the
> > table cast operator. Then you can rewrite your dynamic sql as a
static
> > cursor accepting a parameter. ( static sql being faster). Next you
can do
> > bulk fetch from the opened cursor and operate on it.

> >     Additions/corrections from the experts are welcome.

> > Sunil.



> > > Hi:

> > > Do you think there is a way to optimize the following PL/SQL
code (in
> > > terms of performance)? An example of the parameter "idlist"
could be
> > > "1,2,3".

> > > TIA.

> > > Guang

> > > ------------
> > > function getMethods(idlist in varchar2) return varchar2 is
> > >   c integer;
> > >   dum           integer;
> > >   retstr varchar2(1024) := null;
> > >   base_sql varchar2(256) := 'select distinct method from XYZ '||
> > >          'where method is not null and id in ';

> > > begin
> > >     c := dbms_sql.open_cursor;
> > >     dbms_sql.parse(c, base_sql||'('||idlist||')',
dbms_sql.native);
> > >     dbms_sql.define_column(c, 1, method, 128);
> > >     dum := dbms_sql.execute(c);

> > >     loop
> > >       if dbms_sql.fetch_rows(c)>0 then
> > >          dbms_sql.column_value(c, 1, method);
> > >          -- doing something to set retstr here
> > >       else
> > >          dbms_sql.close_cursor(c);
> > >          return retstr;
> > >       end if;
> > >     end loop;
> > > end getMethods;
> > > -------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by G » Sat, 20 Apr 2002 00:07:54


Hi:

Typical returned string is less than 128 bytes in length.

Table XYZ has about 600000 rows.

Column METHOD has type VARCHAR2(128).

DISTINCT is needed here, there is no unique constraint on that column.

Guang


> Hi Guang,

> A quick question on your problem before I recommend anything ... how
> much data are you planning typically returning?  Also, any reason why
> we have a 'DISTINCT'?  Do we really need that?  If not, no biggie but
> let me know on that too.

> Thx!
> --
> Pablo Sanchez, High-Performance Database Engineering

> Available for short-term and long-term contracts



> > The idea may work in some situation, but not in mine here. I did a
> > quick test and looked at the explain plan of

> > select distinct method
> > from XYZ
> > where method is not null
> > and id in ( select * from THE ( select cast( in_list ('1') as
> >                                     mytableType ) from dual ) a );

> > The cost is huge (3436410) and it is way too slow in my case.

> > Anyway, thanks for your input.

> > Guang



> > > I have not tried this but ,
> > >     If you make the "idlist" an object type you can select from it
>  using the
> > > table cast operator. Then you can rewrite your dynamic sql as a
>  static
> > > cursor accepting a parameter. ( static sql being faster). Next you
>  can do
> > > bulk fetch from the opened cursor and operate on it.

> > >     Additions/corrections from the experts are welcome.

> > > Sunil.



> > > > Hi:

> > > > Do you think there is a way to optimize the following PL/SQL
>  code (in
> > > > terms of performance)? An example of the parameter "idlist"
>  could be
> > > > "1,2,3".

> > > > TIA.

> > > > Guang

> > > > ------------
> > > > function getMethods(idlist in varchar2) return varchar2 is
> > > >   c integer;
> > > >   dum           integer;
> > > >   retstr varchar2(1024) := null;
> > > >   base_sql varchar2(256) := 'select distinct method from XYZ '||
> > > >          'where method is not null and id in ';

> > > > begin
> > > >     c := dbms_sql.open_cursor;
> > > >     dbms_sql.parse(c, base_sql||'('||idlist||')',
>  dbms_sql.native);
> > > >     dbms_sql.define_column(c, 1, method, 128);
> > > >     dum := dbms_sql.execute(c);

> > > >     loop
> > > >       if dbms_sql.fetch_rows(c)>0 then
> > > >          dbms_sql.column_value(c, 1, method);
> > > >          -- doing something to set retstr here
> > > >       else
> > > >          dbms_sql.close_cursor(c);
> > > >          return retstr;
> > > >       end if;
> > > >     end loop;
> > > > end getMethods;
> > > > -------------

 
 
 
Top

Anyway to speed up this pl/sql code?

Post by Pablo Sanche » Sat, 20 Apr 2002 02:21:42


Guang,

Seems that you're a pretty capable coder so I'll rough it out and you
can debug it ... :)

The key is to use BULK COLLECT's to grab your data and DISTINT the
data yourself - rather than letting the instance do it.  I'll assume
that 'id' column is indexed.

If you really want fast speeds with somewhat of a brittle solution,
you can 'cover' your query by indexing the columns in the base table
that comprise the query.  I think Oracle is now smart enough (COB
wasn't before) to walk the index tree to retrieve its data.

Here's what you want to try:
====================================
Assumptions:  idlist is comma separated
---------------------------------------
Create the following TYPE in your instance:

CREATE OR REPLACE TYPE method_collection IS TABLE OF VARCHAR2(128);
------------------------------------------
l_sql_statement            VARCHAR2(1024);
l_method_tbl               method_collection;

TYPE l_type                IS TABLE OF VARCHAR2(128) INDEX BY
BINARY_INTEGER;
l_dedup                    l_type;
l_counter                  PLS_INTEGER;
...

BEGIN
  l_sql_statement := 'BEGIN SELECT method '||
                     'BULK COLLECT INTO :1 '||
                     'FROM xyz ' ||
                     'WHERE method IS NOT NULL '||
                     'AND   id IN ('|| idlist ||'); '||
                     'END;';

  EXECUTE IMMEDIATE l_sql_statement OUT l_method_tbl;

  -- Do we have data?  If so, let's DISTINCT here

  IF l_method_tbl.EXISTS(1) THEN
     FOR i in 1..l_method_tbl.COUNT LOOP
        l_dedup(l_method_tbl(i)) := l_method_tbl(i);
     END LOOP;

     l_counter := l_dedup.FIRST;
     WHILE l_counter <= l_dedup.LAST LOOP
        IF l_counter != l_cache.LAST THEN
           retstr := retstr || l_dedup(l_counter)||',';
           l_counter := l_dedup.NEXT(l_counter);
        ELSE
           retstr := retstr || l_dedup(l_counter);
           l_counter := l_counter + 1;
     END LOOP;
     RETURN retstr;
  ELSE
     RETURN NULL;
  END IF;

...

--
Pablo Sanchez, High-Performance Database Engineering

Available for short-term and long-term contracts

 
 
 
Top

1. NT speeds-ups?

We have a client running on NT and an Online 7.2 server running on UNIX
over a slow WAN. We need to squeeze every bit of performance we can out
of the communications link. We are doing the obvious things to ensure
the database runs fast and trying to move some processing into stored
procedures. Replication is not an option due to support and maintenance
cost and purchase expense of additional equipment, and culture of
centralized data management.

Therefore, has anyone got any tips about:

1) Reducing the latency in the client/server connection.
2) Packing more bits into the same space on the line?

Things we have thought of:

1) Larger TCP/IP buffers on NT. -- Can this be done? how?
2) Using more compact datatypes.
3) Ensuring we are doing select col1...coln and not select *.

Any other ideas? and successful results that can be shared?

2. ODBC API calls from VB

3. TTable time unacceptable, anyway to speed up?

4. Log file SQL 2000

5. sql vs. pl/sql: speed issue

6. sp_columns returns different information....

7. Maxi code for UPS

8. How to identify shared memory segs

9. Anyway to write code in VB for backup?

10. HELP : PL/SQL SPEED Functions

11. Converting PL/SQL - Code into T-SQL

12. *** PL/SQL , SQL Code Generator ***

13. !-!-! PL/SQL , SQL Code Generator !-!-!


All times are UTC
Board index