OraFAQ Forum: Server Administration » prevent high cost queries

Home » RDBMS Server » Server Administration » prevent high cost queries (Oracle 11g Solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

prevent high cost queries [message #636158]

Sat, 18 April 2015 07:03

uman2631
Messages: 16
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?

Report message to a moderator

Re: prevent high cost queries [message #636159 is a reply to message #636158]

Sat, 18 April 2015 07:09

John Watson
Messages: 8963
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

Report message to a moderator

Re: prevent high cost queries [message #636161 is a reply to message #636159]

Sat, 18 April 2015 08:43

uman2631
Messages: 16
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."

Report message to a moderator

Re: prevent high cost queries [message #636162 is a reply to message #636161]

Sat, 18 April 2015 08:55

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: prevent high cost queries [message #636166 is a reply to message #636162]

Sat, 18 April 2015 09:50

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: prevent high cost queries [message #636215 is a reply to message #636162]

Mon, 20 April 2015 02:52

Roachcoach
Messages: 1576
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

Report message to a moderator

Previous Topic:	Can't resize datafile: ORA-03297
Next Topic:	DB Restart after Table Partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 22:26:57 CST 2025