Determining "How Busy" without DBA access...
Bill Murphy, April 19, 2010 - 11:50 am UTC
Tom,
I'd be happy with any/all of those definitions of "busy" if it let me give a plausable explaination of why queries issued by my code were "timing out today" but "working yesterday".
I realize there is no single scalar metric of busy. I'm not sure if I'm contending for IO or CPU or locks or... Knowing how to query any of these would help me.
I do not have dba access, so I do not think I can "install statspack". Is there something I can do with select privs? At my current job, I am viewed as a coder, not as a dba; so some tools are off limits. Also, pestering the dbas with questions is considered off limits.
Thanks again for any help. I will certainly read up on statspack further and see if it will work for me, but I think the need to connect as "sys" will keep me from being able to use your advice until I become a dba.
Thanks!
--Bill
April 19, 2010 - 12:39 pm UTC
... I'd be happy with any/all of those definitions of "busy" if it let me give a
plausable explaination of why queries issued by my code were "timing out today"
but "working yesterday". ...
but one day that could be:
that physical IO was way way up - that would define busy that day.
that CPU utilization went crazy - that would define busy that day.
that hard parsing skyrocketed - that would define busy that day.
and so on.
So, the best bet is to keep a history of what has been happening (that is, automatically gather statspack snapshots on a schedule, maintain a history).
If you are NOT the DBA, you cannot do this, the DBA would have to be responsible for this - it would take their care and feeding to properly run.
Also, pestering the dbas
with questions is considered off limits.
unacceptable - either start looking for a new position or work to change that. I'd even be willing to participate in a call with management on that one to change that attitude entirely. You cannot be successful in such an environment.
Or get them to upgrade to 10g where AWR and ASH exist and are builtin and the DBA would have to go out of their way to disable it...
Bill Murphy, April 19, 2010 - 1:57 pm UTC
...unacceptable - either start looking for a new position...
Has anyone mentioned you're scarily insightful for a Monday?
Yeah. I've got an end date coming up soon.
Maybe at whatever job comes next I'll get to use statspack.
Your posts have made me excited to work with Oracle technologies in the future. (Flashback is just too cool; also your tip that beginners should start with the "Oracle Concepts Guide" helped me lots.) Thanks!
--Bill
Tools to explore sessions connected to Oracle.
Thomas Clark, April 20, 2010 - 8:07 am UTC
Bill most of us developers do not have access to statspack or tracing capability in the production database. Unfortunately DBA resources appear to be stretched too thin these days and I agree it is hard to get their time. If you can get the following role “SELECT_CATALOG_ROLE” (This role can be granted to users to allow SELECT privileges on all data dictionary views) granted to you it would help you in looking into Oracle activities. I utilize tools such as Oracle SQL Developer and Toad to see how many sessions are connected, what sql is being executed, hit ratios, long operations that are being performed …. I have been successful at identifying potential problem areas of the application’s SQL utilizing these tools. You could get the same results from executing queries in sqlplus but I prefer the GUI environment. Please feel free to ask for more help if necessary. I believe that you can find demonstrations on how to accomplish the above mentioned tasks by searching the web. Have a great day!