Layman steps to set up profile to detect above certain amount of rows/data queried

sunhux used Ask the Experts™

I'm no DBA but need to liaise with our DBA.

I'm looking into setting up a profile so that an alert /detection that a query that returns more than 20MB (or 200,000 rows) have been run
(possibly within the last 1 hour as plan to detect  on hourly basis).

Based on above oracle's URL, can someone give me a more layman & step by step instruction?  A ballpark estimation to get to 20MB or 200,000 rows is fine.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA
As stated in your other question, a profile would not generate an alert.  It would stop the query from running whenever it hits the limit.

If you are looking for a way to let the query run and get an alert that a query ran with those parameters, then a profile is not the way to do it.

If you are looking to stop the query and get an alert, I have never done that.  I did find with a quick search that Oracle Auditing seems to be able to trap this as a "logoff by cleanup" event.  Based on Oracle Doc ID 1372067.1, it appears that it may not be written to AUD$ and is only written to an OS audit trail, the reference was old, so it may have changed by now.

As far as the exact command to create or modify a profile to give those parameters, we cannot give it to you as it is based on blocks and we don't know your block sizes.  You and/or your DBA should be able to do that kind of math.  I would highly recommend subjecting SYS, SYSTEM and your DBA users to the profile.
Mark GeerlingsDatabase Administrator
Oracle resource profiles do not offer the kind of query limit that you are asking for.  Oracle resource profiles can stop a query (and forcefully close the client's connection) after either of these events:
1. a certain number of logical blocks have been read
2. a certain amount of CPU time has been used.  

Note that a logical block in an Oracle database may contain anywhere between 0, 1 (or just a part of 1) and a 1,000 rows (or possibly more if you have 16k or 32k blocks) depending on how many bytes are in each row.  And, these numbers are totally unrelated to the numbers of rows that the query will actually return to the user.  Oracle needs to read the blocks to determine which rows match the conditions in the "where" clause of the query.


So to generate an alert when the threshold is reached/crossed, how do we go about doing it?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA
Personally, I don't know of a way to get Oracle Auditing to do that.  There certainly could be a way, but I'm not aware of it.

You can certainly try to write your own alert.  A way (certainly not the only way), would be to check V$SQL at regular intervals.  It is certainly not fool proof.  It can certainly miss things.  You probably want to look at the ROWS_PROCESSED column.  Just be aware that the numbers are for total executions for the query.  So, if it is run 100 times, you need to divide the total rows by the number of executions to get an average.  Assuming you have bound queries, which you should, the number of rows can vary by execution, so it really is an average.  Also, if the query is killed or stopped in the middle, you only get the number of rows returned so far.

I would think that you should be able to do it with AWR reports as well.  Not sure if you are using, or have the packs licensed, to do that.  It would be similar to V$SQL, but again, you have a bigger window of missing some things.
Database Administrator
As far as I know, Oracle does not provide a way to audit the number of rows (or the amount of data) that is returned by a query.

The resource profiles that Oracle provides are all related to the amount of data that Oracle must read before it can determine how many rows to return.  For example, if the business question is: "Which customer ordered the most product last year?" an Oracle query may have to read millions (or more) records, even though it will return only one.  Setting a resource profile too low could prevent a query like that from completing.

And auditing in Oracle can track inserts, updates and deletes, but not queries (select statements).


Thanks very much for educating me on what's feasible

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial