Home » Other » Marketplace » Oracle database monitoring tool
Oracle database monitoring tool [message #624708] Wed, 24 September 2014 17:16 Go to next message
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.
http://phys-reads.com/screenshots/db%20file%20scattered%20read.png


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:
http://phys-reads.com/screenshots/ABSDEPRO1DB%20TIME24.09.2014%2012%2002.png


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
http://phys-reads.com/screenshots/Liste_Instanzen.PNG


The icon near to instancename is linked to a checklist
http://phys-reads.com/screenshots/Database_Entree.PNG


Using the link "Grafiken" one reaches the list of profiles with the graphics
http://phys-reads.com/screenshots/Graphics_1_ODIN.PNG


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 ...
http://phys-reads.com/screenshots/Graphics_3_ODIN.PNG


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.
http://phys-reads.com/screenshots/Graphics_6_ODIN.PNG


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.
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_1.PNG


With the green button one can list the same metric of all instances
http://phys-reads.com/screenshots/Metrik_einzeln_Vergleich.PNG

If you click on the peak in the graphic the graphic will zoom - it is for better targetting the scan period
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_2_zoom.PNG


With the next click on the peak one will reach the object values in sum of all schemas
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_2_zoom.PNG


In this examples the schema SA has a lot of physical reads. By clicking on the left icon one can drill down
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_3_Owner.PNG

Here is shown the objects and their values.
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_4_Owner_Objekte.PNG


The next click on the left shows the object statistics of the selected object
http://phys-reads.com/screenshots/Metrik_von_Delta_zu_Statistik_6_Owner_ObjektHistorie.PNG


Parameter can be listed

http://phys-reads.com/screenshots/Parameter_1_ODIN.PNG

... 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
http://phys-reads.com/screenshots/Parameter_2_ODIN.PNG


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
http://phys-reads.com/screenshots/SQL_Depot.PNG


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 #624719 is a reply to message #624708] Thu, 25 September 2014 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 21688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not a DBA (just a poor developer); however, what to say but impressive!
Re: Oracle database monitoring tool [message #624728 is a reply to message #624708] Thu, 25 September 2014 01:15 Go to previous messageGo to next message
John Watson
Messages: 8599
Registered: January 2010
Location: Global Village
Senior Member
Looks well cool. I have PM'ed you regarding this.
Re: Oracle database monitoring tool [message #624777 is a reply to message #624728] Thu, 25 September 2014 09:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Cool!

Is the tool customizable?
Re: Oracle database monitoring tool [message #624809 is a reply to message #624777] Fri, 26 September 2014 00:17 Go to previous messageGo to next message
lumanier
Messages: 4
Registered: September 2014
Location: Hamburg
Junior Member
Thanks for your feedback! Smile

Lalit - what do you mean with customizable? Do you think about to monitor instances from several customers and keep their data in different tables?
No, this is not implemented yet, but can be realized if it is necessary.
Re: Oracle database monitoring tool [message #624811 is a reply to message #624809] Fri, 26 September 2014 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think he means can we add our own collection, chart and graph?

You said "The tool is available with french and english menues"
but is this also the case for menu legends and so on; your pictures show German ones.

Re: Oracle database monitoring tool [message #624820 is a reply to message #624811] Fri, 26 September 2014 03:44 Go to previous message
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):
http://phys-reads.com/screenshots/checklist_eng.PNG


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.
http://phys-reads.com/screenshots/logbook_eng.PNG


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.

http://phys-reads.com/screenshots/logbook_all_eng.PNG


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.
http://phys-reads.com/screenshots/Reminder_ODIN_eng.PNG

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.
http://phys-reads.com/screenshots/reminder_all_eng.PNG



By navigating in ODINs checklist to "Tablespaces" one get a list of all tablespaces and their values for size an growth
http://phys-reads.com/screenshots/Tablespaces_1.PNG

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
http://phys-reads.com/screenshots/Tablespaces_2.PNG

Or with english headers:
http://phys-reads.com/screenshots/TBS_UNDO.PNG



Maybe a database is regulary shutdown in the night then the 5-minute-online check would alarm.
A blackout list prevent from undesired alarmmessages.
http://phys-reads.com/screenshots/check_online_Intervalle.PNG



A data access list shows the object statistics grouped by time or schema.
http://phys-reads.com/screenshots/Data_access_list_eng.PNG


By schema:
http://phys-reads.com/screenshots/data_access_sum_schema_values.PNG

For example, schema SYS is selected:
http://phys-reads.com/screenshots/data_access_selected_schema_by_time.PNG

Physical reads for user SYS:
http://phys-reads.com/screenshots/data_access_selected_schema_by_time_graphic.PNG


Summed values for the last 21 days for tablespaces
http://phys-reads.com/screenshots/data_access_by_tablespacePNG.PNG

A selected tablespace and it's values in time
http://phys-reads.com/screenshots/data_access_by_tablespace_and_time.PNG


An instance overview in a poor design (I have to change it ...)
http://phys-reads.com/screenshots/instance_overview.PNG

The SQL-Depot with dozens of statements is grouped over theme
http://phys-reads.com/screenshots/SQL_Depot_grouped.PNG

A selected theme shows the stored statements for ARCHIVE infos
http://phys-reads.com/screenshots/SQL_Depot_grouped_selected_theme.PNG


An executed statement looks like:
http://phys-reads.com/screenshots/SQL_Depot_executed_statement.PNG

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
http://phys-reads.com/screenshots/SQL_Depot_Grouped_Server_Entwurfsansicht.PNG



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)
http://phys-reads.com/screenshots/Systemviews_1.PNG

By selecting BACKUP / RMAN related views are shown. With the icon on their left they can be executed.
http://phys-reads.com/screenshots/Systemviews_2.PNG

The output of an executed view
http://phys-reads.com/screenshots/Systemviews_1.PNG

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.
http://phys-reads.com/screenshots/Matrix_1.PNG


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.

http://phys-reads.com/screenshots/Links.PNG

http://phys-reads.com/screenshots/Links_2.PNG

http://phys-reads.com/screenshots/Links_3.PNG

http://phys-reads.com/screenshots/Links_4.PNG
Previous Topic: Lighty for Oracle
Next Topic: Oracle Apps E-Busines Suite Training
Goto Forum:
  


Current Time: Tue Sep 28 14:07:42 CDT 2021