TOP_DYNAMIC_SQL administrative view - Retrieve information about the top dynamic SQL statements
The TOP_DYNAMIC_SQL administrative view returns the top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement.
The queries returned by TOP_DYNAMIC_SQL administrative view are the queries that should get focus to ensure they are well tuned.
The schema is SYSIBMADM.
Authorization
One of the
following authorizations is required:
- SELECT privilege on the TOP_DYNAMIC_SQL administrative view
- CONTROL privilege on the TOP_DYNAMIC_SQL administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- ACCESSCTRL authority
- SECADM authority
In addition, to access snapshot monitor data, one of the
following authorities is also required:
- SYSMON
- SYSCTRL
- SYSMAINT
- SYSADM
Default PUBLIC privilege
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
Information returned
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | Timestamp for the report. |
NUM_EXECUTIONS | BIGINT | num_executions - Statement executions |
AVERAGE_EXECUTION_TIME_S | BIGINT | Average execution time, in seconds. |
STMT_SORTS | BIGINT | stmt_sorts - Statement sorts |
SORTS_PER_EXECUTION | BIGINT | Number of sorts per statement execution. |
STMT_TEXT | CLOB(2 M) | stmt_text - SQL statement text |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |
Example
Identify the top 5 most frequently
run SQL.
SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS,
SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT
FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 5 ROWS ONLY
The
following is an example of output for this query.
NUM_EXECUTIONS AVERAGE_EXECUTION_TIME_S STMT_SORTS ...
-------------------- ------------------------ -------------------- ...
148 0 0 ...
123 0 0 ...
2 0 0 ...
1 0 0 ...
1 0 0 ...
5 record(s) selected.
Output for this query (continued).
... SORTS_PER_EXECUTION ...
... -------------------- ...
... 0 ...
... 0 ...
... 0 ...
... 0 ...
... 0 ...
Output for this query (continued).
... STMT_TEXT
... ------------------------------------------------------------
... SELECT A.ID, B.EMPNO, B.FIRSTNME, B.LASTNAME, A.DEPT FROM E
... SELECT A.EMPNO, A.FIRSTNME, A.LASTNAME, B.LOCATION, B.MGRNO
... SELECT A.EMPNO, A.FIRSTNME, A.LASTNAME, B.DEPTNAME FROM EMP
... SELECT ATM.SCHEMA, ATM.NAME, ATM.CREATE_TIME, ATM.LAST_WAIT,
... SELECT * FROM JESSICAE.EMP_RESUME