Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's. I spoke with them a few times but it seems that their queries are causing high loads and probably bottlenecks

Is there any option in Oracle to monitor or see which SQLs they running ? I'm not talking about only the ones which cause high loads, although they're the most important ones I would like to see all of their queries.

share|improve this question
    
Are you running Enterprise Edition & do you have the diagnostic pack licensed? –  Phil Jan 23 '12 at 16:18

1 Answer 1

You might want to look into AUDIT ALL STATEMENTS BY user. As per Oracle's documentation, audit all statements by ... audits all top-level SQL statements executed. Top-level SQL statements are issued directly by a user. SQL statements run from within a PL/SQL procedure or function are not considered top-level statements. Therefore, this clause does not audit the statements executed within PL/SQL procedures or functions. However, the execution of the PL/SQL procedure or function itself is audited. This clause is useful if you want to audit all the statements in a specific environment, regardless of other auditing configurations that are system wide or user specific.

share|improve this answer
    
Hi. Thanks for the quick answer but this does not answer my question :( I do not wish (or can...) limit those users, as they are special enough to have their own account to perform their own tests. The reason I wish to monitor their SQLs is because I can't speak with them every time they execute a killer SQL which blocks or makes the db high load. –  dotdot Jan 23 '12 at 11:44
    
I see. I have changed the answer accordingly. –  René Nyffenegger Jan 23 '12 at 11:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.