SQL Profiler v14

Suggest edits

Inefficient SQL code is a leading cause of database performance problems. The challenge for database administrators and developers is locating and then optimizing this code in large, complex systems.

SQL Profiler helps you locate and optimize poorly running SQL code.

Specific features and benefits of SQL Profiler include:

  • On-demand traces. You can capture SQL traces at any time by manually setting up your parameters and starting the trace.
  • Scheduled traces. If the current time isn't convenient, you can also specify your trace parameters and schedule them to run later.
  • Save traces. Execute your traces and save them for later review.
  • Trace filters. Selectively filter SQL captures by database and by user, or capture every SQL statement sent by all users against all databases.
  • Trace output analyzer. A graphical table lets you quickly sort and filter queries by duration or statement. A graphical or text-based EXPLAIN plan lays out your query paths and joins.
  • Index Advisor integration. After you find your slow queries and optimize them, you can also let Index Advisor recommend the creation of underlying table indices to further improve performance.

Installation

The following describes the installation process.

1. Install SQL Profiler

SQL Profiler is installed by the EDB Postgres Advanced Server installer on Windows or from the edb-asxx-server-sqlprofiler RPM package on Linux, where xx is the EDB Postgres Advanced Server version number.

2. Add the SQL Profiler library

To include the SQL Profiler library in the shared_preload_libraries configuration parameter, modify the postgresql.conf parameter file for the instance.

For Linux installations, the parameter value must include:

$libdir/sql-profiler

On Windows, the parameter value must include:

$libdir\sql-profiler.dll

3. Create the functions used by SQL Profiler

The SQL Profiler installation program places a SQL script named sql-profiler.sql in the following directories.

On Linux:

/usr/edb/as14/share/contrib/

On Windows:

C:\Program Files\edb\as14\share\contrib\

Use the psql command line interface to run the sql-profiler.sql script in the database specified as the maintenance database on the server you want to profile. If you're using EDB Postgres Advanced Server, the default maintenance database is named edb. If you're using a PostgreSQL instance, the default maintenance database is named postgres.

This command uses the psql command line to invoke the sql-profiler.sql script on a Linux system:

$ /usr/edb/as14/bin/psql -U user_name database_name <
/usr/edb/as14/share/contrib/sql-profiler.sql

4. Stop and restart the serverx

For the changes to take affect, you must stop and restart the servers. After configuring SQL Profiler, it's ready to use with all databases that reside on the server. You can take advantage of SQL Profiler functionality with EDB Postgres Enterprise Manager. For more information about Postgres Enterprise Manager, see the PEM documentation.

Troubleshooting

After performing an upgrade to a newer version of SQL Profiler, you might 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:

  1. Uninstall SQL Profiler by invoking the uninstall-sql-profiler.sql script.
  2. Reinstall SQL Profiler by invoking the sql-profiler.sql script.
← Prev

Index Advisor

Next →

pgsnmpd