Re: Doing large sort in RAM - sort workarea manipulation
- From: GG <grzegorzof@xxxxxxxxxx>
- To: greg@xxxxxxxxxxxxxxxxxx
- Date: Sat, 12 Nov 2011 17:35:14 +0100
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> @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> @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
Other related posts: