Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

How to use chart builder to display top SQLs?

nr1
chart-builder
new-relic-one

#1

I’d like to be able to display top SQLs from a database over time. Is it possible to use the chart builder to do it?
thx!

  • Yuri

#2

Hi, @yshilman: By “top SQLs”, I assume you mean “most time-consuming database queries”? If so, query response time is measured on the application side. If you have New Relic APM monitoring applications that call the database, you can use a query like this to see which queries have the highest average response time:

SELECT average(newrelic.timeslice.value) 
FROM Metric 
WHERE appName = 'Your Application' 
  AND metricTimesliceName LIKE 'Datastore/operation/MySQL/%' 
FACET metricTimesliceName 
SINCE 1 day ago

#3

Thx @philweber -
How would I query to get a value for my appName?


#4

You presumably know the names of applications reporting to your account. :slight_smile: You can use this query to see a list of application names:

SELECT count(*) FROM Transaction SINCE 1 day ago FACET appName

#5

it worked! I can now see my appNames…
@philweber - any chance I can also pull the SQLs into

SELECT average(newrelic.timeslice.value)
FROM Metric
WHERE appName = ‘Billcom_MainApp (PROD)’
AND metricTimesliceName LIKE ‘Datastore/operation/Oracle/%’
FACET metricTimesliceName
SINCE 30 minute ago

?

I think I am trying to get to the query that displays the data under
APM -> Monitoring->Databases (sort by Most Time Consuming).

Thx!


#6

This should show you the list of Oracle queries sorted by highest average response time:

SELECT average(newrelic.timeslice.value) * 1000 AS 'Avg Response Time (ms)' 
FROM Metric 
WHERE appName = 'Billcom_MainApp (PROD)' 
  AND metricTimesliceName LIKE 'Datastore/statement/Oracle/%' 
FACET metricTimesliceName 
SINCE 30 minutes ago

I am not sure you can get most time-consuming, because that is response time * calls per minute, and I don’t know how to get the call count.