'Find who execute the full table scan in mysql
In mysql , we have sys.x$statements_with_full_table_scans, that inform us about the full scan.
But I have a lot of erp users that execute many scripts and I don't have a good software to monitoring my application.
My question is.
Anyone have a script that list full scan scripts with list the users that executed those?
Solution 1:[1]
The MySQL Server doesn't know which client script was responsible for a given query. It might even have been a query run interactively by a user in a MySQL client tool.
You can use show processlist once you know the queries that result in full scans, and if you see the bad query, look at the User and Host to identify the user (assuming each human user has a distinct MySQL username).
Then ask to do a code search for that query in that user's script(s).
In Oracle database, the process detail includes the program that is the client that opened that session. Example: http://www.dba-oracle.com/oracle_tips_process_detail.htm
But MySQL's protocol does not have that information. The client does not send the name of the client program, so the MySQL Server does not know it.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |