Re: Oracle 9.2 PGA_Aggregate_target and a statistics question

In article <I9hX8.1457$zX3.1244_at_news.indigo.ie>, "Telemachus" says...
>
>PGA_AGGREGATE_TARGET : wonderful ! at last !
>
>
>... however I have a question ...
>
>Correct me if I'm wrong
>
>In the following PGA_AGGREGATE_TARGET = PAT
>1. premise :
>SORT_AREA_SIZE and HASH_AREA_SIZE are base inputs to the query optimization
>process so it can figure out how many passes etc etc, which table can fit
>and so on .
>
>2. premise :
>if these are not now directly specified , then it must use some fraction or
>the whole of PAT(minus of course whatever else is hogging PAT from everyone
>else's queries - for simplicity let's call the available bytes delta-PAT)
>
>questions :
>Is all of delta-PAT considered as the base input to both of these parameters
>or does anyone know what the fraction is. ?
>My ev10053 reading skills have never been very good. The reason I ask is
>it's an interesting subtle change to the query optimization process, since
>now sort joins and hash joins have the potential to get a hell of a lot more
>memory and we may come 'right down to the wire' finally for the final
>decision.
>
>Thinking about it a bit more, then it wouldn't take all of delta-PAT at
>run-time, but only whatever was selected by the optimizer as the cheapest
>plan....
>
>there seem to be a fair few metalinks on the issue....

Here's something I sent out on a DBA Mail newsletter I run (sometimes!) that may clarify this one for you. The DBA Mail normally starts out with a tips and hints section in the format "here's my issue" followed by "here's my response to that issue":

Issue: Do I need to configure large value for pga_aggregate_target to avoid disk sorts? I'm testing the automatic PGA memory management feature in 9i. I executed the same SQL script in both manual and automatic mode. The SQL script did a sort operation as follows:

select o1.O_COMMENT
  from orders o1, orders o2
 where rownum < 11
 order by o1.O_COMMENT;

The orders table is in the TPC-d benchmark schema and contains 1500000 records. I monitored the size of memory allocated and sorts(disk) statistic in v$sysstat and execution mode(optimal or onepass). In manual mode, the SQL script required 20M - 30M for sort_area_size. The result is as follows:

sort_area_size sorts(disk)

20M            	1 
30M           	0 

In automatic mode, the results were:

ag_trgt  sorts(disk)   	executions  	glbl_bnd 
30M      1             	optimal     	1536 
100M     1             	optimal     	5120 
... 
400M     1             	optimal     	20480 
500M     0             	optimal     	25600 

"ag_trgt" is pga_aggregate_target and glbl_bnd is the "global memory bound" statistic in v$pgastat.

In the automatic mode, the SQL script executed in optimal mode but there was a sort to disk. To execute the script without disk sort, pga_aggregate_target needed to be configured to a very large size. Is this expected behavior?

Response: The sort(disk) statistic in v$sysstat should work like before and if the execution is optimal, the sort(disk) statistic should not be incremented. I will test this and file a bug if this is not the case. That said, you should use the new statistics added in v$sysstat to monitor how many workareas have been executed in optimal/one-pass and multi-pass mode. This include not only sort statistics but statistics for all other memory intensive SQL operators like hash-join, bitmap-merge and bitmap create. You can also use v$sql_workarea_active to monitor all SQL workareas currently active on your system and use v$sql_workarea (which can be joined with v$sql_plan and v$sql) to get statistics for all memory intensive operator of a given SQL cursor.

The "bizarre" behavior you are reporting in the way pga_aggregate_target is working is in fact an expected behavior. Let me try to explain why:

First, pga_aggregate_target is a very different parameter than sort_area_size. This parameter is only used to indirectly control how much PGA memory a sort operation will get when the instance is running under the new 9i automatic memory management mode.

Under this new mode, the DBA can use the pga_aggregate_target parameter to set a limit in the amount of PGA memory consumed by the instance. As far as SQL memory management is concerned, PGA memory can be classified into two categories:

• tunable memory allocated by memory intensive SQL operators like sort (e.g. big sorts are used for index creation and to run analyze), hash-join, group-by, bitmap merge and bitmap index create. We call this "tunable" memory because Oracle can control, up to a certain limit, the size of the workareas allocated by these operators. As a result, the total amount of tunable memory can be indirectly controlled and tuned by Oracle.
• untunable memory which is the rest of the PGA memory. For example, the untunable PGA memory is used to store context information for each session, for each open/active cursor. The size of the untunable portion of the PGA memory cannot be controlled by Oracle and depends mainly on the query workload.

Generally, the tunable portion of the PGA memory represents the biggest portion of the PGA memory unless the instance is running a pure OLTP workload without (or with very few) reporting/batch operations. When running under the new automatic memory management mode, Oracle wants to achieve two goals:

  1/ "untunable PGA memory" + "tunable PGA memory" <= pga_aggregate_target 2/ maximize the size of the tunable PGA memory such that we maximize the number of memory intensive operators which can run in optimal mode (e.g. in-memory for sort and hash-join operators).

Since the size of the untunable PGA memory cannot be controlled, goal 1 can only be achieved by Oracle assuming that "untunable PGA memory" < pga_aggregate_target. v$pgastat can be used to monitor the untunable memory requirement of the system.

As you can see, these two goals are somewhere opposite especially since the workload is generally not steady. In your example, only one sort is running and this sort needs 25MB to run optimal. Let's assume that you are running with a pga_aggregate_target set to 30MB and that the total PGA untunable memory requirement for the instance is less than 5MB (which is likely your case). In this case, Oracle could give 25MB to this only running sort (25M + 5M <= 30M) but you are right, it does not. As of now, the rule is that a single (serial) SQL operator cannot consume more than 5% of pga_aggregate_target (5% of 500M is 25MB) and a single SQL operator executed in parallel (e.g. parallel create index) cannot consume more than 30% of the PGA memory.

We need to impose these 5/30% limits to ensure that we will not violate our first goal. Still using your example, let's assume that 10 seconds after starting this first sort, you start concurrently several queries with sorts and hash-join operations. These other queries also need to get some PGA memory. Even though the internal algorithms used by each memory intensive SQL operators has been changed in 9i to dynamically release (and acquire) PGA memory based on the global memory pressure of the system, it is unlikely that we can adapt that fast: it is unlikely that the process running the first sort can release its PGA memory fast enough such that this memory is transferred on time to other SQL operators running by the instance.

Generally, it is rare for an Oracle database to run a single query at a time. Even when this is the case (e.g. big batch jobs), queries are (generally) running in parallel and/or several operators within the query are active simultaneously (e.g. several hash-joins and sorts). So most of the time, we will use all the PGA memory and what you are observing with your test might not be a big issue for real life databases. Also, 500MB of PGA memory is far from being excessive for real database systems. As you discovered, this gives a maximum of 25MB sort-area-size which is I believe more than reasonable. I would expect databases which are today setting sort_area_size to 25MB (or more) to use more than 500MB of PGA memory.

Pete
>
>
>
>Other things I like :
>storage mapping
>All the sampling and gather stats improvements (although the docs on the
>DYNAMIC SAMPLE hint are awful - and it would be a bad day if SAMPLE(10)
>level had to be used) , and auto/skewonly histograms are a top notch idea ,
>Remember, gang, ANALYZE is being deprecated .... DBMS_STATS is the way to go
>.
>
>
>T
>
>T.
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Thu Jul 11 2002 - 10:32:04 CDT