Kbase 20327: How to run a SQL-92 query to gather performance information against a SQL statement?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/10/2009 |
|
Status: Verified
GOAL:
How to run a SQL-92 query to gather performance information against a SQL statement?
GOAL:
How to gather performance information for a SQL statement?
GOAL:
How to display query plan for a SQL statement?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Immediately after executing a SQL statement, execute the following SQL command
SELECT SUBSTRING("_Description",1,70)
FROM pub."_Sql_Qplan"
WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" )
FROM pub."_Sql_Qplan"
WHERE "_Ptype" > 0 );
The first line can be any of the columns in pub."_Sql_Qplan". The WHERE clause is the useful part. It retrieves the latest non-system query.
With OE10.1X or higher, you can use the following as well on the SQL Explorer or on the sql tool to get a log file name formatted as
SQL_server_<server-id>_<ddmmmyyyy>_<hhmmss>.log:
SET PRO_CONNECT LOG ON WITH (STATEMENT, QUERY_PLAN);
Where STATEMENT is the SQL itself and the QUERY_PLAN is the actual query plan will be printed on the log file named above. You can also use eitherstatement or QUERY_PLAN by itself instead of both. However, setting both helps in looking at the actual query above the query plan.