[Oracle-l] Adhoc queries and limiting the amount of records queried...

Adhoc queries and limiting the amount of records queried...In SQL Server: "set rowcount " before running the query does the same thing as oracle's ROWNUM in "where" clause.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com


Original Message -----
From: Grabowy, Chris
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 29, 2002 2:29 PM
Subject: Adhoc queries and limiting the amount of records queried...

I just wanted to ping the list to see what other people have done to control or constrain adhoc query users???

We have a group that is struggling with the adhoc query piece that's in production. Some of the users end up firing off insane queries. The group is trying to find a way to limit the amount of records queried for, so that a wild query doesn't hose the database.

Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is another. Any other thoughts??

Dare I ask.this custom app also runs on SQL Server, so SQL Server ideas would also be appreciated.

Many thanks!!!

many shops prohibit ad-hoc queries in busy OLTP databases and many of those
have datawarehouses where ad-hoc is allowed. IMHO that's the only way
prevent ad-hoc queries from causing problems in your OLTP environment.


SQL> select * form users where clue > 0;


no rows selected

You could implement FGAC or create views that only contains necessary
records.

Rick

"Grabowy,
Chris" To: Multiple recipients of list ORACLE-L

com> cc:
Sent by: Subject: Adhoc queries and limiting the amount of
root_at_fatcity.c records queried...
om

10/29/2002
02:29 PM
Please respond
to ORACLE-L

I just wanted to ping the list to see what other people have done to
control or constrain adhoc query users???

We have a group that is struggling with the adhoc query piece that's in
production. Some of the users end up firing off insane queries. The group
is trying to find a way to limit the amount of records queried for, so that
a wild query doesn't hose the database.

Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is
another. Any other thoughts??

Dare I ask?this custom app also runs on SQL Server, so SQL Server ideas
would also be appreciated.

Many thanks!!!

This is what I do in my shop since they allow(not me) ad hoc queries...

I modified the user id that run ad hoc queries to default to a TEMP
tablespace which is rather small. :)
They failed most of the time and I receive no complains. :)

Bing Wong
Open Systems Database Administrator
x25721

This e-mail may contain material that is confidential. Any review, reliance
or distribution by others or forwarding without express permission is
strictly prohibited. If you are not the intended recipient, please contact
the sender and delete all copies received.

Aside from severely restricting access to analysts who need to investigate
problems in production data we limit the CPU time in their profile to 5
minutes. That's a pretty generous limit and they've never had any issues
with it.

Chris,

I don't really have a nice solution, however I would have to recommend
against using rownum type solutions...

There is a really good chance that the users won't understand the
implications of such a clause and therefore won't understand it's affect
upon the results. A user might ask for the sum(sales) of a given
salesperson, for example, and would then have no idea if the result
obtained was correct or an invalid result caused by the rownum clause.

As someoe suggested - are these queries being executed against an OLTP or
OLAP system? If they are connecting to the OLTP then you may need to
create a replica of some sort for them to run their queries against.

You talk about "insane queries" - Are the queries "insane" because they are
complex (but necessary to answer a question) or because they are written
poorly? If they are written poorly then user education is required. If
the queries are complex then determine if they are executed often and, if
so, look at tuning those queries and turning them into a standard report.
I always take the approach of "if that's what they need to know then I'll
help them find out".

Oracle does also provide some features to limit resources to certain users
once the machine hits 100% utilisation. You could look into this so that
the adhoc queries get choked when the system is busy, leaving everything
else to run ok. This way the adhoc users will get valid results (most
important - if they don't get complete results then they might as well not
run the query at all) but they will have to wait a while (might
subconsciously teach them not to write bad queries).

Help I've helped a little,

Mark.

"Grabowy,
Chris" To: Multiple recipients of list ORACLE-L
Subject: Adhoc queries and limiting the amount of records queried...
Sent by:
root_at_fatcity.c
om

30/10/2002
06:29
Please respond
to ORACLE-L

I just wanted to ping the list to see what other people have done to
control or constrain adhoc query users???

We have a group that is struggling with the adhoc query piece that's in
production. Some of the users end up firing off insane queries. The group
is trying to find a way to limit the amount of records queried for, so that
a wild query doesn't hose the database.

Appending a ROWNUM to the WHERE clause is one idea. Using USER PROFILEs is
another. Any other thoughts??

Dare I ask?this custom app also runs on SQL Server, so SQL Server ideas
would also be appreciated.

Many thanks!!!

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Privileged/Confidential information may be contained in this message.

If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Chris - Another alternative for you to consider, depending on your Oracle
version - Resource Manager.

Dennis Williams
DBA, 40%OCP

Lifetouch, Inc.
dwilliams_at_lifetouch.com

That's a great tip! I never know that. Thanks! Ruth

Ruth,

Are you in the same boat, dealing with both: Oracle and SQL Server?

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message -----
To: "Multiple recipients of list ORACLE-L"
Sent: Wednesday, October 30, 2002 7:48 AM

Oracle, DB2 UDB and Sql Server

Igor Neyman a écrit : >
Ruth,
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com

Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca

No, just Oracle. That's enough for me! But I thought it was neat, and
would like to have that one.
Ruth

I am! I am getting DB2 on an AS400 thrown at me too in the near future.

Dave

I'll add my $.02 about rownum...
Rownum is highly variable. It all depends upon access paths, sorting,
physical structures, etc. Even careful use can result in non-repeatable
reads, bad data, etc. Many experienced and talented techies and power users
will not completely understand the oracle internal storage and transaction
architecture and therefore don't completely understand rownum. This is not a
dig against developers or users. Most of them don't have the need to get to
know the under-the-covers Oracle.

When properly understood and very carefully used, it can be a great help.