Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Wednesday, August 07, 2013

DISPATCH the DISPATCHERS Parameter!

As you can tell from the title I have little use of the DISPATCHERS parameter. In the default init.ora provided by Oracle DBCA this value is set, so. Many people leave it set thinking Oracle is saying it is a good idea or best practice to have it set. From the Oracle11g manual:

“Configure the DISPATCHERS parameter if either of the following conditions apply:
You need to configure another protocol other than TCP/IP
Configure a protocol address with one of the following attributes:
  • ADDRESS (ADD or ADDR)
  • DESCRIPTION (DES or DESC)
  • PROTOCOL (PRO or PROT)
You want to configure one or more of the optional dispatcher attributes
  • CONNECTIONS (CON or CONN)
  • DISPATCHERS (DIS or DISP)
  • LISTENER (LIS or LIST)
  • MULTIPLEX (MUL or MULT)
  • POOL (POO)
  • SERVICE (SER or SERV)
  • SESSIONS (SES or SESS)
  • TICKS (TIC or TICK)”

Obviously, the second option is only when you have the DISPATCHERS already set. Most of the time I see DISPATCHERS set to configure the XDB protocol, for example:

dispatchers
(PROTOCOL=TCP) (SERVICE=acmedb1XDB)

Now, the question is, when do you need the XDB protocol? Going again to the Oracle11g
Manual:

“Note:
If you create your Oracle database with Database Configuration Assistant (DBCA), DBCA configures a dispatcher for Oracle XML DB (XDB). This is because XDB protocols like HTTP and FTP require shared server. This results in a SHARED_SERVER value of 1. Although shared server is enabled, this configuration permits only sessions that connect to the XDB service to use shared server. To enable shared server for regular database sessions (for submitting SQL statements), you must add an additional dispatcher configuration, or replace the existing configuration with one that is not specific to XDB. See "Configuring Dispatchers" for instructions.”

So, whether you need it or not, DBCA sets it up, even if you tell it you don’t want shared servers initialized. You will only use XDB if you are doing XML, FTP or HTTP transfers in and out of your database or if you are using APEX you will need the XDB protocol. Now, if you use XDB protocols and need DISPATCHERS set that way, then go no further, you are set correctly (however, you may want to see what this does to your system, so read on!)

So you are probably thinking: “Big deal, so DISPATCHERS is set, so what?” I mean, compared to the myriad other things Oracle creates and runs in the background on our behalf this seems a bit mild, right? Actually, no, let me explain.

Setting DISPATCHERS or setting SHARED_SERVERS turns on shared servers in Oracle. Shared servers are a means to do connection pooling in Oracle with SHARED_SERVERS configuring how many shared processes to have and DISPATCHERS setting up the master DISPATCHER processes which then serves out the connections to the connection processes setup with SHARED_SERVERS. So, if no one connects to the XDB process, shared servers are never used, so what is the big deal?

Well, shared server expects connections to be shared; certain types of shared connections must share memory areas. One example is that some types of memory sharing in PL/SQL will move form the shared pool to the large pool, this can improve PL/SQL performance form some operations, in version 9 of Oracle it was a way to prevent certain out of memory operation from happening in the SQL pool as a result of PL/.SQL. Shared servers also may require processes to share temporary segments.

This is were the rubber meets the road, when the processes must share memory areas (for example temporary segments) then those areas cannot automatically be controlled by automatic memory management and won’t fall under PGA_AGGREGATE_TARGET, MEMORY_MAX_TARGET or SGA_MAX_SIZE  or their related parameters. Processes such as parallel query slaves and RMAN backup processes fall under this set of processes as do some parallel back ground and DDL statements. In addition, if you have STAR_TRANSFORMATION set to TRUE and DISPATCHERS set, then CREATE_BITMAP_AREA_SIZE and BITMAP_MERGE_AREA_SIZE also fall out from under automatic controls.

