Does Splunk DB Connect 2 cause full table scans for every query against Oracle?

[dolivasoh]

My DBA isn't very happy with DBConnect2. He states that Oracle will do a full table scan, even after a tailing input has caught up to the top, for each and every query because DBConnect2 wraps all queries in SELECT * FROM ({{query}}). I'm having trouble finding documentation on this query technique and its effects. Does anyone know why DBConnect2 queries this way? Is there a way to override it? And does it in fact cause a full table scan, even if one or few rows are returned?

[dlisicre]

It would seem that with mysql I am having the same issue:

https://answers.splunk.com/answers/451533/splunk-db-connect-23-why-is-rising-column-value-is.html

If there is any answer to this it would be appreciated!

[jsilverbears]

I have noted the exact same behavior in my tests and posted a similar question to that effect:
https://answers.splunk.com/answers/386332/db-connect-2-inefficient-querying.html

I gave a bit more information on my tests over this. Hopefully, they will give me more of an answer than "It doesn't do that" when clearly, it does. At least that is what it is currently doing.

[dolivasoh]

Interesting. So perhaps this remains un-answered?

[jsilverbears]

It would seem so. I have workarounds in place and they seem to be working well. Mostly I am just limiting by time and making sure that only data over a rolling time frame is being pulled. I am using the advanced query section to ensure this.

[tmuth_splunk]

There are two basic ways to get rows in Oracle, independent of splunk: Full table scans or Index scans that point to table rowids. Yes, there are lots of permutations of this such as partition pruning, index fast-full scans, etc, but those are the basics.

If you are filtering using where predicates on columns that are indexed, the optimizer (CBO) has the option to choose an index to access the table if the cost of using the index is less than a full table access path. How does the CBO determine cost? Accurate object stats (table, index, etc) and accurate system stats. Jonathan Lewis covers this nicely in Cost-Based Oracle Fundamentals: http://www.amazon.com/Cost-Based-Oracle-Fundamentals-Experts-Voice/dp/1590596366

Have your DBA monitor the query issued by splunk. Is there an index that covers all of the filter predicates? Are there recent and accurate stats on the table and indexes? Are you calling a PL/SQL function in your predicates that would prevent the use of the indexes? Do you have any hints such as FULL?

Also let your DBA know that they can enable the database resource manager to constrain the amount of resources that splunk consumes.

Regards,
Tyler Muth

[pmdba]

We use DBConnect 2 on Oracle extensively and have never noted the behavior you are describing. We monitor our database performance extensively, and query against some extremely large tables, so we would notice if a full table scan was taking place on a regular basis.

[jsilverbears]

I downvoted this post because their answer was basically, "it doesn't do that. we would know if it does.". that is not an answer. that is a dismissal.