CODEWITHSUNDEEP
performanceoracle-database
Stephanie Page
Stephanie Page

Reputation: 3893

Can a single SELECT significantly degrade the performance of an Oracle Database?

All,

Consider

Can you construct any circumstance in which a single user with a single connection and read-only access to user tables (not system tables or v$ or x$ tables) degrade the overall performance of an Oracle database. Also list mitigation strategy if any.

As stipulated, consider that this is not about a database which is a few CPU-cycles away from saturation such that any additional load would be dangerous. This is about a well sized box for the current workload.

E.G.

If a user uses a parallel hint, Oracle may use a very high DOP to execute that query and starve other processes of CPU. Mitigation: explain to user that Parallel hints are forbidden.

Upvotes: 3

Views: 804

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

The simplest approach would be to issue a query that does a Cartesian product of your biggest table with itself a bunch of times. That will blow out your TEMP tablespace rather quickly and generate errors for other sessions that need to sort. You can mitigate that either by granting limited quotas on TEMP (which may get tricky if this is an application account that is used by multiple people simultaneously rather than an individually identifiable account) or by using Resource Manager to kill sessions that either run too long or that use too much CPU or I/O resources.

Even without an explicit PARALLEL hint, it's possible that Oracle will use parallelism automatically. Depending on the Oracle version and how you've configured parallelism, turning on parallel automatic tuning to limit the total number of parallel workers at any time. Of course, that doesn't prevent the read-only user from creating a dozen sessions each of which spawns a couple of parallel workers that choke off other sessions that you actually want to run more parallel workers. You can use Resource Manager to configure priorities for different types of workload if and when the system becomes CPU constrained.

And if you allow an anonymous PL/SQL block, there are more ways to generate havoc by, for example, creating a nested table that gets filled with billions of rows until your PGA is exhausted.

Upvotes: 6

Related Questions