Re: Adhoc queries and limiting the amount of records queried...

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 <ORACLE-L_at_fatcity.com>       
                    <cgrabowy_at_fcg.       cc:                                                                       
                    com>                 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.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).