FAQ
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!!!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: cgrabowy_at_fcg.com

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).

Search Discussions

  • Igor Neyman at Oct 29, 2002 at 7:58 pm
    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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    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).
  • STEVE OLLIG at Oct 29, 2002 at 7:58 pm
    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

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 1:29 PM
    To: Multiple recipients of list 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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: STEVE OLLIG
    INET: sollig_at_lifetouch.com

    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).
  • Rick_Cale_at_teamhealth.com at Oct 29, 2002 at 8:23 pm
    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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Rick_Cale_at_teamhealth.com

    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).
  • Wong, Bing at Oct 29, 2002 at 8:53 pm
    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.

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 11:59 AM
    To: Multiple recipients of list ORACLE-L

    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

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 1:29 PM
    To: Multiple recipients of list 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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: STEVE OLLIG
    INET: sollig_at_lifetouch.com

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Wong, Bing
    INET: bing.wong_at_IngramMicro.com

    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).
  • Miller, Jay at Oct 29, 2002 at 9:18 pm
    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.

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 2:29 PM
    To: Multiple recipients of list 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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Miller, Jay
    INET: JayMiller_at_TDWaterhouse.com

    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).
  • Mark Richard at Oct 29, 2002 at 9:43 pm
    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.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --
    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).
  • DENNIS WILLIAMS at Oct 30, 2002 at 5:14 am
    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

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 1:29 PM
    To: Multiple recipients of list 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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    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).
  • Ruth Gramolini at Oct 30, 2002 at 12:48 pm
    That's a great tip! I never know that. Thanks! Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 29, 2002 2:58 PM

    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!!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).
  • Igor Neyman at Oct 30, 2002 at 1:53 pm
    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
    That's a great tip! I never know that. Thanks! Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 29, 2002 2:58 PM


    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!!!



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    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).
  • Paquette stephane at Oct 30, 2002 at 2:59 pm
    Oracle, DB2 UDB and Sql Server

    Igor Neyman a écrit : >
    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

    That's a great tip! I never know that. Thanks! Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 29, 2002 2:58 PM


    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!!!



    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).

    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    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).
    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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?paquette=20stephane?=
    INET: stephane_paquette_at_yahoo.com

    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).
  • Ruth Gramolini at Oct 30, 2002 at 3:09 pm
    No, just Oracle. That's enough for me! But I thought it was neat, and
    would like to have that one.
    Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, October 30, 2002 8:53 AM
    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

    That's a great tip! I never know that. Thanks! Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 29, 2002 2:58 PM


    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!!!



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).
  • Farnsworth, Dave at Oct 30, 2002 at 3:38 pm
    I am! I am getting DB2 on an AS400 thrown at me too in the near future.

    Dave

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

    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
    That's a great tip! I never know that. Thanks! Ruth
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 29, 2002 2:58 PM


    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!!!



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ruth Gramolini
    INET: rgramolini_at_tax.state.vt.us

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Farnsworth, Dave
    INET: DFarnsworth_at_Ashleyfurniture.com

    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).
  • Fink, Dan at Oct 30, 2002 at 4:54 pm
    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.

    -----Original Message-----
    Sent: Tuesday, October 29, 2002 2:44 PM
    To: Multiple recipients of list ORACLE-L

    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.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.com

    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 29, '02 at 7:29p
activeOct 30, '02 at 4:54p
posts14
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase