Home » RDBMS Server » Server Administration » prevent high cost queries (Oracle 11g Solaris)
prevent high cost queries [message #636158] Sat, 18 April 2015 07:03 Go to next message
uman2631
Messages: 13
Registered: November 2011
Junior Member
I have some users that are executing ridiculous queries against multiple tables with no join conditions. Things like:
select * from tableA, tableB, tableC, tableD, tableE;

This kills our database performance until we run out of TEMP space, and the DBAs get an alert, and we eventually kill the job.
The DBAs are telling us that there's no way for them to prevent users from running these kinds of queries. Is that right?
Ideally we'd like to prevent any query with a cost > some threshold from even getting started. Is there any way to do this?
If not, is there some other standard practice beyond waiting for TEMP space to fill up to manage these things?
Re: prevent high cost queries [message #636159 is a reply to message #636158] Sat, 18 April 2015 07:09 Go to previous messageGo to next message
John Watson
Messages: 7623
Registered: January 2010
Location: Global Village
Senior Member
If you have Enterprose Edition licences, this is simple to do with the Resource Manager. In Standard Edition, you do it wih profiles which are not as precise.

Your DBAs should know this already. You might want to consider firing them and outsourcng your DB admin to smeone more competent (my boss will be happy to give you a quote Smile ).
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Re: prevent high cost queries [message #636161 is a reply to message #636159] Sat, 18 April 2015 08:43 Go to previous messageGo to next message
uman2631
Messages: 13
Registered: November 2011
Junior Member
I appreciate the reply. Is there some public documentation that I could read up on that you could point me to? I need something more than, "you should be able to do this with resource manager or profiles."
Re: prevent high cost queries [message #636162 is a reply to message #636161] Sat, 18 April 2015 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>I have some users that are executing ridiculous queries against multiple tables with no join conditions.
It might be simpler to not have folks who issue such SELECT statements against Production DB.
It is always a challenge to solve procedural problems with a technical solution.
Re: prevent high cost queries [message #636166 is a reply to message #636162] Sat, 18 April 2015 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there some public documentation that I could read up on that you could point me to? I need something more than, "you should be able to do this with resource manager or profiles."


https://docs.oracle.com/cd/E11882_01/nav/portal_booklist.htm

Don't be afraid to use the "Search" field at top of the page.

Re: prevent high cost queries [message #636215 is a reply to message #636162] Mon, 20 April 2015 02:52 Go to previous message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
BlackSwan wrote on Sat, 18 April 2015 14:55
>I have some users that are executing ridiculous queries against multiple tables with no join conditions.
It might be simpler to not have folks who issue such SELECT statements against Production DB.
It is always a challenge to solve procedural problems with a technical solution.


I'm in this camp.

A significant undertaking to provide a non-air tight solution or a swift backhand and a "drop user" or "revoke ..." .....wins every time Smile
Previous Topic: Can't resize datafile: ORA-03297
Next Topic: DB Restart after Table Partition
Goto Forum:
  


Current Time: Tue Oct 23 02:54:42 CDT 2018