RE: When optimizer reevaluate SQL statement

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Fri, 14 Sep 2001 12:01:01 -0700
Message-ID: <F001.0038F388.20010914120935@fatcity.com>

!! Please do not post Off Topic to this List !!

There are two types of parsing remember, soft and hard.

Soft simply checks to see if anything has really changed.

Hard is when it allocates memory in the shared pool and goes through the entire process of parsing.

Of course the hard parses are the big problems.

If the statement is another session, it simply does a soft parse as the statement is the exact same and all the security and ownership is the same.

If the user is different, it is mostly true that the objects are different, so it will need to do a hard parse to generate a plan under this user.

When you parse, you store the explain plan, let's say user a has a table which has 1 million rows, an index is very efficient, so it uses this method with the stored plan. Another sql which is typed word for word the same but as a different authenticated user logs in, but the table which has the same name is only 1000 rows, which a full table scan is probably better, so it stores it's explain plan. Now where there are synonyms, it still generates a new plan and parse phase the first time.

Now where you have largely skewed values histograms may provide better performance than bind variables and make it all worth while to have it reparse constantly.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Friday, September 14, 2001 3:35 PM To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

It still has to reparse some stuff for each new user: private synonyms (is it really the same object being queried), user privileges.

Henry

-----Original Message-----
Sent: Friday, September 14, 2001 12:25 PM To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be reparsed.

I'm puzzled. I always thought that different users submitting the identical SQL statement would use the same plan and not need to be reparsed. I understood that to be part of the reasoning behind bind variables and the big advantage of cursor sharing. Please explain.

Jon Walthour
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Walthour, Jon (GEAE, Compaq)
INET: Jon.walthour_at_ae.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Henry Poras
INET: Henry.Poras_at_ctp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------