I would like to ask several questions about Oracle monitoring. Can I use SQL queries to get monitoring data like CPU utilization, RAM utilization, HDD space, table space and etc. Do I need to use privileged user or I can use every Oracle user? If this is not possible what are the alternatives?
2
-
1You ask more for system metrics than Oracle. Via Oracle diagnostics instruments you can monitor sessions and their memory and temp allocations, the waits that sessions wait for, space allocated by objects, table spaces and data files. You need privileges to access data dictionary - mostly select on (G)V$ dynamic performance views.Husqvik– Husqvik2015-12-07 01:31:50 +00:00Commented Dec 7, 2015 at 1:31
-
I'm voting to close this question as off-topic because it belongs on dba.stackexchange.comFrank Schmitt– Frank Schmitt2018-02-28 10:22:10 +00:00Commented Feb 28, 2018 at 10:22
Add a comment
|
3 Answers
From the things you mentioned I think only tablespace usage should be monitored using queries. (Check very good query here: Find out free space on tablespace)
CPU and filesystem should be monitored on OS level, (exception probably being ASM where queries are probably easier to use than ASM console).
If you want to monitor usage of individual sessions then you need privileges to access data dictionaries e.g. v$sql_workarea_active and v$session to get RAM usage for session or query or v$session_wait to get information on waits etc. I don't know what exactly do you wish to monitor, but Oracle documentation is your friend to find information on these dictionaries.
Best solution I know is to use Oracle Enterprise Manager where you can easily monitor all metrics and also create your own.
You can also implement your own metrics monitoring with open source tool like Zabbix (or other of your choice). This is also much cheaper way.
2 Comments
user1285928
Do you know what SQL queries are used by Oracle Enterprise Manager to collect data?
J91321
No not really, I don't think they are publicly available. I can see only results of these metrics through UI. You'll have to write your own.
Oracle has performance views where we will get information about Oracle Database Performance.
To answer your question you can query the v$osstat view to get the info about CPU utilization, RAM utilization,HDD etc using SQL queries.Official Oracle documentation on v$osstat
There are also so many other views especially v$sysstat, v$sqlstat, v$sys_time_model, v$metric where you can dig a lot of performance related information. You can refer the below link to see all the basic metrics that one can query in Oracle
Comments
To Monitor DB behaviour using SQL queries the DB user should have the SELECT ANY DICTIONARY privilege if you‘re not using a system account. Keep in mind that selecting from some views like AWR views (DBA_HIST_xxx) requires the Enterprise Edition with Diagnostics Pack rsp. Tuning Pack license.
To learn how to select several states of the DB by SQL you may use the free analysis tool „Panorama for Oracle“. Setting the environment variable PANORAMA_LOG_SQL=true before starting the app will log all SQL statements to the console that are executed while using the browser GUI of this app.
