If it is the same table through a synonym, Oracle is smart enough to avoid a
hard parse and will simply do a soft parse.
But if the objects are different, as shown below (as per my original
comment) then a hard parse will happen.
Do not criticize someone until you walked a mile in their shoes,
In response to:
I'm afraid I can't agree with the statement that two different users
submitting the same SQL cause a hard parse.
I tried the following tests to confirm my comments.
Following through some simple SQL, the results are obvious.
Connected to Oracle8i Enterprise Edition Release
Yes, but I think Chris' point was if each statement was accessing the *same*
table (say, through a public synonym) then running it under a different user
wouldn't cause a hard parse.
Maybe I'll have time to check this myself tomorrow, right now I have to
leave.
Jay Miller
-Original
Its not really the same SQL though. Although the statement itself
is the same its a different table. That is part of the consideration
when oracle sends things for parsing or not.
-Original Message-
Sent: Wednesday, October 03, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
In
Considering a SQL statement that is the same, but different users. The
objects are different, thus a different execution plan is needed.
I know it is in the manuals somewhere that if a statement has another
parsing user, it is considered a different statement and not sharable.
Either way,
I'm afraid I can't agree with the statement that two different users
submitting the same SQL cause a hard parse. It is my
understanding of the process that part of the parsing occurs when
another user is submitting an identical SQL statement, but this
does not constitute a hard parse, just a
Alex ,
to answer your question ,
yes any index creation will force reparsing of the
SQL.
At least it did on my 8.1.6 on HP ;)
-RS
--- Hillman, Alex [EMAIL PROTECTED]
wrote:
!! Please do not post Off Topic to this List !!
You mean it invalidates SQL which has references to
the newly analyzed
!! Please do not post Off Topic to this List !!
If the statement is not exactly the same, the new statement will be
reparsed.
If you are executing it under a different user the statement will be
reparsed.
If you drop/create an index, it will invalidate the explain plan if that was
part of the
!! Please do not post Off Topic to this List !!
Chris list,
The last time I checked, an ANALYZE also invalidates
the SQL in the shared pool, to force a parse and
rebuild of the execution plan, on the next execution
of the SQL statement.
Regards,
Gaja
--- Christopher Spence [EMAIL PROTECTED]
!! 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
!! Please do not post Off Topic to this List !!
You mean it invalidates SQL which has references to the newly analyzed
objects, not all SQL in cache - right? Also are you sure that creating index
on table will invalidate SQL which references this table or view based on
this table?
Alex Hillman
!! Please do not post Off Topic to this List !!
If the Oracle executing user is different, the execution plan will be
different due to possibility of different objects and security.
Different sessions under the same user can share using bind variables.
Do not criticize someone until you walked
!! Please do not post Off Topic to this List !!
Hi Alex,
Yes, I mean invalidate the SQL that is referencing the
object(s) that was analyzed. The creation of the index
has the same effect. Which means after the index is
created, the next execution of the query to that
table, will be re-parsed,
!! Please do not post Off Topic to this List !!
Thanks, Chris. I've learned something.
Jon Walthour
-Original Message-
Sent: Friday, September 14, 2001 1:56 PM
To: Multiple recipients of list ORACLE-L
!! Please do not post Off Topic to this List !!
If the Oracle executing user is
!! 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
!! Please do not post Off Topic to this List !!
Chris,
I would just change that to the execution plan [may] be different. In most
cases, it will be the same since the objects and security won't change (even
though they could).
Henry
-Original Message-
Sent: Friday, September 14, 2001
!! 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
!! Please do not post Off Topic to this List !!
No takers so far - anybody?
Alex Hillman
-Original Message-
Sent: Thursday, September 06, 2001 4:30 PM
To: Multiple recipients of list ORACLE-L
Let's assume that SQL statement was parsed by user X. If this or another
user reexecute this
18 matches
Mail list logo
