CODEWITHSUNDEEP
oracle-databaseoracle11gtoad
Justin
Justin

Reputation: 23

How to limit SQL-DML resource use

I have a single server running Oracle 11g and Server 2008r2 with 32GB of RAM and 2 quad core CPUs at 3Ghz (8 cores total, no HT). My disks consist of 4 10,000RPM SAS drives in RAID 10.

When I execute a DML statement to insert rows from another table and delete them, this can cap out the CPU on my box and lock up the DB and prevent new records from being written.

Is there a way to limit the amount of system resources or oracle resources that can be used by a SQL query?

Thank you

Upvotes: 2

Views: 263

Answers (1)

APC
APC

Reputation: 146219

I agree with @JustinCave that it seems unlikely that one session can suck up all the CPU on a multi-core server. Unless you have more than one database installed.

Anyway, from an Oracle perspective, your DBA can configure consumer groups and resource plans which control how sessions can use various resources, including CPU. Find our more.

Resource management is a strategic approach, suitable for handling persistent resource allocation problems. If you have an issue with a single query you would be better off tuning it.

Upvotes: 1

Related Questions