Friday, August 05, 2011

Hash Group by exceed temporary tablespace

Tuned a "HASH GROUP BY" after increase the pga_aggregate_target from 190MB to 2GB , since workload increased over the time.

Feeling the data volume in below plan :

- massive SUMs
- 52 milliows row,
- 5 month's data.
PERFSTAT@ABC>@disp_cursor_plan                                                                                             
Enter value for sqlid: 885gs8h6jjxzc                                                                                         
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('885gs8h6jjxzc'))

SQL_ID  885gs8h6jjxzc, child number 0
-------------------------------------
       ,sum(CASE period WHENesets
to_date('201107','YYYYMM') THEN (case hourofday when 0 then mou else 0 end) ELSE 0
       ,sum(case hourofday when 0 then mou else 0 end) as
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case
hourofday when 0 then no_call else 0 end) ELSE 0 END) AS nocall1m_0001
,sum(case hourofday when 0 then no_call else 0 end) as nocall6m_0001
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case hourofday when 1 then
       ,sum(case hourofday when 1 then102
       ,sum(CASE period WHEN2
to_date('201107','YYYYMM') THEN (case hourofday when 1 then no_call else 0 end)
       ,sum(case hourofday when 1 then no_call else 0
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN
(case hourofday when 2 then mou else 0 end) ELSE 0 END) AS mou1m_0203

Plan hash value: 2842556147

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |       |       |   263K(100)|          |
|   1 |  HASH GROUP BY     |                       |    52M|  3929M|   263K  (8)| 00:52:48 |
|*  2 |   TABLE ACCESS FULL| DM_TRAN_LOCALOUTGOING |    52M|  3929M|   253K  (4)| 00:50:43 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CALL_TYP"='VOICE' AND "HOUROFDAY">=0 AND "HOUROFDAY"<=23 AND
              "PERIOD"<=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "PERIOD">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement


37 rows selected.


Hash group (and hash joins, as well as other operations such as sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. The last two methods use TEMP storage and is thus much slower.
By increasing the number of possible items you might have exceeded the number of items that will fit in memory reserved for this type of operations.
Try looking at v$sql_workarea_active whilst the query is running, to see if this is the case. Or look at v$sql_workarea for historical information. It will also give you an indication of how much memory and/or temp space is needed for the operation.
If turns out to be the actual problem - try increasing the pga_aggregate_target initialization parameter, if possible. The amount of memory available for optimal hash/sort operations is usually around a 5% fraction of the pga_aggregate_target.


Hash Join

   The hash join is used for high-volume equi-joins (joins with equals predicates). Oracle performs a single read of the smaller row source (call this T1) and builds a hash table in memory. The join key is used as the hash-key of the hash table. Then a single pass of the larger row source (call this T2) is performed, hashing the join key of each row to obtain an address in the hash table where it will find matching T1 rows.
  Provided T1 remains small enough to build the hash table in memory, T2 can be scaled up to any arbitrarily large volume without affecting throughput or exceeding temp space. If T1 cannot be hashed in memory, then a portion of the hash-table spills to disk. When the hash table is probed by T2, the rows with join keys that match those parts of the in-memory hash table are joined immediately; the rest are written to TEMP and joined in a second pass. The bigger T1 is, the smaller the proportion of the hash table that can fit in memory, and the larger the proportion of T2 that must be scanned twice. This slows the Hash Join down considerably and also makes the join non-scalable.

For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.

 Indexed Nested Loops is used primarily in low volume joins; it is efficient over small volumes and versatile enough to be used in a variety of situations. Although it is fully scalable, Indexed Nested Loops is inefficient over large data volumes.

2.8 millions  rows in driving table looks up a larger table still should be regarded as inefficient. I had once case which is unable to complete running for 12 hours. Solved by using hash join plus partition pruning. 

Sort-Merge

A sort-merge join works by reading each row-source in the join separately; sorting both sets of results on the join column(s); then concurrently working through the two lists, joining the rows with matching keys. Sort-Merge is generally faster than Indexed Nested Loops but slower than Hash Join for equi-joins. It is used almost exclusively for non-equi joins (>, <, BETWEEN) and will occasionally be used when one of the row sources is pre-sorted (eg. a GROUP BY inline view)
If both row sources are small then they may both be sorted in memory, however large sorts will spill to disk making then non-scalable.
There is no way to make a Sort-Merge join scalable. The only other way to resolve a non-equijoin is to use Nested Loops, which is slower. As volumes increase, Sort-Merge will continue to out-perform Nested Loops, but will eventually run out of Temp space. The only solution is to extend TEMP, or convert the join to Nested Loops (and then wait).


 also   read
http://www.dbspecialists.com/files/presentations/temp_space.html
http://stackoverflow.com/questions/154722/can-anyone-explain-how-the-oracle-hash-group-works 
Posted by at
Labels: