20.7. SQL

20.7.1. iQuery

iQuery is more than a typical query executor which can be accessed over a webserver. Not only does it show the results of a typical SQL query, but also shows the results for the execution of a stored procedure.

The query to be executed is entered in the Query text box, which is executed when the button is clicked.

The Data Output text box displays the result of the entered query. The query results are limited to a maximum of 2500 records.

Information about the execution time and the output generated through a DBMS_OUTPUT or RAISE statement can be displayed by selecting the Messages Tab.

The following two images show the data output and the messages displayed by iQuery after a query has been executed.

20.7.2. Query Profiler

The Query Profiler parses your database's log files and gives you a report of the queries pertaining to your database which can greatly help DBA's in improving the performance of their systems. The information made available by Query Profiler can be utilized for two primary purposes:

• For helping you track down the slowest/longest running queries so you can optimize those queries. Queries ranking high on execution time need to be optimized since these queries take the highest amount of time to execute. This information helps the DBA in deciding which queries require tweaking in order to enhance performance.

• To help you track down frequently executing queries so that these queries can be placed in a stored procedure for further performance optimization.

In order for Query Profiler to function properly, you need to have the log_min_duration_statement configuration parameter set to 0. This can be achieved in the following two ways:

• Enabling the Query Logging option present at the top of the page, as shown in the following screenshot:

  

  Please note that turning ON the Query Logging option will enable Query Logging for the current session only and will have to be turned on again when the DBA Management Server is re-started.

• Setting the log_min_duration_statement configuration parameter to 0 in the postgres.conf file.

Enabling Query Logging has performance implications though; hence it should only be used to sample the queries for a short period of time to gather your relevant information and then turned off.

Query Profiler can be used to rank queries on the basis of Total Execution Time, Average Execution Time and Statement Count (the number of times a query was repeated) which can be done by selecting the required ranking option from the Order By dropdown.

You can filter out the type of queries you want to rank, the possible query types include:

• SELECT

• INSERT

• UPDATE

• DELETE

• OTHERS

You can include/omit a particular query type by checking/unchecking against their respective checkboxes and then pressing the or button(s). Pressing the button will not parse the log file over again but just filter out statements from the last time you re-parsed the report, whereas pressing the button will reparse the log file all over again which would be recommended if you have a lot of database activity or have not refreshed your page for a long time.