The SQL menu contains the following two selections:
● iQuery – SQL query executor
● Query Profiler – SQL logging and reporting
Figure 17 DBA Management Server - SQL
iQuery is a SQL query executor that can run one or more SQL commands, execute stored procedures, and run anonymous SPL blocks.
The database on which the commands are to be run is selected from the Database drop down list. The commands to be executed are entered in the Query text box. Click the Submit button to execute the commands.
The Data Output tab displays the results of any SELECT commands. The query results are limited to a maximum of 2500 records.
Runtime information such as execution time, error messages, and the output generated by SPL DBMS_OUTPUT.PUT_LINE statements or PL/pgSQL RAISE statements are displayed on the Messages tab.
Figure 18 iQuery Data Output
The following shows the Messages tab associated with the previous query.
Figure 19 iQuery Messages
The Query Profiler parses the selected database server log file and generates a report of the SQL commands executed on that database. This can help database administrators improve performance of their systems. The information made available by Query Profiler can be utilized for two primary purposes:
● Track down the longest running SQL commands so they can be improved.
● Track down frequently used SQL commands so that these can be placed in a stored procedure for further performance optimization.
The log_min_duration_statement configuration parameter must be set to 0 to activate the Query Profiler for a selected database.
This can be achieved in either of the following two ways.
● At the top of the screen, check the Query Logging check box and click the Apply button as shown in the following figure (recommended method).
Figure 20 Enabling Query Logging
● In the postgresql.conf file set the log_min_duration_statement configuration parameter to 0 and reload the configuration file or restart the database server.
Note: This latter method will turn on query logging for all databases in the database cluster which is not recommended as performance on all databases will be impacted.
Note: Enabling Query Logging has performance implications; hence, it should only be used to sample queries for a short period of time to gather relevant information and then be turned off.
The resulting list of SQL commands can be ordered on the basis of Total Execution Time, Average Execution Time, or Statement Count (the number of times a given SQL command was repeated) by selecting the desired ranking from the Order By drop down list.
The types of SQL commands that appear can be filtered by checking the appropriate boxes:
● Include Inserts
● Include Updates
● Include Deletes
● Include Selects
● Include Others
Clicking the Refresh & Run Report button does not re-parse the log file over again, but just filters statements based upon the current filtering criteria using the data from the last time the log was parsed.
Clicking the Parse Log & Run Report button will re-parse the log file over again. This is recommended if there is a lot of database activity and the log file has not been re-parsed in a while.
Figure 21 Query Profiler
