7.15 SQL Profiler

SQL Profiler helps locate and optimize poorly running SQL code. Before using SQL Profiler, you must:

1.	Modify the postgresql.conf parameter file for the instance to include the SQL Profiler library in the shared_preload_libraries configuration parameter.

For Linux installations, the parameter value should include:

$libdir/sql-profiler

On Windows, the parameter value should include:

$libdir/sql-profiler.dll

2.	Create the functions used by SQL Profiler. The SQL Profiler installation program places a SQL script (named sql-profiler.sql) in the share/contrib subdirectory of the main PostgreSQL installation directory on Linux systems. On Windows systems, this script is located in the share subdirectory.

Using the PEM Client Query Tool or the psql command line interface, run the sql-profiler.sql script in the database specified as the Maintenance Database on the server you wish to profile. If you are using Advanced Server, the default maintenance database is named edb. If you are using a PostgreSQL instance, the default maintenance database is named postgres.

To use the PEM Query Tool to run the script, highlight the name of the maintenance database in the PEM Client tree control, and navigate through the Tools menu, selecting Query tool. When the Query Tool opens, use the Open option on the Files menu to open a web browser and navigate to the sql-profiler.sql script. When the script opens in the SQL Editor panel of the Query Tool, select the Execute option from the Query menu to invoke the script and configure SQL Profiler.

You can also use the psql command line to invoke the configuration script. The following command uses the psql command line to invoke the sql-profiler.sql script on a Linux system:

$ /opt/PostgresPlus/9.5AS/bin/psql -U user_name database_name < /opt/PostgresPlus/9.5AS/share/contrib/sql-profiler.sql

3.	Stop and re-start the server for the changes to take effect.

After configuring SQL Profiler, it is ready to use with all databases that reside on the server.

To open SQL Profiler on the PEM Client, highlight the name of a database you wish to profile in the tree control, and select SQL Profiler from the Management menu. The SQL Profiler wizard (shown in Figure 7.2) opens.

Figure 7.2 — The SQL Profiler wizard.

The SQL Profiler wizard will walk you through the process of defining a new trace, or opening an existing trace. For more information about using SQL Profiler, consult the Postgres Enterprise Manager online help text (accessed through the PEM Client Help menu), or the EDB Postgres (Postgres Plus) Migration Guide, available at:

http://www.enterprisedb.com/products-services-training/products/documentation/enterpriseedition

Troubleshooting

If (after performing an upgrade to a newer version of SQL Profiler) you encounter an error that contains the following text:

An error has occurred:
ERROR: function return row and query-specified return row do not match.
DETAIL: Returned row contains 11 attributes, but the query expects 10.

To correct this error, you must replace the existing query set with a new query set. First, uninstall SQL Profiler by invoking the uninstall-sql-profiler.sql script, and then reinstall SQL Profiler by invoking the sql-profiler.sql script.