###############################################################################
#
# Postgres Enterprise Manager - SQL Profiler Plugin
#
# Copyright (C) 2011 - 2023, EnterpriseDB Corporation. All rights reserved.
#
# This code is licensed under the EnterpriseDB Limited Use License for use
# by customers with an active PostgreSQL, EDB Postgres Advanced Server,
# or Postgres Enterprise Manager subscription.
#
# Redistribution is strictly prohibited.
#
###############################################################################

###############################################################################
#
# This plugin includes code released under the PostgreSQL Licence:
#
# PostgreSQL Database Management System
# (formerly known as Postgres, then as Postgres95)
#
# Portions Copyright (c) 1996-2023, The PostgreSQL Global Development
# Group
#
# Portions Copyright (c) 1994, The Regents of the University of California
#
# Permission to use, copy, modify, and distribute this software and its
# documentation for any purpose, without fee, and without a written
# agreement is hereby granted, provided that the above copyright notice
# and this paragraph and the following two paragraphs appear in all copies.
#
# IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
# FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
# INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
# DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF
# THE POSSIBILITY OF SUCH DAMAGE.
#
# THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
# INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
# AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
# ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS
# TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
# MODIFICATIONS.
#
###############################################################################

PostgreSQL/EDB Postgres Extended/Advanced Server Query Profiler
===============================================================================

The SQL Profiler module provides a means for tracking execution statistics of
all SQL statements executed on a server by specific user(s) and on specific
database(s). Its primary purpose is to capture SQL statements sent to a
Postgres server to help a DBA analyze a set of SQL traffic, identifying the most
resource intensive SQL statements, locating SQL bottlenecks in their system, and
assisting the DBA in tuning problematic SQL code to increase overall server
performance.

This module utilizes the ExecutorStart, ExecutorRun, ExecutorEnd server hooks
to log the executed queries in the database server. As a result, it only
collects information about the Data Manipulation Language (DML) statements.

The module must be loaded by adding sql-profiler to the 'shared_preload_libraries'
directive in postgresql.conf, because it requires additional shared memory.
You must perform a server restart if you add or remove the module. 

For each statement, the profiler records:

   * query
   * execution-plan
   * total execution time
   * start-time
   * end-time
   * tuples affected (returned/fetched/inserted/updated/deleted)
   * user-id
   * database-id
   * application-name (Only in PostgreSQL/EDB Postgres Advanced Server 9.0 & above)
   * Blocks (file-system) in/out
   * Page Faults/Reclaims, Swaps
   * Signals Received, Messages Received/Sent during query execution
   * Voluntary/involuntary context switches
   * Shared Blocks Read/Written, Shared Buffer Hit Ratio
   * Local Blocks Read/Written, Local Blocks Hit Ratio
   * Temporary Blocks Read/Written
   * Type of the parameters (Only for the PREPARED statements)

This data will be stored on the disk to be utilised later by the SQL Profiler
client application.

You can define a trace for logging the statements against specific user(s)
and database(s). You can have multiple traces logging the query statements
at the same time. The query will be logged for each current trace, for which
the statement satisfies the trace criteria.


Loading SQL Profiler
====================
To use the profiler, you must:

	1) create the extension using "CREATE EXTENSION sql_profiler" against the postgres or
	   edb (The maintenance database, mentioned during registering the database
		 server with the Postgres Enterprise Manager client).

	NOTE: If you already have an older version of sqlprofiler, which was not an
	      extension, you could use the following command to migrate it as an
				extension "CREATE EXTENSION sql_profiler VERSION '4.0'".

LOADING THE PROFILER:

	You should preload the profiler into all sessions by adding the following
	line to your postgresql.conf configuration file:

		shared_preload_libraries = '$libdir/sql-profiler'

	There is a tiny (probably immeasurable) amount of extra overhead once
	you've loaded the profiler.

If the shared_preload_libraries parameter already contains entries, add the 
sql-profiler.so library to the end of the comma-separated list.  Please note that 
loading the profiler may introduce a tiny (probably immeasurable) performance 
degradation.

After adding sql-profiler to the shared_preload_libraries parameter, you must 
restart the server:

    system restart <service name>

**Please note that before connecting to the server with the psql client, you may be 
required to modify the pg_hba.conf file.  The pg_hba.conf file is located in 
PGDATA

You are now ready to use the Postgres Enterprise Manager client to:

	Use Postgres Enterprise Manager (client) to
	- Define and start a trace for any user (or, all users) against any
	  database (or, all the databases).
	- Stop the running trace
	- Load an existing trace created earlier
	- Remove an existing trace(s).
	- Schedule a trace to start it later

	  Please Note: You must register the database server with a Postgres
	  Enterprise Manager server; a PEM Agent should be running and bound
	  to that server for starting the scheduled trace.


======================================================================
Copyright (C) 2010 - 2023, EnterpriseDB Corporation. All rights reserved.
For assistance, contact EnterpriseDB Support (support@enterprisedb.com)

