Getting Data In

How to index Oracle dynamic performance views (v_$transaction, v_$session etc) in Splunk?

Explorer

I want to index the dynamic performance views that are available in SYS of Oracle Database on Splunk. These views include SYS.V_$TRANSACTION, SYS.V_$SESSION and so on.
The advantage of indexing these, is that it is dynamic and helps track the database activities real-time. In contrast, events are logged in the trace log only after the completion of an SQL query.
The challenge is, only the database admin has rights to read SYS views.
How can I set this up? Can you please suggest some way to go about this?

1 Solution

Builder

You can set up a user with the "SELECT ANY DICTIONARY" privilege, which will include access to to the V$ views without granting full DBA privileges. Unfortunately, it will also grant select access to certain dictionary tables that your DBA might not want exposed, like USER$, so no guarantees that this will be allowed. You should also be able to create a custom role with select access to the specific views you want, but this may take a while to fine-tune to the point where you're seeing everything you want.

The other thing to be aware of is that v$ views are extremely volatile - the information in them is constantly changing and is only accurate for the split second that the data is returned. Many database activities, including some entire sessions, are completed in milliseconds, so depending on what your refresh rate on your Splunk input is, you will likely miss a LOT of data, including events that you want to capture. Conversely, if your monitor rate is very fast, you will be collecting a lot of duplicate data on longer-running activities that will not really benefit you much, and will quickly exhaust your Splunk license - and still likely miss a lot of data on activities that you want to capture. A better option is to target specific activities that you want to monitor with general or fine-grained auditing, then Splunk the audit trails for changes. This allows you to focus your attention on the things that matter most, while not sucking up your Splunk license with a lot of data that will ultimately be meaningless.

For a complete step-by-step guide on configuring Splunk for Oracle and the various ways to collect data, including auditing, see the Real-Time Log File Analysis for Oracle 11g add-on.

View solution in original post

Builder

You can set up a user with the "SELECT ANY DICTIONARY" privilege, which will include access to to the V$ views without granting full DBA privileges. Unfortunately, it will also grant select access to certain dictionary tables that your DBA might not want exposed, like USER$, so no guarantees that this will be allowed. You should also be able to create a custom role with select access to the specific views you want, but this may take a while to fine-tune to the point where you're seeing everything you want.

The other thing to be aware of is that v$ views are extremely volatile - the information in them is constantly changing and is only accurate for the split second that the data is returned. Many database activities, including some entire sessions, are completed in milliseconds, so depending on what your refresh rate on your Splunk input is, you will likely miss a LOT of data, including events that you want to capture. Conversely, if your monitor rate is very fast, you will be collecting a lot of duplicate data on longer-running activities that will not really benefit you much, and will quickly exhaust your Splunk license - and still likely miss a lot of data on activities that you want to capture. A better option is to target specific activities that you want to monitor with general or fine-grained auditing, then Splunk the audit trails for changes. This allows you to focus your attention on the things that matter most, while not sucking up your Splunk license with a lot of data that will ultimately be meaningless.

For a complete step-by-step guide on configuring Splunk for Oracle and the various ways to collect data, including auditing, see the Real-Time Log File Analysis for Oracle 11g add-on.

View solution in original post

Builder

There really isn't a mechanism outside of specialized performance monitoring software that would allow you to capture all of the v$session data. If you are concerned about long running queries or commands or something like that, you should be able to execute summary queries or specialized alert queries to look for specific things. Let the database summarize and evaluate the session information and only collect the high level metrics into Splunk. This would allow you to keep the total volume of data down for your Splunk license, too.

e.g. "select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), count(*) session_count from v$session where last_call_et > 600 and lower(event) like '%wait'"

Explorer

Yes, that makes sense. Thanks!

0 Karma

Explorer

Thank you!
Yes, missing data is definitely a problem, if not duplication of data.
I did check out the Real-Time Log File Analysis for Oracle 11g add-on. It does help me in some ways, but I still can't look at queries running on a real-time basis, before their execution is complete. Though for now, this should do. Thanks again 🙂

0 Karma

Motivator

Don't know too much about Oracle but here is a sugestion for what it is worth. Can you setup a user that has admin permissions and restrict the user to only the views you want it to see [SYS.V_$TRANSACTION, SYS.V_$SESSION and so on].
Then setup the bdconnect to use this account and read out the data to an index in splunk?

0 Karma