Re: Parse count and elapsed time

In article <36503db6.0203290912.2b22c27e_at_posting.google.com>, anil.chada_at_oracle.com says...
>
>Hi --
>
>We ran trace for one long running program, and i am little bit
>confused by looking at the statistics for one SQL statement.
>Following is the TKPROF output for the SQL statement.
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 13910 139.38 1517.04 0 0 0
> 0
>Execute 13910 7.43 8.87 0 0 0
> 0
>Fetch 13910 254.43 315.83 1126 35943544 0
> 13910
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 41730 401.24 1841.74 1126 35943544 0
> 13910
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 183 (recursive depth: 1)
>
>If we look at the above numbers, oracle spent most of the time parsing
>the SQL statement.
>I thought, if there are no library cache misses (TKPROF has cache
>misses as 1) then oracle should parse the statement only once.
>I am just wondering why oracle is parsing the statement so many time?
>Is there anyway i can fix the problem?
>
>The SQL statement has three bind variable.
>
>Thanks for your time and help
>Anil

There are 2 (well, sort of three) types of parsed in Oracle:

o hard parse (done the first time a statement is ever parsed during the life of an instance)
o soft parse (done every other time)
o a "softer soft parse"

A hard parse is the most expensive.
A soft parse less expensive but expensive none the less. A softer soft parse (achievable via session_cached_cursors) is even less expensive

The LEAST expensive -- having the application parse the SQL statement ONCE per session -- not per execution.

Ask the developer this:

If I asked you to write 13,910 lines into a file, would you code:

1. for i in 1 .. 13910 loop open file in append mode write a line close file end loop

or

b)

   open file
   for i in 1 .. 13910
   loop

      write a line
   end loop
   close file

If they opt for (a), get a new developer. If they opt for (b) -- the correct answer -- then ask them why they are doing (a) with respect to SQL statements?

They must be doing:

   parse
   bind
   execute
   close

instead of:

   if ( first_time )

      parse
   end if
   bind
   execute

• this is optional as program termination will achieve the same: if ( last_time ) close end if

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp