Does Performance Analysis have an option to view all SQL commands that are performing a FULL TABLE SCAN?
There is an metric you can add to the PA GUI which will help to find SQL statement with full table scans.
The metric's name is: Table Scans Rows Gotten
Explanation: The number of rows scanned during full table scans. High values may indicate non-optimal queries and/or the absence of proper indexing.
To add the metric:
1) Click on the hammer button in the GUI
2) Choose the tab Support (last one in the tab line)
3) Mark "Enable grids customization"
4) Click on Ok
5) Now open the History view and select SQL Statements
6) Click somewhere in the right window where the statements and their resource consumption are listed
7) Do a right mouse click and click on "Select Metric"
8) Add metric "Table Scans Rows Gotten"
The metric will added at the end so you have to scroll to the right. Now you can sort and you will find the statements with a high value.
You can highlight the statement, do a right mouse click and choose Execution Plan History. If you are collecting executions plans you will find the execution plan of the statement and you will see more details.
To do deeper tuning investigation install SQL Optimizer and use this to tune the statement.
Note: The metrics changes won't be saved. After closing PA GUI the changes are lost and for the next session you have to add it again.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center