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: Parse count and elapsed time

Re: Parse count and elapsed time

From: anil chada <anil.chada_at_oracle.com>
Date: 30 Mar 2002 04:46:33 -0800
Message-ID: <36503db6.0203300446.7f78fbc8@posting.google.com>


Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a82a2p013qe_at_drn.newsguy.com>...
> 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:
>
> a) 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



Tom --

Thanks for the detailed expaination.
The SQL statement is part of package provided with Oracle APPS (ERP applications). Developer is calling this package within his program. We can't really change the package provided by oracle. I am just wondering is there anyway we can minimize the time spent on parsing. Do you have any ideas?

Also if you don't mind, can you explain me the difference between "Soft Parse" and "Softer Soft Parse"

Thanks for your time.

Anil Received on Sat Mar 30 2002 - 06:46:33 CST

Original text of this message

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