Re: New to Oracle: DDL in EXECUTE IMMEDIATE question

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 Received on Tue Aug 29 2006 - 10:36:03 CDT