RE: When optimizer reevaluate SQL statement
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, 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: Wednesday, October 03, 2001 6:10 PM To: Multiple recipients of list ORACLE-L 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 Message- Sent: Wednesday, October 03, 2001 4:51 PM To: Multiple recipients of list ORACLE-L 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 8.1.6.3.0 Connected as sys SQL create user test1 identified test1 default tablespace users temporary tablespace temp; User created SQL create user test2 identified by test2 default tablespace users temporary tablespace temp; User created SQL grant connect, resource to test1, test2; Grant succeeded SQL grant plustrace to test1, test2; Grant succeeded SQL create table test1.employee as select * from cspence.employee sample(.5); Table created SQL create table test2.employee as select * from cspence.employee sample(.5); Table created SQL connect test1/test1@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test1 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / Hard Parse Count: 0 SQL connect test2/test2@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test2 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / As they say, the proof is in the pudding. I use PL/SQL Developer, and didn't use Auto trace as the PlusTrace role may have implied. Hope this helps clarify things. 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: Sunday, September 23, 2001 10:45 AM To: Multiple recipients of list ORACLE-L 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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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,
RE: When optimizer reevaluate SQL statement
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 8.1.6.3.0 Connected as sys SQL create user test1 identified test1 default tablespace users temporary tablespace temp; User created SQL create user test2 identified by test2 default tablespace users temporary tablespace temp; User created SQL grant connect, resource to test1, test2; Grant succeeded SQL grant plustrace to test1, test2; Grant succeeded SQL create table test1.employee as select * from cspence.employee sample(.5); Table created SQL create table test2.employee as select * from cspence.employee sample(.5); Table created SQL connect test1/test1@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test1 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / Hard Parse Count: 0 SQL connect test2/test2@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test2 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / As they say, the proof is in the pudding. I use PL/SQL Developer, and didn't use Auto trace as the PlusTrace role may have implied. Hope this helps clarify things. 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: Sunday, September 23, 2001 10:45 AM To: Multiple recipients of list ORACLE-L 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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL
RE: When optimizer reevaluate SQL statement
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 Message- Sent: Wednesday, October 03, 2001 4:51 PM To: Multiple recipients of list ORACLE-L 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 8.1.6.3.0 Connected as sys SQL create user test1 identified test1 default tablespace users temporary tablespace temp; User created SQL create user test2 identified by test2 default tablespace users temporary tablespace temp; User created SQL grant connect, resource to test1, test2; Grant succeeded SQL grant plustrace to test1, test2; Grant succeeded SQL create table test1.employee as select * from cspence.employee sample(.5); Table created SQL create table test2.employee as select * from cspence.employee sample(.5); Table created SQL connect test1/test1@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test1 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / Hard Parse Count: 0 SQL connect test2/test2@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test2 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / As they say, the proof is in the pudding. I use PL/SQL Developer, and didn't use Auto trace as the PlusTrace role may have implied. Hope this helps clarify things. 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: Sunday, September 23, 2001 10:45 AM To: Multiple recipients of list ORACLE-L 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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: When optimizer reevaluate SQL statement
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 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 8.1.6.3.0 Connected as sys SQL create user test1 identified test1 default tablespace users temporary tablespace temp; User created SQL create user test2 identified by test2 default tablespace users temporary tablespace temp; User created SQL grant connect, resource to test1, test2; Grant succeeded SQL grant plustrace to test1, test2; Grant succeeded SQL create table test1.employee as select * from cspence.employee sample(.5); Table created SQL create table test2.employee as select * from cspence.employee sample(.5); Table created SQL connect test1/test1@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test1 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / Hard Parse Count: 0 SQL connect test2/test2@jetfuel Connected to Oracle8i Enterprise Edition Release 8.1.6.3.0 Connected as test2 select * from employee where emp_id = 5 / Hard Parse Count: 1 select * from employee where emp_id = 5 / As they say, the proof is in the pudding. I use PL/SQL Developer, and didn't use Auto trace as the PlusTrace role may have implied. Hope this helps clarify things. 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: Sunday, September 23, 2001 10:45 AM To: Multiple recipients of list ORACLE-L 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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL
RE: When optimizer reevaluate SQL statement
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, if I don't find it when I get in tomorrow, I will do a test in my lab and show concrete results. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 9/23/01 10:45 AM 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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
RE: When optimizer reevaluate SQL statement
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 check of permissions, synonyms, etc. If there was a hard parse everytime a different user submitted identical SQL it would negate much of the benefit of using bind variables. Thus the answer is that there is reparsing, but not the kind that really does damage to performance. Regards, Chris Gait On 14 Sep 2001, at 9:55, Christopher Spence wrote: Date sent: Fri, 14 Sep 2001 09:55:33 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California !! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
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 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 -Original Message- Sent: Friday, September 14, 2001 11:40 AM To: Multiple recipients of list ORACLE-L !! 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] wrote: !! 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 chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. 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: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml
RE: When optimizer reevaluate SQL statement
!! 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 chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. 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: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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] wrote: !! 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 chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. 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: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: When optimizer reevaluate SQL statement
!! 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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 -Original Message- Sent: Friday, September 14, 2001 11:40 AM To: Multiple recipients of list ORACLE-L !! 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] wrote: !! 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 chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. 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: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
RE: When optimizer reevaluate SQL statement
!! 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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, execution plan re-built and if it makes sense, the index will be used in the plan. Cheers, Gaja --- 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 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 -Original Message- Sent: Friday, September 14, 2001 11:40 AM To: Multiple recipients of list ORACLE-L !! 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] wrote: !! 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 chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. 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: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: When optimizer reevaluate SQL statement
!! 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 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Walthour, Jon (GEAE, Compaq) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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 3:35 PM To: Multiple recipients of list ORACLE-L !! 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 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 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 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Walthour, Jon (GEAE, Compaq) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Christopher Spence INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: When optimizer reevaluate SQL statement
!! 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 same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Hillman, Alex INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
