Toad World® Forums

P&T for Stored Procedures


How we can use this tool to optimoze the stored procs. Especially procs using cursors.


Message was edited by: nsivashankar_859


The tool can only optimize a single SELECT, INSERT, UPDATE, or DELETE statement one at a time. If you have a stored procedure to optimize, the single SQL statements inside would be the ones you want to tune for their performance. I suggest you first use the SQL Scanner module to scan the stored procedure. The SQL Scanner will extract the SQL statements in your stored procedures, retrieve their query plans for you to review. You can then send those SQL you want to optimize to the SQL Optimizer module to go through the rewrite and test run process.

One note for SQL declared under a cursor is that the query plan of a cursor SQL may be different from that of a dynamic SQL. In the SQL Optimizer window, you will find a SQL for Cursor checkbox that you can check and provide the cursor declaration for your SQL if it is defined under a cursor. Do give the same cursor declaration as your SQL will be used in the stored procedure when you optimize and test run the SQL so that you will get an accurate plan and test run result.