Okay, here is how it works:

If you issue a completely new query, Oracle has to do what is called a hard parse: It reads the SQL string, checks permissions for the accessed objects, statistics about table data size and distribution, existence of indices, and generates the execution plan. This is expensive. For simple queries, this takes more time than the execution itself. In a web application (or any other OLTP scenario) you do not want this to happen more than necessary.

The resulting execution plan is cached in Oracle's server memory. If you issue the same query again, it will only incur a soft parse. It will just find the already existing execution plan and reuse it. This is what happens in the OP's program, and it is much better than a hard parse.

The first priority is to reduce hard parses to soft parses. To do this, the SQL statement must be identical, so you have to use bind variables.

Now, you can take it a little further, by preparing the statement only once, and executing it many times. In this case, you will have only one parse (hopefully a soft parse) and subsequent executions run without any parsing (no parse). Obviously, that is the best case. Using prepare_cached can help you with that.

In reply to Re^2: Question About Proper Use of Bind Variables in Perl DBI Scripts by Thilosophy
in thread Question About Proper Use of Bind Variables in Perl DBI Scripts by JPD

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.