Re: Too many parsings

Scott Urman (surman_at_oracle.com) wrote:
: In article <9507092320.AA28919_at_softex1.cps.softex.br>, softdes_at_cps.softex.br (Software Design Informatica Ltda) writes:
: |> Hi, there!
: |>
: |> We are having an awful situation: we have an Oracle server version 7.0.16
: |> running on top of an SCO Unix that insists in parse every command each time
: |> the command is to be executed (number of parses identical to number of
: |> executions, according to tkprof).
: |>
: |> We have checked and the shared pool is large enough and we are using
: |> CURSOR_SPACE_FOR_TIME as TRUE and MAXOPENCURSORS as 1500.
: |>
: |> Still we cannot tell the server to cache the SQL commands accordingly and we
: |> think that this exactly what is killing performance.
: |>
: |> Can anyone tell us what the problem is? Can it be a bug in Oracle 7.0.16?
: |>
: |> P.S.: Yes, we have some BIG procedures, but those run only once a day (at
: |> the end of the day) and we elliminated that as a cache consuming factor,
: |> since the parsing problem is consistent during the whole day.
: |>
: |> Thanks in advance.
: |>
: |> Wanderley M. Ceschim
: |> wceschim_at_dcc.unicamp.br
: You don't mention where you are submitting these SQL commands from. If you
: are using SQL*Plus, then each new statement is executed in the same context
: area, requiring a new parse call each time. Most likely, the SQL text is in
: fact in the shared pool, but the parse call itself (which is what the parse
: count in tkprof is counting) is still executed. The best way to fix this is
: to use the precompilers or OCI, which allow you to have more control over how
: many context areas you use. Each context area (pointed to by a cda structure
: in OCI or a cursor cache entry in the precompilers) can hold the parsed form of
: a SQL statement, which can then be executed multiple times.

one other comment i can add is that if you are running procedures, you should pin them into the shared pool so they don't get aged out.

Johnny Chan
Independent Oracle Specialist Received on Tue Jul 11 1995 - 00:00:00 CEST