RE: Set Explain with SP's

Jon, Because procedures are optimized when they are stored in the database, that is when you need to set explain on. For example: SET EXPLAIN ON; CREATE PROCEDURE... The "SET EXPLAIN ON" must appear outside of your create procedure. Now, with that said, here's a warning. Don't put all of your faith in the output. I was burned once when I accidentally used a CHAR variable in a select against an integer column. The set explain said that it would use my index. However, when the time actually came, the database was clever enough to realize that it couldn't use the index with my CHAR variable. So, it did a sequential scan instead; contrary to what the set explain predicted. Oh sure, it was my fault. But, I wish I hadn't placed all my faith in the set explain's output... --Craig (craig@chemistry.utah.edu) > > In attempting to determine how the optimizer is processing queries in > stored procedures, we've tried turning 'set explain on' within a procedure. > However, when executing the procedure, I get an empty file. > > Is there anything special which needs to be done, to get set explain output > from stored procedures?? >