Re: ORACLE7 reparses stored procedures all the time (and creates problems)

surman_at_wwsun10.us.oracle.com (Scott Urman) writes:
|> You can 'pin' a stored procedure in the shared pool. Check out
|> dbmspool.sql, found in $ORACLE_HOME/rdbms/admin. This script creates
|> a package called dbms_pool. One of the procedures in this package is
|> keep().
|>
|> y-tamura_at_nri.co.jp (Youichi Tamura) writes:
|> |>
|> |> I heard something utterly unbelievable from the local Oracle people.
|> |> I hope someone out there could prove them wrong.
|> |>
|> |> As we all know, ORACLE7 supports what's called stored procedures. One
|> |> of the advantages of adopting stored procedures is, as I understood it,
|> |> improved performance. They improve performance because ORACLE7 kernel
|> |> need not reparse a set of PL/SQL procedures once it is called, parsed,
|> |> and "stored" in the shared pool.
|> |>
|> |> Now the reality (we use ORACLE7 Server Release 7.0.15.4.2 - Production
|> |> With the procedural and distributed options, on HI-UX): we're in the
|> |> process of developing a large, mission-critical application system for
|> |> our client, using over a hundred stored procedures. We noticed a
|> |> performance problem as we slowly moved into production, examined sql
|> |> trace, and found that stored procedures are loaded into the shared
|> |> pool every time an application calls them (VERSION_COUNT of V$SQLAREA
|> |> simply keeps increasing). So the shared pool soon runs out of space,
|> |> flushing takes place, performance goes down. This happens for users
|> |> granted access to the stored procedures, not for the owner.
|> |>
|> |> Oracle says: "The procedure code (MPCODE) will be copied for each
|> |> execution,
|> |> as the original procedure is known as <owner name>.<proc name> and we are
|> |> comparing only <proc name>."
|> |>
|> |> They suggested we qualify every procedure with the owner name (and not
|> |> use synonyms for stored procedures) in each and every application program.
|> |> We said that's not an option because that would destroy the independence
|> |> of the application from the ORACLE schema. They said it's the "spec."
|> |> No fix.
|> |>
|> |> Is this true? If it is true, the new feature in ORACLE7 has very little
|> |> to offer. How primitive.
|> |>
|> |> # Honestly, I don't care if they call it "spec" or "bug" or "problem," so
|> |> # long as they offer a solution. More often than not, they don't.
|> |>
|> |> Again, we use ORACLE7 Server Release 7.0.15.4.2 - Production
|> |> With the procedural and distributed options, on HI-UX.
|> |> ---
|> |> Yoichi Tamura
|> |> y-tamura_at_nri.co.jp

Some other pieces of information that might also be helpful in this case follow:

As Scott mentions it is a good idea to make sure that you pin commonly used PL/SQL packages into the shared pool. This includes the PL/SQL support packages such as STANDARD and DBMS_STANDARD.

The usage of user.proc vs proc (via a synonym) as stated by the local Oracle people is another way to save space in the shared pool. I can't say for sure if it also causes the VERSION_COUNT to increase. It does save in not having to make a recursive call to look up a synonym definition and cache it. Granted it is not always a desirable solution, but it helps for now.

You may want to consider upgrading to 7.0.16 or 7.1.3 if it is available on your platform. Improvements have been made (and will continue to be made) concerning shared pool usage. For example, in 7.0.16 stored procedure calls with called with different arguments will not cause VERSION_COUNT to increment (ref. bug #174782). You may also want to refer your local Oracle people to bug #197975 as well (though it's not clear if this is affecting your environment -- the local support people might be better equipped to work with you in exploring that possibility if they have not already)

Hope this helps.

Roderick Manalac
Oracle Corporation Received on Tue Oct 11 1994 - 04:09:07 CET