So, what did I just say? Essentially DISPATCHERS turns off PGA_AGGREGATE_TARGET control for temporary segments for certain parallel and background processes and removes bitmap temporary sizing parameters from AMM control. Under this situation, you must use manual sort/hash area control using the SORT_AREA_SIZE parameter. In the Oracle11g 11.2 manual about SORT_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, BITMAP_MERGE_AREA_SIZE:

“Note:
Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility.
Note:
Oracle does not recommend using the CREATE_BITMAP_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. CREATE_BITMAP_AREA_SIZE is retained for backward compatibility.

Note:
Oracle does not recommend using the BITMAP_MERGE_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. BITMAP_MERGE_AREA_SIZE is retained for backward compatibility.”

And, so far, these are the only places this is mentioned, other than HASH_AREA_SIZE, but since HASH_AREA_SIZE is derived from the sessions SORT_AREA_SIZE I am assuming that if we set SORT_AREA_SIZE, HASH_AREA_SIZE will follow, but that could be a bad assumption.

So how can you tell if this is happening in your system? First, you will get a load of sorts and temporary activities between 64K and 512 mb on your system. Check out the AWR PGA Aggregate Target histogram to see this. Why do I say that? Well, according to the released algorithm details of how PGA_AGGREGATE_TARGET works it uses the undocumented parameter ” _PGA_MAX_SIZE” to determine how much temporary area to give to specific user up to 5% of the total PGA_AGGREGATE_TARGET setting. Now on some systems this defaults to 256 megabytes and on others to 512 megabytes. So in the most current releases the 512 megabyte setting is the default so PGA_AGGREGATE_TARGET should handle most sorts and temporary actions at less than 512 megabytes without resorting to going to disk. The reason for 64k being the minimum is that is the setting for SORT_AREA_SIZE if you haven’t reset it so up to 64K the default SORT_AREA_SIZE should handle any out-of-band sort requests. Here is an example PGA Aggregate Target Histogram showing out-of-band sorts.




PGA Aggr Target Histogram

  • Optimal Executions are purely in-memory operations
Low Optimal
High Optimal
Total Execs
Optimal Execs
1-Pass Execs
M-Pass Execs
2K
4K
2,897,891
2,897,891
0
0
64K
128K
8,659
8,659
0
0
128K
256K
5,303
5,303
0
0
256K
512K
208,918
208,918
0
0
512K
1024K
5,130,286
5,130,286
0
0
1M
2M
350,877
350,877
0
0
2M
4M
6,992
6,904
88
0
4M
8M
2,686
2,632
54
0
8M
16M
4,047
4,022
25
0
16M
32M
614
588
26
0
32M
64M
1,166
1,160
6
0
64M
128M
394
381
13
0
128M
256M
227
215
12
0
256M
512M
153
149
4
0
512M
1024M
35
12
22
1
2G
4G
2
0
2
0

As you can see from the above chart only 24 out of 253 of the temporary executions-to-disk were greater than 512 MB and none were lower than 64 KB. This could indicate out-of-band sorts if the PGA_AGGREGATE_TARGET is set to a sufficient size and DISPATCHERS or SHARED_SERVERS is turned on.

Temporary space requests between 64K and 512MB that generate actual disk IO activity are called (at least by me) out-of-band sorts. The best way to clean these up is to turn off the DISPATCHERS parameter if you aren’t using XML DB and XDB related protocols. Also make sure SHARED_SERVERS isn’t set. If you can’t turn off DISPATCHERS because you are using the XDB service it creates, then set SORT_AREA_SIZE to 512MB.

When should you consider turning off DISPATCHERS?
  1. You are running a 3rd party application which makes no use of HTTP, FTP or APEX
  2. You are running an in house application that makes no use of HTTP, FTP or APEX
  3. You aren’t using XML, HTTP, FTP or APEX in your application.

One thing to remember, if you are only using XDB services for batch processing or only for intermittent connections you can actually set DISPATCHERS dynamically and turn it on and off at will. Leaving DISPATCHERS set 24x7 when you only need it for a 10 minute window at night for a batch process can cause many issues as shown above. Consider only turning on DISPATCHERS or SHARED_SERVERS when you need it.