CACHE of SQL in oracle

Question

How oracle CACHE a Query (SQL), Query execution contains the following steps. 1. PARSE 2. Execute 3. Fetch

in First step oracle checks if query exists in CACHE (Shared Pool) or not (It will be exists if query is identical and based on LRU), if exists then PARSING will be skipped and execution will start. So to make query performance intensive we must use bind variables and use the identical sql queries.

But in Parsing oracle verifies authentication also (User Access), if multiple users are using the same query, how oracle skip/use parsing?

Answer 1

Usual practice in Oracle is to create stored procedures with definer rights which means that the queries are executed with privileges of their definer, despite of who calls them. That's why cache works well.

If you create a procedure or package with invoker rights (authid current_user), then the queries will be parsed for each invoker separately.

See Invoker Rights Versus Definer Rights for details.

Answer 2

The parsing of a query is not tied to a user, it is dependent on the query. Note that an exact character for character match is required. Blanks and comments in a query will cause it to miss the Shared Pool match.

The parse-tree is then used in the generation of an execution plan. If the same schema is used in the new query as the matched query then the existing execution plan is used.

You can test this by creating multiple schema, one with a small amount of data and one with a large amount. Then analyze all the tables. Take a look at the execution plans for the same query with vastly different amounts of data. This will show the different execution plans for the same query.

Now run the query a large amount of times and check the amount of time that it takes for the first and then subsequent executions. Use Oracle Trace and look in the left hand pain for the "Re-Parse" frequency. This can also be gleaned from some of the dictionary tables.

Take a look at The Oracle documentation on using Oracle Trace

Answer 3

First step oracle checks if query exists in CACHE (Shared Pool) or not (It will be exists if query is identical and based on LRU), if exists then PARSING will be skipped and execution will start. So to make query performance intensive we must use bind variables and use the identical sql queries.

This is actual process when you execute a query on Oracle:

1. Parsing Steps
   1. Syntax Check
   2. Semantic Analysis
   3. Has the query been executed in some other session?
2. Hard Parse
   1. Parse
   2. Optimize
   3. Generate the plan for the query.

If the answer to #1.3 is yes - Oracle skips the hard parse portion, and uses the existing query plan.

For more info:
* AskTom: Difference between soft parse and hard parse
* Bind variables - The key to application performance