How to find high CPU/memory SQLs in last 24 hours?

[a128]

My boss is asking me a way to find high CPU and memory SQLs during last 24 hours and send out an email to engineers with the SQLs and usage. I can image Statspack may come in handy. However, I don't find a section for high CPU/memory SQLs but high buffer gets, executions, etc for SQLs. Am I missing something here? Or some one has a better way to achieve the objective?

Thanks,

[marist89]

Finding this information after the fact is extremely difficult unless you have the right monitoring jobs setup.

Without knowing which program/session is eating up a lot of CPU, you will have to do a wide-scale analysis of who is using the most resources. To identify these sessions you may need to setup a logon/logout trigger that captures information from v$mystat. You may also want to utilize a periodic snapshot of your OS' process listing that could help you find which processes are using a lot of CPU.

Once you identify some candidates, you can setup a logon trigger to automatically trace their session and analyze the SQL after the job completes.

[KenEwald]

10g OEM. Set your thresholds under "Manage Metrics". Email notification, poc.

[xyz2000]

Topsql is good tool for monitoring sql statement .

[a128]

Thanks to marist89 and KenEwald. I do have one question, though.

Isn't it true: there could have been many relatively fast SQLs running from session A while only one long running SQL from session B? If so, if session A eats up more CPU than session B, it does not necessarily mean the SQL in session B is not a candidate for tuning. In other words, session level performance may not go parallel with SQL level performance.

Thanks,

[KenEwald]

You are right. And lots of good books have been written on the subject of tuning Oracle. Generally, tuning a couple resource intensive SQL statements will benefit you the most.

BUT...

Finding "high CPU and memory SQLs during last 24 hours and send out an email to engineers with the SQLs and usage" seems like a management knee-jerk reaction to a tuning problem you should own. You should know what's running on your database and be proactively looking for trashy sql.

xyz2000, Topsql seems like a good place for a128 to start, where can he get it?

-Ken

[marist89]

Originally posted by a128
Thanks to marist89 and KenEwald. I do have one question, though.

Isn't it true: there could have been many relatively fast SQLs running from session A while only one long running SQL from session B? If so, if session A eats up more CPU than session B, it does not necessarily mean the SQL in session B is not a candidate for tuning. In other words, session level performance may not go parallel with SQL level performance.

Thanks,

True. That's why you have to compare "CPU Used by this session" with the total time the session was connected in order to find out which are "abusers". However, keep in mind that CPU is not the only resource you need to keep track of. If you SQL bangs the disk a lot, it may be waiting on I/O thereby rendering the CPU irrelavent.

[a128]

You should know what's running on your database and be proactively looking for trashy sql.

That's the main reason the question was asked. The problem is the front end is Java applications developed by many developers. It's not pratical for each developer to go back to their code and dig out all the SQLs, run explain plans, etc to tune them. That's why if we can get a report as originally posted, it will be a good starting point.

xyz2000, Topsql seems like a good place for a128 to start, where can he get it?

I believe it's part of OEM. However, it I am not mistaken, it's a snapshot for the TopSQLs at that moment, which is not as good as TopSQLs over a period of time.

Thanks,

[KenEwald]

Sample extracting SQL & running explain plans on them will probably give you more work to do than you have time to fix.

Emailing bad sql to developers never worked here. They probably don't have a clue how to fix it anyways.