Profiling SQL Queries
This page describes the SQL Queries profiling mode.
The SQL Queries profiling mode tracks SQL queries executed using the JDBC API, execution times, invocation counts and call trees, enabling to analyze the traffic between a Java process and database instance. Use this profiling mode to realize what queries are being executed and identify performance bottlenecks or bad design patterns like not using prepared statements. See First Profiling Session for details on starting a profiling session.
SQL Queries Profiling Modes
Two modes are available for profiling SQL queries. The modes and settings can be changed in the Settings pane which is displayed by pressing the Settings switch in Profiler window toolbar.
- All queries collects all executed SQL queries. This mode is selected by default and doesn't require any additional configuration.
- Defined queries collects executed SQL queries containing the provided case-insensitive substring.
- Pause live results allows to stop updating live results. Pressed automatically when a context menu is displayed for the profiling results.
- Update live results is enabled when the Pause live results is pressed, allows to manually update paused live results.
- Show delta/absolute values controls switching between absolute and incremental values.
- Take snapshot saves the currently available profiling data to a file which can be reopened later for offline analysis or compared with another snapshot.
- Reset results clears the currently available profiling data, enabling to start collecting new data from a defined point.
- Take thread dump action takes snapshot of all threads in the profiled JVM including their call stacks and displays it in a separate view. See the Thread Dump chapter for details.
- Take heap dump action saves a snapshot of all objects currently stored in the heap memory in a .hprof format and optionally opens it in a heap viewer. See the Heap Dump chapter for details.
- Request garbage collection action requests garbage collection in the profiled JVM. There's no guarantee when or if at all the garbage collection will be performed. To make sure GC will be run, the action should be invoked several times.
SQL Queries View
The results view displays executed SQL queries, execution times, invocation counts and call trees, SQL command types, referenced database tables and SQL statement types. The view provides the following data columns:
- SQL Query column shows text of the SQL query and its invocation call tree.
- Total Time column shows time spent in all invocations of the SQL query.
- Invocations column shows number of invocations of the SQL query.
- Command Type column shows command type of the SQL query (SELECT, INSERT, ALTER etc.).
- Tables column shows list of database tables referenced by the SQL query.
- Statement Type column shows the SQL statement type (regular, prepared, callable).
The entire SQL query string for the selected row can be displayed in a SQL Query Viewer dialog using the View SQL Query action in the context menu or by pressing Enter key.
Tip: The columns can be displayed or hidden using the right corner button in the view header by clicking the dropdown arrow (or right clicking the header on Mac OS X). The relative value bar can be displayed or hidden for every column which displays a relative value by resizing the column.
Searching And Filtering Results
A SQL query or its invoking method can be found in results using the Find stripe which opens by invoking the Find action in results context menu or IDE Edit menu or using the Ctrl+F keyboard shortcut. Values of the SQL Query column are searched for the entered substring, optionally matching case if selected. Once a search has been performed, it can be repeated using the F3 or Shift+F3 shortcuts.
SQL queries can be filtered using the Queries area at the bottom of the results view. The text field provides a case-insensitive substring filter for the SQL queries. Commands dropdown filters the SQL queries by used command types. Tables dropdown filters the SQL queries by referenced tables. Statements dropdown filters the SQL queries by statement types (regular, prepared, callable).
Invoking methods can be filtered using the Filter stripe which opens by invoking the Filter action in results context menu or using the Ctrl+G keyboard shortcut. Values of the SQL Query column are filtered by the entered substring depending on the selected filter mode (Contains, Does Not Contain, Regular Expression), optionally matching case if selected.
Tip: The invoking methods can be visually marked by frameworks by applying the color filters using Tools | Options | Java | Profiler | Filters | Use defined filters for coloring results.