CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user1285928
user1285928

Reputation: 1476

Monitor Oracle performance with SQL queries

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?

Upvotes: 1

Views: 4100

Answers (3)

Peter Ramm
Peter Ramm

Reputation: 141

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.

https://github.com/rammpeter/panorama

Upvotes: 0

VINAY VKK
VINAY VKK

Reputation: 23

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

Upvotes: 2

J91321
J91321

Reputation: 727

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.

Upvotes: 2

Related Questions