the title said it all. Please let me know if you have any good idea on 3rd party database performance monitoring utilities.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I guess the problem I have is not really tuning the database yet. I have to monitor the performance of the database, to see how SQL statemenets got executed, how many resources it consumes, the toughest part is real time. So I need something like a GUI base monitoring utility to do the job, so any help?
Like I said, you don't NEED a GUI based monitoring utility. The best places to look are V$SYSTEM_EVENT, V$SESSION_EVENT and V$SESSION_WAIT. Those views will tell you which sessions are experiencing issues. Then you can drill down to find the statements for those users and what resources they are waiting on. Then you can tune from there. It's all there.
Andrew
1) You can use the following set command:
set timing on;
Now for all queries you will get hh:mi:ss:ms elapsed where by you can judge and further tune.
2) You can set optimizer_mode=rule/cost in init.ora, restart the database and use EXPLAIN PLAN and COMPUTE STATISTICS command to see the results and statistics for your query.
3) toadfree, toad standard, professional and expert evaluation versions contain a good deal about GUI based tuning.
--- k_murli_krishna
I think I have the solution for you ---
The UTLBSTAT & UTLESTAT are really out of date --
In version 8i and above the replacement is Statspack
To quote oracle:
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data.A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package.This user is granted limited query-only privileges on the V$views required for performance tuning.
Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tuning scripts by collecting more information, and also by storing the performance statistics data permanently in Oracle tables, which can later be used for reporting and analysis.The data collected can be analyzed using the report provided, which includes an "instance health and load" summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.
StatsPack README files include more specific information and history about this tool as well as platform and release specific information that help on installing and using the product. These files are located at:
unix:
Oracle8i 8.1.6
$ORACLE_HOME/rdbms/admin/s
Oracle8i 8.1.7 and Oracle9i 9.0.1
$ORACLE_HOME/rdbms/admin/s
NT:
Oracle8i 8.1.6
%ORACLE_HOME%\rdbms\admin\
Oracle8i 8.1.7 and Oracle9i 9.0.1
%ORACLE_HOME%\rdbms\admin\
I think you will find this very interesting and meets your needs. I have used it and found it lightyears beyond UTLESTAT/UTLBSTAT -- It was discussed quite in length here at IOUG2002.
Hope this helps,
sincerely,
dBalaski
Please update and finalize this old, open question. Please:
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
** Mindphaser - Community Support Moderator **
BTW: my point above
1) there aren't really any good "free" third party database tuning tools -- the word "free" excludes trial versions of software, all which mentioned in this thread eventually expire and cost $$$
2) the only available option for a "free" utlitity is
the ORACLE STATSPACK (which is included with oracle)
(as I pointed out, UTLBSTAT/UTLESTAT are out of date
and soon to be dropped by oracle in favor of statspack)
in this case, "free" is the relative term
since it already comes with bundled with oracle, it costs no more to use it.
dBalaski
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
points to be given to dbalaski
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
anand_2000v
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: asimkovskyPosted on 2002-04-17 at 11:35:19ID: 6948659
There are plenty of good tuning tools out there, but *free* is the hard part. Since tuning an Oracle database can be very complex, most people are not interested in developing a tool to do that if they are not going to make money on it.
If you really can't spend the money, learn the data dictionary and query it yourself with SQL*Plus. Everything a tuning tool can do, you can do yourself. You just have to know where to look.
Andrew