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

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-PROFILE.html#GUID-ABC7AE4D-64A8-4EA9-857D-BEF7300B64C3

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.
sunhuxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
sunhuxAuthor Commented:
So to generate an alert when the threshold is reached/crossed, how do we go about doing it?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

johnsoneSenior Oracle DBACommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunhuxAuthor Commented:
Thanks very much for educating me on what's feasible
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.