Explain Plan and SQL Text Length
What is the longest SQL statement that can be analyzed via explain plan. Is it the maximum length equal to the maximum length of a varchar2. Does the new virtual explain view have any problems with the length of the statement? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Explain Plan and SQL Text Length
Ian: Any valid SQL statement can be explained. I've explained queries that were well over 4k -- even when you excluded the whitespace! Kevin -Original Message- Sent: Wednesday, July 24, 2002 11:50 AM To: Multiple recipients of list ORACLE-L What is the longest SQL statement that can be analyzed via explain plan. Is it the maximum length equal to the maximum length of a varchar2. Does the new virtual explain view have any problems with the length of the statement? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Toepke, Kevin M 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: Explain Plan and SQL Text Length
Oracle Apps r10.7 and r11.0 had some SQL statements from PRO*C batch programs that are over 20K in length. Utterly astounding! Since VARCHAR2 variables can be up to 32767 in PL/SQL, I've written PL/SQL packages that used string variables of that length for automating EXPLAIN PLAN execution and storing the output for analysis, and ran this package in OraApps environments for months... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 10:33 AM Ian: Any valid SQL statement can be explained. I've explained queries that were well over 4k -- even when you excluded the whitespace! Kevin -Original Message- Sent: Wednesday, July 24, 2002 11:50 AM To: Multiple recipients of list ORACLE-L What is the longest SQL statement that can be analyzed via explain plan. Is it the maximum length equal to the maximum length of a varchar2. Does the new virtual explain view have any problems with the length of the statement? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Toepke, Kevin M 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: Tim Gorman 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: Explain Plan and SQL Text Length
As certain southern sportsmen often say, My bad. It wasn't the explain statement but the program which builds them. Work done at 1:30 AM is always suspect. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Oracle Apps r10.7 and r11.0 had some SQL statements from PRO*C batch programs that are over 20K in length. Utterly astounding! Since VARCHAR2 variables can be up to 32767 in PL/SQL, I've written PL/SQL packages that used string variables of that length for automating EXPLAIN PLAN execution and storing the output for analysis, and ran this package in OraApps environments for months... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 10:33 AM Ian: Any valid SQL statement can be explained. I've explained queries that were well over 4k -- even when you excluded the whitespace! Kevin -Original Message- Sent: Wednesday, July 24, 2002 11:50 AM To: Multiple recipients of list ORACLE-L What is the longest SQL statement that can be analyzed via explain plan. Is it the maximum length equal to the maximum length of a varchar2. Does the new virtual explain view have any problems with the length of the statement? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Toepke, Kevin M 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: Tim Gorman 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: MacGregor, Ian A. 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).