• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getting database stats using jdbc (oracle)

 
shalini umrao
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,

I am a regular follower of the post. Thanks to all the people here whose posts help novices like me a lot.

However, there is somethhing that I have not been able to find and need everyone's help.

I have a datbase. I need a java program to know the state of the database at any given instance, as in, is it up or down etc.

and the second thing i need to know is the number of queries being executed at any given time, alongwith the time taken to execute each query.

Please help me with this.

Thanks in advance.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the ranch, i am not sure this is a good starting place for a novice.

well it is easy to tell if the databasse is up, can you connect to it

And i think the rest will depend on which database you are using, so can you tell us that?
 
shalini umrao
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Wendy,

Thanks for the warm welcome.

Well, I meant that even novices like me can get a quick solution to many problems thanks to the ranch.

Also thank you for the tip.

The database I am using is Oracle db.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
shalini umrao wrote:Hello Wendy,

Thanks for the warm welcome.

Well, I meant that even novices like me can get a quick solution to many problems thanks to the ranch.

Also thank you for the tip.

The database I am using is Oracle db.


I will shout for our oracle chap to help you

MAAARTIN
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh yes, here I am. Not sure I'll be able to help much, though.

I don't know how to do this kind of monitoring from Java/JDBC, nor whether it is possible at all. Some kind of monitoring can be done through Oracle performance views, but you need either a DBA access, or grants from a DBA to read these views, and not all DBAs would be willing to provide you with that. These work like normal views, so that you can select from them and read their contents using a ResultSet, and the views, their columns and their meaning are described here.

This will not help you to monitor the state of a database, though. The most closer I can think of is to try to connect to an instance and see what happens. If you get an exception while connecting to it, the exact kind of the exception might provide some clues as of why the database is not up, but that's about all I can think of. I don't think this would really help you, unfortunately.

Monitoring the execution of queries: maybe (just maybe) you find a suitable performance view in the documentation I've linked above. If not, there is a number of reports Oracle can generate about the database performance, the most useful is probably the AWR (automatic workload repository) report. It can be created even from a plain SQL, so you might be able to do this from Java, but again, you'd need the proper grants for that. This report shows the queries taking most resources over many different categories of resources: total time, CPU time, logical IO, physical IO, lock waits, buffer waits and a plethora of others I already forgot about. There are other reports and some of them could contain just the kind of information you need. My experience in this area is poor, so I can only offer a link to Oracle documentation again.

I'd say that monitoring databases in this way is a DBA's job, and they already have various tools for this, so it is quite unusual to do so from Java.

There are lots of other people on this forum who know Oracle, many of them much better than I do. If you explain your requirements from a broader perspective (eg. not only what do you need to achieve, but also why), it is possible someone here could offer a suitable solution.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:Oh yes, here I am. Not sure I'll be able to help much, though.

I don't know how to do this kind of monitoring from Java/JDBC, nor whether it is possible at all. Some kind of monitoring can be done through Oracle performance views, but you need either a DBA access, or grants from a DBA to read these views, and not all DBAs would be willing to provide you with that. These work like normal views, so that you can select from them and read their contents using a ResultSet, and the views, their columns and their meaning are described here.

This will not help you to monitor the state of a database, though. The most closer I can think of is to try to connect to an instance and see what happens. If you get an exception while connecting to it, the exact kind of the exception might provide some clues as of why the database is not up, but that's about all I can think of. I don't think this would really help you, unfortunately.

Monitoring the execution of queries: maybe (just maybe) you find a suitable performance view in the documentation I've linked above. If not, there is a number of reports Oracle can generate about the database performance, the most useful is probably the AWR (automatic workload repository) report. It can be created even from a plain SQL, so you might be able to do this from Java, but again, you'd need the proper grants for that. This report shows the queries taking most resources over many different categories of resources: total time, CPU time, logical IO, physical IO, lock waits, buffer waits and a plethora of others I already forgot about. There are other reports and some of them could contain just the kind of information you need. My experience in this area is poor, so I can only offer a link to Oracle documentation again.

I'd say that monitoring databases in this way is a DBA's job, and they already have various tools for this, so it is quite unusual to do so from Java.

There are lots of other people on this forum who know Oracle, many of them much better than I do. If you explain your requirements from a broader perspective (eg. not only what do you need to achieve, but also why), it is possible someone here could offer a suitable solution.


The problem is bosses (well one of mine did, why can't you developers do it?) are deciding with the new friendly oracle you don't need a DBA anymore
 
Rob Spoor
Sheriff
Pie
Posts: 20750
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
He's right of course. I've been both developer and MS SQL Server DBA for 7 years, so anybody can do that. Right?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy Gibbons wrote:The problem is bosses (well one of mine did, why can't you developers do it?) are deciding with the new friendly oracle you don't need a DBA anymore

We developers can do it, in which case we developers would be the DBAs. It's a role, not a person, after all. However, if we developers aren't allotted enough time for that task, or the boss actually thinks no one needs to perform the work of a DBA, the company is set to lose its data, sooner or later. The meeting after that will be awkward. ("What? How many months old is the last working backup we have?" or "What are these control files and why up to this day did no one of you developers thought of multiplexing them and backing them up?" or "Redo logs? What the hell are redo logs?" etc.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic