How to tune long running sql s?
Original sql is long running, since there are many alternate SQLs created, it is going to take a long time to complete executing all alternates. What is the best way to find the alternate SQLs without taking significant time.
Method 1:
Set an aggressive user-defined terminated time and use the Optimize button instead of the Rewrite and Batch Run buttons. If no better alternatives found, increase the termination time and try again. E.g. If the original SQL takes 1 hour, try a 5 minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc
Method 2:
Set the Execution Method to use Run on client and limit the records retrieved to a smaller number (e.g. 50000).
In this settings, execution will stop once the specified number of records is reached.The time measured using this method may not reflect the actual time of the statement when all records are retrieved.
Note:
- This method works only if SQL statements have records returned consistently. If SQL statements have GROUP BY, ORDER BY, or other actions in the plan that would require all records to be processed in the database first before returning, then time is needed before receiving the first record. In such case, this method would not help.
