Re: Doing large sort in RAM - sort workarea manipulation
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 oneprocess
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