| Oracle database monitoring tool [message #624708] |
Wed, 24 September 2014 17:16  |
 |
lumanier
Messages: 4
Registered: September 2014
Location: Hamburg
|
Junior Member |
|
|
Hello colleages,
I would like to tell you about a monitoring tool which works on it's own way and I would be interested to know how other dbas think about.
The tool uses a schema in an oracle database and uses database links to the instances, which it has to monitor.
An apache webserver, some PHP- and shellscripts provide the GUI for the firefox browser
In my job I have to work at 40 - 50 Oracle databases. The challenge was - how do I know if something has change within the database, data volume has increased, plan stability is given without executing boring SQL-Statements? And how do I know if developers changed the application code or perform an application update? How does the database work after patching? Is the patch for better performance or do the instance works worse.
In the past I worked with several monitoring tools, but they couldn't answer my questions. So I developed a little tool in which I want to introduce you.
The tool called ODIN connects to every full hour to the remote instances with an user with ony SELECT previlegs and query some DBA-views like v$instance, v$database,v$parameter, v$sysstat, v$systemevent, v$waitstat and some more. The values of theses queries distinguish text values and number values. The tool queries also object values from indexes and tables.
After collecting these values in an own repositry they will be compared against the values from last scan. Changed values in v$instance, v$database,v$parameter were logged in a logbook, number values were stored in an own table. ODIN stores the total and delta values from these metrics. With these values the tool creates ever night graphics for each metric and each instance. These graphics are listed in an own site. Because of having about 1000 graphics in 11g one can select chosen graphics in own profiles.
The graphics in a profile can be compared against the same graphics in the same profile for a second instance - this is useful to see if a database is driven by instances in a RAC environment.
The dba can list a chosen graphic from all instances.
All graphics are linked to collected object statistics. If there is a peak in a graphic the dba can navigate by clicking on the peak to the object, which was most used in the scan period. With another link he gets historical data of the selected object.
Two examples - in the following screenshot you will see the effect of bad plan stability, which resulted in unused indexes and fulltablescans. After Index rebuild the instance worked well again.
After patching Oracle RAC 11.2.0.3 to 11.2.0.4 in an ODA-environment two weeks ago the users told me of worse performance. After stopping dbconsole the performance looked better:
From the beginning - the start page shows the list of instances and if they are online or not. By exeuting a shellscript, which connects every 5 minutes the instances, the tool shows the reachability of the instances. If a checks failes, the script sends a mail, a sms and write a message in it's logbook
The icon near to instancename is linked to a checklist
Using the link "Grafiken" one reaches the list of profiles with the graphics
ALL means, the graphics of all metrics for this instance in a list. Because of more than 1000 graphics it is not useful to work with. In the morning I use the profile FIRSTLOOK to get an idea if something have changed. I this profile I choosed metrics like DB Time, scattered reads, physical reads, physical writes and so on ...
The green button "Projektvergleich" is linked to a site, in which the graphics of a selected profile can be compared against the list of graphics of second instance. Or against the same instance, but to a time some weeks ago. With the blue arrows one can navigate back or forward in time.
The listed graphics were created in the night - to get a current graphic one have to click on a graphic. in the field under the graphic is a memo field which I use to explain the meaning of this metric.
With the green button one can list the same metric of all instances
If you click on the peak in the graphic the graphic will zoom - it is for better targetting the scan period
With the next click on the peak one will reach the object values in sum of all schemas
In this examples the schema SA has a lot of physical reads. By clicking on the left icon one can drill down
Here is shown the objects and their values.
The next click on the left shows the object statistics of the selected object
Parameter can be listed
... and compared. Very usefull, if a testinstance works well and the live database should go live. A short equalness check of paramters can prevent from being blamed
A nice feature is the SQL-Depot. I often looked for the right SQL-script in several files and folders. With the SQL-Depot I can store the scripts and make them executable against an instance
The tool is available with french and english menues.
What do you think about his little tool, would there be a market for this?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle database monitoring tool [message #624820 is a reply to message #624811] |
Fri, 26 September 2014 03:44  |
|
lumanier
Messages: 4
Registered: September 2014
Location: Hamburg
|
Junior Member |
|
|
Hi Michel,
yes of course can you create your own profiles.
Maybe you will find an update interesting.
The tool collects data from remote instances and stores them for 21 days, after they will be automatically deleted
All graphics created at night would be stored permanently and not deleted. So it is possible, to navigate to older graphics.
I want to show the checklist in english version (sorry for german menue):
If I recognize in the graphics that the behavior of an instance changed, I want to now why. For this ODIN uses a logbook,
were all changes made to DB status, parameters or filesize will be automatically logged. If the changed behavior caused by changed parameters
the logbook will recognize this.
In the past a developer had have undesired sysdba access to a database and changed a parameter by himself without asking the dbas.
The performance went worse and all dbas told they didn't changed anything in the database. With the logbook we could see,
that a parameter has changed.
The following screenshot shows an example of the logbook for a selected instance.
In the upper menue are all instances listed which I want to monitor. To select the logbook for another instance one have to click on the
instancename in the upper menue.
The blue arrows under the headline refers to the list of instancesnames and can be used to select the logbook in upper or lower order of the instances
one by one.
On the left menue the link "Logbuch" (I have to change it to english naming) means logbook. With this link the whole logbook for all instances
is reachable. This tells me the changes in descending order from all instances and is a good info for the changes made in the last hours or days.
Another table REMINDER in ODIN is used for notes related to an instance, it works like a diary for me. In the checklist I call it DB history.
I used it for example to explain why I had set an underscoreparameter, when and why I had created indexes or were the documentation of interfaces
are stored on the file servers. With this feature I have a centralized documentation function.
The blue arrows ODIN let me navigate through the list of instances and their notes.
On the left menue one can select "DB-history" for the notes of all instances in time descending order. I use this feature also to remind me for things to do.
By navigating in ODINs checklist to "Tablespaces" one get a list of all tablespaces and their values for size an growth
With the icon next to tablespacename a graphic for its size and usage is reachable
These graphics are also linked to object statsitics to see which objects were changed in a selected time period
Or with english headers:
Maybe a database is regulary shutdown in the night then the 5-minute-online check would alarm.
A blackout list prevent from undesired alarmmessages.
A data access list shows the object statistics grouped by time or schema.
By schema:
For example, schema SYS is selected:
Physical reads for user SYS:
Summed values for the last 21 days for tablespaces
A selected tablespace and it's values in time
An instance overview in a poor design (I have to change it ...)
The SQL-Depot with dozens of statements is grouped over theme
A selected theme shows the stored statements for ARCHIVE infos
An executed statement looks like:
To store a new SQL-statement in the SQL-Depot the mask offer a field for short and long description.
The statements have no semicolon at the end and for every selected table a "@@" must follow. They will be
internally replaced by "@<INSTANCE_NAME>" to use the proper database link
With new patchsets or versions Oracle offers some new views.
To get an idea which infos they show there is a feature to list them and make them executable by click (also poor design, but it works)
By selecting BACKUP / RMAN related views are shown. With the icon on their left they can be executed.
The output of an executed view
Sometime an user related info is needed, for example the text of a procedure or the tables of a selected user and their related indexes oder their DDL.
This is in the moment in developer status and not nice in it's outfit, I have to create a better one. But it is fast and it is useful.
With the icons in the upper right corner ODIN can also be used in white/grey or white/blue if pee yellow not the right color.
Like the SQL-Depot there is a depot for links to share them with collegues.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
