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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Which way is moer faster for this?

Re: Which way is moer faster for this?

From: Daniel Morgan <damorgan_at_exesolutions.com>
Date: Sun, 09 Jun 2002 21:10:58 -0700
Message-ID: <3D0426D2.EFC58C32@exesolutions.com>


C Chang wrote:

> Daniel Morgan wrote:
> >
> > C Chang wrote:
> >
> > > I have a procedure to return a set of records such as p_b, p_a .. Among
> > > then, one of the record is return by using another record. so My
> > > question is that in the BEGIN section of the Procedure, Do I just use
> > > another
> > > p_b(count):= v_b; <-- v_b is the select result
> > > BEGIN
> > > SELECT a INTO v_a FROM table_B WHERE b = v_b;
> > > p_a(count):= v_a;
> > > EXCEPTION
> > > WHEN OTHERS THEN
> > > p_a(count):= NULL;
> > > END;
> > >
> > > -- Where v_b is the retrun value of the main select process of the
> > > procedure or I need to put the SELECT a INTO v_a FROM table_B WHERE b =
> > > v_b; in a function call
> > > so that for p_a ( which is the return variable), I just use the
> > > p_b(count):= v_b;
> > > p_a(count):= functionCall_a( v_b);
> > >
> > >
> > > p_# is the return variable.
> > >
> > > C Chang
> >
> > The one that is benchmarked on your system with your platform (you don't
> > tell us), your operating system (you don't tell us), your version of Oracle
> > (you don't tell us), your initSID.ora configuration (you don't supply it)
> > and your user/resource loading (you don't tell us), and we have no idea what
> > your function does which could affect it too.
> >
> > In other words, and not to be unhelpful, this is not something anyone can
> > tell you off the top of their head. We find out these things by trying them
> > in test environments and that is what you should do for yourself. Be sure to
> > run each procedure at least three times. Don't just run each one once and
> > take the faster. And be sure to EXPLAIN PLAN them too.
> >
> > Daniel Morgan
> Sorry for those forgotten. The ORacle 8.1.6 is installed on a NT 4 box
> wiht SP6 equipped under Dell machine with 2 CPU (propably 700Mhz) with 1
> G memory. I am no Oracle Developer, just a web developer occasionally
> doing the fix for oracle. So I have no idea what the user/resource and
> I can not tell what the detail of initSID.ora is ( althought I can go
> into machine and dig it out later). Besides, I heard about the EXPLAIN
> PLAN, but I do not think anyone in our group ( 2 persons now) even our
> FORMER real Oracle developer ever used it. I just followed the
> previous coding pattern to repeat. Thank you for your reply.
>
> C Chang

Based on what you just wrote my working assumption is that there isn't a chance of any of you tuning anything. If you want to work in Oracle you'd best begin your education before you make a total mess of the project.

  1. Buy a copy of Tom Kyte's book 'Expert one-on-one Oracle' and read it cover-to-cover.
  2. Go to http://technet.oracle.com and search for 'EXPLAIN PLAN' and 'TKPROF'

Read everything you find and don't let a single piece of SQL go anywhere without knowing what it is doing.

If you can't answer the question: "When do you slow things down by building an index?" with at least a half-dozen answer don't do any more work until you can.

Daniel Morgan Received on Sun Jun 09 2002 - 23:10:58 CDT

Original text of this message

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