You might want to know what queries to your database are the most common when doing the following:
Prerequisites
In order to execute this procedure in your environment, the following data, services, or apps are required:
Example
Queries with a high execution volume warrant regular review and inspection. You want to create a list of the top ten so that you can work with the owners to optimize such queries.
NOTE: To optimize the search shown below, you should specify an index and a time range.
- Run the following search:
|top query useother=true
Search explanation
The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.
Splunk Search |
Explanation |
|top query useother=true |
Return the top 10 most common queries, with a count and percent value for each query. Merge the results not included in the table into a single new row called "OTHER". Note: You can change the number of queries returned by adding the limit parameter. Example, limit=5. |
Result
The result is a table showing the top 10 queries by count. Owners should be notified when their queries are in the top ten so they can optimize them to make the query exceptionally fast. Where possible, upstream refactoring, such as caching, may be considered to minimize the number of times the query is called.
In addition, the SQL statements themselves can tell a story about potential policy violations, such as large full table scans of sensitive data such as human resources information or passwords.
query |
count |
percent |
SELECT * FROM hr_employee_info |
1700 |
12.834063 |
SELECT password FROM SYS.USER$ WHERE name='SYS' |
887 |
6.696361 |
SELECT * FROM customers WHERE customer_uid=b56a5c74-87a3-4593-9eb5-2d2b1c38a0d8 |
22 |
0.166088 |
SELECT * FROM customers WHERE customer_uid=5d052b26-9b18-44a0-b4db-7c4b028e74e2 |
22 |
0.166088 |
Comments
0 comments
Please sign in to leave a comment.