Oracle – Parallel To PGA (P2P) & Serial To SGA (S2S) – Caching Differences And When Parallelism Is Not The Panacea For Performance!

If you are running into memory issues such as below, there could be several causes but one of the causes is excessive parallelism and exhaustion of PGA memory.

“ORA-01034: ORACLE not available / ORA-27102: out of memory / Linux-x86_64 Error: 12: Cannot allocate”

Please read on to find out how to resolve this. There are hidden parameters to limit PGA MAX memory but you limit the overall performance by doing so. There are other options to look at.

FACT: Parallel Execution uses only PGA and Serial execution only SGA

Oracle is vast. Just understanding it is hard-enough, leave alone mastering it. A while back, a former colleague of mine (Stamen Kolev) brought to my attention something he read, separately. Thanks Stamen. I was researching something related to SGA/PGA usage (more on this later) but did not quite understand the symptoms I was seeing until he casually mentioned – Parallel Execution uses only PGA and Serial execution only SGA and for me, everything fell in place. This write-up is based on this nice coverage on Parallel Execution that he sent me.

Key takeaway is – Parallel query execution only* uses PGA (non-shared, private server process memory) and Serial query execution uses SGA (global, shared memory).

*depends on hidden parameter _small_table_threshold

We have seen the Oracle memory structures before but a quick recap of two main areas is in order – System Global Area (SGA) and Program Global Area (PGA). This is how the internal memory structure looks:

oracle_sga

** Image from Oracle Docs

Basic definition of SGA and PGA

SGA

Shared memory area common to all processes in the instance; has several sub areas.

PGA

Non-shared memory specific to each server process

Parallel vs. Serial Query Execution – Data Caching Differences

Today, we are specifically interested in “parallel” vs “serial” query execution and the differences between the two in data-caching. All the data fetched from the disk is first cached in memory (to speed-up re-reads of same data), typically the Buffer Cache in SGA but in parallel execution of queries beyond a certain data limit, caching happens in the PGA.

Definitions – Parallel & Serial

Let us define them both first:

  • Parallel Execution – Multiple processes work simultaneously to fetch portions of data from disk in parallel which is then put together to form the final result, the theory being, n-workers working on a task take 1/nth of the time it would take 1 worker to do the same.
  • Serial Execution – The name means it all

Influencing “Parallelism”

Oracle has its own internal algorithms to decide when to parallelize and when not to, although we have a role in helping it decide using

Table & index attributes NOPARALLEL

PARALLEL DEGREE [DEFAULT/x]

Query hints PARALLEL(degree)

PARALLEL(table,degree), PARALLEL_INDEX(table, [index], [degree])

NOPARALLEL

Session level settings ALTER SESSION ENABLE PARALLEL/DISABLE PARALLEL
System level initialization parameters PARALLEL_[*] parameters

Once the decision is made however by Oracle, visually, this is how serial vs. parallel execution looks. This image was shamelessly taken from the same nice parallel execution coverage mentioned before to illustrate the behavior (nice illustration).

full_table_scan_serial_vs_parallel

** Image from http://www.tyson1.com/professional/parallel/default.htm

Parallelism is not always the panacea for performance – Key Considerations:

  • Generally, the more the parallel execution, the more the PGA usage and hence overall memory usage
  • Parallel “Fast full index scans” use PGA
  • Parallel “Full index” and “range scans” use SGA
  • Parallel “Full table scans” use PGA.
  • …alternatively put, “PGA based caching results in re-read of data from disk every single time.”
  • Data cached in PGA by a process is not available to another process.
  • Both parallel and serial execution can be forced/coerced for queries at table/index level, query level, session level & system level
  • Oracle can downgrade parallel operations on the fly, mid-way
  • Generous hand-out of parallel sessions at the beginning of time when instance is idle will hurt as system becomes busier
  • Hidden parameter _small_table_threshold decides when parallel sessions use SGA instead of PGA (do not fiddle with this)
  • Parallel settings at the different levels have precedence. E.g., a NOPARALLEL table in a query can still be parallelized with PARALLEL hint assuming PARALLELISM is turned on at the system level. Always test.
  • ….parallelism is not always the panacea for performance

Limiting PGA by user/session

There is no fine-grained control as of Oracle 11g. I am not sure about 12c.

One of the nice things about a superior database like Oracle is the amount of knobs available to tweak just about anything although it takes a lot to understand each knob. In the case of PGA memory however, there are no knobs that would help prevent an user/session from exhausting all the PGA memory as of 11g. Lately we have been running into this error due to bad queries (missing joins, no limiting conditions, excessive sorts etc.) that got parallelized resulting in excessive PGA usage. Even a knowledgeable developer can unwittingly exhaust PGA using in-memory collections excessively.

“ORA-01034: ORACLE not available / ORA-27102: out of memory / Linux-x86_64 Error: 12: Cannot allocate”

The above error is what anyone (including privileged logins) gets when trying to login. Serial execution will not cure bad queries but it can limit memory problems when used under certain conditions.

Parallelism can be enabled in several ways but also be disabled in several ways. When to do which is a balanced decision for your to make.

When & When Not To Use Parallelism?

In a Data Store or Data Warehouse, it is imperative to take full advantage of parallel execution, generally with no-negative side effects especially when there is enough CPU and memory capacity to do so.

Excessive Parallelism can really hurt – bad:

There are however instances (based on the database configuration) where too much of a good thing can turn bad. When parallelized, bad queries burning through PGA and hence the overall memory is one of those situations leading to errors like the ones above. Excessive I/O is another negative side-effect.

  • For tables that hold statuses or lookup information that can benefit from being shared by multiple sessions, disable parallelism at table level using NOPARALLEL hint
  • For queries that non-technical users run where conditions can be forgotten resulting in query going against the whole universe of data and hence getting parallelized, use a NO_PARALLEL query hint
  • For novice users who are just getting their feet wet in SQL in a Data Warehouse/ODS database, disable parallelism at session level
  • In general, if the amount of data fetched from disk for a query is small in comparison the amount of memory available, it can benefit from being serial especially if the data in the query can be re-used by other sessions
  • Forcing parallelism to bring a query down from 17.5 seconds to 15.2 seconds may not be a good idea.
  • Always look at your query’s explain plan
  • After deploying code/queries, monitor parallel performance at query/system level

Save the PARALLEL option as your last-resort tuning option and think what happens if everyone had the same idea (to parallelize the hell out of everything)! Even though Oracle automatically downgrades the parallelism based on load, it can still hurt.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s