1

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?

user1285928
  • 1,328
  • 29
  • 98
  • 147
  • 1
    You 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 Dec 07 '15 at 01:31
  • I'm voting to close this question as off-topic because it belongs on dba.stackexchange.com – Frank Schmitt Feb 28 '18 at 10:22

2 Answers2

2

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.

Community
  • 1
  • 1
J91321
  • 697
  • 1
  • 7
  • 19
  • Do you know what SQL queries are used by Oracle Enterprise Manager to collect data? – user1285928 Dec 07 '15 at 22:29
  • 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. – J91321 Dec 07 '15 at 23:54
2

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

Image of query and result

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

Oracle Metrics List by Don Burleson

VINAY VKK
  • 23
  • 6