3.3 SQL Profiler

Table of Contents Previous Next


3 Database Administration : 3.3 SQL Profiler

SQL Profiler helps you locate and optimize poorly running SQL code.
On-Demand Traces. You can capture SQL traces at any time by manually setting up your parameters and starting the trace.
Scheduled Traces. For inconvenient times, you can also specify your trace parameters and schedule them to run at some later time.
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, and a graphical or text based EXPLAIN plan lays out your query paths and joins.
Index Advisor Integration. Once you have found your slow queries and optimized them, you can also let the Index Advisor recommend the creation of underlying table indices to further improve performance.
Step 1: Install SQL Profiler
Step 2: Add the SQL Profiler library
Modify the postgresql.conf parameter file for the instance to include the SQL Profiler library in the shared_preload_libraries configuration parameter.
Step 3: Create the functions used by SQL Profiler
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 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.
The following command uses the psql command line to invoke the sql-profiler.sql script on a Linux system:
Step 4: Stop and restart the server for the changes to take effect.
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.

3 Database Administration : 3.3 SQL Profiler

Table of Contents Previous Next