Kbase P152886: How to know if a SQL-92 client is doing a table scan ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/10/2009 |
|
Status: Unverified
GOAL:
How to know if a SQL-92 client is doing a table scan ?
GOAL:
How to know if a query is using no indices and doing a table scan ?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1C
FIX:
To enable table scan logging use any ODBC or JDBC client run the following command online:
set pro_server log on with (pro_tablescan_event);
Example
prodb sports2K sports2000
proserve sports2k -S 5000
sqlexp sports2K -S 5000
sqlexplorer> set pro_server log on with (pro_tablescan_event);
This will allow a DBA to turn on logging and capture the events where a table scan is performed against a table.
It will capture the events for all current SQL connections performing queries against the database.
The rolling logs will have a name SQL_server_<clientnumber>_<date>_<timestamp>_<fileversionid>.log and are located either in your dedicated WRKDIR, or in the same location as the database files. Whereby fileversionid refers to the version of this particular log file.
Example
SQL_server_1_20070222_182923_A.log
Once a file exceeds 500 MB, the logging will create a fileversionid of B.
Example
SQL_server_1_20070222_182923_B.log
If a table scan is recognized, the event logging will output the following information.
- the SQL statement being executed
- the userid performing the statement
- The table on which the table scan is occurring.
Limitations:
New files are created when new (SQL) users log on (one logfile per connection). If the active logging file is deleted, all logging stops.
The following example will demonstrate the expected logging results when issuing queries on tables for sports2k :
User 'aaa' queries the Customer table resulting in a full tablescan:
Query plan contains table scan in : select * from pub.customer
By user id : aaa
table scan name : PUB.CUSTOMER
Ttracing continues until the logging is turned off by the DBA or the datbase broker is shut down. To disable, delete the SQL_server* log file or use the following command (in SQL Explorer):
set pro_server log off;