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?

CodePudding user response：

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.

CodePudding user response：

Thanks Bill, I thought it was as easy as oracle and sqlserver.

But OK :)