Re: Doing large sort in RAM - sort workarea manipulation

From: GG <grzegorzof_at_interia.pl>
Date: Sat, 12 Nov 2011 17:35:14 +0100
Message-ID: <4EBEA042.3080800_at_interia.pl>



On 11/12/11 16:45, Greg Rahn wrote:
> If you need to allocate more pga... then parallel execution will
> probably be your friend.  PX lets you address more memory for the sort
> by scaling it out.  At that point you just need to have a large enough
> pga_aggregate_target and large enough DOP to keep the operation from
> spilling.
>

So, You are talking about something like that : SQL> select * from v$version;

BANNER



Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production

SQL> _at_desc sort_px

           Name                            Null?    Type
           ------------------------------- --------

----------------------------
1 ID NUMBER 2 FILLER VARCHAR2(100)

--ID is from rownum (unique numbers)
--filler is rpad('X', 100 , 'X') for every column

SQL> select count(*) from sort_px partition(p2);

  COUNT(*)


   1000000

1 row selected.

SQL> select count(*) from sort_px partition(p1);

  COUNT(*)


   1000000

1 row selected.

--table with 2 partitions , partitioned range by id 1M rows each
--set paralle =2

select * from sort_px order by id ;

SQL> _at_dplan_allstats
Enter value for sql_id: f71m6ht0j5cd8
Enter value for child_no: 0

PLAN_TABLE_OUTPUT



SQL_ID f71m6ht0j5cd8, child number 0

select * from sort_px order by id

Plan hash value: 3188984921



| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M | Max-Tmp |
|   0 | SELECT STATEMENT        |          |        |       |      
|          |         |
|   1 |  PX COORDINATOR         |          |        |       |      
|          |         |
|   2 |   PX SEND QC (ORDER)    | :TQ10001 |   1730K|       |      
|          |         |
|   3 |    SORT ORDER BY        |          |   1730K|   493M| 
7317K|     2/2/0|     220K|
|   4 |     PX RECEIVE          |          |   1730K|       |      
|          |         |
|   5 |      PX SEND RANGE      | :TQ10000 |   1730K|       |      
|          |         |
|   6 |       PX BLOCK ITERATOR |          |   1730K|       |      
|          |         |
|*  7 |        TABLE ACCESS FULL| SORT_PX  |   1730K|       |      
|          |         |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   7 - access(:Z>=:Z AND :Z<=:Z)

Note


  • dynamic sampling used for this statement
  • Warning: basic plan statistics not available. These are only collected when:
    • hint 'gather_plan_statistics' is used for the statement or
    • parameter 'statistics_level' is set to 'ALL', at session or system level
_smm_min_size                                
524                            minimum work area size in auto mode
_smm_max_size                                
102400                         maximum work area size in auto mode (serial)
_smm_px_max_size                             
262144                         maximum work area size in auto mode (global)

pga_aggregate_target                         
536870912                      Target size for the aggregate PGA memory
consumed by
                                                                            

the instance

_pga_max_size                                
209715200                      Maximum size of the PGA memory for one
process

sort is still on disk I'm affraid .
How large PGA do I need for that test case ? Secondly I want to try no sort aproach by using index on ID but filed miserably

SQL> select /*+ FIRST_ROWS */ * from sort_px order by id ;

2000000 rows selected.

Execution Plan



Plan hash value: 3188984921
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1730K| 107M| | 18764 (1)| 00:03:46 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 1730K| 107M| | 18764 (1)| 00:03:46 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 1730K| 107M| 126M| 18764 (1)| 00:03:46 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,00 | P->P | RANGE | | 6 | PX BLOCK ITERATOR | | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| SORT_PX | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------

Note


  • dynamic sampling used for this statement

Statistics


        256  recursive calls
         10  db block gets
      31173  consistent gets
      59088  physical reads
        672  redo size
   21702331  bytes sent via SQL*Net to client
      44481  bytes received via SQL*Net from client
       4001  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)

    2000000 rows processed

SQL> select /*+ FIRST_ROWS(1) */ * from sort_px order by id ;

2000000 rows selected.

Execution Plan



Plan hash value: 3188984921
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1730K| 107M| | 18764 (1)| 00:03:46 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 1730K| 107M| | 18764 (1)| 00:03:46 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 1730K| 107M| 126M| 18764 (1)| 00:03:46 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,00 | P->P | RANGE | | 6 | PX BLOCK ITERATOR | | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| SORT_PX | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------

Note


  • dynamic sampling used for this statement

Statistics


        259  recursive calls
          9  db block gets
      31250  consistent gets
      59089  physical reads
        628  redo size
   21702331  bytes sent via SQL*Net to client
      44481  bytes received via SQL*Net from client
       4001  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)

    2000000 rows processed

SQL> select /*+ INDEX(t idx_id) */ * from sort_px t order by id ;

2000000 rows selected.

Execution Plan



Plan hash value: 3188984921
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1730K| 107M| | 18764 (1)| 00:03:46 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 1730K| 107M| | 18764 (1)| 00:03:46 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 1730K| 107M| 126M| 18764 (1)| 00:03:46 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 1730K| 107M| | 3819 (1)| 00:00:46 | | | Q1,00 | P->P | RANGE | | 6 | PX BLOCK ITERATOR | | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| SORT_PX | 1730K| 107M| | 3819 (1)| 00:00:46 | 1 | 2 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------

Note


  • dynamic sampling used for this statement

Statistics


        257  recursive calls
          9  db block gets
      31173  consistent gets
      59088  physical reads
        672  redo size
   21702331  bytes sent via SQL*Net to client
      44481  bytes received via SQL*Net from client
       4001  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)

    2000000 rows processed

Any ideas ?
GregG



Masz strone www? Dodaj ja do katalogu!
Sprawdz >> http://linkint.pl/f2a8a
--

http://www.freelists.org/webpage/oracle-l Received on Sat Nov 12 2011 - 10:35:14 CST

Original text of this message