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: New to Oracle: DDL in EXECUTE IMMEDIATE question

Re: New to Oracle: DDL in EXECUTE IMMEDIATE question

From: <google_at_easiesttoremember.com>
Date: 29 Aug 2006 09:07:19 -0700
Message-ID: <1156867639.352647.209320@m79g2000cwm.googlegroups.com>

bdbafh_at_gmail.com wrote:
> google_at_easiesttoremember.com wrote:
> > Don't cringe just yet!!
> >
> > I'm new to Oracle (but not databases). I'm fully aware of performance
> > problems with dynamic sql and I never - EVER - use DDL dynamically in
> > an application.... and I'm not planning to now.
> >
> > Keeping the above in mind, I have a purely technical question.
> >
> > Does it make a difference whether a table, view, or stored procedure is
> > created with "EXECUTE IMMEDIATE"? I'm not talking about executing the
> > DDL from within an application every time I need the object... I mean,
> > executing it once and only once... EVER. After the object is created,
> > when I then create an application that uses the previously (once only)
> > created object, does this object have poorer performance than those
> > created in a more customary fasion?
> >
> > Challenge: See if you can answer the question without trying to get
> > into a discussion of *why* one would even think about doing it. I'll
> > be happy to provide that, but after reading the posts here, I'd prefer
> > to not get drawn off target into a discussion that doesn't answer the
> > primary question. The answer to the question will determine my
> > direction forward in my project. I am not an advocate of dynamic sql,
> > especially DDL, so you don't have to try to convince me it's generally
> > a bad idea. My question is specifically about performance *AFTER* the
> > one and only one time creation.
> >
> > Without going too deeply into why I'm asking the question, I'll just
> > say (for now, unless you really really want to know), that because of
> > the unique nature of my current project, it's actually easier and
> > quicker to create my objects using EXECUTE IMMEDIATE than not. Just
> > trust me, for the moment, that it's easier. To comfort those of you
> > biting your nails right now, let me say that I will most definitely not
> > create the objects with EXECUTE IMMEDIATE if it hampers performance
> > later on.
> >
> > Thanks!
>
> How about you run a 10046 trace of each case (waits=>true), run tkprof
> against the statements and compare them?
>
> No discussion required.
>
> -bdbafh

Thanks for the reply. As I am new to Oracle, it will take some time for me to find, learn, and use the tool(s) you mention (and figure out what a 10046 trace is). Not that I'm not willing to do this, I was hoping someone just knew the answer. My DBA said that there's a huge and horrible difference, but he's given reason to show that he doesn't always know what he's saying. I believe he may be applying the "dynamic sql is bad and slow" idea a little too far without really knowing for sure. I've only been involved in Oracle for 2 days now and have already found him wrong in 2 significant cases, so I thought I'd ask the experts here. From the 2 replies I've received so far, it's looking like he may be wrong yet again... though I'm still holding judgement until someone knows for sure or until I can find time to find, learn, and use the tools you're talking about. Received on Tue Aug 29 2006 - 11:07:19 CDT

Original text of this message

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